06-20-2022 12:29 PM - last edited 06-22-2023 06:40 AM
Excel Batch Delete (40x Faster)
Instead of performing a slow 1 deletion per action, these flows send arrays of deletion primary keys to an Office Script. On the Excel side the script can then delete each row it finds with a primary key in the deletion input array.
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. The last few actions will check if the number of rows in the deletion array match the change in number of rows & force a flow failure to make it clear that there was an error.
Office Script Code V3 (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 3 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 BatchDeleteV3.
-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.
Also be aware that some characters in column names, like \ / - _ . : ; ( ) & $ may cause errors when processing the data. Also backslashes \ in the data, which are usually used to escape characters in strings, may cause errors when processing the JSON.
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!
watch?v=LrzjH9dI0is
Excel Batch Create, Update, & Upsert Templates Here:
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
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?
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.
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...
@takolota Very, very kind of you!
I'll work on that this morning and let you know how it turns out. Again, much appreciated!
@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)?