Best method to to get more than 5000 items from SharePoint Online in Flow

[Anonymous]

Hello,

I would like to the Best method to to get more than 5000 items from SharePoint Online in Flow , as there are few documents but they mention it might effect the performance & few say it doesn't.

So kindly provide any links/method to do that , which might least effect the behavior/ performance of the app.

• Also I have flows like sending notifications on teams , mail by storing it on SharePoint lists & showing data in gallery, hope doing this single flow to get more than 5000 items from SharePoint Online would solve the issue & work in these scenario's?
• If we are modifying/adding certain actions to achieve this limit , this should be added as new flow ? or we need modify the actions in the present flows of  for sending notifications on teams , mail by storing it on SharePoint lists & showing data in gallery.

Thanks in advance.

[jatcube]

@Anonymous the 5000 item limit is for a single view or results. In flow, you'd use the odata filters to only get the items that match your criteria.

If you genuinely need to process more than 5000 items at a time, you'd want to call the SharePoint batch API to speed things up and even then need to create a loop with the do until action as a batch call can't have more than 1000 operations.

What is it that you are trying to achieve exactly?

[Anonymous]

Hello @jatcube thanks again for responding.

So let me explain my flows scenario:

• I have SharePoint list as data source using power apps as front end.
• Flow1: whenever an item is created send notifications to user on teams & outlook.
• Flow2: Admin support from power apps, so whenever an item is created in admin list, send notifications to admin & the response from admin to user.
• Flow3: To remind the users on teams that they have booking on particular day. Using the SharePoint list data & recurrence action.
• Also i used 2 types of gallery based on data in SharePoint list.

So basically what if the data in SharePoint list reaches more than 5000 items?

Will these FLows will retrieve from them ? Or will these FLows work after the sp list reaches more than 5k items.

Kindly mention. Let me know if any more details required on same 

[jatcube]

@Anonymous by the sound of it, none of these flows return more than 5k items in any one action, so you should be good to go. A SharePoint list can hold up to 30 million items and you can query 5k at a time using filters. So in case of triggers such as created or modified, only one item is returned, so that's all good, again for the response to users as you have unique IDs.  And for the reminders, if you use the dates for instance and look at the individual days as filter and as long as you have less than 5k items created every day, you'll be fine no matter if you have millions of items in your list. Hope that answers your question 

[Anonymous]

Oh alright sounds different & little confusing. So I have come across this concepts of paging, we are supposed to create loops if we have more than 5000 items in list for the flows , what are these exactly?

Like if we need to get more than 5k items at a time when a flow runs? to power apps or any other app, for what is this process of creating loops or methods to query more than 5k items?

adding links for reference - https://alextofan.com/2019/08/22/how-to-get-more-than-5000-item-from-sharepoint-online-in-flow/

https://tachytelic.net/2020/04/many-ways-get-sharepoint-items-power-automate/

As mentioned by you  you can query 5k at a time using filters, does that mean using views & indexing in SharePoint lists?

Also in few of my flows I also use 'get items' Actions to send notifications, 'Delete items' actions based on user action from SharePoint. So as you said as long as you have less than 5k items created every day there will no be no issues? & no need to add any other functionalities.

So in my flow I'm not calling /retrieving more than 5000 items at a time like all at once, so no need of this paging / adding loops right? In case any documentation requesting for same.

Thank you so much again , please look into the above queries.

[jatcube · Accepted solution]

@Anonymous yep, that's right, based on what you said previously I don't think you need to worry about the 5k limit. But it depends on your filters. What I said is if you filter for created items and you run your flow daily, if you'll never have more than 5k items created per day, no need to spend time on developing a flow that could handle such scenario. 

As for indexing, yes, the columns you'd like to filter on in the list must be indexed, but again, if you fail to create your own indexes, SharePoint will do that automatically for you. Although I always suggest you do them because you know your data better than a script.

About those solutions to get more than 5k in a flow, well, it's always best to avoid it if possible. There isn't one perfect solution, but one that fits your requirements best. Generally, I'd say, only look at such an option for running non critical service flows usually with overnight execution..

That said, if you really need it, querying the lowest and highest Id for which the filter returns results and looping through in increments is usually a solid implementation: https://tachytelic.net/2020/12/power-automate-sharepoint-5000-items-easy/

Hope that answers your questions

[Anonymous]

Thanks again @jatcube  for your detailed explanation , it's all clear. I understand no need to worry about this 5k limit  in my scenario's & as you said it's not preferable idea & better to not to use for any important flows.

Just wanted to understand in future , in which scenario's do we need to implement this  lowest and highest Id for which the filter returns results and looping through in increments, etc., if it's required? And what exactly does this do? It calls all 5k items together or filters/searches more than 5k items?

Just for the knowledge sake to understand this. Kindly brief. 

[jatcube]

@Anonymous essentially what we're doing here is sorting the SharePoint list by ID and applying a filter on the relevant columns so only those rows are returned that match our filter criteria. Then we grab the lowest row id and the highest row id and run multiple get items (multiples of 5k) until we have all the rows we need. Those row IDs btw are automatically created by SharePoint when an item is created and are never reassigned so they are always unique within the list.

But as I said, the linked isn't a perfect solution either, as it assumes we know the number of items we want to return and also that users haven't been deleting a large number of rows (items) that could for instance make an intermediary get item action return no results at all. All of which can be catered for with some logic of course, but that only makes the flow more complicated and more places it can fail, so if you want to include proper error handling, it's another level of complexity for which SharePoint or Power Automate never really was designed for...

[Anonymous]

Okay, thanks again @jatcube , completely clear that better to not to go with these kind of solutions. Appreciate your efforts for briefing.