Cracking the 5000 items limit

Ever tried fetching more than 5000 items from SharePoint in one go with 'Send an HTTP SharePoint Request'?

Spoiler alert: SharePoint isn’t a fan of binge requests! Instead of relying on pagination, I’ve cracked a smarter way—calculating the difference between the first and last item IDs, then splitting the load into manageable threads inside an Apply to Each loop.

Curious how it works?

The flow wan be trigerred from a Power App to fetch files from a SharePoint document library or a huge SharePoint list:

1 - The first action after the trigger will be a Compose action that will contain a Json holding the request settings:

{
  "listName": "[YouLisName]",
  "fields": "ID,[fields you want to fetch]"
}        

2 - The second step will be looking for the lowest ID of the items using Send an HTTP request to SharePoint action.

• Use the GET Method

_api/Web/Lists/GetByTitle('@{outputs('RequestSettings')['listName']}')/Items?$select=ID&$filter=ID gt 0&$top=1&$orderby=ID asc        

Explaining the Uri:

• The list name will be dynamically taken from your compose action

 '@{outputs('RequestSettings')['listName']}'        

• It will look for the First ID that is the superior to zero

 $select=ID&$filter=ID gt 0&$top=1&$orderby=ID asc        

3- The third action will be quite similar and it will fetch the the highest ID in your data source

• you will have to replace the filter as follows

$filter=ID gt 0&$top=1&$orderby=ID desc        

4- The next step is to Initialize a varible of type Array with the below value

[ 0, 1, 2, 3]        

This variable create threads that will devide the load on our REST API call to perfectly manage our large dataset

5- A second Compose action will be needed to calculate the number of items per thread, the following experssion is to be used

add(
    div(
        sub(
            coalesce(body('LastID')?['value'][0]['ID'], 0),
            coalesce(body('FirstID')?['value'][0]['ID'], 0)
        ),
        length(variables('threads'))
    ),
    1
)
        

Let's explain it a bit:

The function calculates the average difference between the IDs of the last and first items, dividing it by the number of threads, and then adds 1 to the result.

body('LastID')?['value'][0]['ID'] and body('FirstID')?['value'][0]['ID'] are the values from our 2 first API calls.

6- Now to have all the items from the gigantic SharePoint data source we need to impelement an Apply to each loop that contains the threads variable output

Inside the loop we have to add to Compose actions that will nest the First ID and the Last ID

Expression FirstID

add
(
  body('FirstID')?['value'][0]['ID'], 
  mul(outputs('itemsPerThread'), item())
)        

Expression LastID

add
(
  body('getLowestID')?['value'][0]['ID'], 
  mul(outputs('itemsPerThread'), add(item(), 1))
)        

Then we will finally make our main REST API call using Send an HTTP request to SharePoint action.

Uri:

_api/Web/Lists/GetByTitle('@{outputs('RequestSettings')['listName']}')/Items?$select=@{outputs('RequestSettings')['fields']}&$filter=ID ge @{outputs('FirstID')} and ID le @{outputs('LastID')}&$top=5000        

Make sure to apply the same headers as the screenshot to avoid gathering unnecessary metadata.

If you want to send back all the data to your Power App or for any other use, you can Initialize another Array variable and the append it inside the loop.

Was it helpful? Have your own tips or questions? Let’s connect and share insights!"