Skip to main content

Notifications

Excel Batch Delete

takolota1 Profile Picture Posted by takolota1 4,777

ExcelBatchDeleteThumbnail.png

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.

 

Version 3.1 closer to what is reviewed in the YouTube video is still available here. But for version 4 you can follow the Import & Set-Up instructions below.

 

 

Version 4 Import & Set-Up

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.

BatchDeleteV4SetUp0-2.png

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

BatchDeleteV4SetUp1.png

 

Open the Office Script Batch Delete action to the 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 New Script & remove all the placeholder script code.

BatchDeleteV4SetUpScript1.pngBatchDeleteV4SetUpScript2.png

 

Control + V paste the Batch Delete script code from the clipboard into the menu. Then rename the script BatchDeleteV4 & save it. That should make the BatchDeleteV4 reference-able in the later Run script flow action.

BatchDeleteV4SetUpScript3.png

 

Go to the Batch Delete Excel scope to the List rows & Filter array actions. Input your Location, Document Library, File, & Table for your dataset.

Then in the Filter array action you can add any more complex filters to filter down to just the rows/records you want deleted.

BatchDeleteV4SetUp2.pngBatchDeleteV4SetUp3.png

 

Then in the Run script Delete Excel Rows action input your Location, Document Library, File, & Script. Once you enter the correct script it should load more parameters where you can enter your Table Name, Primary Key Column Names, & Delete Data. The DeleteDataPrimaryKeyColumnName should be the column name of the primary key from the data that is being fed in the DeleteData input. DestinationPrimaryKeyColumnName should be the column name of the primary key from the destination Excel table where the data is being deleted. In the regular Excel Batch Delete V4 flow & any flow where one is pulling from the destination table itself in the List rows to tell what rows to delete, the two PrimaryKeyColumnName inputs will be the same because they are both the primary key column name from the destination table. So they may only be different when the DeleteData is not coming from the destination table data.

For the DeleteData input, when you initially open this script action you may need to select the right-side toggle to "Switch to input entire array" to get a single input box for an array. There you should enter the dynamic content output for the action immediately preceding the Run script Delete Excel rows action, like "Filter array More filters Excel" or "BatchRecords".

BatchDeleteV4SetUp4.pngBDFix1.pngBDFix2.png

 

 


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,

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 V4 (Also included in a Compose action at the top of the template flow)

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

 

If the legacy import method does not work, see this alternate Solutions package import method: Re: Excel Batch Delete - Page 5 - Power Platform Community (microsoft.com)

 

  • ExcelBatchDeleteV4 is the core piece.
  • ExcelBatchDeleteV4Sync includes a simplified method of filtering to just the rows in Excel that are not in another dataset, but is limited to data sources & Excel tables with 100,000 rows or less.
  • ExcelBatchDeleteV4b 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 fuller set-up that can filter to just the rows in Excel that are not in another dataset with a 300,000 table row limit that can be further expanded by following the pattern of intersections, joins, & filters in the "Get all data, filter to items in destination not in source" Scope.


Categories:

Comments

  • takolota1 Profile Picture takolota1 4,777
    Posted at
    Excel Batch Delete

    @keithmlee19 

    If for some reason it doesn’t maintain the string typing, you can also switch the Map section to the JSON object view & manually put double quotes “ “ around the expression box.

  • keithmlee19 Profile Picture keithmlee19
    Posted at
    Excel Batch Delete

    Thanks a ton, works great now!

    It does seem to get rid of the (visible?) string casting when I save, close, and reopen the flow, but it still seems to work fine so I'm not gonna worry about it too much haha.

  • takolota1 Profile Picture takolota1 4,777
    Posted at
    Excel Batch Delete

    @keithmlee19 Okay, the problem isn't with the script, it's with the flow actions used to determine which rows/primary keys are in the destination that are not in the source. With the SharePoint ID as integer & the Excel ID as a string you'll need to add a string( ) around the item() expressions in the Select actions like so...

    StringIntIssue.png

     

    I'll make these changes to the import files too.

    Thanks,

  • takolota1 Profile Picture takolota1 4,777
    Posted at
    Excel Batch Delete

    @keithmlee19 The version 4 scripts are already set to convert both key column values to strings before comparing / finding matching values, so SharePoint integer ID values should work with a corresponding ID Excel column values.
    Could you share more of what your data looks like in SP & in Excel and what you have set up in the Run script action like for the PrimaryKeyColumnName inputs?

    Thanks,

  • keithmlee19 Profile Picture keithmlee19
    Posted at
    Excel Batch Delete

    Hi, would it be possible for you to make the same update to the BatchDeleteV4 script that you did on the batch create/upsert scripts where it can use SharePoint PKs? Thanks a ton!

  • takolota1 Profile Picture takolota1 4,777
    Posted at
    Excel Batch Delete

    Version 4

     

    -Removes the need to alter the data in a Select to label each primary key value with a "PK" key. The script can now accept any JSON array in the DeleteData as long as the primary key column name for the DeleteData & primary key column name for the Excel table to delete in are specified.

    -Updates the script to try to avoid issues batch deleting on tables with a lot of formulas.

    -Updates the script to allow sending DeleteData that doesn't exist on the destination Excel table. So if the script finds some of the DeleteData doesn't exist in the Excel table the Run Script action will not fail. It will delete any rows it does find from the DeleteData & continue on.

  • takolota1 Profile Picture takolota1 4,777
    Posted at
    Excel Batch Delete

    @Tcoz2000 Ah, thanks. That actually makes a lot more sense. I never went back to this batch delete script to add the lines to turn off spreadsheet calculations during the row updates like I did with the batch update script. The...

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

     

    See the spreadsheet by default will recalculate all its formulas each time a row is updated. So if one tries to update 100 rows, then it will recalculate the entire sheet 100 times. These functions try to turn that off so the formulas only recalculate once after all the rows have been updated.

     

    Here is the updated Batch Delete V4 script with those lines included:

    function main(workbook: ExcelScript.Workbook,
     TableName: string,
     DeleteDataPrimaryKeyColumnName: string,
     DestinationPrimaryKeyColumnName: string,
     DeleteData: deletedata[],
    ) {
    if(DeleteData.length !=0) {
     const table = workbook.getTable(TableName);
     let RowNum: number;
     const TableRange = table.getRange();
     const TableData = TableRange.getValues();
     let ArrayPK = table.getColumn(DestinationPrimaryKeyColumnName).getRange().getValues().map(arr => arr[0].toString());
     const DeleteDataPK: string[] = DeleteData.map(arr => arr[DeleteDataPrimaryKeyColumnName].toString());
     const ColumnCount = TableRange.getColumnCount();
     const ColumnRange = Array.from(Array(ColumnCount - 1).keys()).map(x => x + 1)
     ColumnRange.push(0)
     const InitialRowCount = TableRange.getRowCount();
     const TableSize = InitialRowCount * ColumnCount;
    
     console.log(`Starting table size is ${TableSize} cells.`);
    
     //Set calculation mode to manual
     workbook.getApplication().setCalculationMode(ExcelScript.CalculationMode.manual);
    
     //Create a blank row at the end of the table to copy into each deletion row
     table.addRow();
     const TableRange2 = table.getRange()
     const EndTableBlankRow = TableRange2.getRow(TableRange2.getRowCount() - 1)
     const EndTableBlankRowPK = table.getColumnByName(DestinationPrimaryKeyColumnName).getRange().getRow(TableRange2.getRowCount() - 1).getValue()
    
     // Update tag deletion items row by row if the table is too large for the V2 batch update
     //Iterate through each object item in the array from the flow
     for (let i = 0; i < DeleteData.length; i++) {
     //Get the row number for the line to update by matching the foreign key from the other datasource to the primary key in Excel
     RowNum = ArrayPK.indexOf(DeleteDataPK[i]);
     //If an index was found, so if the index function returned a number greater than 0, then update the row to blank.
     if (RowNum > 0) {
     //Set each deletion row to blank. Can not directly delete the row in this step as that causes the index of all the other deletion rows to change.
     TableRange.getRow(RowNum).copyFrom(EndTableBlankRow, ExcelScript.RangeCopyType.values)
     }
     }
     //RemoveDuplicates on all columns to remove the identical blank records
     table.resize(TableRange.getAbsoluteResizedRange(table.getRange().removeDuplicates(ColumnRange, false).getUniqueRemaining(), ColumnCount));
    
     //Refresh after deletion index changes
     ArrayPK = table.getColumn(DestinationPrimaryKeyColumnName).getRange().getValues().map(arr => arr[0].toString());
     const LastBlank = ArrayPK.indexOf(String(EndTableBlankRowPK));
     let FinalRowCount = workbook.getTable(TableName).getRange().getRowCount().valueOf();
     let RowsDeleted = InitialRowCount - FinalRowCount;
     if (LastBlank > 1 || FinalRowCount > 2) {
     //Delete the last blank row that wasn't removed by the duplicates function	
     table.deleteRowsAt((LastBlank - 1), 1);
     }
     //If the deletion runs against the top of the table adjust the count to avoid a false negative error
     if (FinalRowCount = 2) { RowsDeleted = RowsDeleted + 1 };
    
     //Set calculation mode back to automatic
     workbook.getApplication().setCalculationMode(ExcelScript.CalculationMode.automatic);
    
     console.log(`Number of Rows Deleted: ${RowsDeleted} (Number of rows deleted is also provided as the result value)`);
     console.log(`Initial Row Count: ${InitialRowCount}`);
     console.log(`Final Row Count: ${workbook.getTable(TableName).getRange().getRowCount().valueOf()}`);
     console.log(`Final Table Size: ${workbook.getTable(TableName).getRange().getRowCount().valueOf() * ColumnCount} cells`);
     return RowsDeleted
    }
    }
    
    interface deletedata {
     'DummyReferenceDoNotUse': (string | undefined)
    }
  • Tcoz2000 Profile Picture Tcoz2000
    Posted at
    Excel Batch Delete

    UPDATE: I found another table that was using index to call fields from this particular table I wanted the script for.  For some reason that affected this script from running large batches on ANY table.  I'm not entirely sure why, but it is in fact working after removing that index formula.

  • racheltsky Profile Picture racheltsky
    Posted at
    Excel Batch Delete

    I modified the script from the batch update to update all headers that I provide to a blank value, then run the remove duplicate an delete last blank function. This seems to be working more consistently for me. 

     

    function main(workbook: ExcelScript.Workbook,
     TableName: string,
     PrimaryKeyColumnName: string,
     ForceMode1Processing: boolean,
     UpdatedData: updateddata[]
    ) {
     if (UpdatedData.length != 0) {
     const table = workbook.getTable(TableName);
     let RowNum: number = 1;
     const UpdatedDataLength = UpdatedData.length;
     const UpdatedDataHeaderArray: string[] = Object.keys(UpdatedData[0]);
     const UpdatedDataHeaderArrayLength = UpdatedDataHeaderArray.length;
     const TableHeaderArray = table.getHeaderRowRange().getValues()[0].map(arr => arr.toString());
     const TableRange = table.getRange();
     const TableRangeNoHeader = table.getRangeBetweenHeaderAndTotal();
     let TableData = TableRange.getValues();
     let ArrayPK = table.getColumn(PrimaryKeyColumnName).getRange().getValues().map(arr => arr[0].toString());
     const UpdatedDataPK: string[] = UpdatedData.map(arr => arr[PrimaryKeyColumnName].toString());
     let ArrayPKErrors: (string | number)[] = ["", 0];
     const ColumnCount = TableRange.getColumnCount();
     const ColumnRange = Array.from(Array(ColumnCount - 1).keys()).map(x => x + 1)
     let TableSize = (TableRange.getRowCount()) * (ColumnCount);
     const InitialRowCount = TableRange.getRowCount();
     const TableSizeBatchProcLimit = 1000000;
    
     table.addRow();
     const TableRange2 = table.getRange()
     const EndTableBlankRow = TableRange2.getRow(TableRange2.getRowCount() - 1)
     const EndTableBlankRowPK = table.getColumnByName(PrimaryKeyColumnName).getRange().getRow(TableRange2.getRowCount() - 1).getValue()
    
     //Set calculation mode to manual
     workbook.getApplication().setCalculationMode(ExcelScript.CalculationMode.manual);
    
     console.log(`Table size is ${TableSize} cells.`);
     if (TableSize > TableSizeBatchProcLimit) { console.log(`You have exceeded the ${TableSizeBatchProcLimit} total table cell limit for processing larger batches on larger destination tables in the office script, please either reduce your destination table size or use a batch size of 1000 or less in the cloud flow.`) }
     // If the table is less than 1 million cells & not something likely big enough to make errors in the V2 batch processing method then use the batch processing, else use the V1 row by row update method that isn't as affected by table size, but does have a lower cloud flow batch size limit.
     // So if someone generally uses large batch sizes, but then their table grows past 1 million cells, then this will revert to the V1 row by row processing with the smaller batch file size limit and the cloud flow will start to error and they will need to switch their flow settings back to smaller batch sizes as well.
     if (TableSize < TableSizeBatchProcLimit && ForceMode1Processing != true) {
    
     let CurrentColumnName: string
     //Iterate through each object item in the array from the flow
     for (let i = 0; i < UpdatedDataLength; i++) {
     //If the record's Primary Key value is found continue, else post to error log
     if (ArrayPK.indexOf(UpdatedDataPK[i]) > 0) {
     //Get the row number for the line to update by matching the foreign key from the other datasource to the primary key in Excel
     RowNum = ArrayPK.indexOf(UpdatedDataPK[i])
    
     //Iterate through each item or line of the current object
     for (let j = 0; j < UpdatedDataHeaderArrayLength; j++) {
     //Update each value for each item or column given
     CurrentColumnName = UpdatedDataHeaderArray[j]
     if (TableHeaderArray.indexOf(CurrentColumnName) > -1) {
     TableData[RowNum][TableHeaderArray.indexOf(CurrentColumnName)] = ""
     }
     }
     }
     //Post PK not found value to ArrayPKErrors
     else { ArrayPKErrors.push(UpdatedData[i][PrimaryKeyColumnName]) };
     }
     //Get array of 1st row formulas to re-apply to columns after posting the updates to the table
     let FirstRowFormulas: string[] = []
     for (let c = 0; c < ColumnCount; c++) {
     FirstRowFormulas.push(TableRangeNoHeader.getColumn(c).getRow(0).getFormula());
     }
    
     // If the entire table is smaller than 35,000 cells, then just post to the table. Else batch update.
     if (TableSize < 35000) {
     //Post table in memory to the Excel table
     TableRange.setValues(TableData);
     }
     else {
    
     // Run The Batch Update - (Batch update code source: https://docs.microsoft.com/en-us/office/dev/scripts/resources/samples/write-large-dataset)
     const CELLS_IN_BATCH = 15000;
    
     console.log(`Calling update range function...`);
     const updated = updateRangeInBatches(TableRange.getCell(0, 0), TableData, 10000);
     if (!updated) {
     console.log(`Update did not take place or complete. Check and run again.`);
     }
    
     function updateRangeInBatches(
     startCell: ExcelScript.Range,
     values: (string | boolean | number)[][],
     cellsInBatch: number
     😞 boolean {
     const startTime = new Date().getTime();
     console.log(`Cells per batch setting: ${cellsInBatch}`);
    
     // Determine the total number of cells to write.
     const totalCells = values.length * values[0].length;
     console.log(`Total cells to delete in the target range: ${totalCells}`);
     if (totalCells <= cellsInBatch) {
     console.log(`No need to batch -- deleting directly`);
     updateTargetRange(startCell, values);
     return true;
     }
    
     // Determine how many rows to write at once.
     const rowsPerBatch = Math.floor(cellsInBatch / values[0].length);
     console.log("Rows per batch: " + rowsPerBatch);
     let rowCount = 0;
     let totalRowsUpdated = 0;
     let batchCount = 0;
    
     // Write each batch of rows.
     for (let i = 0; i < values.length; i++) {
     rowCount++;
     if (rowCount === rowsPerBatch) {
     batchCount++;
     console.log(`Calling update next batch function. Batch#: ${batchCount}`);
     updateNextBatch(startCell, values, rowsPerBatch, totalRowsUpdated);
    
     // Write a completion percentage to help the user understand the progress.
     rowCount = 0;
     totalRowsUpdated += rowsPerBatch;
     console.log(`${((totalRowsUpdated / values.length) * 100).toFixed(1)}% Done`);
     }
     }
     console.log(`Updating remaining rows -- last batch: ${rowCount}`)
     if (rowCount > 0) {
     updateNextBatch(startCell, values, rowCount, totalRowsUpdated);
     }
     let endTime = new Date().getTime();
     console.log(`Completed ${totalCells} cells deleted. It took: ${((endTime - startTime) / 1000).toFixed(6)} seconds to complete. ${((((endTime - startTime) / 1000)) / cellsInBatch).toFixed(8)} seconds per ${cellsInBatch} cells-batch.`);
     return true;
     }
     /**
     * A helper function that computes the target range and updates. 
     */
     function updateNextBatch(
     startingCell: ExcelScript.Range,
     data: (string | boolean | number)[][],
     rowsPerBatch: number,
     totalRowsUpdated: number
     ) {
     const newStartCell = startingCell.getOffsetRange(totalRowsUpdated, 0);
     const targetRange = newStartCell.getResizedRange(rowsPerBatch - 1, data[0].length - 1);
     console.log(`Updating batch at range ${targetRange.getAddress()}`);
     const dataToUpdate = data.slice(totalRowsUpdated, totalRowsUpdated + rowsPerBatch);
     try {
     targetRange.setValues(dataToUpdate);
     } catch (e) {
     throw `Error while updating the batch range: ${JSON.stringify(e)}`;
     }
     return;
     }
     /**
     * A helper function that computes the target range given the target range's starting cell
     * and selected range and updates the values.
     */
     function updateTargetRange(
     targetCell: ExcelScript.Range,
     values: (string | boolean | number)[][]
     ) {
     const targetRange = targetCell.getResizedRange(values.length - 1, values[0].length - 1);
     console.log(`Updating the range: ${targetRange.getAddress()}`);
     try {
     targetRange.setValues(values);
     } catch (e) {
     throw `Error while updating the whole range: ${JSON.stringify(e)}`;
     }
     return;
     }
     }
     //Re-apply the formulas from the 1st row to the entire columns after the update
     for (let f = 0; f < ColumnCount; f++) {
     if (FirstRowFormulas[f].toString().startsWith("=")) {
     TableRangeNoHeader.getColumn(f).getRow(0).setFormula(FirstRowFormulas[f])
     TableRangeNoHeader.getColumn(f).getRow(0).autoFill(table.getRangeBetweenHeaderAndTotal().getColumn(f).getAddress(), ExcelScript.AutoFillType.fillDefault)
     }
     }
     }
     // Update row by row if the table is too large
     else {
     let CurrentRow = TableRange.getRow(RowNum).getValues();
     let CurrentColumnName: string;
     //Iterate through each object item in the array from the flow
     for (let i = 0; i < UpdatedDataLength; i++) {
     //If the record's Primary Key value is found continue, else post to error log
     if (ArrayPK.indexOf(UpdatedDataPK[i]) > 0) {
     //Get the row number for the line to update by matching the foreign key from the other datasource to the primary key in Excel
     RowNum = ArrayPK.indexOf(UpdatedDataPK[i]);
     //Create an in-memory copy of the table row to update
     CurrentRow = TableRange.getRow(RowNum).getValues();
    
     //Iterate through each item or key value pair in the update data
     for (let j = 0; j < UpdatedDataHeaderArrayLength; j++) {
     //Update each value in the in-memory table row copy for each item in the update data
     CurrentColumnName = UpdatedDataHeaderArray[j]
     if (TableHeaderArray.indexOf(CurrentColumnName) > -1) {
     CurrentRow[0][TableHeaderArray.indexOf(CurrentColumnName)] = ""
     }
     }
     //Insert the updated in-memory row copy back to the table row
     TableRange.getRow(RowNum).setValues(CurrentRow)
     }
     }
     }
    
     //RemoveDuplicates on all columns to remove the identical blank records
     table.resize(TableRange.getAbsoluteResizedRange(table.getRange().removeDuplicates(ColumnRange, false).getUniqueRemaining(), ColumnCount));
    
     //Refresh after deletion index changes
     ArrayPK = table.getColumn(PrimaryKeyColumnName).getRange().getValues().map(arr => arr[0].toString());
     const LastBlank = ArrayPK.indexOf(String(EndTableBlankRowPK));
     let FinalRowCount = workbook.getTable(TableName).getRange().getRowCount().valueOf();
     let RowsDeleted = InitialRowCount - FinalRowCount;
     if (LastBlank > 1 || FinalRowCount > 2) {
     //Delete the last blank row that wasn't removed by the duplicates function	
     table.deleteRowsAt((LastBlank - 1), 1);
     }
     //If the deletion runs against the top of the table adjust the count to avoid a false negative error
     if (FinalRowCount = 2) { RowsDeleted = RowsDeleted + 1 };
    
     //Set calculation mode back to automatic
     workbook.getApplication().setCalculationMode(ExcelScript.CalculationMode.automatic);
     
     console.log(`Number of Rows Deleted: ${RowsDeleted} (Number of rows deleted is also provided as the result value)`);
     console.log(`Initial Row Count: ${InitialRowCount}`);
     console.log(`Final Row Count: ${workbook.getTable(TableName).getRange().getRowCount().valueOf()}`);
     console.log(`Final Table Size: ${workbook.getTable(TableName).getRange().getRowCount().valueOf() * ColumnCount} cells`);
     }
    }
    
    interface updateddata {
     'DummyReferenceDoNotUse': (string | undefined)
    }
  • takolota1 Profile Picture takolota1 4,777
    Posted at
    Excel Batch Delete

    @Tcoz2000 
    Can you share your dataset in a direct message to me?

    Thanks,