Google Apps Script find a value in an Array

Question

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.

Take a look at the sample code on this Mail Merge Tutorial 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
– Karl_S, Commented Dec 29, 2016 at 13:37

Vytautas · Accepted Answer · 2016-12-29 13:03:26Z

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.

answered Dec 29, 2016 at 13:03

Vytautas

2,2861 gold badge12 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dan Howard Over a year ago

Ah, that makes sense. Thanks very much!

Vytautas Over a year ago

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]

Dan Howard Over a year ago

Ah, yeah. Great clarification. Thanks.

utphx · Accepted Answer · 2016-12-29 13:06:10Z

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));

answered Dec 29, 2016 at 13:06

utphx

1,2651 gold badge8 silver badges19 bronze badges

Collectives™ on Stack Overflow

Google Apps Script find a value in an Array

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related