Unanswered

Refreshing Links, Power Query and OLAP cube connections via Power Automate

(0) Share

Report

Posted on by GP-09101400-0

Hi everyone,

I'm trying to build a cloud Power Automate flow that can automatically update or refresh the following elements in an Excel workbook stored in SharePoint:

External Excel links (e.g. formulas referencing other workbooks)

Power Query connections (e.g. data pulled from other sources)

OLAP connections (e.g. connections to an SSAS cube)

The goal is to ensure that all data sources are refreshed and up to date before the file is used in downstream processes.

Has anyone successfully implemented this kind of automation?

Any guidance, examples, or references would be greatly appreciated!

Thanks in advance,

George

Categories:

Building flows

I have the same question (0)

All responses (2)

Answers (0)

Sort by

Michael E. Gernaey 53,963 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi @GP-09101400-0

To do this you will need to build an Office Excel Script, as power automate directly cannot do it.

Then you would schedule your flow to run (however often you want)

Then in the flow you would Call the Script

The script would update what you asked about.

The flow is super simple but from an office scripting side, you can do things like

function main(workbook: ExcelScript.Workbook) {

myWorkbook.refreshAllDataConnections();

}

As an example of how to refresh your data connections.

Now you just need to find Office Excel Script examples to update what you want and use this

Run Office Scripts with Power Automate - Office Scripts | Microsoft Learn

cheers

If these suggestions help resolve your issue, Please consider Marking the answer as such and also maybe a like.

Thank you!
Sincerely, Michael Gernaey

Was this reply helpful? Yes No
GP-09101400-0 4 on at

Like (0)

Report
Copy link

Link copied!

Thanks @Michael E. Gernaey,

I've tried running the script, and it works when i call it inside the excel file. However, when i call it from a power automate flow it doesn't seem to work. I don't get any errors from the flow itself, but when i look at the code view of the outputs the body is empty and when i check the excel file after the flow has run it has not updated.

I've only tested this for linked workbooks at the moment. I've scripted a find and replace to update filepaths and then used your code to refresh the excel. When i open the workbook the find and replace has worked and the filepaths have updated, but the cells are showing as #Blocked until i refresh the links manually. The script works fine when called inside the excel file but the Power Automate flow doesn't seem to refresh the data.

Thanks,

George

Was this reply helpful? Yes No