Comparing two 2D arrays with different length for matches

Question

i try to wrap my head around a problem I encounter while writing a small script for Google Sheets. The basic thing I want to do is to get the entries of a whole column and compare it with the entries of another column of a different sheet which might have a different length. Then the matching entries should get pushed to another array and in another column of the first sheet each row should be labeled with "Already in other Spreadsheet" where one of the matching entries appear.

First column of the first sheet contain 5 elements [starting at row 1] banana apple cucumber strawberry raspberry

Second column in the second sheet contains 9 elements [starting at row 1] tomato pineapple strawberry walnut apple watermelon kiwi banana raspberry

That is the basic code so far:

    function arraycompare() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var ss1 = SpreadsheetApp.openById("...Sheet_ID...");
  var sheet1 = ss1.getSheetByName("Sheet1");

  var lastrow = sheet.getLastRow();
  var lastrow1 = sheet1.getLastRow();

  var range = sheet.getRange(1, 1, lastrow, 1);
  var sheetdata = range.getValues();

  var range1 = sheet1.getRange(1, 2, lastrow, 1);
  var sheet1data = range1.getValues();

  var match = [];
for (var x = 0; x < sheetdata.length; x++) {
  for (var y = 0; y < sheetdata[x].length; y++) {
     for(var i = 0; i < sheet1data.length; i++){
       for(var j = 0; j < sheet1data[i].length; j++){
         if (sheetdata[x][y] == sheet1data[i][j]){
           sheet.getRange(x, 2).setValue("Already in other Spreadsheet");
           match.push(sheetdata[x][y]);
         };
       };
     };
  };
};

  //test
  Logger.log(match);

};

The output of the logger just shows me [apple, strawberry] as matches and not raspberry. I assume, that the for loop stops at 5 elements but I don't really understand why and how to fix this with 2d arrays?

Also the "Already in Spreadsheet" notion appears at the row position of banana and cucumber in row 2 and not in the same row as apple and strawberry. Really want to know, what i'm doing wrong here and how to fix this to get a better understanding here.

Hope you guys can help me out. Thanks a lot in advance.

Answer 1

I would suggest the following changes:

(1) As you are using data from single columns, you don't need the variables y and j.

sheetdata[x][0] will be the value of the string in the xth row of the array.

And sheet1data[i][0] will be the value of the string in the ith row of the array.

(2) Arrays are zero-based, i.e. the first "row" in the array is 0. Where as the first row on the sheet is 1.

So when writing to the sheet for the xth row in the array, you need to write to the x+1 th row on the sheet.

Here is what the for loops will look like with these changes:

  for (var x = 0; x < sheetdata.length; x++) {
    for(var i = 0; i < sheet1data.length; i++){
      if (sheetdata[x][0] == sheet1data[i][0]){
        sheet1.getRange(x + 1, 2).setValue("Already in other Spreadsheet");
        match.push(sheetdata[x][0]);
      };
    };
  };

---

And as Tanaike-san said, lastRow1 should be used in calculating range1 and not lastRow:

  var range1 = sheet1.getRange(1, 2, lastrow1, 1);