I have a flow where Power Automate takes data from a table in Excel Online, transforms the data in memory, and saves that data as a csv. This works most of the time. However, this is a business critical process and 'most of the time' is not good enough. It has to be reliable and always work. The problem is that the csv sometimes contains data from an older version of the Excel file.
The exact flow:
- User opens an Excel file in Sharepoint using Excel Online
- User adds data to specific table in said Excel file
- User copies file to a special purpose folder on Sharepoint
- Power Automate triggers when a new file is created in this special purpose folder, i.e. when the file is copied there
- Power Automate 'Run Script' accesses the file, reads the data in the table, transforms it to requirement, outputs a JSON
- Power Automate takes the JSON and creates a CSV file containing the data and sends it to an assigned department
The problem occurs in step 5, where Power Automate opens the Excel file and reads the data. Excel Online is slow and its speed depends on general latency of Microsoft's cloud solutions in your region. I have noticed that files sometimes open immediately, sometimes take a few minutes. I suspect that my automation has the same issue. It opens the file with Run Script and immediately runs the Typescript code on it. However, the cloud has not yet propagated with the content of the latest version, so my script is fed an older version.
How can I prevent this? Is there a way to force Excel Online to first switch to the latest Excel file version before my Run Script continuous transforming data?
Having the users switch to Sharepoint or Dataverse for data storage is not an option. They need to be able copy-paste their data from another Excel offline file and are very averse to using anything other than Excel.
