I am not sure if this is the proper area for this question, if not please kindly point me in the right direction. Further,
I want to start by saying that I'm minimally familiar with PowerBI Pro, and less so with the PowerBI On Premises Data Gateway. We have a user who has designed a dashboard in PowerBI Pro, using an Excel file as the data source for the dashboard. The Excel file is in turn updated via queries on a SQL database in our systems.
For some time this user has been manually updating the spreadsheet in order to display the most current data on the dashboard. Our intent is to automate this process as much as possible. In that effort, I am running a small script on a server system in our environment that opens, updates, and then closes the spreadsheet using the queries that are stored within the spreadsheet.
Our hope is then to be able to leverage the On Premises Data Gateway to then update the PowerBI dashboard automatically. We have the gateway installed on the same server where the spreadsheet resides. However, we've not gotten much further. I would like to know if the gateway is intended to be used in this way, and if so, any advice on how to connect the spreadsheet to the gateway would be most welcome. I do not have PowerBI installed on the server, but if that is required I can do that as well.
I sincerely appreciate any help, and thanks for entertaining a question as basic as this.