Batch Update of SharePoint List item using Power Automate

As I’m looking to find a way to optimize the application to update an item from Excel table to SharePoint, I stumbled upon a great article created by Paulie, MVP. The article describes creating a SharePoint List item using the batch requests with the REST APIs implemented in Power Automate. (Check out the reference to learn more)

Before I start the article, I would like to thank Paulie for helping me during this time when I built this Flow. I had a great one-on-one MS Teams session with him when I got stuck with an issue with the REST API and got some tips and tricks from him. Thank you so much for your great help!

So in this article, we will do a batch update to a specific item in SharePoint. I modified the action and expressions in Flow, in which it will update a particular item in the list.

Requirement:

The Excel file is stored in a document library. If the data from Excel exists in the SharePoint list, update the Start Date and Description of the item. Let’s assume the ‘Title’ field has a unique value. We will use the BATCH API to update the item.  

In the ‘Actions in Flow’ section, I’m going to use this format ex. [Compose] settings which means [Action Name] Renamed Action.

Actions in Flow:

• List rows present in a table
  • Location – SharePoint Site
  • Document Library – Document library name
  • File – the Excel file data that you need to update in lists
  • Table – table created in the Excel
  • DateTime Format – if you have a Date Time column, use the ISO 8601 settings so that you don’t need need to use addDays(…) to calculate the date – got this tip from Hiro-san, MVP

• Initialize variable 
  • Name - LoopControl
  • Type – Integer
  • Value - -1

• Initialize variable - GetItems
  • Name – GetItems
  • Type – Array
  • Value - []

• [Compose] settings
  • Input your siteAddress and listName; leave the batchsize as is
    • siteAddress – URL SharePoint site
    • listName – Name of your list where you are going to update the item
    • batchsize – 1000

• Get items – SharePoint list item; Filter the item by 'Title' equals to the 'Title' field in Excel (assuming the 'Title' is unique value)

• Append to array variable - GetItems
  • Create a new array with the data comes from the ‘Get Items’ in SharePoint and then mapped the properties that you need to update in the list from the Excel.
  • "__metadata":{"type":"SP.Data.[Your List Name]ListItem"} – this is line of expression is important and you must add this

• Compose – GetItems

• Filter array – filtering out the empty array

• Condition – check the number of data in ‘Filter array‘
  • If it is not equal to 0 update the item

• [Compose] Template – this is the batch API template
  • Use Delete in the REST API when you want to delete a specific item in the list

---

• Do until – reiterate the number of the data that needs to update from the number of data in LoopControl
• [Select] ReplaceRows – replacing the |Row Data| and |ID| from the Template Output and replace it with the data that we get in the 'Filter array'.

*From: take(skip(body('Filter_array'), mul(outputs('settings')['batchSize'], iterationIndexes('Do_until'))), outputs('settings')['batchSize'])

* Map: replace(replace(outputs('Template'), '|RowData|', string(item())), '|ID|', string(item()['ID']))

• Set LoopControl variable – count the data in the ‘ReplaceRows‘

• [Compose] batchData

• [HTTP request to SharePoint] SendBatch – this is where it will send an update to SharePoint with the batch update action

• [Compose] Results – return the string version of the content in the SendBatch request.

---

Conclusion:

Working with REST API is daunting, but you could do many things, especially when you want to optimize your flow. Another tip to maximize your flow is to minimize using ‘Apply to each' in your flow and using variables. 😉

Let me know if you have questions and have fun building! 🥂

You can download the flow here.

Reference:

• Make batch requests with the REST APIs
• Power Automate Flow batch create SharePoint List Items by Paulie

Featured Image by ThisIsEngineering from Pexels