Skip to main content

Notifications

Community site session details

Community site session details

Session Id : mFDY8XzAKcVrkqgW8Bn3xK

Batch Update, Create, and Upsert SharePoint Lists

takolota1 Profile Picture Posted 10 Jan 2024 by takolota1 4,859 Super User 2025 Season 1

SharePoint Upsert Thumbnail.jpg
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.


By default it is 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.


 

Helpful related resources

David Wyatt's Flow Optimization Post For Loading SharePoint Records: https://www.linkedin.com/pulse/top-5-ways-optimize-your-flows-david-wyatt/?trackingId=X9bMmnTZ2QBuu48HJ29wQA%3D%3D ​​​​​​​

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

 

 

(Old) Update V1

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)

(Old) Update V1.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.

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


Self Update

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.

Solution Zip Download Link: https://drive.google.com/file/d/1ZfDGP48fuMSuLQyv_k-tW5ofGsa3dlMC/view?usp=sharing

Self Batch Update Power Automate Legacy Import: https://drive.google.com/file/d/104v4rKSx9fYqfJnvjxwq4jioOE0rjsy-/view?usp=sharing


Upsert V2.7

-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.

-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.

-Hyperlink columns may require this format

"ColumnName": {
"Description":"UrlTextToShow",
"Url":"Url"
}

So this expression: 

json(concat('{"Description":"', item()?['InsertSourceLinkColumnName'], '","Url":"', item()?['InsertSourceLinkColumnName'], '"}'))


-Multiselect columns may require this format 

"MultiSelectColumnInternalName": {
      "__metadata": {
        "type": "Collection(Edm.String)"
      },
      "results": [
        "Value 1",
        "Value 2",
        "...",
        "Value N"
      ]
}

So something like this expression: 

json(concat('{"__metadata": {"type": "Collection(Edm.String)"},"results":[', item()?['InsertCommaSeperatedMultiSelectValues'], ']}'))

 

Solution Zip Download Link: https://drive.google.com/file/d/1ZfDGP48fuMSuLQyv_k-tW5ofGsa3dlMC/view?usp=sharing

V2.7 Upsert Power Automate Legacy Import: https://drive.google.com/file/d/1KTh00laO4so4cSRPeo8ODW9Vw_vOX5zJ/view?usp=sharing


Upsert Non Unique Keys V1.2

The default upsert template requires you have something that can form a unique primary key & foreign key to match up between source & destination data-sets. The default template will, for each source record, find the 1st matching destination record to update. So if your key is not unique & your destination has multiple records that share the same key that need to be updated, then the default template only updates 1 of the records for each unique destination key value, it does not also update the destination records with duplicate key values.
To update multiple destination records with the same key(s) usually meant doing an Apply to each for each source record, doing a Get items with a Filter query on the destination list for all records relevant to the current source record data, & then updating each relevant destination record in a nested loop.
This set-up offers a possible work-around to batch update multiple destination records that share the same key using XML & Xpath queries. It also allows you to reference both new source values & original destination values in the GenerateSPData action.

(V1.2 Includes performance enhancements so the xPath update can handle larger amounts of data.)

Solution Zip Download Link: https://drive.google.com/file/d/1ZfDGP48fuMSuLQyv_k-tW5ofGsa3dlMC/view?usp=sharing

V1.2 Upsert Non Unique Keys Power Automate Legacy Import: https://drive.google.com/file/d/1FDD7qEYZTTxJvWJc6SCGaIzTlOhX4NLS/view?usp=sharing


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.

Solution Zip Download Link: https://drive.google.com/file/d/1ZfDGP48fuMSuLQyv_k-tW5ofGsa3dlMC/view?usp=sharing

V1.1 Full Sync Power Automate Legacy Import: https://drive.google.com/file/d/1Op_YwcjZE6ZRx21NLJ31Y7bjiTSAbgwZ/view?usp=sharing



 

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.

Also if you find my free builds/templates helpful, note they are often a by-product of work I do with a USAID contractor.

code snippet widget

Categories:

Comments

  • mm_512 Profile Picture mm_512 59
    Posted 15 Oct 2024 at 15:48:05
    Batch Update, Create, and Upsert SharePoint Lists
    Hello Tyler,
    I'm using the "Title" column as the key. The flow runs, but I get {"error":{"code":"-1, Microsoft.SharePoint.Client.InvalidClientQueryException","message":{"lang":"en-US","value":"The expression \"web/lists/getByTitle('Gr12Assets')/items(PF2YC71G)\" is not valid."}}}.

    The Select output gives me all the rows in the changeset:

    The "SendBatch" action fails and no record gets updated. Any thoughts where I am going wrong? Is it the choice of my key column? 
    The batchData action's output is:


  • RC-06091804-0 Profile Picture RC-06091804-0 3
    Posted 23 Sep 2024 at 19:29:28
    Batch Update, Create, and Upsert SharePoint Lists
    I am currently working on multiple batch calls that calls 1000 create items in order, and it works well for the most part but sometimes items don't get added and there is no reason to as why, they just disappear, I ran my flow a few times and everytime it has created a different amout of items, does anyone know how to fix this or what I can possibly do to mitigate this?
     
  • takolota1 Profile Picture takolota1 4,859 Super User 2025 Season 1
    Posted 20 Jun 2024 at 17:16:02
    Batch Update, Create, and Upsert SharePoint Lists

    @emelgar 

    I see this forum post & blog post on updating a multiple person column with an HTTP request

    https://powerusers.microsoft.com/t5/Using-Flows/Update-Multi-People-Picker-Column-with-SharePoint-HTTP-Action/td-p/1422010

     

    https://tomriha.com/how-to-easily-update-sharepoint-people-picker-column-in-power-automate/

     

    So there may be a way to replicate those multiple claims in the Select / GenerateSPData action. But even if that works it may be more complicated as there won’t be a way to dynamically add a variable number of persons/claims to each row without an Apply to each loop. If you generally know the maximum number of people you would need to add to any given row, you might pre-define things in several if( ) expressions to insert 2, 3, 4 etc person claim elements to that field by inserting the claim element if it exists or nothing if it doesn’t (and some logic to remove the last comma in the array).

    I don’t know if all those complications sound worth it to you for a batch call over individual updates.

  • emelgar Profile Picture emelgar
    Posted 20 Jun 2024 at 16:07:39
    Batch Update, Create, and Upsert SharePoint Lists

    @takolota or anyone tried updating the Person Column with multiple selection? My source excel has a column with email addresses semicolon delimited for each row and it needs to be saved on that Multi select Person Field. Also how to handle if one of the user on a row cannot be found, it is possible to parse only the active users? 

  • takolota1 Profile Picture takolota1 4,859 Super User 2025 Season 1
    Posted 06 Jun 2024 at 16:46:49
    Batch Update, Create, and Upsert SharePoint Lists

    @IvoFe If you go to your list settings & go to the Current Lead Time… column & go to the end of the url on the column edit page, what is listed as the back-end column name? It looks a bit too long to be a backend name, they usually get cut shorter than this.

    (PA Community isn't letting me send any private messages at the moment.)

  • IvoFe Profile Picture IvoFe 2
    Posted 06 Jun 2024 at 02:07:47
    Batch Update, Create, and Upsert SharePoint Lists

    Hello @takolota , I send you a DM, could you please give me a hand? 🙂

  • takolota1 Profile Picture takolota1 4,859 Super User 2025 Season 1
    Posted 04 Jun 2024 at 16:10:26
    Batch Update, Create, and Upsert SharePoint Lists

    @st33zygill try reducing the batch size to like 500

  • st33zygill Profile Picture st33zygill
    Posted 03 Jun 2024 at 23:17:40
    Batch Update, Create, and Upsert SharePoint Lists

    Hi, so i have an interesting scenario. I changed the flow to run on a schedule. As a result of this, i had to set pagination to 5000 for the "list rows present in a table" action. I adjsuted the Top Count and Skip Count function to reflect this. I also updated the length() expression to 4990 within the "do until" loop. The flow runs successful, but what i noticed is that it only updated 4800 items out of the 5300 total existing items within both the sharepoint and excel source list. If i run it again, itll then update the remaining items. I also have the batch size set to the max of 1000. Is there a reason to why this occurs? I would figure the "do until" loop would run until it pulls all items? What am i missing so i can update all of the items with just a single run?

    st33zygill_0-1717456626169.png

     

     

  • takolota1 Profile Picture takolota1 4,859 Super User 2025 Season 1
    Posted 30 May 2024 at 02:03:32
    Batch Update, Create, and Upsert SharePoint Lists

    @keithmlee19 

    Sorry, I don’t know anywhere else to check other than the recycling bin

  • st33zygill Profile Picture st33zygill
    Posted 29 May 2024 at 20:40:26
    Batch Update, Create, and Upsert SharePoint Lists

    Did you ever figure out to why it would take longer?