Search code examples
arraysfor-loopgoogle-apps-scriptgoogle-sheetsrow-number

Loop through array and get row number for each element


I am quite new to Google Script, I'm learning on the job. I have a range of data as a variable. It's only one column, column F in this case, but there are empty cells between values. I have a working script (got it from here earlier), which only loops through the cells with values in them. So lets say value1 is in F5, value2 is in F13, it's all random and always changing.

I'm trying to get the row number for these values, so that script should give back "5" for value1 and "13" for value2, ideally together with the value itself.

So far, that's what I have and I can not seem to progress further.

  var sourceID = "sourceID";
  var main = SpreadsheetApp.openById("mainID");
  var mainsheet = main.getSheetByName("Lab Data");
  var sourcesheet = source.getSheetByName("sheet name");
  var dataRange = sourcesheet.getDataRange(); // range full sheet
  var values = dataRange.getValues(); // values full sheet

  var SWrowss = findCellForSW(); // getting start row from another function 
  var CQrowss = findCellForCQ(); // getting last row from another function
  var noRows = CQrowss - SWrowss; // gets number of rows in range
  var colss = sourcesheet.getRange(SWrowss,6,noRows,1).getValues(); // range we need, column F

// get rid of empty cells from range - copied script from stack overflow
  var cResult = colss.reduce(function(ar, e) {
  if (e[0]) ar.push(e[0])
  return ar;
  }, []);
  Logger.log("cResult:   " + cResult); // cResult contains all sub headers - no empty cells

// gets element's position in array
  for(var b = 0; b < cResult.length; b++){
    var position = b+1;
    Logger.log("pos   " + position);
  } // end for 

Solution

  • If you want to know the row number, I would propose you a different approach

    Just loop through your values and retrieve the position of the ones that are not empty:

    ...
    var colss = sourcesheet.getRange(SWrowss,6,noRows,1).getValues();
    var rows = [];
    var calues = [];
    for(var b = 0; b < colss.length; b++){
      if(colss[b][0] != "" && colss[b][0] != " "){
        var row = SWrowss+b+1;
        rows.push(row);
        var value = colss[b][0];
        values.push(value);
      }
    }
    ...