Best way to get more than 5000 items in Power Automate from SharePoint Online list

In this “Get more than 5000 items in Power Automate” article, we will learn how to get more than 5000 items in Power Automate from a SharePoint Online list. We can easily retrieve list items with fewer than or equal to 5000, but it will be challenging when you have a list with more than 5000 items. As we know in SharePoint, we have a threshold limit of 5000 items while reading a list.

We need to handle it differently to get list items for more than 5000 items. Here in this article, we will focus on how to retrieve list items with more than 5000 items using Power Automate.

Get more than 5000 items in Power Automate from SharePoint Online list

Below is the Power Automate flow to get more than 5000 items from the SharePoint Online list.

For this demo, we have created a manually triggered flow, but you can also create a scheduler flow if you want to send some periodic reports to your manager automatically based on certain statuses in the list.

In this section we will explain each actions in the above flow.

Flow Explanation: Get more than 5000 items in Power Automate

Below are the explanation step by step:

Variable initialization:

Initialize the below variables:

• arrItemsFromSPList – Type Array
• intLastItemIDfromSPList – Type Integer, default value 0
• boolVarIsArrayEmpty – Type Boolean, default value false

Variable initialization – Get more than 5000 items in Power Automate from the SharePoint list.

Do until loop in Power Automate to get more than 5000 items

Add Do Until loop control. And add the below:

• boolVarIsArrayEmpty is equal to true
• Change limit counts to 5000 from 60 (default).

Then, add the Get items action and followed by other actions shown below.

Get Items action inside Do Until loop in Power Automate

Add a Get Items action inside the loop and pass the below parameter to it:

• Site Address: Your site’s URL.
• List Name: Your list’s name (Title).
• Include Nested Items: Select “No” when you are getting data from a list or “Yes” when you are reading data from a document library. In general, for lists and document libraries, we need to pass this as “no” or “yes.”
• Filter Query: ID gt intLastItemIDfromSPList. Here you could add to and/or condition the list to meet other criteria. If you’re working with an approval list, for example, you can include “and status,” such as “Approved.” In this case, your approval list has 25000 items, and you want to get only the approved list items.
• Top Count: Enter any number which is less than or equal to 5000.

Total item count length from the Get Items action query in Power Automate

Add a compose action to check the total item count returned by the Get Items query.

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

Select data operation in Power Automate (Power Automate map values)

This step is an optional step but good to have.

Add a Select action to filter columns in Power Automate from Get Items Query.

Add the following to the From field, or choose directly from the Dynamic Content tab.

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

Technically, the Get Items query will return all columns that you may not require, slowing down query execution. So, in the select data operation, map the columns you need, which will double your query performance, and you could also control whether sensitive data is exposed.

Add compose action for union (union function in Power Automate)

Add a compose action for the union function. Add the below for the compose action inputs:

union(variables('arrItemsFromSPList'),body('Select'))

Add a set variable action to assign the list items value to the array variable

Add a set variable action to assign the list item’s value to the array; it is the output of the previous compose action.

outputs('Compose')

Add a condition control to check Get Items query result is empty

Add a conditional control to check to Get Items if the query result is empty. To check this, perform the below steps:

empty(body('Get_items')?['value']) is equal to true

I

If the above condition returns true, set the variable value for “boolVarIsArrayEmpty” to true, and on the false side, set the variable value for “intLastItemIDfromSPList” as below:

last(body('Get_items')?['value']).ID

Summary: What we had here (Get more than 5000 items in Power Automate from SharePoint Online list)?

Thus, in this article, we have learned the following with respect to reading more than 5000 items in Power Automate from the SharePoint Online list:

• How to retrieve more than 5000 items in Power Automate from a SharePoint Online list
• How to use the Do Until loop in Power Automate
• How to use the filter query in the Get Items action in Power Automate
• How to use the compose data operation action in Power Automate
• How to use the Select data operation action in Power Automate to map the SharePoint columns
• How to use the array variable in Power Automate
• How to use the Union() function in Power Automate

See Also: Power Automate Tutorial

You may also like the below Power Automate tutorials:

• 2 ways in SharePoint Online check in all files using Power Automate quickly
• Quickly add multiple items in SharePoint list using power automate – more than 5000
• Instantly Delete items from two list using Power Automate: Delete SharePoint list item based on another list
• 1 Best way to clone SharePoint list: Synchronize two SharePoint lists using Power Automate
• Promptly generate unique ID in SharePoint list using Power Automate – Microsoft 365
• Best way update list item in SharePoint using Power Automate – Microsoft 365
• 3 ways instantly disable Action in Power Automate
• Instantly in 2 steps Copy and Paste action in Power Automate
• Discover exception handling in power automate using Try Catch Finally Scope – in 3 steps instantly
• Instantly Solved: Error from token exchange: Bad Key authorization token in Power Automate – Microsoft 365
• Scheduler Power Automate: Move Old documents in SharePoint Automatically using Scheduled Cloud Flow
• Conditional trigger power automate: Trigger Power Automate (Flow) based on condition in SharePoint Online – Verified
• Power Automate Send Email: Difference between “Send an email notification (V3)” and “Send an email (V2)”
• HTML Table in Power Automate: Learn HTML table formatting using Microsoft Power Automate step by step
• In just 2 marvelous steps export import power automate flow quickly
• Get started with Power Automate

About Post Author

SP Doctor

I am your SharePoint and Power Platform doctor and guide. Sharing knowledge with my fellow colleagues, friends, and the tech community makes me utmost happy in my job.

See author's posts