Introduction
When we use the Get Items action or the Send HTTP Request to SharePoint, by default the returned items are limited to 100. We can increase the returned items in the Get Items action by specifying the Top Count field value and setting it to 5000. Any value beyond 5000 will still fetch you only 5000 items as it’s a design limitation at SharePoint Side.
Same way, in the case of using the Send HTTP Request to SharePoint action, we can use the $top=5000 odata parameter in the URI that we use in the action to get a max of 5000 items in a single call.
| _api/web/lists/GetByTitle(‘10000 Records List’)/items?$top=5000 |
Problem Statement
So what do we do in case we need to get more than 5000 items from the SharePoint list and process them within Power Automate. This is an inherent design limitation that dates back to the older SharePoint Versions which was put in place to ensure that the back-end SQL does not get throttled. In this article, we will see how to work around the design limitation using a Do Until Loop
Implementation
We have a list with 10000 items and we will see how to fetch all the 10K items inside Power Automate.
We have set up an Instant flow that can be manually triggered. We will be using a Do While loop to get all items from SharePoint in 2 Iterations of 5000. We will be using the below 3 variables to store the data that would be used/retrieved when the flow runs.
- varListItemsArray : This is an array variable that will act as the master array of all the items retrieved from SharePoint
- varLastItemID : This is an integer variable that is used to store the Item ID of the last item of a specific batch
- varIsReturnedItemCollectionEmpty : This is a Boolean variable used as the condition variable to exist the Do While Loop when the count of items returned from SharePoint is 0.
Next, we will add a Do Until Loop which will have an exit condition “varIsReturnedItemCollectionEmpty is equal to true”. The idea is to loop and use the Get Items actions to get batches of 5000(max allowed) list items. So in 2 iterations, we will be able to fetch 10K items. In the third iteration, the returned collection will be empty and this will result in the exit of the Do Untill Loop.
Double-clicking on the actions inside the loop, we have the Get Items action that gets 5000 items as we have mentioned Top Count as 5000. In the first iteration, it will return Items with ID 0 to 5000. So as to ensure that the second iteration fetches items with ID 5000-10000, we will be storing the ID of the last item in the current batch in the variable varLastItemID and specify the oData Filter query
| ID gt varLastItemID. |
This way every subsequent iteration will fetch the next 5000 batches of items.
So as to store the returns items inside the array, we will format the item collection using a Select action and use the Value output from Get Items action to map the field and values as below :
Next, we will add a compose action to append the output of the mapped field and values from the previous action to the array that is used to hold the list items. This way after every loop, the array will get appended with a batch of 5000 items from List.
| union(variables(‘varListItemsArray’), body(‘Select_-_Form_a_Collection_with_just_the_needed_Columns’)) |
The appended output of existing array content with the current iterations collection is then assigned back to the array to hold the master data collection.
Next, we add a condition check to see if the returned items collection is empty based on which we will assign the appropriate variables that will control the next iteration.
In the condition, we use the below expression to check if the Get Items action output is empty.
| empty(outputs(‘Get_items_-_From_the_SharePoint_List’)?[‘body/value’]) |
If the output is empty, we will set the varIsReturnedItemCollectionEmpty variable to true so that the loop with an exit in the next iteration. If the output is not empty, we will use the below expression to get the ID of the last item in the Output Value and assign it to varLastItemID variable so that we can use it in the Get Items action in the next iteration to fetch records having ID greater than this variable which will help us in getting the next batch of 5000 items.
| last(outputs(‘Get_items_-_From_the_SharePoint_List’)?[‘body/value’]).ID |
Finally, we use the Create file action to provision a JSON file in one drive by using the base64 converted value of the varListItemsArray
| base64(variables(‘varListItemsArray’)) |
Thus the overall flow will look like this:
Test the flow
Let’s manually trigger the flow, and this will iteratively call the Get Items action to fetch the data from SharePoint List and concatenate the results into an array which will be used for creating the master JSON Output file.
Taking a closer look at the Do Until Loop, it ran 3 times. First Iteration picked up 5000 items and it has assigned the ID of the last item as 5000 in the varLastItemID
Second Iteration has picked up the next set of 5000 items and assigned the last item id as 10000 to the variable
Since our list has only 10000 items, it will return 0 records this time and hence the Condition check evaluates to true and it enters the left branch where it sets the isReturnedCollectionEmpty variable to True which is the loop exit condition.
Summary
Thus we saw how we can retrieve more than 5000 items from a SharePoint List and club them together into one single data set which can be used for further processing.
