Workbook.refreshAllDataConnections() is not supported in Power Automate.

MMPB 55

Dear all, I have an excel sheet with an active connection which I am able to refresh to get the newest data.
Now I've got another PowerAutomate Flow which is working fine with a script inside this Sheet to send me via Teams numbers which I want to be notified about. Unfortunately it only works if I am updating the workbook manually via Data-Refresh all, or if I am executing the script inside The workbook via automate this code line works:
"workbook.refreshAllDataConnections();"

It is now also possible to test the flow in Powerautomate and get all data (without the refresh code line).
With the refresh Code line I get this error message:

We were unable to run the script. Please try again.
Runtime error: Line 7: Workbook.refreshAllDataConnections() is not supported in Power Automate.
clientRequestId: 0b8eaf2a-1429-430a-8ae6-21fcf4330213

How can I refresh the workbook via Power Automate?

Joachim 30 Reputation points

2023-04-20T09:09:40.63+00:00

I had the same issue and had also reported on it: https://powerusers.microsoft.com/t5/Using-Connectors/Running-office-script-with-Workbook-refreshAllDataConnections/m-p/2118970#M18064. @Jaywant Thorat Can you please let me know what "refresh workbook" action you are referring to? workbook.refreshAllDataConnections() is the only way that I know of to refresh a workbook from Power Automate.
Gerard Brown 0 Reputation points

2023-04-20T14:09:36.0133333+00:00

I'm having the same issue. workbook.refreshalldataconnections() was working just 2 weeks ago, so not sure what happened. I would also be interested in where @Jaywant Thorat found that solution since I can't in the actions either.
Alan Cussen 15 Reputation points

2023-04-20T17:33:08.2533333+00:00

I dropped the "()" and my flow ran successfully again.

So change from this: workbook.refreshAllDataConnections()

to this: workbook.refreshAllDataConnections
Ryan Bettilyon 45 Reputation points

2023-05-17T20:59:30.7+00:00
@Yutao Huang - MSFT I'm working on my first Power Automate Flow to refresh an Excel workbook, and came here to report that this still seems to be an issue. It's quite frustrating, as I'd imagine refreshing data in an Excel file is probably the most sought-after and basic type of Excel automation.

Here are the details:

Running the Flow that includes an Excel (Business) Run Script step with the Automation Script defined in the Excel file (which is hosted in SharePoint online):

function main(workbook: ExcelScript.Workbook) { // Refresh all data connections workbook.refreshAllDataConnections(); }

This results in the following Error Details:

We were unable to run the script. Please try again. Office JS error: Line 3: Workbook refreshAllDataConnections: There was an internal error while processing the request. clientRequestId: fd28ea8d-edbe-4c75-a6a4-2b6e3879c42b

Note that when I attempted the tip from @Alan Cussen above (Omitting () from the workbook.refreshAllDataConnections command, the error message does not trigger (implying a successful run of the script) but the data refresh actually does not occur (the workbook data remains stale as it was prior to the Flow running).

Can you please advise on when this will be fixed, or any workarounds for triggering a workbook Refresh All command via Power Automate? Surely there must be a way to reliably accomplish this very basic automation.

Thank you,

-Ryan
Rodolphe F 5 Reputation points

2024-04-04T15:38:08.8566667+00:00

April 2023 and one year has passed. April 2024, still no solutions?
I have the same issue with a script containing : workbook.refreshalldataconnections() > nothing happens. No message of any error but none of my queries in Power Query have been refreshed at all. I tried without (): still don't work.
And of course the "refresh workbook" step in Power Automate doesn't exist. Totally fake.

12 answers

Tyler Graham 1 Reputation point

2023-04-20T19:11:35.3633333+00:00

I don't have an answer for this, but a possible workaround. I just changed my Run Script action from the now, apparently, defunct Refresh script to a script that simply changes the Worksheet's name. If whatever you're doing doesn't require a specific worksheet name, you can do this to force Power Automate to read the most recent values in your spreadsheet. Works for me in other flows where I previously had to run a 5 minute delay action to get Power Automate to recognize the new table.
Please sign in to rate this answer.
Bolsée Jamar de, Louis 10 Reputation points

2023-04-21T08:42:32.72+00:00

What does your script looks like? I'm wondering because if you rename the worksheet, the next time the flow runs, the name is different and won't work?

Tyler Graham 1 Reputation point

2023-04-21T15:07:58.62+00:00

It's just renaming the worksheet, not the workbook itself. In this example, I'm renaming the sheet from Page1 to Page2.

function main(workbook: ExcelScript.Workbook) { let page1 = workbook.getWorksheet("Page1"); // Rename worksheet to "Page2" page1.setName("Page2"); }
Sign in to comment
Dennis-2732 0 Reputation points

2023-09-27T08:19:54.3466667+00:00

Is there already a solution to the problem ?

Has there been an answer from the Microsoft team when the problem will be fixed?
Please sign in to rate this answer.

0 comments No comments
Sign in to comment
Carlos Renato Rodrigues 0 Reputation points

2023-09-27T13:07:11.95+00:00

Hello,

Is there an alternative to refresh only a specific sheet?

BR

Carlos
Please sign in to rate this answer.

0 comments No comments
Sign in to comment
Travis Webb 0 Reputation points

2023-09-27T14:17:54.1066667+00:00

This is the main reason I used PA and this has been a trainwreck for me.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment
Fabio Plos 0 Reputation points

2023-10-17T23:28:24.3+00:00
it seems that for now the only solution is:

Select Data > Queries & Connections > Connections tab, right click a query in the list, and then select Properties.

Click the Usage tab.

Select the Refresh every check box, and then enter the number of minutes between each refresh operation.

fyi 1440 minutes is one day

repeat for all connections
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Share via

Workbook.refreshAllDataConnections() is not supported in Power Automate.

12 answers