Answered

Excel limits

(0) Share

Report

Posted on by leyburn19

2,157

I have a flow that adds rows to an excel table - up to about 1000. I then send th result by email. Lastly I delete all rows and clear the table for the next loop.

My issue is I have set the setting of the List Rows In table action to be 2000 and it finds all rows ok. the delete row action only does 256 of the rows. Is there a way to get all rows deleted?

Categories:

General topics

All responses (5)

Answers (2)

Sort by

Verified answer

Brad_Groux 4,556 on at

Like
a
(2)

Report
Copy link

Link copied!
The Excel connectors are very limited, and are not recommended if possible, including limiting 100 API calls per action, and lockout times of 6 and 12 minutes. So, to complete what you're seeking to do you likely need to build in delays withing your Flow, and/or perform the actions in batches.
Run flows on a schedule
Excel (Deprecated) documentation
Excel Online (Business) documentation
Excel Online (OneDrive) documentation
To get around these limitations, you could also utilize a SharePoint List for the data manipulation, and export back to a Excel file if required.
If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!
Verified answer

leyburn19 2,157 on at

Like
a
(0)

Report
Copy link

Link copied!

in the first instance my list rows in tables setting of 1000 did not save for some reason. I fixed that and it all nearly worked properly. I have now built in a delay in to see if the one or two rows missing are attributed to that. I hav enoted that my flow takes 6 hours to run, so I am also concerned about that as the SP list it all uses gets a lot of activity by users, so I am not sure what impact there is with aflow like this, Although I think the get item sfrom the SP list takes only a couple of minutes. I presume the get items stores its result in a cache of some sort.

You mentioned 100 API calls. Can that be explained in more detail? What is scope of each call?

I should have also said I am using excel online(business) which in reading is more attune to what I want to achieve
leyburn19 2,157 on at

Like
a
(0)

Report
Copy link

Link copied!

I had to rearrange my flow so the delete rows occurred before the add rows. It still takes 6 hours as the flow cycles through 29 loops, with rows close to 1000 on most. I need to do this daily. So my big concern now is I also do a lot of daily flows based on teh same list. Again I assume the time does not lock out a list for any purpose other than my get rows action, which is minimal in the flow.
Brad_Groux 4,556 on at

Like
a
(1)

Report
Copy link

Link copied!

@leyburn19 wrote:
You mentioned 100 API calls. Can that be explained in more detail? What is scope of each call?
A call is any action, exactly what is contained within a single API call is difficult to gauge, it depends on the source data, how the connector communicates, and how your Flow is organized and written.
Every time the connector queries the Excel file from your Flow, is a call. Sometimes it can perform multiple queries or actions per call, sometimes it can't - it all depends on the actions being performed.
The documentation says that it has a limit of 100 calls "per action," the actions in this case are the steps within the Flow. You can likely add more/repeating steps within the flow to increase the overall potential number of calls.
takolota1 4,980 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Anyone trying to do this can also use Excel batch actions to make things much faster & more efficient

Update & Create: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Excel-Batch-Create-Update-and-Upsert/td-p/1624706

Delete: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Excel-Batch-Delete/m-p/1634375#M735