Super Simple flow to get more than 5000 SharePoint Items

I’ve done a series of blog posts on how to get more than 5000 items from a SharePoint list using Power Automate. While helping someone else out I figured out a way to make it much easier.

If you’re not interested in knowing how it works, just download the flow and reconfigure it for your environment.

Overview

In this example the flow is triggered by PowerApps, but the same method could be used for any trigger. Let’s take a look at the complete flow and then I will explain each step in detail:

Step by Step Explanation

Here is a explanation of every step of the flow in the order they appear in the screenshot above

1. Trigger Action
   Is the flow trigger action. This could be PowerApps, the HTTP connector or anything that suits your situation.
2. Compose Action ‘querySettings’
   This is a compose action which specifies the query settings for the flow.
   
   It has two properties:
   • listName – Specifies the name of the SharePoint list that you want to retrieve items from.
   • fields – Is a comma separated list of fields that will be retrieved from the SharePoint list.
3. Send an HTTP Request to SharePoint ‘getLowestID’
   This step gets the ID of the first record in the SharePoint List.
4. Send an HTTP Request to SharePoint ‘getHighestID’
   Gets the ID of the last record in the SharePoint List.
5. Compose Action ‘threads’
   This flow is going to execute multiple simultaneous queries to SharePoint. Each of which will return a maximum of 5,000 records. Specify the number of threads you will need to cover the number of records that you have in total.
   For example, if you have 9,000 records you will need 2 threads. If you have 23,000 you will need 5 threads. For example:
   [ 0, 1, 2, 3, 4 ]
6. Compose Action ‘itemsPerThread’
   This action calculates the number of items to fetch in each thread. If you have 9,000 items it will get 4,500 items per thread. The expression is:
   add(div(sub(body('getHighestID')?['value'][0]['ID'],body('getLowestID')?['value'][0]['ID']), length(variables('threads'))),1 )
7. Apply to each
   The apply to each action is where the SharePoint queries take place. It is important to configure the concurrency options on this step to ensure it is set to at least the number of threads you have configured:
   
   Within the apply to each the following actions take place:
   1. Compose action ‘lowID’
      Calculates the lowest ID to find in the current iteration:
      add(body('getLowestID')?['value'][0]['ID'], mul(outputs('itemsPerThread'), item()))
   2. Compose action ‘highID’
      Calculates the highest ID to find in the current iteration:
      add(body('getLowestID')?['value'][0]['ID'], mul(outputs('itemsPerThread'), add(item(), 1))
   3. Send an HTTP Request to SharePoint
      This is where the actual SharePoint query takes place. Using the inputs from the previous steps. The expression in the Uri field is:
      _api/Web/Lists/GetByTitle('@{outputs('querySettings')['listName']}')/[email protected]{outputs('querySettings')['fields']}&$filter=ID ge @{outputs('lowID')} and ID le @{outputs('highID')}&$top=5000
      
8. Compose Action ‘Sample Output’
   This action isn’t required for the flow to run. It is used to output two records from the first iteration of the SharePoint API query. This can be used to generate the required JSON Schema in the final step.
   take(outputs('querySharepoint')[0]['body']['value'],2)
9. Response Action
   This is used to send the data back to PowerApps. You need to modify the Response Body JSON Schema. If you run the flow once you can use the output of the “Sample Data” step and use the “Generate from Sample” function.
   
   The body content unions all of the data that is returned from SharePoint into a single array. This will need to be adjusted according to the number of threads that you have configured:

union(
outputs('querySharepoint')[0]['body']['value'],
outputs('querySharepoint')[1]['body']['value'],
outputs('querySharepoint')[2]['body']['value'],
outputs('querySharepoint')[3]['body']['value']
)

Conclusion

This is an incredibly easy way to get more than 5000 items from a SharePoint list. It is also very fast. I was able to get 17,000 rows in 5 seconds and someone I am working with was able to get 5,000 rows in two seconds!

Download the Flow

This flow is simple enough that you don’t really need to understand how it works, you can just import it into your environment and reconfigure to your needs.

Download the flow here and import it into your environment.