Counting max contiguos non-empty cells per row in GAS

Question

I'm trying to write a code in Google App Script to count the maximum number of contiguous non-empty cells per row. I already searched for the answer but I've found anything useful for this purpose.

Here you can find the example of my worksheet:

Worksheet

My goal is to write in column R the maximum consecutive working days for 2 weeks, in the picture I populated the R column with the results for every person.

There you can find the code I wrote (it is incomplete):

function countingScript() {

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var rangeData = sheet.getDataRange();
var lastColumn = rangeData.getLastColumn();
var lastRow = rangeData.getLastRow();
var searchRange = sheet.getRange(3,5, lastRow-1, 14);
var rangeValues = searchRange.getValues();  // Get array of values in the search Range
var counter = 0;
var counterArr = []; 
var maxArr = [];

// Loop through array and if condition met, add relevant

  for ( j = 5 ; j < lastRow - 1; j++){    // j -> rows
    for ( i = 3; i < 17; i++){    // i -> columns
      if(rangeValues[j][i] != ""){
        counter = counter + 1;
          if(rangeValues[j][i] = ""){
              counterArr.push(counter);
            };        
          };
            var max = Math.max.apply(null, counterArr);  //find the maximum counter for every row
            
    };
/////// max array with the results of every row
    maxArr.push(max); // write this array in column R
  };
Logger.log(maxArr);  //there i shoould write the result of maxArr in column R
};

The idea of the script was create a counter that loops first every column to find a maximum value of contiguous non-empty cells and write it to an array (maxArr) and then resets the counter when passing to the next row.

Thank you very much, Marco

Answer 1

Do I understand correctly that your problem is the counter not resetting to zero? Try moving your 'var counter = 0;' inside your first array. This resets it to zero for every new row it loops through.

for ( j = 5 ; j < lastRow - 1; j++){    // j -> rows
var counter = 0;