How can I remove cells mentioning containing "takehome" or similar phrases?

Hopper Kremer 0
Viewed 42

I want to remove all jobs mentioning "takehome", "take home", "take-home", etc. from this sheet: https://docs.google.com/spreadsheets/d/1UgiKHokV3bpeuCF4lhgc9aQisE3c86OyVjZeats9u24/edit#gid=0

This code does not do anything:

function myFunction() {
var sheet = SpreadsheetApp.openById("1UgiKHokV3bpeuCF4lhgc9aQisE3c86OyVjZeats9u24");
var data = sheet.getDataRange().getValues();
  for (var i = data.length - 1; i>=0; i--) {
     if (data[i][0].includes('takehome')) {
     sheet.deleteRow(i); 
     }
  }
}

How can I do this, thanks!

google-sheets google-apps-script
3 Answers

Try to change the lines:

if (data[i][0].includes('takehome')) {
sheet.deleteRow(i);

with:

if (data[i][0].match(/take([^a-z]*)home/i)) {
sheet.deleteRow(i+1);

[^a-z]* means any (or none) symbols except the alphabetical ones from 'a' to 'z'.

i means to ignore case.

Just in case, the full code is here:

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  for (var i = data.length - 1; i >= 0; i--) {
    if (data[i][0].match(/take([^a-z]*)home/i)) sheet.deleteRow(i + 1);
  }
}
Yuri Khristich0

I made this change and it worked:

var ss = SpreadsheetApp.openById("1UgiKHokV3bpeuCF4lhgc9aQisE3c86OyVjZeats9u24");
var sheet = ss.getSheets()[0];
var data = sheet.getDataRange().getValues();
userFDSs0

Try this:

function myFunction() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const rem = ["take home", "takehome", "take-home"];//you can add other options in this array
  rem.forEach(s => {
    sh.createTextFinder(s).matchEntireCell(false).findAll().forEach(r => {
      sh.deleteRow(r.getRow());
      //Logger.log(r.getRow())
    })
  })

}

Another Possibility:

function myFunction() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  sh.createTextFinder("take.*home").useRegularExpression(true).matchEntireCell(false).findAll().sort((a,b) => b.getRow() - a.getRow()).forEach(r => {
    sh.deleteRow(r.getRow());
    //Logger.log(r.getRow())
  })
}

The latter one will handle just about everything that can be between take and home including multiple characters. It will remove all of these:

take   home
take-home
takehome
take_home help
takehome
take and add a bunch of charactors home
user30hu0
Related