How to delete rows fast if they have empty values at specific columns in Google App Script

user_19240194 0
Viewed 83

below is a code that checks if the cells at columns [G, H, J] are empty and delete the row where the the condition is true. Nevertheless, the runtime of the code below is extremely slow, needs approx 15 minutes per 1500 table entries. is there any way to optimise it ? can I hash the rows that meet the condition below and then delete them all at once?

P.S: the original code can be found here https://gist.github.com/dDondero/285f8fd557c07e07af0e which I adapted it to my use case.

function deleteRows() {
    var sheet = SpreadsheetApp.getActiveSheet();
    var rows = sheet.getDataRange();
    var lastRow =sheet.getRange(1,1).getDataRegion(SpreadsheetApp.Dimension.ROWS).getLastRow() + 1;
    var values = rows.getValues();
    var row;
    var rowsDeleted = 0;
    for (var i = 0; i < lastRow; i++) {
      row = values[i];
      if (row[9] == '' && row[7] == '' && row[6] == ''
     ) {
        sheet.deleteRow((parseInt(i)+1) - rowsDeleted);
        rowsDeleted++;
      }
    }
  }
google-apps-script google-sheets javascript
2 Answers

Try:

function DeleteEmptyRows() {

  const sheet = SpreadsheetApp.getActiveSheet()
  const values = sheet.getDataRange()
                      .getValues()
                      .filter(row => row[9] !== '' && row[7] !== '' && row[6] !== '')

  sheet.getDataRange().clearContent()

  return sheet.getRange(1, 1, values.length, values[0].length)
              .setValues(values)                      

}

If you have any background colors attached to rows, let me know and I can make an adjustment for you.

This code will filter out all rows with the empty cells specified, clear the sheet, and then set the values.

user_190435340

Delete rows

function deleteRows() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const rg = sh.getDataRange();
  const vs = rg.getValues().filter(r => !(!r[6] || !r[7] || !r[9]));
  rg.clearContent();
  sh.getRange(1, 1, vs.length, vs[0].length).setValues(vs);
}
user_72150910
Related