Reputation: 15
I'm trying to delete rows when a specific cell in the row matches any value from a list from another sheet. Existing solutions I find only deal with a single value to be matched.
The script I have below either deletes the wrong rows (the cell doesn't match any values in the list) or returns the error 'rows are out of bounds'. Any pointers appreciated.
function main() {
var ss = SpreadsheetApp.getActive();
var sheet = ss.getSheetByName("Sheet1");
var ssData = sheet.getRange('A2:I').getValues();
var negSheet = ss.getSheetByName("negs");
var negValues = negSheet.getRange('A1:B').getValues();
Logger.log(negValues);
for(var i = 0; i < ssData.length; i++) {
var row = ssData[i];
var matched = row[2];
if(matched == "")
break;
Logger.log(matched);
for(var j = 0; j < negValues.length; j++) {
var negRow = negValues[j];
var negVal = negRow[0];
if(negVal == "")
break;
var rowsDeleted = 1;
for(k = 0; k < ssData.length; k++) {
if (matched == negVal) {
sheet.deleteRow(rowsDeleted);
rowsDeleted--; // Deleted a row
continue;
} else {
rowsDeleted++; // Go to the next row
}
}
}
}}
Upvotes: 0
Views: 662
Reputation: 64120
Try this:
function deleteRowsWithValuesThatHaveMatchesinList() {
const ss=SpreadsheetApp.getActive();
const list=ss.getSheetByName('List');
const rg=list.getRange(1,1,list.getLastRow(),1);//assuming list is in column 1
const lvA=rg.getValues().map(function(r){return r[0]});
const sh=ss.getSheetByName('Sheet');
const rg1=sh.getDataRange();
const vs=rg1.getValues();
let d=0;
let x='index of column you searching for'
vs.forEach(function(r,i){
if(lvA.indexOf(r[x])!=-1) {
sh.deleteRow(i+1-d++);
}
});
}
Maybe this:
function deleteRowsWithValuesThatHaveMatchesinList() {
const ss=SpreadsheetApp.getActive();
const list=ss.getSheetByName('List');
const rg=list.getRange(1,1,list.getLastRow(),1);//assuming list is in column 1
const lvA=rg.getValues().map(function(r){return r[0]});
const sh=ss.getSheetByName('Sheet');
const rg1=sh.getDataRange();
const vs=rg1.getValues();
let d=0;
let x='index of column you searching for'
vs.forEach(function(r,i){
for(var j=0;j<lvA.lengh;j++) {
if(lvA.join('~~~').indexOf(r[x])!=-1){
sh.deleteRow(i+1-d++);
}
}
});
}
Upvotes: 2