I would like to find if a certain value is in a range using app scripts for google sheets.
var sheet = SpreadsheetApp.getActiveSheet();
var rangeBikeNumbers = sheet.getDataRange("A5:A5000");
var values = rangeBikeNumbers.getValues();
If I have my range rangeBikeNumbers, how can I check if the number "42" for example is in that range. I have searched for hours now and have beeb unable to find any answer to this. indexOf only seems to return -1, regardless of whether or not the value is in the range.
var indexDataNumber = values.indexOf(42); for example always ends up being -1
I believe your goal as follows.
42 is existing in the range of A5:A5000.In this case, I would like to propose to use TextFinder. Because when TexiFinder is used, the process cost is low. Ref By the way, getDataRange has not arguments. From your script, I thought that you might want var rangeBikeNumbers = sheet.getRange("A5:A5000");.
When this is reflected to your script, it becomes as follows.
function myFunction() {
var sheet = SpreadsheetApp.getActiveSheet();
var rangeBikeNumbers = sheet.getRange("A5:A5000");
var find = rangeBikeNumbers.createTextFinder("42").matchEntireCell(true).findNext();
if (find) {
// In this case, the value of 42 is existing in the range.
} else {
// In this case, the value of 42 is NOT existing in the range.
}
}
About var indexDataNumber = values.indexOf(42); for example always ends up being -1, I think that the reason of this issue is due to that values is 2 dimensional array. If you want to use this, you can also use the following script.
function myFunction() {
var sheet = SpreadsheetApp.getActiveSheet();
var rangeBikeNumbers = sheet.getRange("A5:A5000");
var values = rangeBikeNumbers.getValues();
var find = values.map(([e]) => e).indexOf(42); // of values.flat().indexOf(42);
if (find > -1) {
// In this case, the value of 42 is existing in the range.
} else {
// In this case, the value of 42 is NOT existing in the range.
}
}