Get Over 5000 Items From A SharePoint List In Power Automate

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 ItemsGet Items From SharePoint And Increase Top Count To 5000Run The Flow To Get Over 5000 List Items

Method 2: SharePoint HTTP Request With Pagination
• Initialize Variables For List Items and SharePoint URIUse Pagination To Get Over 5000 SharePoint List ItemsSend An HTTP Request To Get SharePoint List ItemsStore The Array Of SharePoint List Items In A VariableFollow The Next Link To Get The Next Page Of ResultsRun The Flow To Get Over 5000 Items From A SharePoint ListCompleted 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


IDCarYearCarMakeCarModel
12,009MazdaMX-5
21,985HondaAccord
32,001FordWindstar
41,994MitsubishiEclipse
52,003LamborghiniGallardo
100002,024HyundaiElantra



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.

HeaderValue
Acceptapplication/json;odata=verbose




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.

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.

Matthew Devaney

Subscribe
Notify of
guest

8 Comments
Oldest
Newest
Inline Feedbacks
View all comments
Dean
Dean
10 days ago

Hi Matt,

Thank you for the very useful article. For the Send HTTP request to SharePoint method do you not need to map crawled properties to managed properties in the SharePoint search schema? I have done something similar in the past using the SharePoint seach API but needed to set up the managed properties.

Dean
Dean
10 days ago

When using SharePoint search they are used to map columns (crawled properties) to managed properties. The columns can be used in search refiners. If using the SharePoint search API via Power Automate these need to be set up if we want to return metadata in those columns.

Dean
Dean
9 days ago
Reply to  Dean

// List search endpoint

_api/web/lists/GetByTitle(‘Addresses’)/items?$top=100&$filter=Title eq ‘Some Address’

// SharePoint search API endpoint

_api/search/query?querytext=’Some Address AND (Path:/sites/YourSite/Lists/Addresses)’&rowlimit=100

The SharePoint search API is more powerful and can be scoped across multiple lists. I was always under the impression it works better with larger lists but that may no longer be the case. It does rely on content being crawled and indexed and having the managed properties set up.

Xavier
Xavier
10 days ago

Hi Matt,

Thank you for sharing those tips! I found them really helpful. I do have a few questions for clarification, though:

  1. In a few places, the articles mention fetching 100,000 items. Was that meant to be 10,000 instead?
  2. Have you tried applying this method to a more complex table? The example uses items with only string-type data, so I’m curious how the flow performs when the records include images or other more complex column types.
  3. On a related note, how would you handle such scenarios for tables with more complex records (as in the previous question), considering the 100MB maximum size for variables in Power Automate?

Looking forward to your insights. Thanks again! 😊

Federico
Federico
9 days ago

Hi Matthew,
Thank you for this; it’s really useful.
I have a question, and maybe you can help me. Is it possible to filter the Send an HTTP Request to SharePoint action?
I’m trying to use something like this:
_api/web/lists/GetByTitle('spLIST')/items?$select=INVENTARIO,Title,CODE,LOTE,ALMACEN&$filter=INVENTARIO eq 1&$top=5000
However, it doesn’t work, and I don’t receive any errors. Power Automate just keeps running without any updates.

Jiboxy
Jiboxy
6 days ago

the compose expression is invalid
length(outputs(‘Get_items_2:_LISTNAME_’)?[‘body/value’])

Osasere
Osasere
23 hours ago
Awaiting for approval

Thanks Matt for the tips. What if i have 500k records and I want to duplicate them to another SharePoint list?