SharePoint Batch Update V1

Significant improvements have been made to this template since this video was made several years ago. Please visit the updated download & community forum page for the new version download & new video.

Download & Community Forum Post: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Batch-Update-Create-and-Upsert-SharePoint-Lists/td-p/1365410

New Video: https://youtu.be/QCkjQy6sHZg

Version 1
This demonstrates how to use SharePoint Batch API calls in Power Automate to quickly update thousands, hundreds of thousands, or millions of records in SharePoint while conserving Power Automate action API calls.

Download & Community Forum Post: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Batch-Update-SharePoint-List-With-External-Data/td-p/1365410
(New Version Now Available)

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

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.

***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”}’))

#PowerPlatform #Office365 #Excel #LowCode #Databases #Automation #API #DataTransfers #Data #CitizenDeveloper #Flows #Microsoft365 #MicrosoftExcel #microsoftsharepoint #Microsoft