2

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. enter image description here

and the run history of flow x: enter image description here

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) enter image description here

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: enter image description here

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

5
  • 1
    Are you updating any row in the same excel file from flow X? Accessing the same file via multiple flow instances during same time period may exceeds the limit of excel online API that occurs in a background. Perhaps you may have to change logic for these flows. – Dikesh Gandhi Dec 3 '20 at 7:00
  • No, just reading from the Excel file. If there is a limit on the online API call, how can I set FIFO and avoid deadlock? – Mohsen Sichani Dec 3 '20 at 7:16
  • Reading excel file should not take long execution time. It's better you wait for instances to be cancelled automatically that way it will show error to troubleshoot. – Dikesh Gandhi Dec 3 '20 at 7:38
  • @DikeshGandhi thx for your thoughtfulness. There is no error, just takes time, kind of deadlock. See the new screenshot. The first 30-40 executions of flows were running fine but it seems there is an issue that appears after some calls (or parallelism). – Mohsen Sichani Dec 3 '20 at 8:07
  • @DikeshGandhi, I have seen an error regarding to many calls of Excel as you guessed, now how to fix it? – Mohsen Sichani Dec 4 '20 at 1:26
2

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!

6
  • Excellent idea about the JSON thx for that, can you please elaborate on this more (how can this be done):Set mechanism to identify 429 status code error and set delay of duration that is provided in error message and run your logic again. – Mohsen Sichani Dec 4 '20 at 8:02
  • Upvoted thanks, will wait abit longer for other answers before accepting this one. thx again – Mohsen Sichani Dec 4 '20 at 8:37
  • Updated my answer. – Dikesh Gandhi Dec 4 '20 at 8:51
  • Thanks Dikesh, I think converting Excel to Json is great and reduces the load, however, since flow x is called lots of times & each time it needs to read the Excel and convert it to JSON, so I assume it again may create deadlock, I am thinking of converting Excel to Json outside the flow (through an app) and save the json in the flow x which releases flow x from calling Excel. I am not sure in which variable in the flow I must save this long JSON? Is "Object" a good choice? My excel file has lots of text in it, maybe 2000 lines and each line has 5 to 10 columns, – Mohsen Sichani Dec 5 '20 at 6:28
  • My idea is to read all lines from excel at once when your flow x kicked off and store temporary in a object variable provided in a flow. After that you can filter this object as per your need further in flow in loop. So it will reduce your api call. Please accept my answer if it's helpful to you for resolving deadlock problem. For new question, please ask a new question in portal. This way it will more visible to others and will help others too. – Dikesh Gandhi Dec 5 '20 at 6:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.