Delete row values in more than 1 sheet if exists in another sheet

Question

The code below is from an answer from this post regarding copying row values to a new sheet if it exist in another sheets.

Now, what if instead of copying the duplicate values to sheet 3, I want to delete them from sheets 1 and 2 if it exists in Sheet 3. With the same spreadsheet, I have 3 sheets. The unique value that will be compared on the first 2 sheets is the first column, "ID NUMBER".

Given the values, 784 | John Steep | I.T Department, which exists in all 3 sheets, the same row value should be deleted in Sheet 1 and 2 and retain the same value on Sheet 3.

function copyRowtoSheet3() { 
  var s1 = SpreadsheetApp.openById("ID").getSheetByName('Sheet1');
  var s2 = SpreadsheetApp.openById("ID").getSheetByName('Sheet2'); 
  var s3 = SpreadsheetApp.openById("ID").getSheetByName('Sheet3'); 
  var values1 = s1.getDataRange().getValues();
  var values2 = s2.getDataRange().getValues();
  var resultArray = [];
  for(var n=0; n < values1.length ; n++){
    var keep = false;
    for(var p=0; p < values2.length ; p++){
      Logger.log(values1[n][0]+' =? '+values2[p][0]);
      if( values1[n][0] == values2[p][0] && values1[n][3] == values2[p][4]){
        resultArray.push(values1[n]);
        Logger.log('true');
        break ;// remove this if values are not unique and you want to keep all occurrences...
      }
    }
  }  
  s3.getRange(+1,1,resultArray.length,resultArray[0].length).setValues(resultArray);
}

Can't seem to find the right solution. Tried several scripts but failed to make it work.

Thank you for any advice/suggestion.

Answer 1

Although the other answer works (I didn't test but I guess it does) it uses a lot of spreadsheetApp calls and might be slow if you have a lot of data.

It is possible to get this result using only arrays (if you don't need to keep sheet formatting and/or formulas).

The approach is slightly different as it is easier to keep data instead of removing it.

There are for sure many possible solutions, below is the one I tried : I created a special array that contains only the first column of sheet3 to make the duplicate search simpler.

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;
}

Answer 2

Sheet1

ID NUMBER   NAME        DEPARTMENT
784         John Steep  I.T.
901         Liz Green   H.R.

Sheet2

ID NUMBER   NAME        DEPARTMENT
784         John Steep  I.T.
653         Bo Gore     Marketing

Sheet3

ID NUMBER   NAME        DEPARTMENT
784         John Steep  I.T.
999         Frank White Sales
121         Abid Jones  Engineering
901         Liz Green   H.R.

Script

function main() {
  var ss = SpreadsheetApp.openById("ID");

  var s1 = ss.getSheetByName("Sheet1");  
  var s2 = ss.getSheetByName("Sheet2");  
  var s3 = ss.getSheetByName("Sheet3");  

  var idCol = 1;  // Assuming location of ID column is same in all sheets.

  var s1RowCount = s1.getLastRow();  

  for (var i = 2; i <= s1RowCount; i++) {  // Start at var i = 2 to skip the 
                                           // first row containing the header.
    var id = s1.getRange(i, idCol, 1, 1).getValue();
    deleteDuplicates(s2, id);
    deleteDuplicates(s3, id);
  }
}

function deleteDuplicates(sheet, id) {
  var idCol = 1; // Assuming location of ID column is same in all sheets.
  var rowCount = sheet.getLastRow();
  for (var i = 2; i <= rowCount; i++) {
    var data = sheet.getRange(i, idCol, 1, 1).getValue();
    if (data === id) {
      // Use this to test out the function.
      Logger.log("Duplicate of ID " + id + " in sheet " + 
          sheet.getSheetName() + " at row " + i);
      // Uncomment the next line when ready.
      // sheet.deleteRow(i);
    }
  }
}

Logging Output

[14-11-04 09:16:04:551 PST] Duplicate of ID 784 in sheet Sheet2 at row 2
[14-11-04 09:16:04:587 PST] Duplicate of ID 784 in sheet Sheet3 at row 2
[14-11-04 09:16:04:727 PST] Duplicate of ID 901 in sheet Sheet3 at row 5