Skip to main content
Power Automate
    • Connectors
    • Templates
    • Take a guided tour
    • Digital process automation
    • Robotic process automation
    • Business process automation
    • Process advisor
    • AI Builder
  • Pricing
  • Partners
    • Blog
    • Documentation
    • Roadmap
    • Self-paced learning
    • Webinar
    • Business process and workflow automation topics
    • Overview
    • Issues
    • Give feedback
    • Overview
    • Forums
    • Galleries
    • Submit ideas
    • User groups
    • Register
    • ·
    • Sign in
    • ·
    • Help
    Go To
    • Microsoft Power Automate Community
    • Welcome to the Community!
    • News & Announcements
    • Get Help with Power Automate
    • General Power Automate Discussion
    • Using Connectors
    • Building Flows
    • Using Flows
    • Power Automate Desktop
    • Process Advisor
    • AI Builder
    • Power Automate Mobile App
    • Translation Quality Feedback
    • Connector Development
    • Power Platform Integration - Better Together!
    • Power Platform Integrations
    • Power Platform and Dynamics 365 Integrations
    • Galleries
    • Community Connections & How-To Videos
    • Webinars and Video Gallery
    • Power Automate Cookbook
    • Events
    • 2021 MSBizAppsSummit Gallery
    • 2020 MSBizAppsSummit Gallery
    • 2019 MSBizAppsSummit Gallery
    • Community Engagement
    • Community AMA
    • Community Blog
    • Power Automate Community Blog
    • Community Support
    • Community Accounts & Registration
    • Using the Community
    • Community Feedback
    cancel
    Turn on suggestions
    Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
    Showing results for 
    Search instead for 
    Did you mean: 
    • Microsoft Power Automate Community
    • Galleries
    • Power Automate Cookbook
    • Excel Batch Create, Update, and Upsert

    Excel Batch Create, Update, and Upsert

    06-13-2022 09:31 AM - last edited 05-29-2023 07:34 AM

    Super User takolota
    Super User
    43106 Views
    LinkedIn LinkedIn Facebook Facebook Twitter Twitter
    takolota
    Super User takolota
    Super User
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    Excel Batch Create, Update, and Upsert

    ‎06-13-2022 09:31 AM

    Update & Create Excel Records 50-100x Faster

    I was able to develop an Office Script to update rows and an Office Scripts to create rows from Power Automate array data. So instead of a flow creating a new action API call for each individual row update or creation, this flow can just send an array of new data and the Office Scripts will match up primary key values, update each row it finds, then create the rows it doesn't find.
    And these Scripts do not require manually entering or changing any column names in the Script code.

     

    • In testing for batches of 1000 updates or creates, it's doing ~2500 row updates or creates per minute, 50x faster than the standard Excel create row or update row actions at max 50 concurrency. And it accomplished all the creates or updates with less than 25 actions or only 2.5% of the standard 1000 action API calls.

     

    • The Run Script code for processing data has 2 modes, the Mode 2 batch method that saves & updates a new instance of the table before posting batches of table ranges back to Excel & the Mode 1 row by row update calling on the Excel table.
    The Mode 2 script batch processing method will activate for creates & updates on tables less than 1 million cells. It does encounter more errors with larger tables because it is loading & working with the entire table in memory.
    Shoutout to Sudhi Ramamurthy for this great batch processing addition to the template!
    Code Write-Up: https://docs.microsoft.com/en-us/office/dev/scripts/resources/samples/write-large-dataset
    Video: https://youtu.be/BP9Kp0Ltj7U

    The Mode 1 script row by row method will activate for Excel tables with more than 1 million cells. But it is still limited by batch file size so updates & creates on larger tables will need to run with smaller cloud flow batch sizes of less than 1000 in a Do until loop. 
    The Mode 1 row by row method is also used when the ForceMode1Processing field is set to Yes.

     

    Office Script Code
    (Also included in a Compose action at the top of the template flow)

    Batch Update Script Code: https://drive.google.com/file/d/1kfzd2NX9nr9K8hBcxy60ipryAN4koStw/view?usp=sharing
    Batch Create Script Code: https://drive.google.com/file/d/13OeFdl7em8IkXsti45ZK9hqDGE420wE9/view?usp=sharing

    You can download the Version 5 of this template attached to this post, copy the Office Script codes into an online Excel instance, & try it out for yourself.
    -Open an online Excel workbook, go the the automate tab, select New Script, then copy & paste the Office Script code into the code editor. Do this for both the Batch Update and the Batch Create script code. You may want to name them BatchUpdateV6 & BatchCreateV5 appropriately.
    -Once you get the template flow into your environment, follow the notes in the flow to change the settings to your datasources, data, & office scripts.

     

    If you need just a batch update, then you can remove the batch create scope.
    If you need just a batch create, then you can replace the Run script Batch update rows action with the Run script Batch create rows action, delete the update script action, and remove the remaining batch create scope below it. Then any update data sent to the 1st Select GenerateUpdateData action will just be created, it won't check for rows to update.



    (ExcelBatchUpsertV5 is the core piece, ExcelBatchUpsertV5b includes a Do until loop set-up if you plan on updating and/or creating more than 1000 rows on large tables.)

     

    Anyone facing issues with the standard zip file import package method can check this post for an alternative method of importing the flow: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Excel-Batch-Create-Update-and-Upsert/m-p...


    Thanks for any feedback!

     

     

    watch?v=HiEU34Ix5gA

    ExcelBatchUpsertV5.4b.zip
    ExcelBatchUpsertV5.4.zip
    Labels:
    • Labels:
    • Button flows
    • Desktop flows
    • Scheduled flows
    Message 1 of 238
    43,106 Views
    18 Kudos
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic
    • « Previous
      • 1
      • …
      • 18
      • 19
      • 20
      • …
      • 24
    • Next »
    RobFol
    RobFol
    Frequent Visitor
    In response to takolota
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎03-21-2023 09:14 AM

    RobFol_0-1679415252351.png

    by the way I tested this on another list that did not have multiline columns with 8000 records and it worked just fine. 

    Message 181 of 238
    443 Views
    0 Kudos
    Reply
    takolota
    Super User takolota
    Super User
    In response to RobFol
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎03-21-2023 10:04 AM

    @RobFol 

    I'm really not sure on this one.
    There are a few parts of the flow expressions & script that could be messed up on special character combinations in your data like _|- or -#- or _#- or #|# but I don't see those in the data records you have shared.

    Message 182 of 238
    476 Views
    0 Kudos
    Reply
    ExcelGuy_1
    ExcelGuy_1
    New Member
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎03-22-2023 02:46 AM

    @takolota Thanks man, this was very useful. I found a way to slightly improve the performance and allow use when many formulas are referencing the table. I did this by moving the updating table to a separate sheet called "data insert" and then referencing it in the original sheet "Data" with "=table1" in cell "A1". 

    Now the formulas are only calculated when the workbook is opened.

    Message 183 of 238
    459 Views
    2 Kudos
    Reply
    jwilli4380
    jwilli4380
    Regular Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎03-22-2023 05:59 PM

    Hello,

    I am updating a simple product inventory file, I keep getting the following error for the SelectDestinationIndexCells section. Any suggestion? 

     

    InvalidTemplate. The execution of template action 'SelectDestinationIndexCells' failed: The evaluation of 'query' action 'where' expression '@if(equals(mod(item(), 2), 0), if(equals(outputs('CustomDelimitedCells')[add(item(), 1)], '""'), '_||', if(or(equals(outputs('CustomDelimitedCells')[item()], 'PK'), equals(outputs('CustomDelimitedCells')[item()], '_|-')), concat('"', outputs('CustomDelimitedCells')[item()], '":"', replace(replace(outputs('CustomDelimitedCells')[add(item(), 1)], '"', '″'), 'null', ''), '"'), concat('"', outputs('ReformatHeaderObject')[outputs('CustomDelimitedCells')[item()]], '":"', replace(replace(outputs('CustomDelimitedCells')[add(item(), 1)], '"', '″'), 'null', ''), '"') ) ), '_||' )' failed: 'The template language expression 'if(equals(mod(item(), 2), 0), if(equals(outputs('CustomDelimitedCells')[add(item(), 1)], '""'), '_||', if(or(equals(outputs('CustomDelimitedCells')[item()], 'PK'), equals(outputs('CustomDelimitedCells')[item()], '_|-')), concat('"', outputs('CustomDelimitedCells')[item()], '":"', replace(replace(outputs('CustomDelimitedCells')[add(item(), 1)], '"', '″'), 'null', ''), '"'), concat('"', outputs('ReformatHeaderObject')[outputs('CustomDelimitedCells')[item()]], '":"', replace(replace(outputs('CustomDelimitedCells')[add(item(), 1)], '"', '″'), 'null', ''), '"') ) ), '_||' )' cannot be evaluated because property '4' doesn't exist, available properties are 'Handle, Title, Body (HTML), Vendor, Product Category, Type, Tags, Published, Option1 Name, Option1 Value, Option2 Name, Option2 Value, Option3 Name, Option3 Value, Variant SKU, Variant Grams, Variant Inventory Tracker, Variant Inventory Qty, Variant Inventory Policy, Variant Fulfillment Service, Variant Price, Variant Compare At Price, Variant Requires Shipping, Variant Taxable, Variant Barcode, Image Src, Image Position, Image Alt Text, Gift Card, SEO Title, SEO Description, Google Shopping / Google Product Category, Google Shopping / Gender, Google Shopping / Age Group, Google Shopping / MPN, Google Shopping / AdWords Grouping, Google Shopping / AdWords Labels, Google Shopping / Condition, Google Shopping / Custom Product, Google Shopping / Custom Label 0, Google Shopping / Custom Label 1, Google Shopping / Custom Label 2, Google Shopping / Custom Label 3, Google Shopping / Custom Label 4, Variant Image, Variant Weight Unit, Variant Tax Code, Cost per item, Included / United States, Included / International, Price / International, Compare At Price / International, Status'. Please see https://aka.ms/logicexpressions for usage details.'.

    Message 184 of 238
    438 Views
    0 Kudos
    Reply
    takolota
    Super User takolota
    Super User
    In response to jwilli4380
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎03-23-2023 07:01 AM

    @jwilli4380 

    Could you please show some screenshots of your flow run that failed?
    Especially of some of the actions immediately before the failed action like SelectArrayOfArraysUpdateData & CustomDelimitedCells...
    Example:
    ExcelBatchUpsert1.pngExcelBatchUpsert2.png

    Thanks,

    Message 185 of 238
    424 Views
    0 Kudos
    Reply
    leemaa
    leemaa
    Frequent Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎03-23-2023 09:13 AM

    Hello!

     

    First of all, thank you so much for sharing this Power Automate code - it has been so useful for me and have been using it every day

     

    I have 2 questions though:

    1. How can I make it so that it only updates cells that are empty?

    2. How can I make it so that it only updates cells if the source is not empty?

     

    E.g. below will update Sourcing Name and Negotiation

    For the first question - if I had the below information and Sourcing Name already contains a value in the final file, how do I continue updating Negotiation but prevent the source value from overwriting Sourcing Name?

    For the second question - if the source value for Sourcing Name was blank and there Negotiation contained data, how do I prevent the blank value overwriting the existing data and only have source value containing data update?

    leemaa_0-1679587715154.png

     

    Message 186 of 238
    414 Views
    0 Kudos
    Reply
    takolota
    Super User takolota
    Super User
    In response to leemaa
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎03-23-2023 09:27 AM

    @leemaa 

    For the 2nd question, you should be able to use an expression like 

    if(empty(item()?['Sourcing_Name']), '', item()?['Sourcing_Name']) if it isn't already skipping updates for blanks in the source data.

    Your 1st question is tougher and I have to ask if you really need to use a batch update for your scenario as it may be much easier for you to program the logic you specified with some slower looping actions that filter / check if each value is blank in the destination file before writing to it.
    It is possible to perform that logic without looping for a destination file with 100,000 rows or less, but it does involve a significantly more complicated action/JSON/expression set-up to allow the GenerateUpdateData Select action to query the destination data set within an expression within one of the Select input table lines.
    Message 187 of 238
    409 Views
    0 Kudos
    Reply
    leemaa
    leemaa
    Frequent Visitor
    In response to takolota
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎03-23-2023 09:39 AM

    @takolota 

     

    Thank you for the swift reply!

     


    @takolota wrote:

    @leemaa 

    For the 2nd question, you should be able to use an expression like 

    if(empty(item()?['Sourcing_Name']), '', item()?['Sourcing_Name']) if it isn't already skipping updates for blanks in the source data.

    I did try this method as I saw in your notes but it did not work - the blank value in the source data still overwrote the existing data in the final file

     

    See below output I got which overwrote my file:

    I used if(empty(item()?['Location/Tracking']),'',item()?['Location/Tracking'])

     

    {
        "body": [
            {
                "PrimaryKey": "31015867-1-1",
                "Updates": "",
                "Location/Tracking": "",
                "Date Stamp": "2023-03-23"
            },
            {
                "PrimaryKey": "31015867-2-1",
                "Updates": "",
                "Location/Tracking": "",
                "Date Stamp": "2023-03-23"
            },
            {
                "PrimaryKey": "31015867-3-1",
                "Updates": "",
                "Location/Tracking": "",
                "Date Stamp": "2023-03-23"
            },
            {
                "PrimaryKey": "31016149-1-1",
                "Updates": "",
                "Location/Tracking": "",
                "Date Stamp": "2023-03-23"
            }
        ]
    }

     

     

    And the below is the output I got in ComposeDestinationJSON (just before script is run)

    [
        {
            "73": "",
            "74": "",
            "77": "2023-03-23",
            "PK": "31015867-1-1"
        },
        {
            "73": "",
            "74": "",
            "77": "2023-03-23",
            "PK": "31015867-2-1"
        },
        {
            "73": "",
            "74": "",
            "77": "2023-03-23",
            "PK": "31015867-3-1"
        },
        {
            "73": "",
            "74": "",
            "77": "2023-03-23",
            "PK": "31016149-1-1"
        }
    ]
    Message 188 of 238
    403 Views
    0 Kudos
    Reply
    takolota
    Super User takolota
    Super User
    In response to leemaa
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎03-23-2023 10:54 AM

    @leemaa 

    Sorry, that part got messed up in the V5 update.

    Here is a patch...
    On "SelectAddRecordBreakToEach" replace the expression in the Map field with...

     

     

    removeProperty(
    addProperty(
    addProperty(item(), 'PK', item()['PrimaryKey']),
    '_|-', '~'),
    'PrimaryKey')

     

     


    And in "FilterOnlyKeyValueStrings" click edit in advanced mode and replace the filter condition with...

     

     

    @and(not(equals(item(), '_||')), not(contains(item(), '":""')))

     

     


    Then in "ComposeDestinationJSON" replace the JSON expression with...

     

     

    json(concat('[{',
    replace(join(body('FilterOnlyKeyValueStrings'), ','), ',"_|-":"~",', '},{'),
    '}]'))

     

     


    Version 5.3 now has these changes

    Message 189 of 238
    396 Views
    0 Kudos
    Reply
    leemaa
    leemaa
    Frequent Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎03-24-2023 02:04 AM

    @takolota 

     

    Amazing! Once again thank you so much! This has resolved my issue pretty much 🙂

    Message 190 of 238
    373 Views
    0 Kudos
    Reply
    • « Previous
      • 1
      • …
      • 18
      • 19
      • 20
      • …
      • 24
    • Next »

    Power Platform

    • Overview
    • Power BI
    • Power Apps
    • Power Pages
    • Power Automate
    • Power Virtual Agents

    • Sign up free
    • Sign in

    Browse

    • Templates
    • Connectors
    • Partners

    Downloads

    • Mobile
    • Gateway

    Learn

    • Documentation
    • Learn
    • Support
    • Community
    • Give feedback
    • Blog
    • Pricing

    • © 2023 Microsoft
    • Contact us
    • Trademarks
    • Privacy & cookies
    • Manage cookies
    • Terms of use
    • Terms & conditions
    Consumer Privacy Act (CCPA) Opt-Out Icon Your Privacy Choices