Delete row in google sheet via app scripts if columns meet criteria

Question

I want to be able to delete a row if certain columns match the criteria. This is what I have so far:

function deleteEachRow(){
  let tempSheet = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = tempSheet.getSheetByName("Intermediate");
   var RANGE = sheet.getDataRange();
  var rangeVals = RANGE.getValues();



}

from here I would like to find out if column[11] !=='' and column[12] ==='completed', if it meets the criteria then delete the row.

Any ideas?

Here is the code, I'm sure that will helpful: https://stackoverflow.com/questions/37924842/deleting-rows-in-google-sheets-using-google-apps-script — Nauman Ilyas, Sep 24 '21 at 12:57

score 1 · Answer 1 · answered Sep 24 '21 at 15:18

function deleteEachRow(){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getSheetByName("Intermediate");
  const sr = 2;//data start row
  const rg = sh.getRange(sr, 1, sh.getLastRow() - sr + 1, sh.getLastColumn());
  const vs = rg.getValues();
  let d = 0;
  vs.forEach((r,i) => {
    if(r[10] && r[11] == 'completed') {//column 11 and 12
      sh.deleteRow(i + sr - d++);//d increments on each delete
    }
  });
}

Delete row in google sheet via app scripts if columns meet criteria

1 Answers1