0
  var amazon = activeSpreadsheet.getSheetByName('Amazon');
  var lastRow1 = amazon.getLastRow();
  var array1 = amazon.getRange('A2:A' + lastRow1).getValues();

  var source = activeSpreadsheet.getSheetByName('ProductDetails');
  var lastRow2 = source.getLastRow();
  var array2 = source.getRange('B2:B' + lastRow2).getValues();

  n = 2;
  x = 0; // match
  z = 0; // non-match

  for (var i = 0; i < array2.length; i++){
    for (var j = 0; j < array1.length; j++){

      if (array2[i] !== array1[j]){
        z = z + 1;
      }
      else {
        x = 9999
      }
    }
    newsheet.getRange([n],[5]).setValue(z);
    newsheet.getRange([n],[6]).setValue(x);
    if (z > x) {
      newsheet.getRange([n],[1]).setValue(array2[i]);
      n == n++;
      z = 0;
      x = 0;
      }
    else if (z < x) {
      z = 0;
      x = 0;
    }
}

My project is written in GAS (google app scripts) which is essentially, for all intents and purposes JS with variation in libraries.

Basically I am grabbing an element in the array2 and passing it through a loop to match to array1. For every time it does not match it adds 1, and when it matches (should only match once if it has any matches) it stores an arbitrary large number (larger than length of array1) and compares them.

As you can see I've written out to display these values and I always get z = 5183 (length of array1) and x = 0 (meaning no non-matches found). Therefore even if something exists in array 2 and 1 it will always write it to the cell.

What should happen is if there is a match, z= 5182 and x= 9999 (or arbitrary large number) and since 5182 < 9999 it doesn't do anything.

Is my scope wrong? Or am I not writing the If/Else correctly? Or is it something else?

Anthony Li
  • 1

1 Answers1

0

Your code performs a strict comparison between the elements of two Arrays. That's fine, in general. However, for those specific Arrays those elements are also Arrays, which means strict (in)equality is checking to see if those are the exact same array object in memory. See this question for more information.

You probably wanted to do a value-based comparison, which means you need to compare the specific element of that inner array (i.e., index again). if (array2[i][0] !== array1[j][0]) {...} will check the 1st element of the inner array.

Looking at the instantiation of array1 and array2, we see that these are indeed 2D arrays from a single-column Ranges, so there will be only 1 element in each inner array. You can reduce the level of indexing necessary by flattening these arrays when you read them:

const array1 = sheet.getRange(...).getValues().map(function (row) { return row[0]; });
const array2 = ...;

I'm also not sure why you are passing in arrays to Sheet#getRange - you should pass in 1-4 arguments in manners consistent with the method signatures detailed in the Apps Script documentation.

Note that there are much better algorithms for checking if a value exists in a given array - you re-scan all of the 2nd array for every value of the first array. You should conduct thorough research into this topic before asking a new question on how to improve your algorithm.

Lastly, you should implement the best practice of using batch methods - you currently call setValue in a loop. Consider storing the results to write in an array, and then writing with Range#setValues once your loop has completed. There are many questions available for you to review on this topic.

tehhowch
  • 9,645
  • 4
  • 24
  • 42