Parallelism for reading an excel file in Power automate

Question

I have a flow (say x) which is called by other flows multiple times. In the flow x, I am reading an Excel file but I have noticed the flow x stocks in a loop that contains reading from this Excel file. I guess this happens as reading this Excel file is time-consuming and since flow x is running several times it kind of creates a deadlock on this Excel file. How can I solve this issue?

Part of flow X contains reading this Excel file.

and the run history of flow x:

The red ones are those ones that I canceled manually. As shown above, this happens after some successful runs.

This Excel file is located on our SP site and only reading is done on this file.

About hours later: The run history of the same flow (flow x)

Plz note the increase in the duration of the execution of this flow after continuous calls.

Not sure if this helps: I put flow x under pressure today and noticed the below error:

{ "statusCode": 429, "message": "Rate limit is exceeded. Try again in 14 seconds." }

Above is the error is shown by the red Exclamation mark as shwon below:

It has some suggestion about reducing the use of Excel Online, how can I do it? Is there any other kind of storage that I can the content of the Excel file without having this issue? Unfortunately, I do not have access to any kind of db.

And, is there is a way to stop all the running flows at once? Found this, very helpful : http://www.dynamicscrm.blog/power-automate-cancel-all-running-flows/

Any help is highly appreciated.

Update

I have added some delay actions with rand (30,60) in all the flows, and now it seems everything working fine. I will report back on this later but also appreciate a better solution.-> helped but not 100 bullet proof solution, still gettign those deadlocks

Answer 1

In your flow, instead of reading few rows in a loop, why don't you try to read whole file at once and take data in JSON object variable? You can use this JSON object later in loop for further operations. This way it will reduce the excel online API calls on excel file and will not locked out.

The 429 status code error shows that your flow instances are exhausting API limits and reaching to the threshold. Whenever API gets error of this threshold limits, it suspends that particular account for certain time. In the flow, since excel actions has one account's authentication so this account gets exhausted.

This limitation is set intentionally so performance of services would not impact when any one account demands extraordinary API requests on the server that threaten the availability and performance characteristics of the server.

Please see more details for this threshold limit here: https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/api-limits

Other limits associated to power automate https://docs.microsoft.com/en-us/power-automate/limits-and-config

You can try below things to avoid this, but there is no any straight forward thing to mitigate this error:

• Set logic in flow to use excel connectors with more than one user accounts.
• Set random delay in flows whenever you are using loop.
• Set mechanism to identify 429 status code error and set delay of duration that is provided in error message and run your logic again.
• Use batch API concepts with MS Graph API & HTTP action in flow instead of these explicit action connectors like: Excel.

Update:

In flow, you can check if excel action/HTTP action got an error or not using "configure run after". If error occurred, check the error message and take out the duration of waiting from message. And add the delay relatively and call your API action again.

I hope this helps!