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 03-13-2023 10:46 AM

    takolota
    Community Champion
    25817 Views
    LinkedIn LinkedIn Facebook Facebook Twitter Twitter
    takolota
    takolota Community Champion
    Community Champion
    • 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 V5
    (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 BatchUpdateV5 & 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.2.zip
    ExcelBatchUpsertV5.2b.zip
    Labels:
    • Labels:
    • Button flows
    • Desktop flows
    • Scheduled flows
    Message 1 of 174
    25,817 Views
    17 Kudos
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic
    • « Previous
      • 1
      • …
      • 11
      • 12
      • 13
      • …
      • 18
    • Next »
    rsalam
    rsalam Helper I
    Helper I
    In response to takolota
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎01-27-2023 01:08 PM

    I have over 99 columns, Where can i expand the limit over 50? 

    Message 111 of 174
    339 Views
    0 Kudos
    Reply
    takolota
    takolota Community Champion
    Community Champion
    In response to rsalam
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎01-27-2023 01:13 PM

    @rsalam 

    What version are you using? The newest versions should not have any column limits. If there is still a Select action with a line for each potential column, then that is an earlier version.

    Thanks,

    Message 112 of 174
    337 Views
    1 Kudo
    Reply
    rsalam
    rsalam Helper I
    Helper I
    In response to takolota
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎01-27-2023 01:43 PM

    I just wanted to ask before i start mapping out. I am using the newest version. I will map it out tonight. I am getting the data from Power Bi and exporting it to excel template however i have six different sheets that i need to update. What is the best way to update all the sheets in the same flow? 

    Message 113 of 174
    325 Views
    0 Kudos
    Reply
    takolota
    takolota Community Champion
    Community Champion
    In response to rsalam
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎01-27-2023 01:50 PM

    @rsalam 

    You may want to use parallel branches for that. But you will probably want to put the two scopes into another larger scope & copy that across branches so you don't have to re-write connections between actions each time you create & copy a new branch.
    ParallelBranch.png

    Message 114 of 174
    318 Views
    1 Kudo
    Reply
    rsalam
    rsalam Helper I
    Helper I
    In response to takolota
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎01-27-2023 02:13 PM

    Thanks for your help. I really appreciate it. I try putting the two scope into another scope. But looks like for some of the action, i have to rewrite the connections. 

    rsalam_0-1674857593606.png

     

    Message 115 of 174
    311 Views
    2 Kudos
    Reply
    takolota
    takolota Community Champion
    Community Champion
    In response to jbleach
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎01-31-2023 12:58 AM

    @jbleach 

     

    I looked over & added the suggested expression adjustments to a V5.3

    Message 116 of 174
    239 Views
    0 Kudos
    Reply
    rsalam
    rsalam Helper I
    Helper I
    In response to jbleach
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎01-31-2023 09:40 AM

    Looks like the minus does throws an error. I have replaced the select arrays with yours and it works perfectly fine. 

    Message 117 of 174
    224 Views
    2 Kudos
    Reply
    rorib91
    rorib91
    New Member
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎02-10-2023 03:03 AM

    Thanks for this amazing tool. Unfortunately I face an error which I don't understand. My source file is 1,500 rows long and my dest file is 23,000 rows long, both have 35 columns.

    When running the flow I have this error:

    rorib91_0-1676026906702.png

    It successfully ran on smaller files of same type....

    I tried to decrease BatchSize to 100 with no success

    Message 118 of 174
    135 Views
    0 Kudos
    Reply
    takolota
    takolota Community Champion
    Community Champion
    In response to rorib91
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎02-10-2023 06:01 AM

    @rorib91 

     

    I haven't dealt with that error before. HTTP documentation: https://developer.mozilla.org/en-US/docs/Web/HTTP/Status/502

    But if it was working with smaller table-sizes, then started to error on larger tables, try switching the ForceMode1Processing to Yes

    Message 119 of 174
    126 Views
    0 Kudos
    Reply
    rorib91
    rorib91
    New Member
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎02-10-2023 06:20 AM

    Thanks for the answer !

    I just tried with ForceMode1Processing to Yes, but ran into another error message:

    {
        "statusCode": 400,
        "headers": {
            "Access-Control-Expose-Headers": "Version",
            "mise-correlation-id": "xxxxxxxxxxxx",
            "x-ms-workflow-name": "xxxxxxxxxxxxxx",
            "Version": "3.6",
            "x-ms-client-request-id": "xxxxxxxxxxxxxxxxxxxxxxxxx",
            "x-ms-request-id": "xxxxxxxxxxxxxxxxxxxx",
            "Strict-Transport-Security": "max-age=31536000; includeSubDomains",
            "X-Content-Type-Options": "nosniff",
            "X-Frame-Options": "DENY",
            "Timing-Allow-Origin": "*",
            "x-ms-apihub-cached-response": "true",
            "x-ms-apihub-obo": "false",
            "Cache-Control": "no-store, no-cache",
            "Date": "Fri, 10 Feb 2023 14:07:13 GMT",
            "Content-Length": "298",
            "Content-Type": "application/json"
        },
        "body": {
            "message": "We were unable to run the script. Please try again.\nOffice JS error: Line 171: Range getCell: The request failed with status code of 429.\r\nclientRequestId: xxxxxxxxxxxxxxxx",
            "logs": [
                "[2023-02-10T14:07:11.1970Z] Table size is 840492 cells."
            ]
        }
    }
     
    Message 120 of 174
    117 Views
    0 Kudos
    Reply
    • « Previous
      • 1
      • …
      • 11
      • 12
      • 13
      • …
      • 18
    • 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
    California Consumer Privacy Act (CCPA) Opt-Out Icon Your California Privacy Choices