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!
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
That's really strange, I still get the link to visit the flow & fix things.
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...
