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

[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-refreshAllD...

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.

[retrohead]

If you are trying to load data from a Power BI report this may work for you.

Create a 2nd Power BI report and import the data from the first report using a DAX import query.

AnalysisServices.Database("powerbi://api.powerbi.com/v1.0/myorg/WorkspaceName", "ReportName", [Query="EVALUATE tableName", Implementation="2.0"])

Link your Excel file to the new Power BI report instead.

This worked for me on a file I have had a lot of problems with in the past. I assume it works as Power BI is doing the calculations during the import of 2nd report leaving Excel to retrieve the data as it is.

Interested to hear if this works for others too.

[Yutao]

@retrohead thanks for the callout! Yes, as the Office Scripts documentation points out, at this moment refreshAllDataConnections only works when PowerBI is the source: Troubleshoot Office Scripts running in Power Automate - Office Scripts | Microsoft Learn.

I will amend my last reply to include this callout in case using PowerBI as source might help some of the folks.

[retrohead]

Thanks @Yutao To be clear this was my setup before that was not working:

Power BI -> Excel (Heavy DAX Calculations) -> Power Automate

This is the setup now

Power BI -> Power BI (Heavy DAX Calculations) -> Excel -> Power Automate

The exact same data file is now refreshing correctly, I just need to get the timing right on my refresh intervals.

Interested to see if others can get it working by doing this too. That may mean the DAX calculations is the part causing the problem here.

[TravisCB1]

This is great if I were using DAX or anything that needed power BI. I hope it helps others that find this post.  (this sounds snarky. I can't help it. it's not. )
I am just trying to refresh a connection to a CSV at our data center, on a data sheet with some real basic calculations.  
I have three of these.  There is a CSV that is posted after hours.  And my little reporting sheets on Onedrive are connected with Power Query and until recently ran a refresh all script at 5, 6 and 7 am.  It was glorious and I miss the good times.  

but ya.  They are not going to give me PowerBI.  😞 

[Yutao]

@TravisCB1 Sorry to hear the frustration. I can totally understand. I also hope we can get it working soon so we would have many happy customers instead of annoyed ones...

So it seemed the refreshAllDataConnections API indeed had worked for you previously, right? I may need to double check but I always thought this API should have never worked unless you are using Power BI. Although it's possible that I might have mixed it up with some other scenarios.

[TravisCB1]

@Yutao Here is what was set up before. 
1. There is an excel macro with "refresh all" as its first step saved to a scripts folder on onedrive. 

2. The excel spreadsheets are saved in a Sharepoint folder.

3. A power automate script runs the macro on the excel spreadsheet on Mondays. 

The result was it would run the refresh, then a series of copy and paste actions, save an archive, then email a link to a group. 

Since this started, it runs, does not refresh all, then does all the copy pastes, makes a mess of the data, archives the mess then emails the mess to the group. 

Best Monday ever the first time... Better Monday the second time. I have since paused the script. I come in at 5AM on Mondays now and run the script.  As long as I open the file its fine.  the refresh happens, the copy pastes, everything.  It's just when power automate runs it. 

If you need more details, please feel free to ask, I will help any way I can. 

[TravisCB1]

Any word? I have a meeting coming up, and even if there is an update of "no updates" that helps me. 

[Yutao]

We have been making progress but unfortunately ran into a blocker and are still working on it. So sorry no we are not there yet but getting closer now.

[TravisCB1]

That is better news than I was expecting.  Thank you. 

[javgregorio]

Hi, 

Anyone solve this challenge?

[TravisCB1]

Doesn't look like it. Feels a little like they are planning on just letting it go.  😞
I gave up completely. I still couldn't get them to get me powerBi.  I am manually coming in early to update every Monday, while they look into other vendors for reporting. 😞

[rwittels]

I am posting this here because my issue is relevant to this.

I have a script in my Excel file which uses workbook.refreshAllDataConnections(). If I run the script with the file open, it gets all my data so I know that the script works. When I use "Run script" in a basic Automate flow, the flow runs successfully but when I open my file, there is no new data. I am using the same user account everywhere and there are no errors.

Is this Automate feature still just broken?

@Yutao- Any update?

[TravisCB1]

Still broken and I doubt it will ever be fixed. I have gone back to doing everything manually and requested that replace MS for this kind of work.

[joachimmoens]

For me it does work again. You might want to check your authentication settings or reducing the file size as I mentioned in my previous comments.

[gwlfe]

This is NOT working for me - I have checked authentication settings and my file sizes are small (under 100k).  Waiting on an update from Microsoft on re-enabling this functionality.

[rwittels]

This article explains the limitations:
Troubleshoot Office Scripts running in Power Automate 

[TravisCB1]

Well after months, it seems you found the answer. 😞

[MatHeHO]

Hi @rwittels, I'm currently encountering the exact same issue. Have you managed to overcome it?

[TravisCB1]

Our company gave up. We are doing it manually until we find a new solution that is not Microsoft.

[rwittels]

Hi. See my post above. It is only supported when PowerBI is the data source.