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

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

No comments

 7 total views,  7 views today

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.

Get more than 5000 items in Power Automate from SharePoint Online list
Get more than 5000 items in Power Automate from SharePoint Online 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 SharePoint Online list

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.

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

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

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.
Get Items action inside Do Until loop in Power Automate
Get Items action inside Do Until loop in Power Automate

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'])

Compose action to check item count in Power Automate Get Items query
Compose action to check item count in Power Automate Get Items query

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']

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

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 compose action for union function
Add compose action for union function

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 set variable action to assign the list items value to array
Add a set variable action to assign the list items value to array

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

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

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:

 

About Post Author

Do you have a better solution or question on this topic? Please leave a comment