in Flow, SharePoint

How to get more than 5000 items from SharePoint Online in Flow

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.

Write a Comment

Comment