Copy row values to a new sheet if it exist in another sheet

0
votes

I am new to Google Script and I have a script to create. I found this question, where in he should delete row if a value in it exists in another sheet. Now, my situation is different. Here is my sample Spreadsheet. In that Google 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 exists in the first 2 sheets therefore the whole row should be copied to sheet3.

Sum up, if that ID NUMBER exists in Sheet 1 and 2, it should be copied on Sheet 3.

I tried to modify the script but I can't make it work:

function copyRowtoSheet3() { 
  var s1 = SpreadsheetApp.openById("1RlQTLZyPLasoJGplKemKg9qgcLcvCZZ_tPn6lWXEePw").getSheetByName('Sheet1');
  var s2 = SpreadsheetApp.openById("1RlQTLZyPLasoJGplKemKg9qgcLcvCZZ_tPn6lWXEePw").getSheetByName('Sheet2'); 
  var s3 = SpreadsheetApp.openById("1RlQTLZyPLasoJGplKemKg9qgcLcvCZZ_tPn6lWXEePw").getSheetByName('Sheet3'); 

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

Thanks. Any help/advice is greatly appreciated.

2
google-apps-scriptgoogle-sheets

2 Answers

1
votes

Not sure your condition works... Getting the values that are equal in both sheets is easier than the example you refer to, just keep the data where equality==true.

Try like this (change the ID's to yours):

function copyRowtoSheet3() { 
  var s1 = SpreadsheetApp.openById("1x8buwr______w7MeqZAiJJIX0yC-oITBAtykBAM").getSheetByName('Sheet1');
  var s2 = SpreadsheetApp.openById("1x8buwr______w7MeqZAiJJIX0yC-oITBAtykBAM").getSheetByName('Sheet2'); 
  var s3 = SpreadsheetApp.openById("1x8buwr______w7MeqZAiJJIX0yC-oITBAtykBAM").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][1] == values2[p][1]){
        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);
}
0
votes

Try:

var s1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet 1');
var s2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet 2');
var s3 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet 3');

and further:

s3.clear()

s3.getRange(1,1,resultArray.length,resultArray[0].length).setValues(resultArray);