Hello Everyone, first time poster here!
I have an Excel Spreadsheet that I am using to keep a log of daily maintenance checks that is fed by a Form. All of these items are kept on the same SharePoint site and interact well (i.e. Form responses are migrated correctly to the Excel table.
I am now adding a dashboard that can be displayed on a different page of this SharePoint site that is fed by a number of graphs and pie charts, several of which are run by pivot tables. I have this part working correctly as well through File Viewer (No access to PowerBI unfortunately). I have found however, that these graphs do not update until I manually go in to the spreadsheet and refresh the data connections.
Found a solution for this too, set up a script through the Automate tab that sorts the Data table and refreshes all data connections, code for this is as follows:
function main(workbook: ExcelScript.Workbook) {
//Refresh all data connections
let datatable = workbook.getTable("Data");
datatable.getSort().apply([{ key: 1, ascending: true }]);
workbook.refreshAllDataConnections();
}
When I run this script in the Excel sheet itself it works as expected, the table is sorted by column 2 (date) and the pivot tables and subsequent graphs update correctly. However, when I try to run this through Power Automate as part of the response migration script, it does not seem to work at all. Flow for this is as below:
As you can see, Power Automate registers no problems with this process, but a check of the sheet does show that this has not been actioned.
Has anyone experienced this issue before and know a solution? I've already researched and can't find any answers that seem to solve this issue.
Thanks in advance.
FURTHER INFORMATION:
Adding a delay to the flow between adding the row and running the script seems to correct this issue. From my other research I believe this is to do with Power Automate interacting with a 'Cached' version of the sheet and not the one it has just updated with the new data.
Currently I have the delay set to 5 Minutes (which is fine for this purpose), but it would be interesting to know what the actual timing is on the refresh.
