I have been banging my head against the screen for the last week..
I have one library with 45k files.
I want to update metadata across the library. Since with the Get function we reach the 5,000 limit threshold I want to use a DoUntil loop and apply filter by the last item/file ID. So far so good. I tried with a list with over 20k items and it worked well.
When I tried with the library, no matter what I do and what filter I apply to it, I cannot get more than the first 5000 files of that library. If I use the Filter Query 'ID gt 6000' it returns me an empty array and I know that the library has 45k files!
For simplicity I then create an HTML table on those outputs and result is as follows...
If I set the filter query say to 'ID gt 100' and the TopCount=100 than I get the result as expected.
This is probably a limitation of working with Sharepoint Libraries? is there any work around? or shall I move the 45k files to another location e.g. OneDrive?
Yes, the documentation says that if you exceed the threshold limit Power Automate will not run properly and will prompt an error message. (I'll paste the link to the documentation here: In-depth analysis into 'Get items' and 'Get files' SharePoint actions for flows in Power Automate | ...). I have one question, do you have a premium plan? If you do, you can increase that limit up to 100.000 items (runs a little bit slower though).
Here's another solution (Do Until Method): How to get more than 5000 items from SharePoint Online in Flow - Alex Tofan's blog where they explain how to achieve this without a premium plan!
Hope I could help!
Thanks for your reply. So as I understand for libraries we basically need a premium license. I tried the DoUntil method but it just does not work. It won't get passed the 5000 items. when ID>5000 it returns and empty array and then the DoUntil ends because the array is empty although the reality is there are still 40k files to go.
Now, even if I go for a premium license, 100k files is still not a lot. I managed to store 45k files in less than 1 year so if I upgrade to a Premium I will still be facing problems next year. Is there another way to manage and update files in SP libraries or do I just have to face the fact and re-think my strategy so that I never have more than 100k in a document library?
Well, the documentation does tell us that in order to process more items we could trigger multiple flows over the same data (this could be one idea). Another recommendation could be to separate the data into different libraries and create different flows, but that really depends on what you need, but if the amount of data is projected to grow in the next couple of year or months, then it would really be a problem because of the threshold limit. Unless I'm overseeing something, in which case I would appreciate if someone had any other piece of advice!
Keep up to date with current events and community announcements in the Power Automate community.
A great place where you can stay up to date with community calls and interact with the speakers.
Check out the latest Community Blog from the community!