How can I deal with the Apply to Each 5000 limit?

[TYu036]

When I use Get files (Properties only), I can set pagination to 5000 and use Top count 5000, this setting let me get item properties larger than 5000. It works fine.

However, after that, I may need to use Apply to each to do some actions, then Apply to each will have view limit error. How can I deal with the Apply to Each 5000 limit? It seems there is no pagination feature

[Sundeep_Malik · Accepted solution]

Hey @TYu036 

Not sure you were looking for this only. 

You can use a do until loop for this. Providing you a link to take reference from:

https://www.tachytelic.net/2020/04/get-more-5000-items-sharepoint-power-automate/

[TYu036]

Hi Sundeep,

Many appreciated the information you provided. I tried the loop once before based on another blog but reject because of the speed performance problem. But in the blog you provided, it provides another API way to improve the speed performance. I will have a try and post back the result.  

[TYu036]

Hi Sundeep，

I think the solution should work. but to save my time, I use another workaround to meet my specific need. Anyway, thanks for the information you provided! This may help me in the future as well.

[grantjenkins]

@TYu036 It would be interesting to see why you would need to loop over more than 5000 items. Often there are other ways to architect the flow to achieve what you're after without even needing a loop.

Would you be able to share what you were trying to do? Ok if you don't want to 🙂

On a side note - Apply to each can loop over more than 5000 items. I'll do some research and let you know the max limit. I just did a quick test and looped over 5,997 items without any issues. To speed up the Apply to each you can go into Settings and turn on Concurrency Control and set the Degree of Parallelism to have it run multiple threads. Note that this isn't always recommended such as when you are updating items in a list, etc.

[TYu036]

Hi Grantjenkins,

Thanks for the reply.

What I want to do is to send email to every user whose files meet specific conditions.

The initial way I used is to:

1.  Get files (properties only), this helps get more than 5000 items with simple filter.
2.  Filter array, this helps get more than 5000 items with detailed filter queries.
3.  Compose action with sort() function. This helps me sort the “created by” user email address for items in filter array.
4. Then I started using apply to each to loop condition, if variable “owner” is equal to “created by email”. If it’s yes, then send email, if it’s false, then do nothing. I want to use this to only send 1 email to each user. And the view limit error appear at this step.

Finally I use another way to deal with the problem. That is, to use select to create an array only with user email address, then use union() function in compose, remove duplicate user email address, because the user email address will not be more than 5000, then I can do anything I want with it.

I don’t know whether compose can have different result. But if you use “get file properties” or “condition”, the apply to each will give 5000 limit error.

[grantjenkins]

Normally if you're having to loop across more than 5000 items there's another way to approach it - which is what you've done here. Very nice solution in the end 🙂

Just a quick question - are you sending a list of all the files affected for each person in the email too? If not, but you wanted to, you could potentially use some XPath to extract the file names for the person, joined by <br> very efficiently so they show up as a list of files.

[TYu036]

Is this html code? I am not good at coding. So I use "create html table" action to do that. Then put the outputs to email content.

[ervinw]

This seems quite difficult:
https://www.tachytelic.net/2020/04/get-more-5000-items-sharepoint-power-automate/

A more lightweight example, check this:
https://piyushksingh.com/2020/07/06/power-automate-read-excel-file/