Why is this comparison not working, using google aps scripts?

Question

The code below is not giving me the expected result.
It's to compare rows from two ranges and, although the second range's last row equals the one from the first range, it gives me false as the result.

var entryValuesCom = sheet.getRange(7, 1, LastRowSource, 9).getValues();
var dbDataCom = dbSheet.getRange(2, 1, dbSheet.getLastRow(), 9).getValues();
var entryVlArray = new Array();
var dbArray = new Array();

for (var r = 0; r < entryValuesCom.length; r++) {
  if (entryValuesCom[r][0] != '' && entryValuesCom[r][5] != 'Daily Ledger Bal') {
    entryVlArray.push(entryValuesCom[r]);
  }
}
for (var a = 0; a < dbDataCom.length; a++) {
  if (dbDataCom[a][1] != '' && dbDataCom[a][8] == bank) {
    dbArray.push(dbDataCom[a]);
  }
}
var duplicate = false;
loop1:
for (var x = 0; x < entryVlArray.length; x++) {
  loop2:
  for (var j = 0; j < dbArray.length; j++) {
    if (JSON.stringify(entryVlArray) == JSON.stringify(dbArray)) {
      duplicate = true;
      break loop1;
    }
  }
}

Here's a snapshot of how the array is coming:

I've tried it using .join(), but still... This is for thousands of rows, so is this going to do well performance wise?

Answer 1

I believe your goal as follows.

• You want to compare the arrays of entryVlArray and dbArray using Google Apps Script.
• When the duplicated rows are existing between entryVlArray and dbArray, you want to output duplicate = true.

Modification points:

• When your script is modified, at if (JSON.stringify(entryVlArray) == JSON.stringify(dbArray)) {, all 2 dimensional arrays are compared. I think that this might be the reason of your issue. From your script, I think that it is required to compare each element in the 2 dimensional array.

When above points are reflected to your script, it becomes as follows.

Modified script:

From:

var duplicate = false;
loop1:
for (var x = 0; x < entryVlArray.length; x++) {
  loop2:
  for (var j = 0; j < dbArray.length; j++) {
    if (JSON.stringify(entryVlArray) == JSON.stringify(dbArray)) {
      duplicate = true;
      break loop1;
    }
  }
}

To:

var duplicate = false;
for (var x = 0; x < entryVlArray.length; x++) {
  for (var j = 0; j < dbArray.length; j++) {
    if (JSON.stringify(entryVlArray[x]) == JSON.stringify(dbArray[j])) {
      duplicate = true;
      break;
    }
  }
}
console.log(duplicate)

• By this modification, when each element (1 dimensional array) in the 2 dimensional array is the same, duplicate becomes true.

Note:

• As other method, when an object for searching each row value is prepared, I think that the process cost might be able to be reduced a little. In this case, the script is as follows. Please modify as follows.

  • From:

      var duplicate = false;
      loop1:
      for (var x = 0; x < entryVlArray.length; x++) {
        loop2:
        for (var j = 0; j < dbArray.length; j++) {
          if (JSON.stringify(entryVlArray) == JSON.stringify(dbArray)) {
            duplicate = true;
            break loop1;
          }
        }
      }
    

  • To:

      var obj = entryVlArray.reduce((o, e) => Object.assign(o, {[JSON.stringify(e)]: true}), {});
      var duplicate = dbArray.some(e => obj[JSON.stringify(e)]);
    

References:

• reduce()
• some()

Added:

About your following 2nd question,

AMAZING!!!! Would there be a way of capturing these duplicates in a pop up, using reduce() and some()?

When you want to retrieve the duplicated rows, how about the following script? In this case, I thought that filter() is useful instead of some().

Modified script:

var obj = entryVlArray.reduce((o, e) => Object.assign(o, {[JSON.stringify(e)]: true}), {});
// var duplicate = dbArray.some(e => obj[JSON.stringify(e)]);
var duplicatedRows = dbArray.filter(e => obj[JSON.stringify(e)]);
console.log(duplicatedRows)

• In this modified script, you can see the duplicated rows at the log.

• About a pop up you expected, if you want to open a dialog including the duplicated rows, how about adding the following script after the line of var duplicatedRows = dbArray.filter(e => obj[JSON.stringify(e)]);?

    Browser.msgBox(JSON.stringify(duplicatedRows));