javascript - Google Script: Delete row if a value in it exists in another sheet

Question

I have a question with Google Scripting. I just started using Google Script this February so I don't have deep understanding with it yet. Anyway my question is:

Can I delete a row if certain values of it exist in another sheet? I tried several concepts I found in the internet but I was not able to come up with a working function.

Here's the Google Doc for a detailed view.

For example, in Sheet 1 I have two rows:

---

NAME | PLACE | AGE

Carl | Florida | 45

Mike | Florida | 41

---

And in Sheet 2:

---

NAME | PLACE | AGE

Mike | Florida | 41

---

The script should delete Mike Florida row in Sheet 1 since it has duplicate data in Sheet 2. So basically, if data in Sheet 2 exists in Sheet 1, it should be deleted in Sheet 1. But the tricky part is, I have to compare column 1 and column 2 only. Column 3 is not important. If column 1 and 2 in Sheet 2 have the same exact value in Sheet 1, it should be deleted in Sheet 1 and data in Sheet 2 should remain.

I tried this tutorial in Google site but still I was not able to compare the two sheets and remove the duplicate data.

Thank you so much. Any help/idea/advice will be greatly appreciated. :)

+++++++++

I just came up with a code now and used 2D Arrays Library.

function deleteRowInSheet1() { 
   var s1 = SpreadsheetApp.openById("COPY SPREADSHEET ID HERE").getSheetByName('Sheet1');
   var s2 = SpreadsheetApp.openById("COPY SPREADSHEET ID HERE").getSheetByName('Sheet2'); 
   var values1 = s1.getDataRange().getValues();
   var values2 = s2.getDataRange().getValues();
  // Check if Mike exists in SS1.
  if (ArrayLib.find(values2, 0, 'Mike') != -1) {
    for( var row = values1.length -1; row >= 0; --row ) {
      if (values1[row][0] == 'Mike')
      s1.deleteRow(parseInt(row)+1);
    }
  }
}

But, this code only gets specific value which is Mike. If row Mike exists in Sheet 2, row Mike in Sheet 1 will be deleted too. I tried modifying the code more to compare the two values from the two different sheets but I can't seem to make it work.

Answer 1

The problem with the 2D array library you use is that the find method returns an integer corresponding to its position in the array, which is not really a helpful information for you in this case. I'd sugggest to loop in both arrays (which is very quick) and use a boolean to determine if we keep (or not) the values in sheet1. Here is the code I used for testing and it seems to work as expected.

function deleteRowInSheet1() { 
  var s1 = SpreadsheetApp.openById("1RxtFWZJRWxgW-zUM6S-dvZ0yFe-G2DGJFWI3gAt-1T0").getSheetByName('Sheet1');
  var s2 = SpreadsheetApp.openById("1RxtFWZJRWxgW-zUM6S-dvZ0yFe-G2DGJFWI3gAt-1T0").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);
}

As you can see I delete the whole sheet to rebuild it with a new data array but doing this I loose any formatting that might have been present. If that's an issue for you then you'll have to use clearContents() instead. See doc here but delete any formatting in every unused rows below the last row... not very hard to implement I guess.