How do I get values of a column in an array and search for a value in it? (Google Sheets' Apps Script)

user_13042823 0
Viewed 65

I have this sheet in my Google sheets spreadsheet which looks like this - (this is a simpler representation of my actual data)

Now let's say I want to find the row number of where animal = dog - wrt above data I should get 4 in return - as in the column 1, the value "dog" is in the 4th row. I built this code in Apps Script but it keeps returning -1 and I don't understand why...

var values = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getDataRange().getValues();
Logger.log(values);
//above prints [[animal, owner], [cat, Max], [doggo, Sam], [dog, Jack], [cow, Mary], [rabbit, Jimmy]]
var index = values[0].indexOf("dog");
var responsePrint = index;
Logger.log(responsePrint); // -1 is printed

What should I do if I want to get the 1st column of my sheet in an array and search for a particular value in it? I'm trying to avoid using a loop to scan each and every element, as my data might get large - so if an inbuilt function can do this, it'd be great for me... Any idea is appreciated! Thanks :)

P.S. I'd like to say this might sound like a question which has been asked before, but trust me I've searched a lot, and I can't seem to find a suitable solution for me...

google-apps-script google-sheets search
2 Answers

values is a 2D array, or an array of arrays. The first index of values is the row and the second index is the column so values[1][0] would be the second row first column.

So in your case you could do this.

let index = values.findIndex( row => row[0] === dog );

To get the row you have to add 1 to the index because index are zero based.

What findIndex does is it takes each element of values which is a "row" and then looks at the first value in the row array and compares to "dog"

Reference

user_36567390

If you want to use indexOf you need to isolate the first column of your data and then use the indexOf method:

function myFunction(){
var values = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getDataRange().getValues();
var index = values.map(r=>r[0]).indexOf("dog") + 1;
console.log(index); //outputs 4
}
user_112252910
Related