Excel Scripts "Refresh All" function not working for Excel Online

Question

Excel Scripts "Refresh All" function not working for Excel Online

Keiran 0

I have recorded a very basic Office script, as below: function main(workbook: ExcelScript.Workbook) {

// Refresh all data connections

workbook.refreshAllDataConnections();

}

It refuses to refresh the Power Query data that is coming from 2 tables within the same workbook.

It works perfectly on the desktop version of Excel, but not on the Online version. When i manually select "Refresh All" on Excel Online, it works perfectly fine. What gives? I thought the whole point of Office scripts was to allow it to work online?

Anyone have any guidance?

Alan Ramos 0 Reputation points

2025-01-24T14:44:24.1766667+00:00

Hi Keiran if you want to run script to refresh your connections in an Excel File I discover that with a connection to get data from Power BI Datasets the script works, why? cause these connections are with a standard text request using DAX Query instead of Power Query transformations to clean your data, but not all time works it will depend in what complex is your query in the connection. that's the only way I found the script works

3 answers

Your answer

Alan Ramos 0 Reputation points

2025-01-24T14:44:24.1766667+00:00

Hi Keiran if you want to run script to refresh your connections in an Excel File I discover that with a connection to get data from Power BI Datasets the script works, why? cause these connections are with a standard text request using DAX Query instead of Power Query transformations to clean your data, but not all time works it will depend in what complex is your query in the connection. that's the only way I found the script works

Answer 1

Jiajing Hua 18,175 Moderator

Hi @KeiranDo you refresh this workbook with the Office Script to update the Queries and reuse the Query in Power Automate?

Please note, refresh not fully supported in Power Automate. Office Scripts can't refresh most data when run in Power Automate, Workbook.refreshAllDataConnections only refreshes when PowerBI is the source.

For more, please refer to this link: https://learn.microsoft.com/en-us/office/dev/scripts/testing/power-automate-troubleshooting#refresh-not-fully-supported-in-power-automate

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Keiran 0 Reputation points

2024-10-10T23:49:43.8766667+00:00

So i have two tables within the Excel workbook.

I then use power query to transform and merge the data. This then create a new tab, with the output table that is linked to the Power Query process.

I i manually click on the "Refresh All" on the data tab, it works fine.

However when i record an Office Script - which is just me clicking on the "Refresh All" button, and then try to run that Script, it says it has successfully run the script, but the table output that is linked to a power query process does not update.

I've tried different scripts, where i get errors saying that Office Scripts does not support that script, however, i'm not getting that error.

Here is a Loom link showing exactly what is wrong - https://www.loom.com/share/a2807914b0b148c4a5b39d9f48f638b8?sid=38280baa-d3a9-4aa3-b6e4-f062dd179049

Answer 2

Fernando Gois 30

Is the "Refresh All" still not working when against a Power Query?

0 comments

Answer 3

Anjuri, Pavan Sai Kumar 0

Have you found any solution for this issue?

0 comments

Share via

Excel Scripts "Refresh All" function not working for Excel Online

3 answers

Your answer