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 Delete

    Excel Batch Delete

    06-20-2022 12:29 PM - last edited 01-07-2023 07:21 AM

    Super User takolota
    Super User
    4301 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 Delete

    ‎06-20-2022 12:29 PM

    BatchDeleteForumThumbnail.png

    Excel Batch Delete (40x Faster)

    I was able to develop an Office Script to delete rows in Excel from Power Automate array data. So instead of a flow creating a new action API call for each individual row deletion, this flow can just send an array of deletion primary keys and the Office Script will match up primary key values and delete each row it finds.

     

    In testing the flow completes 1500 row deletions per minute, about 40x faster than the standard Excel delete row actions at max 50 concurrency. And it accomplished each 750 deletions with less than 8 actions or about 1% of the standard 750 action API calls.

     

    The "results" output of the Run script action returns the change in the number of rows, so the number of rows removed since the script started running.

    Version 1 was twice as fast as Version 2, but Version 1 could not delete rows with formulas. The design of Version 2 trades off some speed & efficiency so it can handle a wider range of use-cases like deleting rows with formulas.

     

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

    https://drive.google.com/file/d/1yLqB9Od_r3Mk9wvzBqubBO2bMzm4Ye3U/view?usp=sharing 

     

     

    You can download the Version 2 of this template attached to this post, copy the Office Script code 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. You may want to name the script BatchDeleteV2.
    -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.

     

     

    ExcelBatchDeleteV2 is the core piece.
    ExcelBatchDeleteV2b includes a Do until loop set-up if you plan on deleting more than 750 rows in a single flow run. It also includes a set-up that can filter to just the rows in Excel that are not in another dataset.

     

    If you have any trouble with the standard zip file import, then you can also try an alternate import method of copying the code of a scope containing the flow to the clipboard & pasting it into a new action. See this post:
    https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Excel-Batch-Delete/m-p/1843994/highlight... 



    And please remember to check your dataset & who might be using parts of it before running a large batch delete that may affect their work.

     

     

    Thanks for any feedback!

    ExcelBatchDeleteV2.zip
    ExcelBatchDeleteV2b.zip
    Labels:
    • Labels:
    • Automated Flows
    • Button flows
    • Scheduled flows
    Message 1 of 17
    4,301 Views
    7 Kudos
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic
    • « Previous
      • 1
      • 2
    • Next »
    takolota
    Super User takolota
    Super User
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎06-20-2022 12:31 PM

    Excel Batch Create, Update, & Upsert Templates Here:

    https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Excel-Batch-Create-Update-and-Upsert/td-...

    Message 2 of 17
    3,713 Views
    0 Kudos
    Reply
    takolota
    Super User takolota
    Super User
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎06-20-2022 01:18 PM

    @Yutao 

    Batch Delete V1 is now ready too.

    Message 3 of 17
    3,670 Views
    0 Kudos
    Reply
    takolota
    Super User takolota
    Super User
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎06-28-2022 03:31 PM

    Batch Delete Version 2

    Version 1 was twice as fast as Version 2, but Version 1 could not delete rows with formulas. The design of Version 2 trades off some speed & efficiency so it can handle a wider range of use-cases like deleting rows with formulas.

     

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

    https://drive.google.com/file/d/1yLqB9Od_r3Mk9wvzBqubBO2bMzm4Ye3U/view?usp=sharing 

    ExcelBatchDeleteV2b.zip
    ExcelBatchDeleteV2.zip
    Message 4 of 17
    3,206 Views
    0 Kudos
    Reply
    rbunge
    rbunge Advocate V
    Advocate V
    In response to takolota
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎07-21-2022 12:58 PM

    Hi @takolota -

     

    This looks fantastic! Just what we need at the moment!

     

    Having a bit of trouble importing it -- getting this error:

     

    Flow save failed with code 'MultipleErrorsOccurred' and message 'The dynamic operation request to API 'excelonlinebusiness' operation 'GetSingleScript' failed with status code 'NotFound'. This may indicate invalid input parameters. Error response: { "status": 404, "message": "Script not found. It may have been unshared or deleted.\r\nclientRequestId: f28d9f0b-a413-434d-ab51-7b463782b269", "error": { "message": "Script not found. It may have been unshared or deleted." }, "source": "excelonline-eus.azconn-eus-002.p.azurewebsites.net" };The dynamic operation request to API 'excelonlinebusiness' operation 'GetSingleScript' failed with status code 'NotFound'. This may indicate invalid input parameters. Error response: { "message": "Script not found. It may have been unshared or deleted.\r\nclientRequestId: f28d9f0b-a413-434d-ab51-7b463782b269", "status": 404, "error": { "message": "Script not found. It may have been unshared or deleted." }, "source": "excelonline-eus.azconn-eus-002.p.azurewebsites.net" };The dynamic operation request to API 'excelonlinebusiness' operation 'GetTable' failed with status code 'NotFound'. This may indicate invalid input parameters. Error response: { "status": 404, "message": "Item not found\r\nclientRequestId: f28d9f0b-a413-434d-ab51-7b463782b269\r\nserviceRequestId: e815083e-9700-4710-bfdd-bbfd83608db2", "error": { "message": "Item not found" }, "source": "excelonline-eus.azconn-eus-002.p.azurewebsites.net" }'

     

    Have confirmed that a file named Book.xlsx exists in root, and that the script has been pasted and shared with the workbook.

     

    Ideas?

    Message 5 of 17
    2,909 Views
    0 Kudos
    Reply
    takolota
    Super User takolota
    Super User
    In response to rbunge
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎07-21-2022 01:07 PM

    @rbunge 

     

    If this is from the flow edit screen from the link in the import warning screen, then I ran into a similar error recently importing it into another account.


    Once I added the Scripts to Excel, I had to retry the entire import, select my own Excel file tables in all the Excel actions, & select the Scripts that I just added to Excel, then it allowed me to save it.

    I don’t know why it wouldn’t recognize the scripts until after I restarted the entire import.

    Message 6 of 17
    2,906 Views
    0 Kudos
    Reply
    rbunge
    rbunge Advocate V
    Advocate V
    In response to takolota
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎07-21-2022 02:32 PM

    Thanks for the quick reply.

     

    This is at the initial import stage -- can't even get past that, so never get to the point where I can edit the flow and customize actions, etc...

     

    rbunge_0-1658439079770.png

     

    Message 7 of 17
    2,909 Views
    0 Kudos
    Reply
    takolota
    Super User takolota
    Super User
    In response to rbunge
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎07-21-2022 03:03 PM

    @rbunge 

    That's really strange, I still get the link to visit the flow & fix things.
    BatchDeleteImport.png

    But if you are still not getting that link, then you can try importing the attached file where I removed all the Excel actions. You'll have to go back through and re-add them with the help of the pictures below...

    ReCreate1.pngReCreate2.pngReCreateFlow.png

    ExcelBatchDeleteV2(No-Connections).zip
    Message 8 of 17
    2,896 Views
    1 Kudo
    Reply
    rbunge
    rbunge Advocate V
    Advocate V
    In response to takolota
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎07-22-2022 05:44 AM

    @takolota Very, very kind of you!

     

    I'll work on that this morning and let you know how it turns out. Again, much appreciated!

    Message 9 of 17
    2,875 Views
    1 Kudo
    Reply
    rbunge
    rbunge Advocate V
    Advocate V
    In response to rbunge
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎07-22-2022 07:22 AM

    @takolota  -- all sorted now, and it's BRILLIANT! 1000 records deleted in 17 seconds vs 26 minutes for row-by-row Apply to Each.

     

    Question - as it came, the pagination was on and threshold was set to 25,000. On the action side, the Top Count was set to 750. First time I tested, it only deleted the first 750. On the second test, I removed the Top Count and it deleted all 1,000. Does pagination with Excel not work as it does with SharePoint (Top Count being batch size, and Threshold being total number of items processed)?

    Message 10 of 17
    2,870 Views
    0 Kudos
    Reply
    • « Previous
      • 1
      • 2
    • 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