Apps Script doesn't compare 2 values in if-statement

Question

I've created a new project that should compare a name from Sheet1 with a list of names in Sheet2 and check if the name is already in that list. For that I chose a for-loop to get through the list in Sheet2 and compare every list entry with the name from Sheet1. Only if the name already exists in the list stuff should happen.

function myFunction() {
  var tabSheet1 = 'Sheet1';
  var tabSheet2 = 'Sheet2';

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName(tabSheet1);
  var sheet2 = ss.getSheetByName(tabSheet2);
  var lastRow1 = sheet2.getLastRow() + 1;
  var playerNameSheet1 = sheet1.getRange(1, 1).getValue();

  for (var j = 1; j < lastRow1; j++) {
    var playerNameSheet2 = sheet2.getRange(j, 1).getValue();
    if (playerNameSheet2 == playerNameSheet1) {
      ...stuff...
    }
  }
}

Now my problem is that it seems like the script isn't able to identify that a name already exists in the list. Both values (playerNameSheet1 and playerNameSheet2) are completely identical (no space or other hidden obstacles), however the script would never continue with stuff in the if-statement. My example name to test my script was "Oliver Baumann".

I'm a bit confused about it - even more, because another comparison a bit later in the script code works just fine.

I've already tried to change the operator into === but that wouldn't work either.

if (playerNameSheet2 === playerNameSheet1) {
   ...stuff...
}

I've also observed that if I put a dot behind both variables I'm only able to choose further functions with playerNameSheet2, but not with playerNameSheet1. Maybe I did a typing error and am just too blind to see it? I don't know. Anyone an idea how to resolve the issue?

The complete project can be found here. However, a lot of stuff is in german and very rudimental. I just started it and haven't got time to clean it up. Just so you don't wonder.

Answer 1

You will likely benefit from a change to your inspection routine - currently what you have is not scalable due to the slow, repeated calls to the Spreadsheet Service. Use a batch method - getValues() - to return a Javascript Array that contains all the content you could want from your 'master list' of names:

// Create an N x 1 array of arrays, e.g. [ [r1c1], [r2c1], [r3c1], ... [rNc1] ],
// of data in column A in sheet2. There will be blanks at the end if other columns have more data.
var allNames = sheet2.getRange(1, 1, sheet2.getLastRow(), 1).getValues();

To check if the name from the first sheet is present, we can replace this code:

for (var j = 1; j < lastRow1; j++) {
  var playerNameSheet2 = sheet2.getRange(j, 1).getValue();
  if (playerNameSheet2 == playerNameSheet1) {
    /* do stuff */

with this code (note j now starts at 0):

for (var j = 0; j < allNames.length; ++j) {
  if (playerNameSheet1 === allNames[j][0]) {
    /* do stuff */

If you only need to do stuff on a name once in the function call (e.g. you don't need to execute the loop body twenty times when the sheet 1 name is "Bob" and there are twenty instances of "Bob" on sheet 2), you can simplify checking allNames for a value with the Array#indexOf method. First, one must collapse the "2D" array of arrays of values into an array of values. We want to apply a function to every element of the outer array and construct an array of its outputs, so we choose to call Array#map on it:

var db = allNames.map(function (row) { return row[0]; });

The function we use simply returns the first element of the passed element - i.e. the value in the first column, resulting in an output like [ r1c1, r2c1, r3c1, ... rNc1 ].

The replacement code is then:

if (db.indexOf(playerNameSheet1) === -1) {
  console.log({
    message: "Did not find '" + playerNameSheet1 + "' in database.",
    database: db, original: allNames, searched: playerNameSheet1
  });
  return;
}
/* do stuff */

Which says "if the name is not on sheet 2, log the failed lookup and then quit running the function." To promote actual logging, the log is sent to Stackdriver, which will keep it for much longer than the native Logger class would.

If your do stuff bits use the j index, you can still obtain that index and use the associated row in sheet 2:

var index = db.indexOf(playerNameSheet1);
if (index === -1) {
  console.log({
    message: "Did not find '" + playerNameSheet1 + "' in database.",
    database: db, original: allNames, searched: playerNameSheet1
  });
  return;
}
/* do stuff with the user's existing row of data, e.g.

var userDataRow = sheet2.getRange(index + 1, 1, 1, sheet2.getLastColumn()).getValues();
var userData = userDataRow[0];
...
 */

A possible improvement to the indexOf modification, which I leave for you to investigate and/or implement, would be to use an Object to hold the names as "keys" (object properties) and the index of the associated sheet data (or even the data directly) as the associated value of the key-value pair.

Answer 2

you can try to convert data in array and compare in for-loop:

  var dataRangeSpieler = sheetSpieler.getDataRange().getValues();
  var dataRangeDBSpiele = sheetDBSpieler.getDataRange().getValues();


  for (i in dataRangeSpieler ) {

    for (j in dataRangeDBSpiele) {

      if (dataRangeSpieler[i][1] == dataRangeDBSpiele[j][0]) {

      Logger.log(dataRangeSpieler[i][1]); //Oliver Baumann

      }
    }

  }