Silently refresh Excel file stored in SharePoint library using Office Script that refreshes data connections

Question

I have many Excel files stored in a SharePoint library (containing client data, 1 workbook per client), each with their own parameterized Power Query that extracts data from MSSQL.

I have created a simple Office Script (below) which refreshes the data connections successfully whilst the Excel workbook is open.

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

    workbook.refreshAllDataConnections();
}

I have attempted to create a Power Automate flow (below) which executes successfully (I am able to access the relevant workbooks as well as the Office Script through Power Automate) however the Excel workbook is not updated with the newly extracted data; this does work when the workbook is open however having the workbook open is not an option.

As per the title, I would like to silenty refresh an Excel workbook using an Office Script on a schedule using Power Automate.

Is this possible?

PowerBI is not an option, and absolute references for each and every workbook through some combination of VBA, Python and/or PowerShell is an inefficient solution.

Answer 1

Refresh not fully supported in Power Automate
Office Scripts can't refresh most data when run in Power Automate. Most refresh methods, such as PivotTable.refresh, do nothing when called in a flow. Workbook.refreshAllDataConnections only refreshes when PowerBI is the source. Additionally, Power Automate doesn't trigger a data refresh for formulas that use workbook links.

Source: https://learn.microsoft.com/en-us/office/dev/scripts/testing/power-automate-troubleshooting#refresh-not-fully-supported-in-power-automate

Alternative ideas:

• Use Windows Task Scheduler to run a PowerShell script. This script opens each file, does a refresh, saves, then close.
• Use Power Automate to get the data from MSSQL, then write directly to Excel via https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Excel-Batch-Create-Update-and-Upsert/td-p/1624706