I'm in the making of a google sheets app script where I want to check if a value from one cell is in an array of values, then find what the index is so I can direct my function to that cell.
I'm at the point where I have my array in a variable called distArray, and I want to check if "id" is in that array.
Here's the code to better visualize:
function logs() {
let app = SpreadsheetApp
let dest = app.getActiveSpreadsheet().getSheetByName("Baza Danych");
let lastrow = dest.getLastRow();
let destArr = dest.getRange(2, 1, lastrow).getValues();
let id = app.getActiveSpreadsheet().getSheetByName("Zgloszenia").getRange(6, 2).getValue();
let position = destArr.indexOf(id);
Logger.log(id)
Logger.log(destArr)
Logger.log(position)
}
And here is the output I get.
My problem is that no matter what the value of "id" is, the index is either -1 or 0 meaning the value either is not in the array or is in the first cell.
Try to add .flat() at the end of the line:
let destArr = dest.getRange(2, 1, lastrow).getValues();
This way:
let destArr = dest.getRange(2, 1, lastrow).getValues().flat();
Explanation:
The method getValues() gives you a 2d array [[1],[2],[3],...].
The flat() method converts a 2d array into an ordinary flat array [1,2,3,...].
After that you will able to use array.indexOf(element) to get an index of the element in the array.
Description
Yuri's solution is a good example if you don't want to know which element of the array contains the value your looking for. But if you need to know which row of the array contains the value the following example shows how to search a 2D array.
Script
function find() {
try {
let a = [['a','b'],['c','d'],['e','f'],['g','h']];
let b = "f";
let c = a.findIndex( d => d.indexOf(b) >= 0 );
console.log("c = "+c);
}
catch(err) {
console.log(err);
}
}
7:51:23 AM Notice Execution started
7:51:24 AM Info c = 2
7:51:23 AM Notice Execution completed
Reference