Your code uses the SpreadsheetApp API. The reason it runs slowly is that it makes a separate call to delete each row individually. To delete rows more efficiently through that API, group consecutive rows into batches and delete such row batches each in one go, like this:
/**
* Deletes all rows in Sheet1 where the value in column B2:B is blank.
*/
function deleteRowsWhereColumnBIsBlank() {
const ss = SpreadsheetApp.getActive();
const column = ss.getRange('Sheet1!B2:B');
const condition = (row) => row[0] === '';
ss.toast(`Deleting rows...`);
const numDeletedRows = deleteRowsConditionally_(column, condition);
ss.toast(`Deleted ${numDeletedRows} rows.`);
}
/**
* Deletes all rows where a condition closure returns true.
*
* @param {SpreadsheetApp.Range} range The range to look at.
* @param {Function} condition A closure {String[]} that determines whether a row should be deleted.
* @return {Number} The number of rows deleted.
*/
function deleteRowsConditionally_(range, condition) {
// version 1.0, written by --Hyde, 8 September 2022
// - see https://stackoverflow.com/q/73651127/13045193
const rowStart = range.getRow();
const values = range.getDisplayValues();
const rowsToDelete = [];
values.forEach((row, rowIndex) => {
if (condition(row)) {
rowsToDelete.push(rowStart + rowIndex);
}
});
deleteRows_(range.getSheet(), rowsToDelete);
return rowsToDelete.length;
}
/**
* Deletes from a sheet the rows whose row numbers are given in an array.
*
* @param {SpreadsheetApp.Sheet} sheet A spreadsheet sheet where to delete rows.
* @param {Number[]} rowsToDelete The rows to delete, identified by 1-indexed row numbers.
* @return {Number} The count of delete operations done, i.e., number of the consecutive row runs deleted from the sheet.
*/
function deleteRows_(sheet, rowsToDelete) {
// version 1.1, written by --Hyde, 21 August 2022
const rowNumbers = rowsToDelete.filter((value, index, array) => array.indexOf(value) === index);
const runLengths = getRunLengths_(rowNumbers.sort((a, b) => a - b));
for (let i = runLengths.length - 1; i >= 0; i--) {
sheet.deleteRows(runLengths[i][0], runLengths[i][1]);
}
return runLengths.length;
}
/**
* Counts consecutive numbers in an array and returns a 2D array that
* lists the first number of each run and the number of items in each run.
*
* The numbers array [1, 2, 3, 5, 8, 9, 11, 12, 13, 5, 4] will get
* the result [[1, 3], [5, 1], [8, 2], [11, 3], [5, 1], [4, 1]].
*
* For best results, sort the numbers array like this:
* const runLengths = getRunLengths_(numbers.sort((a, b) => a - b));
* Note that duplicate values in numbers will give duplicates in result.
*
* @param {Number[]} numbers The numbers to group into runs.
* @return {Number[][]} The numbers grouped into runs, or [] if the array is empty.
*/
function getRunLengths_(numbers) {
// version 1.1, written by --Hyde, 31 May 2021
if (!numbers.length) {
return [];
}
return numbers.reduce((accumulator, value, index) => {
if (!index || value !== 1 + numbers[index - 1]) {
accumulator.push([value]);
}
const lastIndex = accumulator.length - 1;
accumulator[lastIndex][1] = (accumulator[lastIndex][1] || 0) + 1;
return accumulator;
}, []);
}
For even better performance, use the Sheets API as demonstrated by Tanaike in this thread and at Delete specific columns in Google App Script FAST.
