Scenario
You have a large SharePoint list and you need to get all those items in Flow.
By default, each request you send from Flow to SharePoint Online will return a response limited to 100 items. This limitation is not very obvious, even if you show the advanced options on the action and read the hint text on the Top Count field.
The hint text mentions that the action will retrieve all items by default (default = all), which is technically correct, because all refers to how many items can be pulled by the request.
Given that the Get Items request is designed to get 100 items max, then if you don't put anything in the Top Count field, the action will retrieve 100 items.
To count the items from any array, you can add a Compose action with this expression: length(body('Get_items')?['value'])
And the result looks like this:
5000 items limit
If you want to increase the limit, you can enter a Top Count value. The maximum top count you can enter is 5000, which changes the way Flow sends the Get Items request to SPO.
More specifically, when you add a Top Count to the Get Items action, a $top parameter is added to the query that's sent to SharePoint Online and thus more than 100 items are retrieved.
Using the same Compose action and expression as above, you can see that the result is 5000:
Getting more than 5000 items
Using the Pagination method
To get more than 5000 items, you can turn ON the Pagination feature. This is done from the action's settings:
Turn ON the Pagination feature and set the threshold to 100,000:
The flow will take longer to run, but the action will return all the results, as expected:
Note that you will need a Flow Premium Plan (Plan 1 or Plan 2) in order to set the Pagination threshold to more than 5000 items, as documented here.
The maximum threshold you can set today is 100,000.
Using the Do Until method
If you don't have a Premium plan or if you want to get more than 100,000 items, you can use a Do Until control to loop through all items in the SPO list.
The logic is simple: the flow should be designed to get the items in batches of 5000 items each, using a filter query. For each Get Items request, the flow should pass the last ID from the previous batch.
To do this, start with a button trigger:
Then continue by declaring three variables. An array variable varItems to store all items:
An integer variable varID to store the item ID:
And a boolean variable varIsArrayEmpty that will turn true when the Do Until control reaches the last batch:
Continue with a Do Until control and set it to stop when the varIsArrayEmpty variable turns true:
Add a Get Items action inside the Do Until container and set the Filter Query to ID gt @{variables('varID')} and the Top Count to 5000.
The Filter Query is necessary in order to get the next 5000 items, so the action will look for the next batch of items that match the criteria where the item ID is greater than the last ID recorded in the previous batch.
Next go to the action's settings and, if you have a Premium plan, set the Pagination Threshold to 100,000, else set it to 5,000.
In the same Do Until container add a Compose action and set the Inputs to the following expression: union(variables('varItems'),body('Get_items')?['value'])
This expression will combine the array of items stored in the varItems variable with the array of items stored in the current batch.
Continue with a Set variable action and set the varItems variable to the following Output coming from the Compose action: @{outputs('Compose_array_union')}
Inside the same Do Until container add a condition and set it to evaluate if the array of items returned by the Get Items action is empty: empty(body('Get_items')?['value']) is equal to true
Inside the If yes container add a Set variable action and set the varIsArrayEmpty variable to true.
This means that, if the Get Items array is empty, the variable will be set to true and the Do Until loop will stop.
Inside the If no container add a Set variable action and set the value of the varID variable to the item ID of the last item in the array by using this expression: last(body('Get_items')?['value']).id
For testing purposes, I added a Compose action to output the varItems variable:
Below is how the output looks like in the browser. My list contains 56003 items. I use Firefox because I like how it formats the JSON objects.
In case you were wondering, why is there a difference between the object number and the item ID (56002 vs 56003), it's because the JSON object count starts from 0:
Finally, if you did everything right, the flow should look like this:
If you want to test this flow yourself, you can download it from here.
I don't see the option for "Pagination" if I want to bulk update a Document library with thousand photos… using Get files ( properties only).
Get Files action doesn't support Pagination. Instead you can use the do until method.
Hi Alex
Fantastic blog. Quick question regarding the screenshot you provided at the end where it showing the results. Which tool you are using to show the JSON results the domain starting with prod-123….?
Hi Ajith, it's a browser extension in chrome called Full Page Screen Capture
Hi Alex,
This is great stuff.
Would it be possible to use this with a Document Library and a Get Files(Properties Only) action instead of the Get Items? If so, what formula would I use to filter?
Thanks in advance
Hi Mark, yes Get Files supports Filter query. Files have IDs so you can use the same query as described in the article, however you can't use the pagination method because the Get Files action doesn't support it, so you need to use the do until method.
You can use the "Get Items" action on a document library, you just need to specify the name of the document library as a custom property as libraries don't display in the dropdown. This is how we had to do it before there was a "Get Files (Properties Only)" action, and this looks to be yet another way that the old stuff is better than the new stuff.
You are right, Denis, I completely forgot that you can use the Get items action for files.
Great Post Alex, can you tell me how i can delete all the data i got from this Out, also im going to add 6000 data. Thanks.
You may run a delete using the Delete Item action: https://docs.microsoft.com/en-us/connectors/sharepointonline/#delete-item
Very helpful ! Thank you.
Hi, great work on this. I need to copy all the items from get items and copy them to a new sharepoint list, in which area of the flow should i do this?
You can add an Apply to each container at the end of the flow. Set the apply to each input to varItems and then add a Create Item inside.
Looks like its hit a limite – "The variable 'varItems' has size of more than '114426680' bytes. This exceeded the maximum size '104857600' allowed.". Any thoughts?
If the purpose is only to copy, then you don't need to set the varItems in the first place. Instead, you can create the items as you continue to get them from the original list inside the "do until". If you want to do it after the "do until" and not inside it, use the compose output, not the variable result to create the items, and remove the "Set variable varItems" action. The flow will no longer set the varItems with the output from compose and you will no longer run into this issue. Hope that helps.
Thanks for your help so far. I tried adding the copy in the do until and it looks like its looped the first time around but it seems to have stopped at 10k? I'm really unsure why this would be the case.
Hi Alex,
Such an amazing solution, Have an issue with getting data from smartsheet and update rows in Excel online..Due to number of row limitation, I am following the above step but how i can update more than 5000 rows in excel
Hi, this is great. However I have a problem.
You are using this to solve Pagination from a Get item, which is a table.
Is it possible to use this for a JSON array?
The problem i'm facing is because you are using Sharepoint ID as an index for the Do Until Loop, but how can I Do Until for a JSON object that doesn't have an ID column/field?
Hi, Dante. In that case, when you don't have an index in the payload itself, you can use the skip function (https://docs.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#skip) and leverage the iterationIndexes function to know how much to skip (https://docs.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#iterationIndexes). I don't know how your data is structured, but I am assuming that you have a very large JSON array (with more than 5k items) and you can't do more than 5K in one batch. If that's the case, you should store the array in a variable and then use the iterationIndexes to check when the Apply to each hits 5k. When 5k is hit, you can use skip over the JSON array to ignore the first 5k and continue with the rest of the array (at this point you should set the variable to the skipped array). When you hit again 5k you continue to trim down. All this should be pun in a Do Until control that should run until the array becomes empty.
Thank you a lot for taking the trouble of explaining it to me. It's been really helpful
Hello Alex,
I have a files library with 300000 files. I want to use if … then do something.
I need to move file to different sites based on the beginning of the file name.
I managed this part but run into problems with 5000 limit etc.
What is a good way/workaround to beat this problem?
I don't have time to split the library into 5000 files as that will take ages to complete. I can't send a picture from my flow now but would love to get some insights as you have been helping others greatly!
Yes, you can use this method to loop through the files. Basically you have to break the 300k load into 5k batches and you can easily do that by using the same batching method described here. It does not matter if it is a list or a document library, the method applies to both scenarios.
Hi Alex. Had a question. I am redoing your template to do a Do Until for get files. When I change out Get items to get files, some of the functions do not work.
What do I need to change in the variables to get it to work? For example:
union(variables('varItems'),body('Get_items')?['value']) doesn't work.
Hi Wayne – this union(variables('varItems'),body('Get_items')?['value']) is referencing the value array from the Get Items action. The reference to get items must be changed. If your get files action name is Get Files then the function should look like this: union(variables('varItems'),body('Get_files')?['value'])
Hope this helps
Hello Alex,
Thanks for the response. I can't formulate this formula:
I want to make a condition below compose items output but it give me an error.
It says the expession is invalid…..
Can you help out with that?
@equals(first(body('Get_items')?['value'])?['Name'],'0202′)
Very clear and useful. Thank you for sharing it.
Thanks, Omar. Glad to hear that it helped!
Hi Alex,
Thanks for the flow. I am trying to insert more than 5000 items from list file to excel file. I have designed the same flow but still only 5000 rows are getting inserted. Can you please help !
Hi Srisahithi. I am not sure what could be wrong. The insert is driven by the Do Until container, so if you're inserting only 5000 then it means that your do until ran only once. I would check the output of the condition, especially the Set variable varIsArrayEmpty
Hi Alex,
Thanks for your reply.
Should the Add rows be outside the "Do Until" loop or inside it ?
Hi Srisahithi!
I know it's been 3 years after this comment.
Do you remember if you insert Add rows inside or outside the "Do Until"?
I'm experience same issue.
Can you or Alex help me?
Thanks in advanced!
I don't see the option for "Pagination" in my app settings. i am using sharepoint list and get items
Hi Yamini, I am guessing that by "app settings" you are referring to the Settings on the Get Items action. If that's the case, then the pagination should be there. Make sure you don't select Get Item or Get Files. Not sure why you don't see it…
Hi Alex,
Using this method, how can I also add a filter so that output contains only records with a specific entry in a column e.g. I only want the records listed as 'Field Operations' in the 'Department' column in the sharepoint list?
Thanks
Scott
Hi Scott. In the get items action, add the following odata query in the Filter Query field: Department eq 'Field Operations'
Hi Alex There are 873 items in 「list rows present in a table」, and I want to get all of the items and update to the sharepoint list. I've tried to add your flow, but it doesn't work. > do until shows "ActionFailed." & get items shows "NotFound.". How to modify my flow?
Thank you so much.
Hi Joy, the flow I built works for SharePoint only. If you want to make it work for Excel you have to adapt it and use the Do Until method by leveraging a different parameter that you could pass in your filter query in the list rows present in a table action. You first have to replace the Get Items with List rows present in a table and then you have to make sure that the flow is referencing the newly added action.
This is extremely helpful, thank you.
I would now like to create a collection in PowerApps from the data collected, is this possible and if so how would i go about this
Many Thanks
Jason
If you want to do it directly in Power Apps, then it's not possible to collect a large list. Even though SPO is a delegable data source, when you run a ClearCollect(colMyCollection, MyDataSource), where MyDataSource is a SPO list with more than 2000 items, the function will only give you 2000 items max. If you want this to run in Power Automate and respond back to Power Apps with the entire array, then that's possible, but you may be limited to the payload size that you will be able to bring back to Power Apps, in which you will probably want to bring your data in several batches. In any case, it's NOT a good idea to fill up a collection with thousands of items because it will affect performance.
Hi Alex
I one the flow twice- no issue and run successfully.
But does not delete anything
[ is it possible to delete items <= Date Value]
your thoughts are appreciated
Hey! The flow is not designed to delete items, however you can use the filter query in the Get Items action to limit your items to DATE_FIELD_NAME le DATE_VALUE, where DATE_FIELD_NAME is the actual field from your list that you want to use in your criteria and DATE_VALUE is the value you compare to. Once this is done, you may use the Delete Item action to delete: https://docs.microsoft.com/en-us/connectors/sharepointonline/#delete-item
Buna ziua,
Incerc sa creez un backup pentru o lista din SharePoint, iar pana acum acest articol m-a ajutat foarte mult! Am introdus un Recurrence Trigger in loc de Manual, insa as dori sa trec aceste iteme in CSV si sa le salvez intr-un Document Library. Stiti cumva cum pot face acest lucru? (stiu ca exista un Create CSV table control; il pot adauga in "If yes"?)
Salut! Da, trebuie sa folositi Create CSV table pe array-ul varItems. Uitati-va la acest template mai intai: https://flow.microsoft.com/en-us/galleries/public/templates/79095f001ee911e79dc3835964ea3218/copy-sharepoint-list-items-into-a-csv-each-week/ Ideea e ca in flow-ul creat de mine toate item-urile din lista se stocheaza in varItems, asta daca folositi metoda cu Do until. Daca folositi metoda cu paginare, la fel, Create CSV table trebuie sa fie configurat cu array-ul din Get Items. Deci trebuie doar sa adaugati Create CSV table si apoi sa generati un fisier (in OndeDrive sau SharePoint) cu continutul rezultat din Create CSV table action.
Va multumesc si imi cer scuze de intarziere :D!
Hi Alex,
How long does your flow take to run?
I created the flow exactly as you described, but mine keeps timing out at 10 minutes.. Can you help?
Hi John, it really depends on how many items you have in the list. No, it should not time out at 10 minutes. I'm thinking that maybe there's an issue with your get items action – perhaps there's no response from SharePoint? That's the only action that communicates with an external service, all other actions are Logic Apps and they're highly unlikely to time out. One way to test is to limit your do until to 3 iterations and troubleshoot by looking at the input/output of the Get Items action (I am assuming you're using the Do Until method). Hope that helps.
Thanks Alex for the post it helped me alot with the issue I had with my sharepoint. But I have a follow up problem you might help me. After Get Items I apply another Filter Array function to just get files which match specific criteria. For my first flow this worked and performed perfecly fine, but for more complex filter it will cause the flow to finish too early eventhough I can see that the varlsArrayEmpty function was never set to true. Do you have an idea why this is happening?
Hi Alex, thank you for your excellent blog and for posting this tutorial, it has been really useful as I have been teaching myself how to code Flow. I have a question. You say to put ID gt @{variables('varID')} in the ODATA filter of the get items step. Would you please tell me what "ID" refers to? I don't have a column named ID in my SharePoint Library but it works nevertheless.
I have a column called "Reviewdue" which I want to filter on. So when I enter Reviewdue le '@{utcNow()}' in the ODATA filter field, it hangs the Flow and it eventually times out. Please help.
It works because the doc library is esentially a list of items and files have IDs like any other item. Not sure why it is timing out for Reviewdue. You can go to SharePoint and click on the field in library settings and look in the url. Make sure the field name you see in the URl is matching the field name you are using in your filter query. Can't think of anything else. As long as your field is date/time it should work.
Hi Alex,
Nice Article!
Do you have any article to copy 5K list items to a new list?
Unfortunately, no. I would simply use the create item action on the new list for each item in the old list.
I have requirement for more than 30,000 items. what's the Premium Plan ?
Thank you for sharing this, fantastic work.
Can you tell me how I might output the results to a csv file though? I've tried various methods, and it works ok as long as the number of rows is below the pagination limit. Otherwise it fails every time.
Hello Sir, my flow is runnig and never stops and i am doing the do until method. please advise ?
I want to use the same method for reading rows from excel online so I use the 'List rows present in a table' instead of the SharePoint 'Get items'.
I'm getting an error with the query filter = Invalid filter clause: unsupported operation. Only single 'eq', 'ne', 'contains', 'startswith' or 'endswith' is currently supported – "ID gt @{variables('varID')}"
Hello Matt, could you to solve this question? because I have the same situation. I want to read from excel file and copy each item to my Sharepoint List. If you could please share here. 😉 thanks
Thank you for the post!
Trying out the flow now – would the compose action be replaced by the actions we would want to continue with? For example, updating those records collected and so on.
In your experience, how long did 53k items take to loop through and are there performance improvements available?
Hi Alex,
Thank you for the blog.It was very helpful.I have a doubt.
If I have around 80,000 folders in sharepoint and each folder contains 3 subfloders. I want to get the files uploaded inside one of the subfolders and delete them after 5 days. How this can be done?Can you help me here?
Superbly written, thank you. I needed to update 30,000 items (no premium license)
Hi Alex,
This is very helpful. I have a pagination limit of 5000 due to my license so I will have to use the 2nd method as a list that I need the data from is nearly 15000 items long. Once I have all of the items combined, is there a way I can preform a lookup and update type of workflow?
I have 2 lists. List 1 is populated with MS form responses and List 2 (around 15000 items) has staff data (name, job title, dept., etc.). I’ve set up a flow to feed MS form responses to List 1 but after the item is created, I am trying to get staff data from List 2, such as name, job title, dept, to populate onto the newly created list 1 item based on employee Id . Unfortunately, with the flow I currently have set up, it is doing an apply to each for each of the 5000 get items from list 2. This is causing the flow to be very lengthy and time consuming. Do you have any solutions other than lookup columns?
Lookup columns won’t work in our scenario because we want to lock in the staff data values at the time the MS form was submitted.
Any guidance would be greatly appreciated!
Fantastic article. Thank you.
My ~14k count document library seems to be swirling at the Do Until, but I can't see why. I'm wondering if it is the below, although I'm seeing others note that this works for their document libraries despite the "get items" vs. "get files."
ID gt @{variables('varID')}
I'm wondering if my syntax is bad here or elsewhere.
can you help with flow which pick data from sharepoint excel and upload it into sharepoint list with update and create item in sharepoint list
Thanks in Advance
Thank you!I have run the flow but getting this error. The variable 'varItems' has size of more than '109649581' bytes. This exceeded the maximum size '104857600' allowed. Any Thoughts?
Thanks for the post, I have 5200 data in my list and I got in JSON only 4999 why this is so?
Thanks for this detailing Alex, after Do until step I added a step to create csv table but I'm unable to custom the required columns in this step.
Thanks for this post! For the filter query you had, will this work if I include another filter such as Title eq '{Text}' ??