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.

If you call the same endpoint from JavaScript or a REST client instead of a flow, get SharePoint list items with the REST API walks through $select, $filter, $orderby, $top and the __next paging link.

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.</