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

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...

CodePudding user response：

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

CodePudding user response：

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
}