CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
novice
novice

Reputation: 157

Google Script: Delete row if a value in it exists in more than one sheet

I had posted a question months ago regarding deleting a row if a value in it exists in another sheet.

This is the accepted answer in my previous post.

function deleteRowInSheet1() { 
  var s1 = SpreadsheetApp.openById("SS_ID").getSheetByName('Sheet1');
  var s2 = SpreadsheetApp.openById("SS_ID").getSheetByName('Sheet2'); 
  var values1 = s1.getDataRange().getValues();
  var values2 = s2.getDataRange().getValues();
  var resultArray = [];
  for(var n in values1){
    var keep = true
    for(var p in values2){
      if( values1[n][0] == values2[p][0] && values1[n][1] == values2[p][1]){
        keep=false ; break ;
      }
    }
    if(keep){ resultArray.push(values1[n])};
  }
  s1.clear()
  s1.getRange(1,1,resultArray.length,resultArray[0].length).setValues(resultArray);
}

Now, my question is what if there are more than 1 sheet to be compared to. Let's say I have 4 sheets.

Sheet1:

NAME | PLACE | AGE

Carl | Florida | 45

Mike | Florida | 41

Suzy | Florida

Sheet 2:

NAME | PLACE | AGE

Mike | Florida | 41

Sheet 3:

NAME | PLACE | AGE

Mike | Florida | 41

Sheet 4:

NAME | PLACE | AGE

Mike | Florida | 41

The script should delete Mike Florida row in Sheet 2 to 4 since it has duplicate data in Sheet 1. Column 1 and 2, again are the basis to compare all the data in the 4 sheets since the values will be unique. I tried to modify the answered script but I can't seem to make it run. I understood the logic but not sure which part I missed:

This is my code:

function deleteRowInSheets1to3() { 
      var s1 = SpreadsheetApp.openById("SS_ID").getSheetByName('Sheet1'); //this is the basis of all sheets. values to be compared is column 2
      var s2 = SpreadsheetApp.openById("SS_ID").getSheetByName('Sheet2');  
      var s3 = SpreadsheetApp.openById("SS_ID").getSheetByName('Sheet3');
      var s4 = SpreadsheetApp.openById("SS_ID").getSheetByName('Sheet4'); 

      var values1 = s1.getDataRange().getValues();
      var values2 = s2.getDataRange().getValues();
      var values3 = s3.getDataRange().getValues();
      var values4 = s4.getDataRange().getValues();

      var resultArray = [];
      for(var n in values1){
        var keep = true
        for(var p in values2){
          if( values1[n][0] == values2[p][0] && values1[n][1] == values2[p][1]){
            keep=false ; break ;
          }

          for(var q in values3){
            if( values1[n][0] == values3[q][0] && values1[n][1] == values3[q][1]){
              keep=false ; break ;
            }

            for(var r in values4){
              if( values1[n][0] == values4[r][0] && values1[n][1] == values4[r][1]){
                keep=false ; break ;
              }
            }  
          }  
        }
        if(keep){ resultArray.push(values1[n])};
      }
      s1.clear()
      s1.getRange(1,1,resultArray.length,resultArray[0].length).setValues(resultArray);
    }

Thanks a bunch for any advice! :)

Upvotes: 1

Views: 311

Answers (1)

Orlando
Orlando

Reputation: 40

I think we almost have the same situation. This is a possible duplicate question. See this post.

The answer provided was below (not my code):

function removeDupsInOtherSheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s1 = ss.getSheetByName("Sheet1").getDataRange().getValues();  
  var s2 = ss.getSheetByName("Sheet2").getDataRange().getValues(); 
  var s3 = ss.getSheetByName("Sheet3").getDataRange().getValues();  
  // iterate s3 and check in s1 & s2 if duplicate values exist
  var nS1 = [];
  var nS2 = [];
  var s3Col1 = [];// data in column1 of sheet3
  for(var n in s3){
    s3Col1.push(s3[n][0]);
  }
  for(var n in s1){ // iterate sheet1 and test col 1 vs col 1 in sheet3
    var noDup1 = checkForDup(s1[n],s3Col1)
    if(noDup1){nS1.push(noDup1)};// if not present in sheet3 then keep
  } 
  for(var n in s2){  // iterate sheet2 and test col 1 vs col 1 in sheet3
    var noDup2 = checkForDup(s2[n],s3Col1)
    if(noDup2){nS2.push(noDup2)};// if not present in sheet3 then keep
  }
  Logger.log(nS1);// view result
  Logger.log(nS2);
  ss.getSheetByName("Sheet1").getDataRange().clear();// clear and update sheets
  ss.getSheetByName("Sheet2").getDataRange().clear();
  ss.getSheetByName("Sheet1").getRange(1,1,nS1.length,nS1[0].length).setValues(nS1);
  ss.getSheetByName("Sheet2").getRange(1,1,nS2.length,nS2[0].length).setValues(nS2);
}

function checkForDup(item,s){
  Logger.log(s+' = '+item[0]+'  ?')
    if(s.indexOf(item[0])>-1){
      return null;
    }
  return item;
}

Hope this helps.

Upvotes: 1

Related Questions