I'm working on a script to archive (copy and delete) rows from a spreadsheet.
To do this I go through every row in the worksheet to see if it's completed.
I can batch the first copy process by gathering up all the rows and writing the range of values all at the end.
However, when it comes time to delete the rows, I still have to loop through and call sheet.deleteRow()
once for each row. I can't use sheet.deleteRows()
because the rows are not consecutive.
Here's an example of the way I'm trying to use the script:
<!-- language: lang-js -->function archiveRows_(archiveSheet, sheetID) {
// get req values
var reqSpreadSheet = SpreadsheetApp.openById(sheetID);
var reqSheet = reqSpreadSheet.getSheetByName("Mstr_Wrkbk");
var currentValues = reqSheet.getDataRange().getValues();
// container for staged data
var rowsToArchive = [];
var rowsToDelete = [];
// check for archivable content
var currentRows = currentValues.length;
for (i = 0; i < currentRows - 1; i++) {
var row = currentValues[i]
// check if we meet the criteria for archival
if (shouldArchive_(row)) {
// add row to archive list
rowsToArchive.push(row);
rowsToDelete.push(i + 1);
}
}
// archive rows if we found some
var numRows = rowsToArchive.length; if (!numRows) return 0;
var numCols = rowsToArchive[0].length;
// add rows to archive sheet
var nextFreeRow = archiveSheet.getLastRow();
archiveSheet.insertRows(nextFreeRow + 1, numRows);
// write archive rows
archiveSheet.getRange(nextFreeRow + 1,1, numRows, numCols).setValues(rowsToArchive);
// Committ Changes
SpreadsheetApp.flush();
// delete original rows (we have to loop backwards so delete works)
for (i = rowsToDelete.length - 1; i >= 0; i--) {
// delete row from master sheet
var rowNum = rowsToDelete[i];
reqSheet.deleteRow(rowNum);
}
// Committ Changes
SpreadsheetApp.flush();
return numRows;
}
It's this part that runs really slowly:
<!-- language: lang-js -->// delete original rows (we have to loop backwards so delete works)
for (i = rowsToDelete.length - 1; i >= 0; i--) {
// delete row from master sheet
var rowNum = rowsToDelete[i];
reqSheet.deleteRow(rowNum);
}
Is there any way to optimize / batch this operation?