Power Platform Community Forum Thread Details

Hello everyone,

I'm seeking clarification on the feasibility and reliability of automatically refreshing data connections (sourced from Power BI ) used within Excel Pivot Tables.

My Setup: My Excel workbooks are stored on SharePoint. Each workbook contains multiple sheets and several Pivot Tables. The data source for these Pivot Tables is a Power BI dataset

My Current Automation Attempt: I've implemented a scheduled Power Automate Cloud flow that triggers an Office Script for each relevant Excel file.

This refresh is initiated by an Office Script executed as part of a Power Automate Cloud flow.

Here is the Office Script I am using:

function main(workbook: ExcelScript.Workbook) {
workbook.refreshAllDataConnections(); // Refreshes external data connections

// Optional: Refreshes all pivot tables
let pivoTables = workbook.getPivotTables();
pivoTables.forEach(pivot => {
pivot.refresh();
});

// Optional: Saves the workbook (Excel Online often does this automatically after a script)
// workbook.save();
}

My Problem: Despite employing workbook.refreshAllDataConnections() and pivot.refresh(), I've observed that the data in my Pivot Tables does not reliably update, even though both my Power Automate flow and the script execution are reported as "Succeeded."

I am uncertain if this approach (Office Scripts via Power Automate Cloud) is the correct one

Is this method generally considered a reliable approach for automating Excel data refreshes from Power BI?

Why might this method not always work, especially on larger files with multiple sheets and Pivot Tables? Are there specific performance limitations or timeouts in executing Office Scripts via Power Automate Cloud that could prevent a complete refresh for more complex files?

Any help, shared experiences, or suggestions would be greatly appreciated.

Thank you in advance for your time.

Categories:

Building flows