I created an office script in Excel to refresh my Power Query connections. The script works just fine but when I try to automate it, the refresh doesn't work. I've tested other scripts (like filtering a column), automating that script, and they work just fine. It's something with the refresh. I did some research into it and loaded everything onto my SharePoint site to connect it that way and to use the "Run Script From SharePoint Library" connector but it still doesn't refresh.
Here's the code in the office script:
function main(workbook: ExcelScript.Workbook) {
// Refresh all data connections
workbook.refreshAllDataConnections();
// Refresh all data connections
workbook.refreshAllDataConnections();
}
The reason that there are two refreshes is because there's the connections to source other Excel workbooks and then there's another sheet with some Excel equations. From my experience, when you refresh all, only the connections refresh. You need to refresh the sheets separately or refresh all connections a second time for the Excel equations to update and reflect into a final report.
Anyway, here's the flow. All tests have been successful. It's like it will run, skip over the refresh, and then complete. I always receive an email.
I've also verified that the script is in the workbook and is pointing to the SharePoint library.
How do I fix this?
