Comparing values from two arrays runs but fails to evaluate

Question

I've spent two full working days searching the forums and troubleshooting this in general, but as I'm relatively new to java, this could yet be a case where a newbie is violating the fundamentals. To the extent that that is true, please pardon.

Context: The business case I'm coding for requires comparing records between two separate Google Spreadsheet files, very much like sql queries two tables using primary and foreign keys. To facilitate, the two ranges from the respective Spreadsheet files are loaded into separate arrays, and then the fields acting as primary and foreign keys are compared for a match.

Problem: The code runs and, from what I've observed using the debugger, a match should be identified if the IF statement were to evaluate, but the IF statement fails to evaluate and so no such match is identified. I've tried to make this easy to see in the log that will be produced when you run the code in the accompanying reconstruction (where i = 2 and the matched value is 32).

I've checked that the data types of the compared fields are the same in the two Spreadsheets, and I've experimented with different comparison operators in case the array values were floating-point numbers. Furthermore, I've attempted loading the arrays using a for loop after declaring them with the [ [ ],[ ] ] syntax instead of the syntax used in the accompanying reconstruction. And finally, I've made the IF statement evaluate properly only when the function's arguments are not stored in an array at all. This however is an unsatisfactory solution on account of the constraint mentioned below.

Constraints: Workarounds that eliminate the use of the arrays are likely impractical if they proliferate calls to the server, as was the case when I attempted using the singular getValue() method (versus the plural getValues() method) to load the array one value at a time. This phenomenon would not be observed in the accompanying reconstruction because the two tables intended for comparison are in the same file.

Also, whereas the two series loaded into the array are in adjacent columns in the accompanying reconstruction, they are not in my actual use case. For that reason, any workarounds that consolidate the two getValues() methods into one will likely be impractical as well.

What Success Will Look Like: When the variable named "return" is no longer undefined in the log after the code runs, then a match has been identified and stored!

Supplementary Material: The essence of this problem is reconstructed for you in this Google Sheet to illustrate, and I'll copy the code below.

function simulation() {

  var thisSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var nameObj = {}
  var nameArray = []
  nameObj.name = thisSheet.getRange(2,1,3,1).getValues();
  nameObj.number = thisSheet.getRange(2,2,3,1).getValues();
  nameArray.push(nameObj);

  var vendorObj = {}
  var vendorArray = []
  vendorObj.vendorName = thisSheet.getRange(2,4,3,1).getValues();
  vendorObj.vendorNumber = thisSheet.getRange(2,5,3,1).getValues();
  vendorArray.push(vendorObj);

  for (var i = 1; i < vendorArray[0].vendorNumber.length + 1; i++) {
    matchOnNumber(nameArray,vendorArray[0].vendorNumber[i - 1]);
  }
}


function matchOnNumber(nameArray, vendorNumber) {
  var thisSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  for (var i = 0; i < nameArray[0].name.length; i++) {
    Logger.log("vendorNumber = " + vendorNumber + ", i = " + i + ", Matched against: " + nameArray[0].number[i]);
    if (nameArray[0].number[i] == vendorNumber) {
      var result = nameArray[0].number[i];
    }
  }
  Logger.log("Result: " + result);
  return result;
}

So what do you see in the log? – johnnyaug Sep 15 '16 at 23:03 — johnnyaug, Sep 15 '16 at 23:03
Welcome to Stackoverflow. A question well asked! – Robin Gertenbach Sep 16 '16 at 07:25 — Robin Gertenbach, Sep 16 '16 at 07:25

score 0 · Answer 1 · answered Sep 16 '16 at 07:23

You can fix this by changing

if (nameArray[0].number[i] == vendorNumber)

to

if (nameArray[0].number[i][0] == vendorNumber)

The results will then be undefined, 32 and undefined (Which makes sense given 23 and 100 are not in the Data for Name Array table.

The reason this is is because getValues() always returns a 2d array of rows and columns to be unambiguous about the layout of the data.

nameArray contains one element (index 0) which is the nameObj.
nameObj.number gets assigned the getValues result of the table.
That is ostensibly a column vector but in terms of Google Apps Script it is a 3x1 Matrix.
To access an actual value you need to reference the row and then index 0 for the only column available.

Comparing values from two arrays runs but fails to evaluate

1 Answers1