Running office script with Workbook.refreshAllDataConnections() does not work when called from Power Automate

(0) Share

Report

Posted on by joachimmoens

Hello,

I use Power automate to refresh an excel table that is connected to Power BI every day.

This is via a 'Run Script' prompt that refers to a script containing Workbook.refreshAllDataConnections().

As of wednesday may 10th I receive the below error message:

Office JS error: Line 4: Workbook refreshAllDataConnections: There was an internal error while processing the request. clientRequestId: a7b48e93-4b3b-4db6-93a2-56378d34dd67

-> the script does work within excel (or excel online) itself.

I had priorly had a similar problem as now. This was then resolved, but I think the issue has come back.

https://powerusers.microsoft.com/t5/Using-Connectors/Running-office-script-with-Workbook-refreshAllDataConnections/m-p/2117793#M18047

Do others have this issue as well?

EDIT:

AS OF MONDAY 15TH MAY I HAVE NO ERROR MESSAGE ANYMORE. HOWEVER THE REFRESH SIMPLY DOESN'T PERFORM A REFRESH. THE SCRIPT DOES WORK FINE WHEN IT IS NOT CALLED FROM POWER AUTOMATE.

Thank you for the help.

Categories:

Using connectors

I have the same question (2)

All responses (68)

Answers (1)

joachimmoens 48 on at

Like (0)

Report

I have now done some additional testing and have noticed the error only pertains if I want to refresh multiple connections at once. This was no issue before though.

Was this reply helpful? Yes No
joachimmoens 48 on at

Like (0)

Report

The 'refresAllDataConnections' is definitely not a stable script to call from Power Automate. Now I have no errors anymore in Power Automate, the script also works perfectly in excel online, but when called from Power Automate the script doesn't perform a refresh (without error message now).

Was this reply helpful? Yes No
Yutao Huang Microsoft Employee on at

Like (0)

Report

Hey @joachimmoens , sorry to hear you are running into issues using the refresh API in Office Scripts with Power Automate. Refreshing data connections is a complex task and there are some limitations and gaps that we are still trying to address. Thanks for your patience!

For the data connections you are trying to refresh, are they all Power BI connections?

Also, I took a look at the logs based on the client request ID you shared in your first post. I wasn't able to see the details, but the error seemed to be timeout. Do you know how long it would normally take your script to finish?

Was this reply helpful? Yes No
joachimmoens 48 on at

Like (0)

Report

Hey @Yutao, my entire flow in the past always ran in less than 7 minutes, including a 6 minute Delay. The troublesome script runs in 16 seconds. If I perform the script itself it normally takes less than 2 minutes for excel to do all necessary recalculating.
I did manage to have no errors anymore in the flow by not refreshing one of the queries triggered by the script anymore. However, despite not having errors, the script does not always perform the refresh somehow...

Thank you for your help!

Was this reply helpful? Yes No
retrohead 48 on at

Like (1)

Report

Same issue here however the refresh seems to work if the resulting Excel workbook is less than 5MB in size which is a conflict with the maximum file size of 25MB for the Excel Online (Business) connector.

Was this reply helpful? Yes No
joachimmoens 48 on at

Like (0)

Report

Downsizing my excel file to below 5MB unfortunately doesn't do the trick for me. The flow still runs without error message but doesn't perform the refresh.

Was this reply helpful? Yes No
retrohead 48 on at

Like (0)

Report

Your Office script has refresAllDataConnections or refresAllDataConnections() with parenthesis at the end?

I have seen a lot of comments online saying to remove the parenthesis but this is simply not true.

Was this reply helpful? Yes No
jimbolongo 2 on at

Like (0)

Report

Hi @joachimmoens - have you found a solution or a workaround?

Thanks!

Was this reply helpful? Yes No
joachimmoens 48 on at

Like (0)

Report

Unfortunately not

Was this reply helpful? Yes No
Verified answer

joachimmoens 48 on at

Like (0)

Report

The problem has solved itself by reducing the size of the file under 3MB

Was this reply helpful? Yes No