Batch update with external data template flow.
Explanation Video: https://youtu.be/l0NuYtXdcrQ
Compared to the using the basic Apply to Each, Get items, & Update item approach on large amounts of data, this method requires a small fraction of the API calls towards the daily action limit and drastically reduces flow run-times.
It's currently set to take data from any Excel sheet and update records in SharePoint with corresponding data. It works with any potential Power Automate data-source, including HTTP API GET requests, but Excel provides a simple example.
Part of it works like a Vlookup function where it identifies if a row of updated data in Excel has some set values in it and matches it to the SharePoint record with those set values through the SharePoint ID. Then it uses the batch update method to update those SharePoint records.
The flow can easily expand to any size of SharePoint list by adding more Get items actions and batch update parallel branches. If speed is a concern for anyone, there are ways to make the Get items actions all load in parallel too (up to 50 branches). It's really only limited by how much effort people want to put into their flows & lists.
Version 1.5
This version makes it easier to handle cases where the list name may have changed since its creation and moves a few of the primary key column matching inputs to the 'settings' compose action so users don't have to look through & edit the more complicated expressions to set up the flow.
Google Drive Link to Flow Zip Files: https://drive.google.com/file/d/10p7EB730xsEj-azVYuTIuu8dS0w-AflR/view?usp=sharing
Google Drive Link to Text File to a Scope Action Containing The Flow: https://drive.google.com/file/d/1BVGoeM5mykYlMobAyFkhuLRh3r7jMSLz/view?usp=sharing
Version 1
Download The Template Batch Update Flow
Google Drive Link to Flow Zip Files: https://drive.google.com/file/d/10gFkycdx6zpRfrI-s_jCDwIK6dpyyDqk/view?usp=sharing
Google Drive Link to Text File to a Scope Action Containing The Flow: https://drive.google.com/file/d/1e6-INUykIT22ppVh5m4kxz8us_7qXy7q/view?usp=sharing
***LISTS WITH UNDERSCORES _ IN THE TITLE MAY CAUSE ERRORS WITH EARLIER VERSIONS OF BATCH ACTIONS.***
If you have a list with underscores in the name, then in TachyTelic.Net's batch create you will want to go to the __metadata line on the GenerateSPData select action and substitute the expression
json(concat('{"type":"SP.Data.', replace(outputs('settings')['listName'], ' ', '_x0020_'), 'ListItem"}'))
for
json(concat('{"type":"SP.Data.', replace(replace(outputs('settings')['listName'], '_', '_x005f_'), ' ', '_x0020_'), 'ListItem"}'))
TachyTelic.Net Blog & Videos
Batch Create Flow
Blog: https://www.tachytelic.net/2021/06/power-automate-flow-batch-create-sharepoint-list-items/
Video: https://youtu.be/2dV7fI4GUYU
Batch Delete Flow
Blog: https://www.tachytelic.net/2021/06/power-automate-delete-sharepoint-items/
Video: https://www.youtube.com/watch?v=2ImkuGpEeoo
Formulas For Random Number Columns
SharePoint Rand1To50 Column Calculated Default Value Formula:
=INT(INT(RIGHT(NOW(),2))/2)
Excel Random Column Formula:
=ROUNDDOWN(((Rand()*100)+1)/2, 0)
If the batch call succeeds, but individual rows fail to load to SharePoint, then the flow will not throw an error. I personally change out the Results Compose actions with Append to variable actions and use an expression that returns the results text if it identifies an error. Then at the end of the flow I have a condition checking the length of that variable. If it has some text/length to it, then something failed and it sends me an email with a link to the flow run.
Expression to conditionally return results on failure:
if(
or(
or(
or(
or(
or(
or(
or(
or(
contains(base64ToString(body('sendBatch')['$content']), '400 Bad Request')),
contains(base64ToString(body('sendBatch')['$content']), '401 Unauthorized')),
contains(base64ToString(body('sendBatch')['$content']), '403 Forbidden')),
contains(base64ToString(body('sendBatch')['$content']), '404 Not Found')),
contains(base64ToString(body('sendBatch')['$content']), '408 Request Timeout')),
contains(base64ToString(body('sendBatch')['$content']), '409 Conflict')),
contains(base64ToString(body('sendBatch')['$content']), '500 Internal Server Error')),
contains(base64ToString(body('sendBatch')['$content']), '502 Bad Gateway')),
base64ToString(body('sendBatch')['$content']),
'')
watch?v=l0NuYtXdcrQ
