Speed Up row deletion on apps script

Question

I've made this code to remove empty rows based on if a column is blank, the code is running and doing what it needs to do, however it's taking more than 10 minutes to delete data from a sheet that has more than 15k rows of data, is there a way to speed this up?

function deleteEmptyRowsAll() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  var data = sheet.getRange('B:B').getDisplayValues();
  for (i = data.length - 1; i >= 2; i--) {
    if (data[i][0] === '') {
      sheet.deleteRow(i + 1);
    }
  }
}

Edit: the question is not answered as the post How to delete rows fast if they have empty values at specific columns in Google App Script clears the content of the row instead of deleting the entire row.

Answer 1

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.

Answer 2

I believe your goal is as follows.

• You want to reduce the process cost of your showing script.
• You want to reduce the process cost of sheet.deleteRow(i + 1).

Unfortunately, when sheet.deleteRow() is used in a loop, the process cost becomes high. In this case, how about using Sheets API? When Sheets API is used, I thought that the process cost can be reduced a little.

Modified script:

Before you use this script, please enable Sheets API at Advanced Google services.

function deleteEmptyRowsAll() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('Sheet1'); // Please set your sheet name.
  var sheetId = sheet.getSheetId();
  var values = sheet.getRange("B1:B" + sheet.getLastRow()).getDisplayValues();
  var requests = values.reduce((ar, [b], i) => {
    if (b == "") {
      ar.push({ deleteDimension: { range: { sheetId, startIndex: i, endIndex: i + 1, dimension: "ROWS" } } });
    }
    return ar;
  }, []).reverse();
  Sheets.Spreadsheets.batchUpdate({ requests }, ss.getId());
}

• When this script is run, the column "B" in the data range is checked. When the column "B" is empty, the rows are deleted using Sheets API.
• From var data = sheet.getRange('B:B').getDisplayValues();, if you want to check all rows except for the data range, please modify var values = sheet.getRange("B1:B" + sheet.getLastRow()).getDisplayValues(); to var data = sheet.getRange('B:B').getDisplayValues();.
• If you want to check rows in the data range, you can also modify var values = sheet.getRange("B1:B" + sheet.getLastRow()).getDisplayValues(); to var values = Sheets.Spreadsheets.Values.get(ss.getId(), "'Sheet1'!B1:B").values;. By this, I thought that the process cost might be able to be reduced a little. Ref

References:

• Method: spreadsheets.batchUpdate
• DeleteDimensionRequest

Answer 3

Try this:

function lfunko() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  var data = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getDisplayValues().filter(e => e[1] != '');
  sheet.clearContents();
  sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
}