Get All SharePoint List Items using REST API Pagination in Power Automate

If you work with a SharePoint list that contains a large number of items and you want to retrieve all the list items in Power Automate, this tutorial may be useful.

To retrieve all items from the SharePoint list, we use the Get Items action. This action will return 100 items by default. If we want more, we need to enable pagination. In any case, if you need to retrieve more than 100,000 records, the ‘Get Items’ action cannot be used in that scenario. In this case, we will use the REST API to retrieve all SharePoint list items in Power Automate.

In this tutorial, I will show you how to get all SharePoint list items using rest api pagination in Power Automate.

Read Large SharePoint List Items Using REST API with Pagination

For this example, I have a SharePoint list which have more than 11000 items. I will use pagination to get 5,000 records at a time until the entire set of items is retrieved.

In the SharePoint list, I have columns with the data types. Check the screenshot below.

• Order ID (unique identifier) – Single line of text
• Customer Name – Single line of text
• Order Date – Date and time
• Order Amount – Currency

Follow the steps below to get all the SharePoint list items:

1. Open the Power Automate Home page and choose Instant cloud flow with trigger Manually trigger a flow.
2. Add an ‘Initialize Variable‘ action and provide the name of the variable varItems, set the type as Array, and leave the blank in the value section. This variable will be used to store the list items.

3. Add another ‘Initialize Variable’ action and provide the name of the variable varURI, set the type as String, and in the value section, provide the below URI, which we will use to get items:

_api/web/lists/GetByTitle('Monthly Orders')/items?$select=OrderID,CustomerName,OrderDate,OrderAmount&$top=5000

This SharePoint REST API query is used to retrieve data from a list named “Monthly Orders”. It requests up to 5000 items (rows) from the list but only selects specific fields: OrderID, CustomerName, OrderDate, and OrderAmount. By using the $select parameter, it limits the response to just those columns, which helps improve performance and keeps the data clean.

Check out Check If SharePoint List Column Equals in Power Automate

Create Pagination to Get All SharePoint List Items

As I mentioned at the top, we will get 5000 records on a single page. To get the next 5000 results, we will use the Do until action:

1. Add the Do until action and provide the Loop until parameter like below:

variables('varURI') is equal to string('')

On the left side, add the varURI variable using dynamic content, then choose the is equal to operator, and on the right side, add a blank string expression.

2. Add a Send an HTTP Request to SharePoint action inside the Do Until loop and provide the below parameters:
   • Site Address: Select the SharePoint Site Address.
   • Method: Select GET as the method.
   • Uri: Provide the varURL variable from the dynamic content.

3. Add a Compose action and provide the below expression to add the list item in the varitems array variable:

if(
  equals(
    empty(variables('varItems')),
    true
  ),
  body('Send_an_HTTP_request_to_SharePoint')?['d']?['results'],
  union(
    variables('varItems'),
    body('Send_an_HTTP_request_to_SharePoint')?['d']?['results']
  )
)

This expression checks if the variable varItems is empty. If it is, it sets the variable to the current set of results returned by the SharePoint API. If varItems already contains data, it combines the existing data with the new results using the union() function. This ensures that all results are collected across multiple requests without duplication.

4. Add a Set variable action and select the variable name as varitems, then provide the output of the compose action in the value section:

5. Add another Set variable action and provide the below expression in the varURI string variable:

if(
  equals(
    body('Send_an_HTTP_request_to_SharePoint')?['d']?['__next'],
    null
  ),
  '',
  last(
      split(
        body('Send_an_HTTP_request_to_SharePoint')?['d']?['__next'],
        'PowerAutomateTutorial/'
      )
  )
)

This expression checks whether the SharePoint response includes a __next link, which indicates there are more items to fetch (pagination is needed). If the __next value is null, that means there are no more pages, so it returns an empty string.

But if __next has a value, it means there’s another page of data. In that case, the expression splits the URL of the __next link using ‘PowerAutomateTutorial/’ as a separator and returns the last part of the split.

5. On the outside of the Do until control action, add a compose action to the column the length of the array variable:

length(variables('varItems'))

6. Now save the flow and run it manually. After the flow runs successfully, click the compose action, and you can see the count of all list items.

Here, all 11,151 results are stored in the varItems variable. As shown in the screenshot above, the flow completed in just 11 seconds. If we used the Get items action instead, it would take at least 3 minutes.

This is how you can utilize REST API Pagination to retrieve SharePoint list items in Power Automate.

I hope you found this article helpful.

You may also like the following Power Automate tutorials:

• Check If a Field Is Blank in Power Automate
• Send Email Using REST API in Power Automate
• Check If an Input String Is an Integer Using Power Automate
• Delete Files From SharePoint Document Library Using Rest API in Power Automate