1

I have an Excel file on SharePoint with data from an external data source (SQL Server). I want to setup an Office script to automate the refresh of the data and then trigger it via power automate. However, i am unable to get the data refreshed (even using the manual Refresh All button in Excel Online). It doesn't give any errors in the UI, shows as refreshing, but nothing changes.

I can refresh the data when i open in the Excel desktop app.

Any help on where should i check to get the data refreshed via Excel Online as well would be greatly appreciated?

TIA !

Dee
  • 199
  • 4
  • 17
  • 1
    Are you trying to refresh with PowerQuery? If so, PowerQuery refreshes are not currently supported in Excel Online. – Brian Gonzalez Mar 31 '22 at 20:20
  • @BrianGonzalez Thank you! I was suspecting that but hoped I might be wrong. Would there be an alternate way in which I can automate refreshed PowerQuery data connections ? – Dee Mar 31 '22 at 20:28
  • If you could get the data from the SQL Server query as JSON, you could get that data using `fetch` with office scripts. You'd probably want to talk to someone in your IT department about doing this. Once you had the data in JSON, you'd format it to a JavaScript array and then write it out to a range of cells, Excel table, etc. You can see an example of how that might work here: https://learn.microsoft.com/en-us/office/dev/scripts/resources/samples/external-fetch-calls – Brian Gonzalez Mar 31 '22 at 21:05
  • My suggestion would be to get the data via a PowerAutomate action and then pass it through to an office script for loading into the worksheet, or, see if you can load it into the workbook from PowerAutomate as well. There are multiple options. – Skin Apr 01 '22 at 01:02
  • 2
    Thanks all for the suggestions! i was able to achieve this using PowerAutomate Desktop and running a macro to refresh the excel files! thank yu so much cheers! – Dee Apr 06 '22 at 12:56

0 Answers0