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
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
See @DamoBird365 go through a join / merge example similar to the Reformat JSON pieces of this template here: https://youtu.be/R_bD8nAoJ8Y
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
Thanks for what must have been a very laborious project in putting this together.
I am a one-person IT department within a one-person operation. And I have no IT background, just learning while doing. With those disclaimers, here is my issue: I am having no success in making any updates in SP despite what I think is a correctly running flow.
Project: I have many thousands of billing records being transferred from paper format to SP list. Currently the OCR etc is working well enough that I can now take raw data items like date of birth as scanned (in a variety of formats) and break them down to allow for standardized formats across the data set. This is just one example of many such data items, which would all need to be converted in parallel. While I was comfortable doing this work in Excel it would be more efficient to use an update function to the list itself as in the example below.
This is from a test list of 10 rows. The purpose of the test is getting it to work on a limited set of changes, and then it will be adapted to the complexities of the whole data set. I used HTTP GET request to load the rows, where I take the given field 'RawDOB' (from OCR, eg 2000-JAN-01) and manipulate to fill 3 blank fields 'DOBYear' (2000), 'DOBMonth' (01), 'DOBDay' (01). This all works fine.
The issue arises when I go to update the list rows in SP with these new fields. The unique identifier here is the field 'Document ID' (aka 'Document_x0020_ID') which identifies the original paper label OCR in SP. The update array for each row is { ID, RawDOB, DOBYear, DOBMonth, DOBDay, DOB_Id (which is the same unique identifier) }. Note I am using the array notation here informally for explanatory purposes.
My settings action (renamed settings_DOB for this particular task) looks like this:
{
"siteAddress": "https://YYYYYY.sharepoint.com/sites/ZZZZZZ/",
"SPListDisplayTitleName": "AAAAAAFromLabels_TestingList",
"SPListWebAddressName": "https://YYYYYYY.sharepoint.com/sites/ZZZZZZ/Lists/AAAAAAFromLabels_TestingList",
"batchSize": 1000,
"SharePointKeyColumnName": "Document_x0020_ID",
"NewDatasourceKeyColumnName": "DOB_Id"
}
Here is the peek code for my SendBatch action in the 'Do Until' loop.
{
"inputs": {
"host": {
"connectionName": "shared_sharepointonline",
"operationId": "HttpRequest",
"apiId": "/providers/Microsoft.PowerApps/apis/shared_sharepointonline"
},
"parameters": {
"dataset": "https://YYYYYY.sharepoint.com/sites/ZZZZZZZ",
"parameters/method": "POST",
"parameters/uri": "/_api/$batch",
"parameters/headers": {
"X-RequestDigest": "digest",
"Content-Type": "multipart/mixed;boundary=batch_@{actions('settings_DOB')?['trackedProperties']['batchGUID']}"
},
"parameters/body": "--batch_@{actions('settings_DOB')?['trackedProperties']['batchGUID']}\nContent-Type: multipart/mixed; boundary=\"changeset_@{actions('settings_DOB')?['trackedProperties']['changeSetGUID']}\"\nContent-Length: @{length(outputs('batchData'))}\nContent-Transfer-Encoding: binary\n\n@{outputs('batchData')}\n--changeset_@{actions('settings_DOB')?['trackedProperties']['changeSetGUID']}--\n\n--batch_@{actions('settings_DOB')?['trackedProperties']['batchGUID']}--"
},
"authentication": {
"type": "Raw",
"value": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"
}
},
"limit": {
"timeout": "P1D"
},
"metadata": {
"operationMetadataId": "e8614c0d-a078-4f0b-bc2a-6d2ec432fc3e"
}
}
You can see the action, raw input and output in the attached images. Raw input does have the correct 10 items for updating, I have cut off the screen shot after 2. Raw output seems rather ...um, short.
Comparing the SendBatch code above with your Scope Code, I see that mine is missing this:
Can you suggest a way around this? Thanks for all your work.
@KoldKoffee
Sorry there is a problem with some things in the Microsoft forums here where when I post scope code, some special characters or something often break the message editor & I can't edit or update the post with said scope code later.
If you got the flow code from one of the previous thread messages & tried to copy & paste it directly into your flow, that code may be out of date and/or faulty. Please try downloading the flow from the main / top thread message. Then in the Power Automate flows screen, go to Import and go to Import Package (Legacy) to start the import process with the flow zip file.
Thanks so much! I eventually used the .txt file from your Gdrive, which I could 'import' via clipboard, to get your full flow loaded. The Import function of the zip file was throwing errors and I don't have the time/brain to figure that out.
After tweaking for my local settings I got it to run!
This is a migraine cure. Really appreciate what you've put into all this (not just for me - learned a ton from your responses to others too).
Just sharing. This may be listed in a separate blog post.
We ended up needing to do the following: UPDATE then DELETE (items that are no longer needed on the Destination list)
This way the items that match during the Update remain, and everything else that did not match gets Deleted.
'value' = @outputs('Get_items_1st_100000_from_DESTINATION_list')?['body/value']
'output' = @body('GenerateSPData_2')
overal filter = not(contains(body('GenerateSPData_2'),'NoMatch'))
The new array generated can now be use to Delete all remaining items on the destination list.
Note: the 'BatchTemplate' and 'SendBatch' steps need to be modified from PATCH to DELETE, or as per delete protocols.
Again thank you @takolota for sharing a great flow.
Hello @novice1508
Yes, Paulie has a blog post on doing a batch Delete in SharePoint here: https://www.tachytelic.net/2021/06/power-automate-delete-sharepoint-items/
Also, I suspect there may be a simpler way to generate a list of only the records in your dataset not found in another dataset through the use of some Select & Filter array actions instead of the entire batch update flow.
For the source (most up to date dataset) & the destination (dataset to be updated by deleting items that no longer exist). Usually I would use a Select action with the source values & toggle the right-side button to switch the view from table to single box input. Then I would use something like item()?['InsertPrimaryKeyColumnName'] so the action would generate an array of all the primary keys in the source dataset of up to 100k records. From there I would then use the Filter array action on the destination dataset values with the condition set to if the Select action array of source primary keys Does not contain item()?['InsertKeyColumnName'] to get all the items to delete. Because that should check all the key values of the destination dataset against the key values of the source dataset & return only the destination records that do not share a key value with any of the source key values.
Hi,
I try to import your flow, both version 1 a 1.5 into my Power Automate, but I'm getting the following error:
DynamicOperationRequestClientFailure, zpráva The dynamic operation request to API 'sharepointonline' operation 'GetTable' failed with status code 'Unauthorized'. This may indicate invalid input parameters. Error response: { "error_description": "Exception of type 'Microsoft.IdentityModel.Tokens.AudienceUriValidationFailedException' was thrown." }.
Can anyone please help me?
Thanks
Lukas
This post has something you can try control + C copy to your clipboard, then go to a new action menu in Power Automate, go to the My clipboard tab & Control V paste into the options there.
Hi @takolota ,
thanks for your reply. Unfortunately on my make.powerautomate.com I am not able to paste raw code into the flow, MS does not support this. But I already solved the problem. I changed definition.json inside you your .zip file - changed your sharepoint url to my and also list ID to my, than I was able to import flow succesfully.
Lukas
Any suggestions for examples on where/how to add the error catching code? Super new to all this and definitely want to figure out what happened to my missing 200 records!
@dbmamaz
You can follow the outline of instructions in the main post...
" 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']),
'')
The condition at the end of the flow should be something like if length(InputVariableDynamicContent) is greater than 0
And on the yes side of the condition, you can add something like the scope from this flow failure template below, but the run-after condition can just be left at the default "On success" because it is purposefully triggered by the conditional checking the variable length.
https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Flow-Failure-Notifications/td-p/1494079