08-01-2023 07:08 AM
SharePoint Batch Update, Create, & Upsert Template
(Also an option for full dataset synchronizations with the "Full Sync" template below)
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 or another datasource matches another SharePoint key column and gets the SharePoint record ID for that match. Then it uses the batch update method to update those SharePoint records and it uses the batch create method to create new items for any records without a match.
David Wyatt's Flow Optimization Post For Loading SharePoint Records: https://www.linkedin.com/pulse/top-5-ways-optimize-your-flows-david-wyatt/?trackingId=X9bMmnTZ2QBuu4...
Microsoft Batch API Documentation: https://learn.microsoft.com/en-us/sharepoint/dev/sp-add-ins/make-batch-requests-with-the-rest-apis
TachyTelic.Net Blog & Videos
SharePoint Batch Create Flow
Blog: https://www.tachytelic.net/2021/06/power-automate-flow-batch-create-sharepoint-list-items/
Video: https://youtu.be/2dV7fI4GUYU
SharePoint Batch Delete Flow
Blog: https://www.tachytelic.net/2021/06/power-automate-delete-sharepoint-items/
Video: https://www.youtube.com/watch?v=2ImkuGpEeoo
Version 2.7 - Upsert
-Includes a batch create segment to create an upsert capability. If anyone wants to only update records, then they can remove the Batch Create section. If anyone wants to only create records, then they can go to the GenerateSPData action, remove the expression for the ID field and insert the null value expression.
-Further simplifies the set-up, removing the need to add any additional SharePoint Get items actions & removing the need for parallel branches.
-Can now work on lists with a few million items without adding more actions or branches. It also implements a faster load method using the SharePoint HTTP action as described in point 5 of this article.
-The batch loops have been changed from Do until loops to chunking into Apply to each loops so the batch actions can now run concurrently for additional speed. If you have many batches of data you want to process faster, you can try increasing the concurrency settings on the Apply to each loops containing the SendBatch actions.
-The "setting" inputs action was moved to the top of the flow to help accommodate the new streamlined set-up.
-A SP HTTP call now automatically fixes some issues with referencing the correct list name.
-Faster list load time.
-If you need to batch create &/or update hyperlink columns, check this post
-Adds another HTTP call to get the site users into an object indexed/reference-able by email addresses & gives an example of how to use that to batch update a person column. Anytime the updated source dataset has a blank or an email value not found in the top 5000 site users, it will replace any person in that item with a null value.
Updated set-up screenshots & instructions in this post: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Batch-Update-SharePoint-List/m-p/2225500...
Full Sync V1.1 (Combined Upsert & Batch Deletion Sync)
The SharePoint Batch Full Sync template pulls in all the SP List & Source data to perform a batch upsert. But then it also adds on a Batch Deletion Sync to find & delete all the records in the SP List that are not in the Source dataset.
Now, this is initially set up to pull in all the datasource records for all batch actions in the same Do until Get source data loop. And that piece will be limited by the 100MB maximum message / variable size limits for Power Automate, so this Full Sync version will initially only work with datasources with a 100MB or less total size. But this is really only because I'm trying to keep the flow simpler for the majority of users who likely will not have datasources of many 100s of thousands of records.
If you want to further push out against this 100MB limitation, then you will need to separate out the source get data for the batch upsert section from another source get data for the batch deletion sync section. So for the batch upsert section you can use a set-up like in the main batch upsert template where it loads records with all columns 100,000 at a time (or 100 or 5000 or whatever your source dataset per load limitations are) and runs the batch upsert on each source load before running the Do until loop again to get the next source load (which avoids holding anywhere near 100MB in memory at once because it is performing things one load at a time). Then the batch deletion sync section can use a different source get data set-up similar to the "Do until Get destination list IDs + keys" section of the templates where each loop can pull a load from the source dataset & then use a Select action to select only a few of the columns to pass on to the variable holding everything in memory. Since deletions only require the primary key values, you can set the Select to only get the primary key column from each source data load & pass that onto the "Source data outputs" / variable. A full listing of all the primary key values in your source dataset will be much smaller than all columns for the entire table, so that 100MB limit should then hold a few million records worth of the required primary key data to run the batch deletion sync process.
Self Update (See the 1st comment below the main post for the zip download)
The SharePoint Self Batch Update assumes you just want to perform simple updates using only the existing data in the list and removes all the actions related to comparing two datasets to find updates. This may be much easier to use if you just want to quickly do something simple like get all the items created in the past month and mark them all with a Complete status.
But you will be limited to using just the data already in the list and any values you can manually input into the flow.
Version 1.5 - Update
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.
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.
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 - Update
Version 1 Explanation Video: https://youtu.be/l0NuYtXdcrQ
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
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 you have trouble importing any of the flows using the standard legacy import, you can also try importing a Power Apps Solutions package here: Re: Batch Update, Create, and Upsert SharePoint Li... - Page 25 - Power Platform Community (microsof...
Thanks for any feedback,
Please subscribe to my YouTube channel (https://youtube.com/@tylerkolota?si=uEGKko1U8D29CJ86).
And reach out on LinkedIn (https://www.linkedin.com/in/kolota/) if you want to hire me to consult or build more custom Microsoft solutions for you.
watch?v=QCkjQy6sHZg
I had to create a separate flow to update the people/groups. On the excel file l have the user provide the email address for the people then after bulk upload autorun a flow to update the people field using the email address.
I was thinking about doing that as well, however, I think I am not sure --that would required the same API calls as if I were to do the entire excel file? I will try that in case hopefully it won't take long to run.
Thank you for your input @roverton
It's not too bad, but I did add a teams chat function to send the user two messages. 1. The estimated time the system needs to process their request. 2. When the update is finished. In addition, it will send a message that the update has failed. I have received positive feedback from users.
I guess you could set up something to get all the distinct user names or emails in your data, then do the additional call to get the needed claims/other format. Then the Select could reference that set of name index & claims values the same way it does for IDs.
That way you could reduce the calls from 1 per record to 1 per distinct / unique user.
This flow is great, i just having one issue (i had a look throught the comments and couldnt find an answer), i'm trying to update a sharepoint list that contains the following columns,
Title - Index Value
Employee - Lookup Column
Element - Lookup Column
DateCompleted - Date field
The source file (excel)
Title - Index value
Employee - ID Number of the employee
Element - Text value of the element
Date - Date Value
I have been sucessful in getting it to run with just the Title & Date field but when I add in the lookup columns it fails .
Not sure where im going wrong here
@takolota Really fantastic material and really excited about what you can do with this.... that said, I have limitations at my end for loading Flow Zip files... Would it be possible to publish a Version 2.6 Upsert using Scope Action... That would be amazing. Cheers
SharePoint Batch Upsert V2.6
You will need to add these variables to the top of your flow before the Scope action containing the template flow actions...
Scope With Template Flow Actions...
{"id":"81ad968a-cef2-441d-9da1-b518235da09c","brandColor":"#8C3900","connectionReferences":{"shared_sharepointonline":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_sharepointonline/connections/shared-sharepointonl-a8f77d69-1351-45d3-b2b1-c5056a806428"}},"shared_excelonlinebusiness_2":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness/connections/shared-excelonlinebu-ee4e6dfa-6245-4284-a4c8-17477349e414"}},"shared_sharepointonline_1":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_sharepointonline/connections/shared-sharepointonl-d3218a72-f89d-410f-bf45-2082-e6f1e67e"}}},"connectorDisplayName":"Control","icon":"data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=","isTrigger":false,"operationName":"SharePoint_Batch_Upsert_V2.6","operationDefinition":{"type":"Scope","actions":{"PlaceholderValue_Delete_after_importing":{"type":"Compose","inputs":"Placeholder","runAfter":{},"description":"A placeholder value to avoid import errors. Please delete once imported.","metadata":{"operationMetadataId":"57516a16-1840-4d1e-a88f-7c06802ac894"}},"settings":{"type":"Compose","inputs":{"siteAddress":"https://OrgName.sharepoint.com/sites/SiteName/","listName":"ExampleListName","batchSize":800,"DestinationListKeyColumnName":"Email","DestinationListKeyColumnName2":"","SourceKeyColumnName":"Email","SourceKeyColumnName2":"","Additional Settings Notes":"The siteAddress must have a forward slash / at the end or the SendBatch will not batch update, it will return a 400 Bad Request. The maximum batch size is 1000. The DestinationListKeyColumnName2 & SourceKeyColumnName2 settings allow you to add a second column to form a concatenated primary key, incase you do not have a single unique column for every row or incase one dataset already has the columns concatenated for a key field & the other does not (leave them blank if the dataset already has the single primary key column)."},"runAfter":{"PlaceholderValue_Delete_after_importing":["Succeeded"]},"description":"List to batch update & the batch size (max 1000). The column name(s) in SP & in the updated source to match in the lookup step, 1st SP row /w matching key column value(s) is updated /w the new data.","trackedProperties":{"meta":{"type":"SP.Data.@{outputs('settings')?['listName']}ListItem"},"batchGUID":"@{guid()}","changeSetGUID":"@{guid()}"},"metadata":{"operationMetadataId":"459dc501-ad7e-4252-8540-df307a69607f"}},"HTTP_Get_backend_listName":{"type":"ApiConnection","inputs":{"host":{"connection":{"name":"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$connections']['shared_sharepointonline']['connectionId']"}},"method":"post","body":{"method":"GET","uri":"/_api/web/lists/getbytitle('@{outputs('settings')['listName']}')?$select=ListItemEntityTypeFullName"},"path":"/datasets/@{encodeURIComponent(encodeURIComponent(outputs('settings')['siteAddress']))}/httprequest","authentication":{"type":"Raw","value":"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"}},"runAfter":{"settings":["Succeeded"]},"metadata":{"operationMetadataId":"cf2f49dd-6952-4367-b70a-ebc951ab4e02","flowSystemMetadata":{"swaggerOperationId":"HttpRequest"}}},"Do_until_Get_destination_list_IDs_+_keys":{"type":"Until","expression":"@less(length(body('SharePoint_HTTP_Get_items')?['value']), 1)","limit":{"count":200,"timeout":"PT10H"},"actions":{"Set_variable_LastID":{"type":"SetVariable","inputs":{"name":"LastID","value":"@if(empty(body('SharePoint_HTTP_Get_items')?['value']), 0, last(body('SharePoint_HTTP_Get_items')?['value'])?['ID'])"},"runAfter":{"SharePoint_HTTP_Get_items":["Succeeded"]},"metadata":{"operationMetadataId":"5cae10ed-4f1a-450f-bdd0-ace20ed5fb42"}},"SharePoint_HTTP_Get_items":{"type":"ApiConnection","inputs":{"host":{"connection":{"name":"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$connections']['shared_sharepointonline']['connectionId']"}},"method":"post","body":{"method":"GET","uri":"/_api/Web/Lists/getByTitle('@{outputs('settings')['listName']}')/Items?$select=ID,@{outputs('settings')['DestinationListKeyColumnName']}@{if(empty(outputs('settings')?['DestinationListKeyColumnName2']), '', concat(',', outputs('settings')['DestinationListKeyColumnName2']))}&$filter=ID gt '@{variables('LastID')}'&$orderby=ID&$top=5000","headers":{"accept":"application/json","odata":"nometadata"}},"path":"/datasets/@{encodeURIComponent(encodeURIComponent(outputs('settings')['siteAddress']))}/httprequest","authentication":{"type":"Raw","value":"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"}},"runAfter":{},"description":"Get items using the HTTP action to take advantage of the nometadata & select options that can reduce load times.","metadata":{"operationMetadataId":"99bfe3cd-7182-4a2d-b0b0-622dbeaf9730","flowSystemMetadata":{"swaggerOperationId":"HttpRequest"}}},"Append_to_array_variable_SetOfOutputs":{"type":"AppendToArrayVariable","inputs":{"name":"SetOfOutputs","value":"@join(json(replace(string(body('Select_IDs_+_Keys')), ',\"\":null', '')), '~%^&*;')"},"runAfter":{"Select_IDs_+_Keys":["Succeeded"]},"metadata":{"operationMetadataId":"b3368b9d-ffe7-4d71-bee0-307f69e9d99c"}},"Select_IDs_+_Keys":{"type":"Select","inputs":{"from":"@body('SharePoint_HTTP_Get_items')?['value']","select":{"ID":"@item()['ID']","@{outputs('settings')['DestinationListKeyColumnName']}":"@item()[outputs('settings')['DestinationListKeyColumnName']]","@{if(empty(outputs('settings')?['DestinationListKeyColumnName2']), '', outputs('settings')['DestinationListKeyColumnName2'])}":"@item()?[outputs('settings')?['DestinationListKeyColumnName2']]"}},"runAfter":{"Set_variable_LastID":["Succeeded"]},"description":"Remove unnecessary properties from the JSON returned in the HTTP Get items. Reducing the size / # of characters here helps avoid any message size limits in later steps on 1million+ item lists.","metadata":{"operationMetadataId":"c0499e71-04f0-4f1f-98af-ec90f5b93d40"}}},"runAfter":{"HTTP_Get_backend_listName":["Succeeded"]},"metadata":{"operationMetadataId":"3953a053-e170-4591-bb7b-2da6ec8689db"}},"CombinedArray":{"type":"Select","inputs":{"from":"@reverse(skip(reverse(split(join(variables('SetOfOutputs'), '~%^&*;'), '~%^&*;')), 1))","select":"@json(item())"},"runAfter":{"Do_until_Get_destination_list_IDs_+_keys":["Succeeded"]},"description":" Join & then split the Apply to Each outputs into a single array or a single JSON array","metadata":{"operationMetadataId":"a3b520fb-6436-4bac-a255-a61406e91ee6"}},"Batch_Upsert":{"type":"Scope","actions":{"Do_until_Upsert":{"type":"Until","expression":"@less(length(body('List_rows_present_in_a_table')?['value']), 99990)","limit":{"count":150,"timeout":"PT8H"},"actions":{"Batch_Update":{"type":"Scope","actions":{"Filter_array_Remove_items_without_a_key_to_ID_match_in_SharePoint":{"type":"Query","inputs":{"from":"@body('GenerateSPData')","where":"@not(equals(item()?['ID'], null))"},"runAfter":{},"metadata":{"operationMetadataId":"1c5c3129-35c5-444d-9a4f-aad8f88a4446"}},"Apply_to_each":{"type":"Foreach","foreach":"@chunk(body('Filter_array_Remove_items_without_a_key_to_ID_match_in_SharePoint'), outputs('settings')['batchSize'])","actions":{"Select":{"type":"Select","inputs":{"from":"@items('Apply_to_each')","select":"@replace(replace(outputs('batchTemplate'), '|ID|', string(item()?['ID'])), '|RowData|', string(item()))"},"runAfter":{},"metadata":{"operationMetadataId":"d90bce05-ae93-482c-a536-bd36310988b6"}},"SendBatch":{"type":"ApiConnection","inputs":{"host":{"connection":{"name":"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$connections']['shared_sharepointonline_1']['connectionId']"}},"method":"post","body":{"method":"POST","uri":"/_api/$batch","headers":{"X-RequestDigest":"digest","Content-Type":"multipart/mixed;boundary=batch_@{actions('settings')?['trackedProperties']['batchGUID']}"},"body":"--batch_@{actions('settings')?['trackedProperties']['batchGUID']}\nContent-Type: multipart/mixed; boundary=\"changeset_@{actions('settings')?['trackedProperties']['changeSetGUID']}\"\nContent-Length: @{length(outputs('batchData'))}\nContent-Transfer-Encoding: binary\n\n@{outputs('batchData')}\n--changeset_@{actions('settings')?['trackedProperties']['changeSetGUID']}--\n\n--batch_@{actions('settings')?['trackedProperties']['batchGUID']}--"},"path":"/datasets/@{encodeURIComponent(encodeURIComponent(outputs('settings')['siteAddress']))}/httprequest","authentication":{"type":"Raw","value":"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"}},"runAfter":{"batchData":["Succeeded"]},"metadata":{"operationMetadataId":"14767ee6-e101-48b3-9a1f-6f02831d4db6","flowSystemMetadata":{"swaggerOperationId":"HttpRequest"}}},"batchData":{"type":"Compose","inputs":"@join(body('Select'), decodeUriComponent('%0A'))","runAfter":{"Select":["Succeeded"]},"metadata":{"operationMetadataId":"37cd3c50-9877-4ec3-a32f-33290e7cf6cb"}},"Append_to_string_Errors":{"type":"AppendToStringVariable","inputs":{"name":"Errors","value":"@if(\r\nor(\r\nor(\r\nor(\r\nor(\r\nor(\r\nor(\r\nor(\r\ncontains(base64ToString(body('SendBatch')['$content']), '400 Bad Request')),\r\ncontains(base64ToString(body('SendBatch')['$content']), '401 Unauthorized')),\r\ncontains(base64ToString(body('SendBatch')['$content']), '403 Forbidden')),\r\ncontains(base64ToString(body('SendBatch')['$content']), '404 Not Found')),\r\ncontains(base64ToString(body('SendBatch')['$content']), '408 Request Timeout')),\r\ncontains(base64ToString(body('SendBatch')['$content']), '409 Conflict')),\r\ncontains(base64ToString(body('SendBatch')['$content']), '502 Bad Gateway')),\r\nbase64ToString(body('SendBatch')['$content']),\r\n'')"},"runAfter":{"SendBatch":["Succeeded"]},"metadata":{"operationMetadataId":"0548e3b3-d419-4689-a471-8e4099084085"}}},"runAfter":{"batchTemplate":["Succeeded"]},"runtimeConfiguration":{"concurrency":{"repetitions":5}},"metadata":{"operationMetadataId":"c7f1c8de-055f-42fc-bb62-dcb5d0862e14"}},"batchTemplate":{"type":"Compose","inputs":"--changeset_@{actions('settings')?['trackedProperties']['changeSetGUID']}\nContent-Type: application/http\nContent-Transfer-Encoding: binary\n\nPATCH @{outputs('settings')['siteAddress']}_api/web/lists/getByTitle('@{outputs('settings')['listName']}')/items(|ID|) HTTP/1.1\nContent-Type: application/json;odata=verbose\nAccept: application/json;odata=verbose\nIf-Match: *\n\n|RowData|\n","runAfter":{"Filter_array_Remove_items_without_a_key_to_ID_match_in_SharePoint":["Succeeded"]},"metadata":{"operationMetadataId":"d0af0142-cd1d-437c-97a2-98210ea8dbde"}}},"runAfter":{"GenerateSPData":["Succeeded"]},"metadata":{"operationMetadataId":"92a89bdf-e08b-4767-a93f-831f6b8eeb76"}},"List_rows_present_in_a_table":{"type":"ApiConnection","inputs":{"host":{"connection":{"name":"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$connections']['shared_excelonlinebusiness_2']['connectionId']"}},"method":"get","path":"/drives/@{encodeURIComponent(outputs('PlaceholderValue_Delete_after_importing'))}/files/@{encodeURIComponent(encodeURIComponent(outputs('PlaceholderValue_Delete_after_importing')))}/tables/@{encodeURIComponent(outputs('PlaceholderValue_Delete_after_importing'))}/items","queries":{"source":"@{outputs('PlaceholderValue_Delete_after_importing')}","$top":100000,"$skip":"@sub(mul(iterationIndexes('Do_until_Upsert'), 100000), iterationIndexes('Do_until_Upsert'))","dateTimeFormat":"ISO 8601"},"authentication":{"type":"Raw","value":"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"}},"runAfter":{},"description":"The expression in Skip Count will skip any 100k batches already loaded by previous loop runs. You may need to create something similar if you use any alternative get data action in this Do until loop.","runtimeConfiguration":{"paginationPolicy":{"minimumItemCount":100000}},"metadata":{"016B7R4Y7EON4XZ3K5URCJG7TCJZ2K7B6S":"/Power Automate Files/DateInfo.xlsx","flowSystemMetadata":{"swaggerOperationId":"GetItems"},"016B7R4YYVOTO4F52WHNAZ4NTGFWMYOH66":"/Power Automate Files/thanksgiving-metropolitan-ministries-holidays11-22.xlsx","operationMetadataId":"d3258554-7310-4453-9d1c-103d10112938","016B7R4Y3LVLCEITRXYVBIVHINFYOQURWY":"/Example_Folder/Example_File.xlsx","01RD23GUU5UPXK7LQNWBCITP653FJPSCXW":"/TestExcel.xlsx","01JWBUU4ALFRUXCIXBLBHID53UZJN4V6CX":"/TestExcelSPUpdate.xlsx"}},"Batch_Create":{"type":"Scope","actions":{"Filter_array_Get_items_without_a_key_to_ID_match_in_SharePoint":{"type":"Query","inputs":{"from":"@body('GenerateSPData')","where":"@equals(item()?['ID'], null)"},"runAfter":{},"metadata":{"operationMetadataId":"1c5c3129-35c5-444d-9a4f-aad8f88a4446"}},"GenerateSPData_2":{"type":"Select","inputs":{"from":"@body('Filter_array_Get_items_without_a_key_to_ID_match_in_SharePoint')","select":"@removeProperty(item(), 'ID')"},"runAfter":{"Filter_array_Get_items_without_a_key_to_ID_match_in_SharePoint":["Succeeded"]},"metadata":{"operationMetadataId":"0feccc6b-de02-4e8c-b80b-c7c7e87a6dad"}},"batchTemplate_2":{"type":"Compose","inputs":"--changeset_@{actions('settings')?['trackedProperties']['changeSetGUID']}\nContent-Type: application/http\nContent-Transfer-Encoding: binary\n\nPOST @{outputs('settings')['siteAddress']}_api/web/lists/getByTitle('@{outputs('settings')['listName']}')/items HTTP/1.1\nContent-Type: application/json;odata=verbose\n\n|RowData|\n","runAfter":{"GenerateSPData_2":["Succeeded"]},"metadata":{"operationMetadataId":"897eac5e-49e0-457b-880c-ff3e10a6d6b1"}},"Apply_to_each_2":{"type":"Foreach","foreach":"@chunk(body('GenerateSPData_2'), outputs('settings')['batchSize'])","actions":{"SendBatch_2":{"type":"ApiConnection","inputs":{"host":{"connection":{"name":"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$connections']['shared_sharepointonline']['connectionId']"}},"method":"post","body":{"method":"POST","uri":"/_api/$batch","headers":{"X-RequestDigest":"digest","Content-Type":"multipart/mixed;boundary=batch_@{actions('settings')?['trackedProperties']['batchGUID']}"},"body":"--batch_@{actions('settings')?['trackedProperties']['batchGUID']}\nContent-Type: multipart/mixed; boundary=\"changeset_@{actions('settings')?['trackedProperties']['changeSetGUID']}\"\nContent-Length: @{length(outputs('batchData_2'))}\nContent-Transfer-Encoding: binary\n\n@{outputs('batchData_2')}\n--changeset_@{actions('settings')?['trackedProperties']['changeSetGUID']}--\n\n--batch_@{actions('settings')?['trackedProperties']['batchGUID']}--"},"path":"/datasets/@{encodeURIComponent(encodeURIComponent(outputs('settings')['siteAddress']))}/httprequest","authentication":{"type":"Raw","value":"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"}},"runAfter":{"batchData_2":["Succeeded"]},"limit":{"timeout":"P1D"},"metadata":{"flowSystemMetadata":{"swaggerOperationId":"HttpRequest"},"operationMetadataId":"932c2f63-16a8-49d5-ac63-d2c0caeddbe9"}},"batchData_2":{"type":"Compose","inputs":"@join(body('Select_2'), decodeUriComponent('%0A'))","runAfter":{"Select_2":["Succeeded"]},"metadata":{"operationMetadataId":"ca3fd1e6-b17f-4952-8d00-6f5109117bd6"}},"Select_2":{"type":"Select","inputs":{"from":"@items('Apply_to_each_2')","select":"@replace(outputs('batchTemplate_2'), '|RowData|', string(item()))"},"runAfter":{},"metadata":{"operationMetadataId":"d3e5d38c-e318-421b-86b1-708ccedd889a"}},"Append_to_string_Errors_2":{"type":"AppendToStringVariable","inputs":{"name":"Errors","value":"@if(\r\nor(\r\nor(\r\nor(\r\nor(\r\nor(\r\nor(\r\nor(\r\ncontains(base64ToString(body('SendBatch_2')['$content']), '400 Bad Request')),\r\ncontains(base64ToString(body('SendBatch_2')['$content']), '401 Unauthorized')),\r\ncontains(base64ToString(body('SendBatch_2')['$content']), '403 Forbidden')),\r\ncontains(base64ToString(body('SendBatch_2')['$content']), '404 Not Found')),\r\ncontains(base64ToString(body('SendBatch_2')['$content']), '408 Request Timeout')),\r\ncontains(base64ToString(body('SendBatch_2')['$content']), '409 Conflict')),\r\ncontains(base64ToString(body('SendBatch_2')['$content']), '502 Bad Gateway')),\r\nbase64ToString(body('SendBatch_2')['$content']),\r\n'')"},"runAfter":{"SendBatch_2":["Succeeded"]},"metadata":{"operationMetadataId":"25e44306-43ac-4c9a-951c-490b56bf30f3"}}},"runAfter":{"batchTemplate_2":["Succeeded"]},"runtimeConfiguration":{"concurrency":{"repetitions":5}},"metadata":{"operationMetadataId":"0705ab68-b533-4f10-b06e-f9838b547f31"}}},"runAfter":{"Batch_Update":["Succeeded"]},"metadata":{"operationMetadataId":"6977a845-39cb-4825-85fc-a970389e3eb9"}},"Select_Keys_from_Get_items":{"type":"Select","inputs":{"from":"@body('CombinedArray')","select":{"@{concat(item()[outputs('settings')['DestinationListKeyColumnName']], if(empty(outputs('settings')['DestinationListKeyColumnName2']), '', item()[outputs('settings')['DestinationListKeyColumnName2']]))}":"@item()"}},"runAfter":{"List_rows_present_in_a_table":["Succeeded"]},"metadata":{"operationMetadataId":"dac3de39-a3e5-422a-be37-81089ee64a51"}},"Compose_Reformat_keys_to_single_JSON_object":{"type":"Compose","inputs":"@json(replace(replace(replace(string(body('Select_Keys_from_Get_items')), '[', ''), ']', ''), '},{', ','))","runAfter":{"Select_Keys_from_Get_items":["Succeeded"]},"metadata":{"operationMetadataId":"69b1d189-d56f-45fc-8dbb-a49740832a64"}},"GenerateSPData":{"type":"Select","inputs":{"from":"@body('List_rows_present_in_a_table')?['value']","select":{"__metadata":"@json(concat('{\"type\":\"', body('HTTP_Get_backend_listName')?['d']?['ListItemEntityTypeFullName'], '\"}'))","ID":"@outputs('Compose_Reformat_keys_to_single_JSON_object')?[concat(item()[outputs('settings')['SourceKeyColumnName']], if(empty(outputs('settings')['SourceKeyColumnName2']), '', item()[outputs('settings')['SourceKeyColumnName2']]))]?['ID']","Date":"@addDays('1899-12-30', int(item()['Date']), 'u')","Title":"@item()?['Name']","Email":"@item()?['Email']","Status":"@item()?['Status']"}},"runAfter":{"Compose_Reformat_keys_to_single_JSON_object":["Succeeded"]},"description":"Use the exact SharePoint column names on the left side of the table & input the dynamic content for their new values on the right. DO NOT alter the __metadata or ID Map inputs.","metadata":{"operationMetadataId":"ce0487d5-63c8-40b4-9b4c-5229c0439c94"}}},"runAfter":{},"description":"If you change the List rows present in a table to something else, change the length() expression below to the length of your new get data action & the 99990 to the new get data batch size. Then insert the action values in GenerateSPData From fields.","metadata":{"operationMetadataId":"f668d1f3-7245-4e88-9952-6b7e2bec05b9"}},"Condition_If_SendBatch_errors_fail_flow":{"type":"If","expression":{"greater":["@length(variables('Errors'))",1]},"actions":{"Terminate":{"metadata":{"operationMetadataId":"9b6b9e6f-2571-4518-abed-13c121fec401"},"type":"Terminate","inputs":{"runStatus":"Failed","runError":{"message":"One of the SendBatch actions encountered one of the following HTTP errors while processing the data.\n400 Bad Request\n401 Unauthorized\n403 Forbidden\n404 Not Found\n408 Request Timeout\n409 Conflict\n502 Bad Gateway\n\nYou can visit (https://developer.mozilla.org/en-US/docs/Web/HTTP/Status) for more information."}},"runAfter":{}}},"runAfter":{"Do_until_Upsert":["Succeeded"]},"metadata":{"operationMetadataId":"2ae3fbab-7166-443a-bda2-7f35ef55d0d8"}}},"runAfter":{"CombinedArray":["Succeeded"]},"description":"Adjust the \"settings\" compose action to fit your data & datasources. Then adjust the GenerateSPData actions in each branch to include the right SharePoint column names & new updated data for your use-case.","metadata":{"operationMetadataId":"921a4fd4-4def-40df-a301-6402606fa56f"}}},"runAfter":{"Initialize_variable_SetOfOutput":["Succeeded"]}}}
SharePoint HTTP Get items uri
/_api/Web/Lists/getByTitle('@{outputs('settings')['listName']}')/Items?$select=ID,@{outputs('settings')['DestinationListKeyColumnName']}@{if(empty(outputs('settings')?['DestinationListKeyColumnName2']), '', concat(',', outputs('settings')['DestinationListKeyColumnName2']))}&$filter=ID gt '@{variables('LastID')}'&$orderby=ID&$top=5000
Append to array variable SetOfOutputs
join(json(replace(string(body('Select_IDs_+_Keys')), ',"":null', '')), '~%^&*;')
Compose Reformat keys to single JSON object
json(replace(replace(replace(string(body('Select_Keys_from_Get_items')), '[', ''), ']', ''), '},{', ','))
GenerateSPData ID input
outputs('Compose_Reformat_keys_to_single_JSON_object')?[concat(item()[outputs('settings')['SourceKeyColumnName']], if(empty(outputs('settings')['SourceKeyColumnName2']), '', item()[outputs('settings')['SourceKeyColumnName2']]))]?['ID']
Append to string Errors
if(
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']), '502 Bad Gateway')),
base64ToString(body('SendBatch')['$content']),
'')
Append to string Errors 2
if(
or(
or(
or(
or(
or(
or(
or(
contains(base64ToString(body('SendBatch_2')['$content']), '400 Bad Request')),
contains(base64ToString(body('SendBatch_2')['$content']), '401 Unauthorized')),
contains(base64ToString(body('SendBatch_2')['$content']), '403 Forbidden')),
contains(base64ToString(body('SendBatch_2')['$content']), '404 Not Found')),
contains(base64ToString(body('SendBatch_2')['$content']), '408 Request Timeout')),
contains(base64ToString(body('SendBatch_2')['$content']), '409 Conflict')),
contains(base64ToString(body('SendBatch_2')['$content']), '502 Bad Gateway')),
base64ToString(body('SendBatch_2')['$content']),
'')
I put up some replies with the scope set-up, but as expected it gets very messy because copying & pasting it into a flow very often will miss/remove expressions & pieces of the flow. I tried to provide all the expressions that were left out in my test of importing through the Scope so you can go in & fix them after importing.
@takolota thank you so much for posting this. I am currently working through it.... couple of errors at the moment but with this very strong background of detail, I am sure to crack it.