Best way to update SharePoint list with more than 5000 items using Power Automate

Best way to update SharePoint list with more than 5000 items using Power Automate

No comments

 3 total views,  3 views today

In this “Update SharePoint list with more than 5000 items using Power Automate” article, we will learn how to update a SharePoint list that has more than 5000 items using Power Automate.

If you have a large list that has more than 5000 items and later you add a new column to the list or you want to update some column values, we can use this Power Flow to update the list.

Update SharePoint list with more than 5000 items using Power Automate

Here is our Global-Superstore list, which has 7100 items.

SharePoint Online list with more than 5000 items
SharePoint Online list with more than 5000 items

For this demo, we have added a new column named “Updated” with a choice type (Yes and No values in it).

Add choice column in SharePoint Online list
Add choice column in SharePoint Online list

Here, what we will do is update the “Updated” column with “Yes” wherever it has “No.”

The Power Automate flow shown below will update the SharePoint online list flawlessly, even if it contains more than 5000 items.

For this demo, we have created a manually triggered flow, but you can also convert this to a scheduler flow.

Update more 5000 items SharePoint Online list using Power Automate
Update more 5000 items SharePoint Online list using Power Automate

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

Flow Explanation: Update SharePoint list with more than 5000 items using 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 – update more than 5000 items in Power Automate from the SharePoint list.

Do until loop in Power Automate to update 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

Update more 5000 items SharePoint Online list using Power Automate flow
Update more 5000 items SharePoint Online list using Power Automate flow

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 and Updated eq ‘No’. This filter will return items whose “Updated” value is No.
  • Top Count: Enter any number which is less than or equal to 5000.
Get Items filter Query - Update more 5000 items SharePoint Online list using Power Automate flow
Get Items filter Query – Update more 5000 items SharePoint Online list using Power Automate flow

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

Apply to each loop to update items in SharePoint Online using Power Automate

Add an apply to each loop control and add the below in “Select an output from the previous step.”


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

Apply to each loop to update items in SharePoint Online using Power Automate

Apply to each loop to update items in SharePoint Online using Power Automate flow
Apply to each loop to update items in SharePoint Online using Power Automate flow

Update item action inside the apply to each loop in Power Automate

Add a update item action inside the loop and pass the below parameter to it:

  • Site Address: Your site’s URL.
  • List Name: Your list’s name (Title).
  • ID: Select from the Get Items output.
  • Updated Value: Select as Yes (“The “Updated” is a choice column in the SharePoint list which has two values Yes and No).
  • Rest all columns are left blank because we do not intend to update them.

For the ID column parameter, you can pass the below value directly as well:


items('Apply_to_each')?['ID']

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

This step is optional, but it is recommended.

Add a Select action to filter columns from the Get Items query.

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


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

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

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

 

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

Update SharePoint list with more than 5000 items using Power Automate – Demo

Now, manually trigger the above flow. Wait for Sometimes, the waiting duration depends on the number of items on your list; in our demo, for about 7000 items, it took 00:58:32.

Your flow ran successfully - Update more 5000 items SharePoint Online list using Power Automate flow
Your flow ran successfully – Update more 5000 items SharePoint Online list using Power Automate flow

After successfully completing the flow execution, we could see that the “Updated” column had been updated with the value “Yes.”

Update SharePoint list with more than 5000 items using Power Automate - Demo
Update SharePoint list with more than 5000 items using Power Automate – Demo

Summary: What we had here (Update SharePoint list with more than 5000 items using Power Automate)?

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

  • 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 apply to each loop in Power Automate to update item in SharePoint Online list
  • 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