Excel Batch Create, Update, and Upsert 05-09-2024 09:48 AM

[takolota]

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 ~2000 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 4 actions or only .4% 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 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 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.

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.

Version 7 Note

Diverting from what is shown in the video, I was able to remove almost all the flow actions in the "Match new and existing data key values then batch update" scope after replicating their functions in the scripts themselves. The flow now goes directly from the "SelectGenerateData" action to the "Run script Update Excel rows" action and the script handles matching up the UpdatedData JSON keys/field names to the destination table headers.

Also version 7 changes the primary key set up in the SelectGenerateData and changes the logic for skipping cell value updates & blanking out cell values.

Now the primary key column name from the destination table must be present in the SelectGenerateData action with the dynamic content for the values you want to match up to update. No more 'PrimaryKey' line, the update script will automatically reference the primary key column in the SelectGenerateData data based on the PrimaryKeyColumnName input on the update script action in the flow.

Now leaving a blank "" in the SelectGenerateData action will make that cell value in the Excel table empty, while leaving a null in the SelectGenerateData action will skip updating / not alter the cell value that currently exists in the Excel table there.

Version 6 which looks closer to the version shown in the video can still be accessed here: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Excel-Batch-Create-Update-and-Upsert/m-p... 

Version 7 Set-Up Instructions

Go to the bottom of this post & download the BatchExcel_1_0_0_xx.zip file. Go to the Power Apps home page (https://make.powerapps.com/). Select Solutions on the left-side menu, select Import solution, Browse your files & select the BatchExcel_1_0_0_xx.zip file you just downloaded. Then select Next & follow the menu prompts to apply or create the required connections for the solution flows.

Once imported, find the Batch Excel solution in the list of solution & click it to open the solution. Then click on the Excel Batch Upserts V7 item to open the flow. Once inside the flow, delete the PlaceholderValue Delete after import action.

Open the Office Script Batch Update compose action to the BatchUpdateV7 script code. Select everything inside the compose input & control + C copy it to the clipboard.

Then find & open an Excel file in Excel Online. Go to the Automate tab, click on All Scripts & then click on New Script.

When the new script opens, select everything in the script & control + V paste the BatchUpdateV7 script from the clipboard into the menu. Then rename the script BatchUpdateV7 & save it. That should make the BatchUpdateV7 reference-able in the later Run script flow action.

Do the same process to import the BatchCreateV7 script.

Then go to the List rows Sample source data action. If you are going to use an Excel table as the source of updated data, then you can fill in the Location, Document Library, File, & Table information on this action. If you are going to use a different source of updated data like SharePoint, SQL, Dataverse, an API call, etc, then delete the List rows Sample source data placeholder action & insert your new get data action for your other source of updated data.

Following that, go to the Excel batch update & create scope. Open the PrimaryKeyColumnName action, remove the placeholder values in the action input & input the column name of the unique primary key for your destination Excel table. For example, I use ID for the sample data.

Then go to the SelectGenerateData action.

If you replaced the List rows Sample source data action with a new get data action, then you will need to replace the values dynamic content from that sample action with the new values dynamic content of your new get data action (the dynamic content that outputs a JSON array of the updated data).

In either case, you will need to input the table header names from the destination Excel table on the left & the dynamic content for the updated values from the updated source action on the right. You MUST include the column header for the destination primary key column & the the primary key values from the updated source data here for the Update script to match up what rows in the destination table need which updates from the source data. All the other columns are optional / you only need to include them if you want to update their values.

After you have added all the columns & updated data you want to the SelectGenerateData action, then you can move to the Run script Update Excel rows action. Here add the Location, Document Library, File Name, Script, Table Name, Primary Key Column Name, ForceMode1Processing, & UpdatedData. You will likely need to select the right-side button to switch the UpdatedData input to a single array input before inserting the dynamic content for the SelectGenerateData action.

Then open the Condition If new records continue else skip action & open the Run script Create Excel rows action. In this run script action input the Location, Document Library, Script, Table Name, & CreateData. You again will likely need to select the right-side button to change the CreateData input to a single array input before inserting the dynamic content for the Filter array Get records not found in table output.

If you need just a batch update, then you can remove the Filter array Get records not found in table & Run script Create Excel rows actions.
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 Filter array Get records not found in table action. Then any updated source data sent to the SelectGenerateData action will just be created, it won't check for rows to update.

Thanks for any feedback,

Please subscribe to my YouTube channel (https://youtube.com/@tylerkolota?si=uEGKko1U8D29CJ86).

And reach out on LinkedIn (https://www.linkedin.com/in/kolota/) if you want to hire me to consult or build more custom Microsoft solutions for you.

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

(ExcelBatchUpsertV7 is the core piece, ExcelBatchUpsertV7b includes a Do until loop set-up if you plan on updating and/or creating more than 1000 rows on large tables.)

watch?v=HiEU34Ix5gA

[takolota]

@racheltsky 

The batch delete hasn’t been set to accept other primary key column names. There should be an action in the template preceding the script where the primary key values are mapped to a key label of “PK”.

So it would look like 

[
{
“PK”:26228

},
{
“PK”:24992

},
…

[racheltsky]

Confirming though that if my primary key column isn't named pk in my table that it would still fail?

[takolota]

@racheltsky No. The primary key column in the Excel table should be whatever column name you enter on the script action for PrimaryKeyColumnName. But there should be a Select action preceding the script where it puts all your primary key values with a PK key label for the DeleteData input on the script action.

[MasaYR]

Hello @takolota ,
I am pretty sure that someone already asked this question but I am wondering if I could combine “ExcelBatchUpsert”and “ExcelBatchDelete” in one flow? 
I am trying to create a flow to compare excel table A (source) and B (destination), and update rows in B to match A, create rows that are in A but not in B, delete rows that are in B but not in A. 

I install your “ExcelBatchUpsertV7”, and it is working beautifully! Now, I am ready to install "ExcelBatchDelete", ideally, in same flow but if not, I will create a new flow for delete. 

Thank you very much for sharing this with all of us!! 

[takolota]

@MasaYR 

Thanks. For ease of set-up I would probably just import the Excel Batch solution package & if you will always maintain less than 100,000 rows then I’d put everything in the ExcelBatchDeleteV4Sync flow into a scope & copy that scope over to the batch upsert flow.

That may leave you with multiple Excel reads for the same data, but as long as you aren’t worried about flow run time that shouldn’t be a big deal.

[MasaYR]

Hi @takolota !

Thank you! It did work! 

I put all the action from "Delete V4Sync" in scope and pasted it in "Upsert V7". 

The only thing is that the last action below does not work. Somehow, the expression in the left side of "is not equal to" was not grabbing anything. 

I put the "result" from "Run script Delete Excel rows" in both left and right for now while I am trying to figure out the expression. 

Anyways, thank you so much for sharing your build! 🙂

[heyjay]

Hi @takolota ,

Can I ask how to go about if what I only need is the batch create?
From the large excel file, there is a specific column that needs to be filtered. Filtered data result has to go to a new workbook Sheet1, then another filter category has to be made, from Sheet1, then the filtered result goes to Sheet2, then another filter for Sheet3 and Sheet4.

How can I go about doing this in bulk since this is an almost 100k rows.

Thanks!

[heyjay]

Hi @takolota ,

In this batch create v7 script :

function main(workbook: ExcelScript.Workbook,
TableName: string,
CreateData: createdata[],
) {
if (CreateData.length != 0) {
const table = workbook.getTable(TableName);
const TableRange = table.getRange();
const CreateDataHeaderArray: string[] = Object.keys(CreateData[0]);
const CreateDataHeaderArrayLength = CreateDataHeaderArray.length;
const TableHeaderArray = table.getHeaderRowRange().getValues()[0].map(arr => arr.toString());
const ColumnCount = TableRange.getColumnCount();
const Blank2dArray = new Array(CreateData.length).fill(null).map(() => new Array(ColumnCount).fill(null));
let TableData = Blank2dArray;
const CreateDataLength = CreateData.length;

//Set calculation mode to manual
workbook.getApplication().setCalculationMode(ExcelScript.CalculationMode.manual);

let CurrentColumnName:string;
//Iterate through each object item in the array from the flow
for (let i = 0; i < CreateDataLength; i++) {
//i is also the row number
//Iterate through each item or line of the current object
for (let j = 0; j < CreateDataHeaderArrayLength; j++) {
//Create each value for each item or column given
CurrentColumnName = CreateDataHeaderArray[j]
if (TableHeaderArray.indexOf(CurrentColumnName) > -1) {
TableData[i][TableHeaderArray.indexOf(CurrentColumnName)] = CreateData[i][CurrentColumnName];
}
}
}
//Append the in-memory TableData to the Excel table
//Split the in-memory TableData into batches that addRows can handle without error & loop through to add each batch in addRows
const BatchSize = 51;
//length - 1 prevents a later slice(n, n) or slice(1000, 1000) which would return a null array & error the addRows
const NumBatches = Math.floor((TableData.length - 1) / BatchSize);
for (let k = 0; k <= NumBatches; k++) {
let BatchRows = TableData.slice(k * BatchSize, (k + 1) * BatchSize)
table.addRows(-1, BatchRows);
}

//Set calculation mode back to automatic
workbook.getApplication().setCalculationMode(ExcelScript.CalculationMode.automatic);
}
}

interface createdata {
'DummyReferenceDoNotUse': (string | undefined)
}


Where do I need to modify to make the creation of data to start on the 2nd row, I added an action to get sample header from an excel template I have because I have excel formula inserted in the first 5 columns starting from column A. My template is set as a table and in that table, there is the headers of 48 columns and the data creation should start on the 6th column leaving the first 5 as nulls. The script and the flow runs perfectly, however there is an empty row after the header because the data creation starts on the 3rd row.

TIA.

[takolota]

@heyjay 
The V7 script will add data to whatever columns match up to the JSON keys in your CreateData array. So to not create data in the first few columns just do not include fields/JSON key labels for the 1st few columns in the JSON array going to the CreateData input.

As for where the rows start to be created, they are added to the bottom of the table. So if your table has 2 rows in it already, then it will start adding rows to the 3rd row position. Can you remove the 2nd empty row in your template?

[heyjay]

@takolota 

no, i can't remove it because there are formulas prepared in it one the data is created. however i was able to figure it out. I modified your script in the line 39, from 

table.addRows(-1, BatchRows); to 

table.addRows(0, BatchRows); and it worked, no more empty rows on the 2nd row, creation starts on the 2nd row already.

However another concern is, I need to create this kind of batch create per sheet in the same workbook, can use the same batch create script? or do i need to modify the script where it calls out the sheet name aside from the table name?

TIA