5

I can't figure out how to check if a value exists in an Array. I assumed this should be trivially simple, but can't get there.

I have this code:

function findPlayer() {
 //This section gets the values from the first row of all the columns
 var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Players");
 var lastColumn = ss.getLastColumn()
 var playerNameRow = ss.getRange(1, 2, 1, lastColumn - 3); 
 var playerNameValues = playerNameRow.getValues(); 

 //This just sets the value we're looking for (which in this example is in the 7th Column "G"). And logs the Array, just to check it.
 var findPlayer = "Dan";
 Logger.log(playerNameValues)


 //and here we just loop through the playerNameValues array looking for the findPlayer value
 for(var n in playerNameValues){
 if(playerNameValues[n][0]== findPlayer) {break}
}

//and here, if the above search does find the matching value it should log it 
Logger.log(n);
}

What's happening is the playerNameValue is logging correctly, but n is always logging as 0. Which implies it's finding the value in the first item it checks, rather than column 7 where the value is.

Dan Howard
  • 176
  • 2
  • 6
  • 16
  • Take a look at the sample code on this [Mail Merge Tutorial](https://developers.google.com/apps-script/articles/mail_merge) and especially pay attention to the part in the Full Code section from the getRowsData() function down. It is a way to read the data into an Object. You could then use playNameValues.player[n] or whatever your header row shows. – Karl_S Dec 29 '16 at 13:37

2 Answers2

4

I notice you mentioned that the player is column 7. Note that what you are actually checking is

Row 1 Column 1 Row 2 Column 1 Row 3 Column 1

And never actually touch any columns apart from the first one because you have [0] in if(playerNameValues[n][0]== findPlayer) {break}. Instead do this (I assume you have var i and var n already)

for (i = 0; i < playerNameValues.length; i++) {
  for (n = 0; n < playerNameValues[i].length; n++) {
    if (playerNameValues[i][n] == findPlayer) break
  }
  if (playerNameValues[i][n] == findPlayer) break
}

we do a second break to break out of the second loop, but there are better ways of writing that code, this is just to ilustrate what you need to do.

Vytautas
  • 2,238
  • 1
  • 9
  • 20
  • Ah, that makes sense. Thanks very much! – Dan Howard Dec 29 '16 at 13:28
  • Keep in mind that what I wrote considers that any row and any column can have the player name. If the player names are all in a specific row, then the array you get by using `.getValues()` then you access them by using `array[row][column]`. So if they are all only on row 5, then you can also be simply looping through `array[4][col]` – Vytautas Dec 30 '16 at 04:53
  • Ah, yeah. Great clarification. Thanks. – Dan Howard Dec 30 '16 at 08:28
4

Your rows and columns are reversed, use:

 for(var n in playerNameValues[0]){
 if(playerNameValues[0][n]== findPlayer) {break}
}

Or another way is to use indexOf instead of above.

Logger.log(playerNameValues[0].indexOf(findPlayer));
utphx
  • 1,287
  • 1
  • 8
  • 19