javascriptfor-loopgoogle-sheetsgoogle-apps-script

how to get a loop iteration number as value in google apps script?


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


Solution

  • 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().