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

39 Comments

  1. 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).

  2. 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….?

  3. 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.

  4. 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.

  5. 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.

  6. 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?

  7. 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

  8. 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′)

  9. 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 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…

  10. 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'

  11. 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.

  12. 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.

  13. 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

  14. 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"?)