cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
leyburn19
Level 10

Excel limits

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?

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Excel limits

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.

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!

View solution in original post

leyburn19
Level 10

Re: Excel limits

 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

View solution in original post

4 REPLIES 4
Super User
Super User

Re: Excel limits

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.

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!

View solution in original post

leyburn19
Level 10

Re: Excel limits

 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

View solution in original post

leyburn19
Level 10

Re: Excel limits

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.


Highlighted
Super User
Super User

Re: Excel limits


@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. 

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 463 members 6,125 guests
Please welcome our newest community members: