Get Over 5000 Items From A SharePoint List In Power Automate

The Power Automate SharePoint – Get Items action can return over 5000 SharePoint list items by increasing the top count and using pagination. Or if we have a large SharePoint list with over 100,000 items then we must use a SharePoint – Send An HTTP Request action instead. Both methods are covered in this article.

Table of Contents

Method #1: Increase Top Count And Enable Pagination
• Create A SharePoint List With Over 5000 Items
• Get Items From SharePoint And Increase Top Count To 5000
• Run The Flow To Get Over 5000 List Items

Method 2: SharePoint HTTP Request With Pagination
• Initialize Variables For List Items and SharePoint URI
• Use Pagination To Get Over 5000 SharePoint List Items
• Send An HTTP Request To Get SharePoint List Items
• Store The Array Of SharePoint List Items In A Variable
• Follow The Next Link To Get The Next Page Of Results
• Run The Flow To Get Over 5000 Items From A SharePoint List
• Completed Flow Code: Get Over 5000 Items From A SharePoint List

Method #1: Increase Top Count And Enable Pagination

The easiest way to get over 5000 records from a SharePoint list is to use the SharePoint Get Items action. To do this increase the Get Items action Top Count to 5000, enable pagination, and update the threshold to 100,000 items. This will make it possible to fetch up to 100,000 items.

Create A Large SharePoint List With Over 5000 Items

We need to build a SharePoint list with over 5000 items. Make a new list named Car Inventory and include the following columns:

• ID – unique identifer
• CarYear – number
• CarMake – single line of text
• CarModel – single line of text
  
  
  

Populate the SharePoint list with items. In my example I created a list with 10000 items.

Get Items From SharePoint And Increase Top Count To 5000

In Power Automate create a new flow with an instant trigger named Get Over 5000 SharePoint List Items.

Add a SharePoint – Get Items action to the flow and select the Car Inventory list. The Get Items action will only fetch 100 list items by default. We can increase the maximum number of list items retrieved to over 100,000 items by changing two settings.

Increase the Top Count to 5,000. This will allow us to get 5,000 records on each page of results. It is the maximum value allowed in the Top Count field.

Then go to Settings for the Get Items action and enable pagination. Set the threshold to 100,000 records. It is the maximum value for this field. The flow will now get up to 100,000 records by requesting 5,000 records at a time.

Finally, we want to verify the number of SharePoint list items retrieved. Insert a Compose action into the flow.

Then add this Power Automate expression to calculate the number of items returned.

length(outputs('Get_items:_Car_Inventory')?['body/value'])

Run The Flow To Get Over 5000 List Items

That’s all it takes to get over 5,000 list items from SharePoint. Give the flow a test run to ensure it works. Then check the Compose action to validate the number of list items fetched.

Method #2: Increase Top Count And Enable Pagination

If we need to get more than 100,000 list items it is only possible by using the SharePoint – Send An HTTP Request action. We will use pagination to get 5,000 records at a time until the entire set of items is retrieved. The SharePoint – Send An HTTP Request action is a standard action.

Initialize Variables For List Items and SharePoint URI

We will start by creating two variables in our flow: varListItems and varSharePointUri. Start a new Power Automate flow with an instant trigger and add a Initialize Variable action. Set varListItems variable to Type Array and leave the value blank. This variable will store the SharePoint list items we retrieve.

Add another Initialize Variable action. Name the variable varSharePointUri, give it a Type String and input the value shown below.

The value represents a SharePoint REST API endpoint to get the items in a SharePoint List named Car Inventory. It retrieves only the selected columns Id, CarYear, CarMake & CarModel and gets the top 5000 results. 5000 results is the maximum that can be returned in a single call. To get all records in the SharePoint list we will need to make multiple API calls.

_api/web/lists/GetByTitle('Car Inventory')/items?$select=Id,CarYear,CarMake,CarModel&$top=5000

Use Pagination To Get Over 5000 SharePoint List Items

The SharePoint REST API returns maximum of 5000 records on a single page (API call). To get the next 5000 results we must ask the REST API for the next page. This programming technique is called pagination.

Insert a Do Until loop into the flow. Set the left side of the comparison to the variable varSharePointUri, the comparison operator to is equal to and the right side to an empty string. We will keep on requesting more pages from SharePoint until there are no more remaining.

Use this code to define an empty string.

string('')

Send An HTTP Request To Get SharePoint List Items

We will use a Send An HTTP Request To SharePoint action to get items from the SharePoint list because it is faster than the standard SharePoint – Get Items action and it returns the next page link when there are more than 5000 results.

Add a Send An HTTP Request To SharePoint action inside of the Do Until loop, use the GET method to retrieve list items and supply the variable varSharePointUri inside of the Uri field.

Include the Accept header to indicate that the response should be in JSON format.

Store The Array Of SharePoint List Items In A Variable

Once the list items are returned from SharePoint we to add them to the varListItems variable. Start by adding a Data Operations – Parse JSON action and use the Body of the Send An HTTP Request To SharePoint action as the Content. Generate the schema of the JSON from a sample.

Then we must insert a Data Operations – Compose action to assemble the array of SharePoint list items before storing in in a variable. We cannot do this directly in a Set Variable action because self-referencing a variable is not allowed in Power Automate.

Use this code in the Inputs field of the Compose action. The Power Automate expression checks to see if varListItems is empty. An empty variable means this is the first page of SharePoint list item results and we only need to add those results to the variable.

When the varListItems variable is not empty we need to take a different approach. We must append the list items we just retrieved to the list items already contained in the list variable. We do this by using the Power Automate Union function.

if(
  equals(
    empty(variables('varListItems')),
    true
  ),
  body('Parse_JSON:_List_Items')?['d']?['results'],
  union(
    variables('varListItems'),
    body('Parse_JSON:_List_Items')?['d']?['results']
  )
)

Then insert a Set Variable action after the Compose action. Use the Outputs of the Compose action to update the variable with the array of List Items.

Follow The Next Link To Get The Next Page Of Results

The final action inside of the Do Until loop checks the output of the Send An HTTP Request To SharePoint action to see if there is a link to the next page of results and stores it in a variable. Add a Set Variable action and choose the varSharePointUri variable.

Write this code inside of the value field. If no next page link exists in the results, the varSharePointUri variable will be set to and empty string and the Do Until loop will exit.

if(
  equals(
    body('Parse_JSON:_List_Items')?['d']?['__next'],
    null
  ),
  '',
  last(
      split(
        body('Parse_JSON:_List_Items')?['d']?['__next'],
        'MatthewDevaneyBlog/'
      )
  )
)

Here is an example of what a next link looks like:

Run The Flow To Get Over 5000 Items From A SharePoint List

We are finished building the Power Automate flow to get over 5000 items from a SharePoint list. Test the flow to ensure it works. The flow in this example returned all 10000 results in 6 seconds!

All 10000 results are stored in the varSharePointUri variable.

Completed Flow Code: Get Over 5000 Items From A SharePoint List

An image containing all flow actions is shown below.

Questions?

If you have any questions or feedback about Get Over 5000 Items From A SharePoint List In Power Automate please leave a message in the comments section below. You can post using your email address and are not required to create an account to join the discussion.