CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Jenkins B
Jenkins B

Reputation: 15

App Script to delete rows if cells match values from list

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

Answers (1)

Cooper
Cooper

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

Related Questions