加快应用程序脚本上的行删除速度

我编写了这段代码，根据列是否为空来删除空行，代码正在运行并执行其需要执行的操作，但是从包含超过 15k 行的工作表中删除数据需要 10 多分钟数据，有没有办法加快速度？

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);
    }
  }
}

编辑：问题没有作为帖子得到回答如果 Google App 脚本中的特定列有空值，如何快速删除行 https://stackoverflow.com/questions/72447883/how-to-delete-rows-fast-if-they-have-empty-values-at-specific-columns-in-google清除该行的内容而不是删除整行。

您的代码使用SpreadsheetAppAPI。它运行缓慢的原因是它单独调用来单独删除每一行。要通过该 API 更有效地删除行，请将连续行分组并一次性删除这些行批次，如下所示：

/**
* 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;
  }, []);
}

为了获得更好的性能，请使用 Sheets API，如 Tanaike 在本线程和快速删除 Google 应用脚本中的特定列 https://stackoverflow.com/a/72602266/13045193.