Delete rows after check text in a specific column

Question

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

Since deleterowoncheck is not the name of one of the simple triggers then I assume it must be an installable trigger so which type of trigger did you use? — Cooper
– Cooper, Commented Jan 20, 2023 at 17:26

Fernando Lara · Accepted Answer · 2023-01-20 23:25:05Z

3

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:

answered Jan 20, 2023 at 23:25

Fernando Lara

2,4462 gold badges6 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Diego Kersjes Over a year ago

Does this search for text in the desired column? Also can I add more sheets?

Fernando Lara Over a year ago

It searches for text in the whole defined sheet and returns the range where it finds the text, then gets the row and deletes it.

Fernando Lara Over a year ago

Can you provide me more details on your use case about adding more sheets?

Diego Kersjes Over a year ago

I mean can I use this script for multiple specific sheets only? How to implement this?

Fernando Lara Over a year ago

You can use the script in other sheets, just create another Apps Script file, then change the name of the sheet in the code and make sure you create another trigger

Collectives™ on Stack Overflow

Delete rows after check text in a specific column

1 Answer 1

References:

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

References:

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related