Speed Up row deletion on apps script-CodePudding

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.

CodePudding user response：

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:

CodePudding user response：

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