Same values does not match in if condition

Question

This is my first attempt in Google Sheets scripting

I try to compare each row of the first column in two sheets.

If any value in sheet 1 is not in sheet 2, I would add it at the end of sheet 2.

This is the code, the compare values never match, I tried ==, ===, with and without toSting(). I never get the flag found=1, all values in sheet1 col1 are copied at sheet2 col1.

var ui = SpreadsheetApp.getUi();
var ss = SpreadsheetApp.getActiveSpreadsheet();

function Prueba2() {
  var sheet = ss.getSheets()[0];
  var rangeData = sheet.getDataRange();
  sheet.getRange('A1').activate();

  var lastRow = rangeData.getLastRow();
  var searchRange = sheet.getRange(2,1, lastRow, 1);

  var sheet2 = ss.getSheets()[1];
  var rangeData2 = sheet2.getDataRange();
  var lastRow2 = rangeData2.getLastRow();
  var searchRange2 = sheet2.getRange(2,1, lastRow2, 1);

  var l1= searchRange.getLastRow();
  var l2= searchRange2.getLastRow();

  var rangeValues = searchRange.getValues();
  var rangeValues2 = searchRange2.getValues();

  var found=0;
  var arr = [];
  // Loop through array and if condition met
  for ( i = 0; i < lastRow - 1; i++){
    found=0;
    for ( j = 0 ; j < lastRow2 - 1; j++){
      var vi=rangeValues[i];
      var vj=rangeValues2[j];
      if(rangeValues[i].toString() === rangeValues2[j].toString()){
        found=1;     
        break; 
      }; 
    };
    if (found==0){
      Logger.log(rangeValues[i]);
      arr.push(rangeValues[i]); 
    };      
   };
  var toAddArray = [];
  for (k = 0; k < arr.length; ++k){
    toAddArray.push([arr[k]]);
  }

  sheet2.getRange(lastRow2+1, 1, arr.length, 1).setValues(toAddArray);
};

EDIT

This worked, but I don't know why

for ( j = 0 ; j < lastRow2 - 1; j++){
  var vi=rangeValues[i].toString();
  var vj=rangeValues2[j].toString();
  //if(rangeValues[i] === rangeValues2[j]){
  if(vi===vj||vi==""){  
    found=1;     
    break; 
  }; 
};

Answer 1

How about this modification? The value retrieved by getValues() is 2 dimensional array. By considerating this, the script is modified.

Pattern 1:

If your script is modified, how about this modification?

From:

for ( i = 0; i < lastRow - 1; i++){
  found=0;
  for ( j = 0 ; j < lastRow2 - 1; j++){
    var vi=rangeValues[i];
    var vj=rangeValues2[j];
    if(rangeValues[i].toString() === rangeValues2[j].toString()){
      found=1;
      break; 
    }; 
  };
  if (found==0){
    Logger.log(rangeValues[i]);
    arr.push(rangeValues[i]);
  };      
 };

To:

for ( i = 0; i < lastRow - 1; i++){
  found=0;
  for ( j = 0 ; j < lastRow2 - 1; j++){
      // var vi=rangeValues[i]; // This is not used in this script.
      // var vj=rangeValues2[j]; // This is not used in this script.
    if(rangeValues[i][0].toString() === rangeValues2[j][0].toString()){ // Modified
      found=1;
      break; 
    }; 
  };
  if (found==1){ // Modified
    Logger.log(rangeValues[i][0]); // Modified
    arr.push(rangeValues[i][0]); // Modified
  };
 };

• In your situation, you might be able to replace if(rangeValues[i][0].toString() === rangeValues2[j][0].toString()){ to if(rangeValues[i][0] === rangeValues2[j][0]){.

Pattern 2:

As other pattern, how about this modification?

From:

var found=0;
var arr = [];
// Loop through array and if condition met
for ( i = 0; i < lastRow - 1; i++){
  found=0;
  for ( j = 0 ; j < lastRow2 - 1; j++){
    var vi=rangeValues[i];
    var vj=rangeValues2[j];
    if(rangeValues[i].toString() === rangeValues2[j].toString()){
      found=1;     
      break; 
    }; 
  };
  if (found==0){
    Logger.log(rangeValues[i]);
    arr.push(rangeValues[i]); 
  };      
 };
var toAddArray = [];
for (k = 0; k < arr.length; ++k){
  toAddArray.push([arr[k]]);
}

sheet2.getRange(lastRow2+1, 1, arr.length, 1).setValues(toAddArray);

To:

var toAddArray = rangeValues.filter(function(e) {return rangeValues2.some(function(f) {return e[0] && f[0] && e[0] == f[0]})});
sheet2.getRange(lastRow2+1, 1, toAddArray.length, 1).setValues(toAddArray);

Note:

• The reason vi===vj of your added script works is as follows.
  • When rangeValues[i].toString() and rangeValues2[j].toString() are run, 1 dimensional array is converted to a string like ["sample"] to "sample". By this, vi===vj works.

References:

• getValues()
• filter()
• some()

If I misunderstood your question and this was not the result you want, I apologize.