I'm working on some basic reports that I'd like to completely automate within the MS Stack.
I have a number of flows already set up that grab data reports received via email and uploads them into a dedicated SharePoint site.
From there, I have various excel working "reporting templates" of various complexity that also live in a SharePoint site. The automation I'm focused on for now is a simple report that is just a straight query refresh and the output table is the actual report.
The flow I'm working on would:
- Trigger at a scheduled time
- Call an office script that is supposed to refresh the query and place a time stamp on a sheet for the last refresh
- Delay to allow time for the query to refresh
- Get the file metadata
- Delay again to ensure the correct version has been synced with SharePoint
- Condition based on the data output from the table
- If there is no data in the table, send an email saying "nothings here"
- If there is data in the table, select specific columns, convert to an HTML table with certain formatting, and send an email saying "here is the data"
- Finally, update a SharePoint list with the date of the last successful flow run
The flow works, but my issue is actually with the office script being used. The script is:
function main(workbook: ExcelScript.Workbook) {
workbook.refreshAllDataConnections();
let sheet = workbook.getWorksheets()[0];
sheet.getRange("A1").setValue("Refreshing...");
sheet.getRange("A1").setValue(new Date().toISOString());
}
The timestamp of when the script is triggered populates, and the action in the flow shows as successful, but the query output table is wrong. When I go in and manually refresh the query the refresh works exactly as expected. I'll then delete a row or two and allow the flow to trigger, and the output table clearly doesn't refresh as the deleted rows are still missing but the query refresh should restore them.
Curious if anyone has an idea of how to effectively trigger a power query refresh through an office script or other means via power automate.
Appreciate any assistance. Thanks!
