429 and GatewayTimeout for Add a Row into a Table Action

[pickiiii123]

I have a Flow that extracts information from an email and then adds that information into an Excel Table using the 'Add a Row into a Table' Excel Connector. The Flow was working fine for months and now randomly, for a few days at a time, will result in 429 errors and GatewayTimeout errors. I have a retry policy on my action of 50 times but each retry fails with the same error. 

My Excel table has around 2500 rows and is 4MB. 

Anyone have any fixes I can try? 

[wskinnermctc]

Google Search Says:

Error 429 in Power Automate indicates a rate limit has been exceeded. This error occurs when an excessive number of requests are made to a service in a short amount of time, exceeding the allowed limit. The error message “Too many requests” or “429 Too Many Requests” is a common response to indicate this issue.

So either the requests to the excel document are happening too fast or you might be reaching the account limits. 

What is the trigger for this flow?

[pickiiii123]

The Flow trigger is 'When a new email arrives (V3)'. Below is the Flow.

When you say account limits, does that mean all the Flows across my account could be hitting a limit?

[wskinnermctc]

Yes you have a daily limit of api calls. But I don't think that is the issue here.

Why do you have the step "List rows present in a table"? 

Are you getting like "Table123" from List rows present in a table. And then Add a row into table "Table123"?

If you are listing rows from the same table you are adding a row, there might be some kind of issue with calling the workbook too fast.

Like is there some data from "List rows present in a table" that you are using for the "Add a row into a table"? 

[pickiiii123]

Yeah, I moved the 'Add a row into a Table' up in the example to see if it was still failing and it did. 

The Flow looks at all the rows in the Excel table, if a row is found that matches the emails data (Unique ID), then update that row. If not, then add that row into the table. That is why I need to 'List all the rows in a table' action. 

The 'Apply to each 3' looks at the body of the 'Filter Array' to see if there are any matching values. Most of the time this filter array will return nothing because we need to add a new row of data. 'Add a row into a table 2' is where the Flow receives the 429 or GatewayError.

[pickiiii123]

Also, I put that 'Add a row into a table' before my 'Filter array' in the before example because I wasn't sure if the Filter was using a ton of calls checking each row thus causing the 429/GatewayError. It still failed though

[wskinnermctc]

Oh yeah, you're going to have problems with this set up. It is doing too much to the Excel workbook at the same time. On top of that, you're Excel workbook is big; PA defaults to getting 500 rows, so beyond that it will probably be slower.

What if 2 emails come in within a minute of each other? It will be trying to do this while the last flow is still using the workbook. 

Read Microsoft Learn about Excel Connector and it shows this exact error code and potential issues.

[pickiiii123]

I had it running for months with around 2000 rows just fine. The flow running regularly has a runtime of around a minute. 

There is a small chance two emails would occur at the same time. It's less than 10 throughout the day. 

Think I just need to get more creative with my approach. 

[wskinnermctc]

I don't know why it would stop functioning as intended now, but it is not surprising. Using Excel as a database is going to cause a problem. You could try using a SharePoint list or create a Dataverse table which you could probably use Dataverse for Teams to handle something like this.

Another thing to do would be to just always add a new row. Don't worry about filtering the workbook. Just keep adding new rows.

Then separately make another flow that runs at night or something which gets the workbook and deletes duplicates. You could probably make a Script in the workbook and have a flow to call it and perform the removal.

A duct tape fix for the current flow would be to try putting delays between the steps. So between the List Rows and Update Row, put like a 10 minute delay action.

[pickiiii123]

I created a separate Flow with one action of trying to 'Adding a row into a table' and that got a Gateway Timeout. Seems after around 2,000 rows these errors start occurring. 

My potential solution is I just created a separate Table (same format) under a new sheet to add all the email data. I can successfully write data there from Power Automate. Then, have a user manually copy over the new entries. 

Thanks for the help!