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!
CodePudding user response:
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.
CodePudding user response:
I made this change and it worked:
var ss = SpreadsheetApp.openById("1UgiKHokV3bpeuCF4lhgc9aQisE3c86OyVjZeats9u24");
var sheet = ss.getSheets()[0];
var data = sheet.getDataRange().getValues();
CodePudding user response:
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