Delete rows after check text in a specific column-CodePudding

I would like to know which script to use with the following conditions:

Built in trigger Google Sheets at a specific time, not after edit or change.
Delete entire rows where the text "canceled" is in one of the cells of column D (4).
Should only check in rows >=5.
Text "canceled" appears because of a formula in column D.
Specific sheets only!

What I have so far, but not working.

function deleterowoncheck (e){
 
    var sheets = ["TEST"]; // Please set your expected sheet names;
    var sheet = e.range.getSheet();
    if (sheets.includes(sheet.getSheetName())) 

   if (e.range.getColumn() == 4 && e.range.getRow() >= 5 && e.range.getValue() == "CANCELED") {

  {

    var sheet = e.range.getSheet(); // Sheet in which the change was made
      sheet.deleteRow(e.range.getRow());
       e.source.toast('Deletion complete.');
  }
}
}

CodePudding user response：

You can try the following script:

function deleteRowOnCheck() {
  var ss = SpreadsheetApp.getActive().getSheetByName("Name of the sheet");
  var data = ss.createTextFinder("CANCELED").findAll();
  for(var i=0; i<data.length; i  )
  {
    var textFinder = ss.createTextFinder("CANCELED");
    textFinder.findNext();
    ss.deleteRow(textFinder.getCurrentMatch().getRow());
  }
}

Just remember to configure the installable time driven trigger as desired.

References: