07-21-2022 07:20 AM - last edited 07-21-2022 07:25 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
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...
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.
Thanks for any feedback!
it matches and doesnt have any special characters do i put it as dynamic content or just the text?
now i get: We were unable to run the script. Please try again.
Runtime error: Line 14: Cannot read property 'join' of null
@Anonymous This is on the Batch Update, correct?
let ArrayPK = table.getColumn(PrimaryKeyColumnName).getRange().getValues().join("#|#").split("#|#")
When making this, I found that a join followed by a split helped get everything into a single level of an array, in other words it took an array of arrays and helped turn it into just a regular array.
That join error is saying the primary key column array is returning null or it isn't finding any values in your primary key.
It is on the Batch Update, I have values in my table and they show up when i try to pivot table or power query so i dont know why its doing that
You can try creating a new Script in Excel & just add the “table” variable initialization & run that above code within a console.log( ) to see what it returns.
Could you please share some of the outputs from preceding actions.
For example, what is the output for
Select Generate Update Data
All earlier steps are completed successfully. See below snapshots. just give you some background: 1) destination file already has data in the table. source data needs to be added below the existing data. 2) destination file has more columns than what I have mapped but i think that won't be an issue.
is it necessary that primary key column should be the first one always ?
No the primary key does not need to be the 1st column. That should all work fine.
But I need you to click on the outputs of each of those actions so I can see what all the inputs are to the failing action.
If it needs to open a separate window to view all the outputs, then you can save that output as a .txt file & share it here if your data isn’t sensitive.
That is the normal template. You shouldn’t need to edit this action.
But I can’t really tell much beyond that, I don’t know what your primary key is from the previous action or if it matches the primary key in your source.
If you can share more details of the flow run in a private message, please do.