I'm stuck on an apps script and I don't understand why it's not working Basically in my range "lookfor", I look for a match to my variable "Produit" (the cell N36 is a dropdown with items from the "lookfor" range) , if there is a match I want to know which row the match is on. My variable rowProduit then updates into that row value (and I use that value later in the function). For example, if the match was in the 5th row, it should return 18. Except it doesn't update...
function deplacer(){
var stocks = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('stocks');
var Produit = stocks.getRange(36,14).getValue();
var Qte = stocks.getRange(36,16).getValue();
var rowProduit = 13 ;
var lookfor = stocks.getRange("S13:S37")
for ( var i = 0 ; i < lookfor.length ; i++) {
if ( lookfor[i][0] == Produit) {
rowProduit += i ; /////// var doesn't update..........
break;
}
}
I tried with or without the break; line in the loop but it doesn't seem to change anything. I'm only doing this as a hobby so I don't really see how to solve this on my own...
Use Range.getValues()
, Array.flat()
and Array.indexOf()
, like this:
function deplacer() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('stocks');
const product = sheet.getRange(36, 14).getValue();
const range = sheet.getRange('S13:S37');
const index = range.getValues().flat().indexOf(product);
if (index === -1) throw new Error(`Cannot find product '${product}'.`);
const rowNumber = range.getRow() + index;
// ...
}
See Range.getValues(), Array.flat() and Array.indexOf().