Beyond the 5000 Limit: Fetching SharePoint List Items with Pagination in Power Automate

When you query a SharePoint list from Power Automate, the Get Items action returns only the first 100 items by default. More importantly, SharePoint imposes a hard limit of 5000 items per query for any action that uses the list view threshold — unless you enable pagination or bypass the threshold with the REST API. In practice, many business scenarios require processing thousands or even hundreds of thousands of records: migrating data, generating bulk reports, or synchronizing inventories. This article explores two reliable methods to retrieve more than 5000 items from a SharePoint list in Power Automate, covering the built-in pagination option up to 100,000 items and a custom REST API approach for unlimited datasets.

We’ll walk through a realistic example — a large product inventory list — and build two different flows step by step.

Prerequisites

• A SharePoint Online list with at least 10,000 items (you can use any list; for the example we’ll use a list called Contoso Equipment Inventory).
• Power Automate access with a premium or standard plan (the REST API method requires a Send an HTTP Request to SharePoint action, which is available in most plans).
• Basic familiarity with Power Automate expressions and flow building.

Our Scenario: Contoso Equipment Inventory

For this guide, let’s imagine Contoso maintains a SharePoint list of all their office equipment. The list contains these columns:

To follow along, create a list named Contoso Equipment Inventory and populate it with at least 5000–10,000 records. You can generate test data using a script or simply reuse an existing large list.

Method 1: Built‑in Pagination in the Get Items Action

The easiest way to exceed the 5000‑item barrier is to use the standard Get Items action with two adjustments: increase the Top Count and enable Pagination.

Step 1 – Create the Flow

Create an instant cloud flow with a trigger of your choice (e.g., Manually trigger a flow). Add the SharePoint – Get Items action and select your Contoso Equipment Inventory list.

Step 2 – Increase Top Count

In the Get Items action settings (the ellipsis menu), locate the Top Count field and set it to 5000. This tells SharePoint to return up to 5000 items in each response batch.

Step 3 – Enable Pagination

Still in the Get Items action, open the Settings tab. Under Pagination, toggle Pagination to On and set Threshold to the maximum number of items you want to retrieve. The maximum allowable threshold is 100,000 — beyond that, you must use the REST API (Method 2).

Step 4 – Verify the Results (Optional)

To confirm the flow retrieved all items, add a Compose action after Get Items with this expression:

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

Note: The exact action name may differ — adapt the expression accordingly.

Step 5 – Run the Flow

Save and run the flow. If your list has 12,000 items, you should see 12,000 in the Compose output. The pagination mechanism works transparently, fetching batches of 5000 until the threshold is met.

Limitations of Method 1

• Maximum threshold: 100,000 items.
• For every 5000 items, your flow may hit API throttling limits if run frequently.
• You cannot customise the columns retrieved or apply server‑side filters within the Get Items action beyond what the UI offers.

Method 2: REST API Pagination for Unlimited Items

When you need to process more than 100,000 items — or you require full control over the request — use the SharePoint – Send an HTTP Request to SharePoint action together with a Do Until loop. This approach uses the SharePoint REST API’s pagination (__next link) and can theoretically retrieve an unlimited number of records, limited only by the flow’s execution time and the API’s per‑request quotas (still 5000 per page).

Step 1 – Initialise Variables

Add two Initialize variable actions:

• varInventoryItems — Type Array, leave the default value empty. This will collect all items across pages.
• varSharePointUri — Type String, set to the initial REST API endpoint. For the Contoso Equipment Inventory list, the URI looks like:

_api/web/lists/GetByTitle('Contoso Equipment Inventory')/items?$select=ID,EquipmentName,Category,PurchaseYear,Location&$top=5000

Adjust the $select query to include only the columns you need; this improves performance and reduces payload size.

Step 2 – Create the Do Until Loop

Add a Do Until control action. Configure the loop to run until varSharePointUri equals an empty string:

• Left side: @variables('varSharePointUri')
• Operator: is equal to
• Right side: @string('')

Step 3 – Send HTTP Request to SharePoint

Inside the loop, add the SharePoint – Send an HTTP Request to SharePoint action. Use these settings:

• Site Address: (your SharePoint site)
• Method: GET
• Uri: @variables('varSharePointUri')

In the Headers section, add:

Step 4 – Parse the JSON Response

Add a Data Operations – Parse JSON action and set the Content to the body of the HTTP request. Generate the schema from a sample payload (the easiest way is to run the flow once, copy the output, and use the Generate from sample option in Parse JSON).

Step 5 – Merge the New Items into the Array

Because you cannot directly reference a variable inside itself in a Set Variable action, use a Compose action to prepare the merged array:

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

Replace 'Parse_JSON:_Items' with the actual name of your Parse JSON action.

Then add a Set Variable action that sets varInventoryItems to the output of that Compose.

Step 6 – Retrieve the Next Page Link

Still inside the loop, add a second Set Variable action for varSharePointUri. Use this expression to extract the __next link:

if(
equals(
  body('Parse_JSON:_Items')?['d']?['__next'],
  null
),
'',
last(
  split(
    body('Parse_JSON:_Items')?['d']?['__next'],
    'Contoso%20Equipment%20Inventory/'
  )
)
)

This splits the __next URL at the list‑specific part and returns the remainder. If there is no next link, the variable becomes empty and the loop ends.

Step 7 – (Optional) Process or Export the Array

After the loop completes, you can use the varInventoryItems array to write to another system, create a CSV, or perform further transformations.

Performance and Throttling Considerations

• Each Send an HTTP Request action costs an API call; use the fewest $select columns possible.
• If your flow runs frequently, consider adding a Delay inside the loop to reduce throttling risk.
• For truly huge lists (millions of items), break the work into multiple flows and use a scheduling pattern.

Common Mistakes and How to Avoid Them

Which Method Should You Choose?

• Method 1 (Get Items + pagination) is perfect for lists with up to 100,000 items and when you don’t need custom column selection beyond what the UI offers. It’s simpler, faster to build, and easier to maintain.
• Method 2 (REST API + Do Until) is the right choice for list exceeding 100,000 items, or when you need to fine‑tune the query (e.g., additional $filter, $expand, or select only a few columns). It’s more flexible but also more complex.

Final Recommendation

Always start with the built‑in pagination – it covers 95% of real‑world scenarios. If you hit the 100,000 cap or need advanced REST features, upgrade to the custom pagination pattern. Whichever path you choose, always test with a moderately large list (like 10,000 items) before deploying to production.

References

• Matthew Devaney, Get Over 5000 Items From A SharePoint List In Power Automate — Original article
• Microsoft Learn, Work with SharePoint list items using REST — Documentation
• Microsoft Learn, Pagination in Power Automate — Documentation