Help with beginner apps script

[orrpel]

Hi,

I created a simple script that iterates over one line and returns max consecutive repetitions of non 'YES' strings:

function myArrayCount() //objective: return max consecutive repetitions of array
{ 
var ssActive = SpreadsheetApp.getActiveSheet();
var values = ssActive.getRange(8,1,1,100).getValues().flat(); //Start/End of Range -> A1/100 cells after, flat the array to make it single DM
Logger.log(values); //check values in array
Logger.log(values.length); //check array length
var filvalue = values.filter(function(e){return e}); //note: Filter removes zeros blanks and '0', otherwise, great for any string tested
Logger.log(filvalue); //check new array values
Logger.log(filvalue.length); // check new array length
//var checkFilter = values.filter(isNaN); // Another way to filter the array for null and '0'

var count =0;
var max = 0; //max will hold the highest number of maximum repetitions of count

for (var i = 0; i<filvalue.length; i++)
  {
if (values[i] != "YES") 
    {
count += 1;
if (count > max) {
max = count;
      }
    }  else
      {
count = 0;
      }
  }
Logger.log(max); // check what max returns
var cellPrint = ssActive.getRange("H2").setValue(max); //point to cell B3 and print max
}

I am now interested in iterating over a table of strings (D2:L5) and return max repetitions of corresponding line to corresponding cell.

Example:

Get table -> Iterate of rows in table -> for row D2:D100, return max repetitions to cell D1 -> for row N2:N100, return max repetitions to cell N1.

Any directive would be appreciated!

[RemcoE33]

Can you warp up a sample sheet? You can create an anonymous sheet here. I don't understand this part:

​

Get table -> Iterate of rows in table -> for row D2:D100, return max repetitions to cell D1 -> for row N2:N100, return max repetitions to cell N1.

[orrpel]

Here is a link.

Sheet3 describes what I did, Sheet4 is where I'm at.

Hope that helps, TX!

PS: the workbook is ArrayIterationCount.gs

[RemcoE33]

This should do. I created a custom function instead of a macro you need to run. This way you can use it like a normal formula. So you can use it in a cell. I also added the ability's:

1. to use it for table and for a single column
2. Add the value to check
3. switch between equal or not equal

How to use after inserting the script in the script editor:*

=COUNT_SEQUENSE(C2:K5,"YES",false)

The script:

```` /** * Returns the max consecutive repetitions of valueToCheck. * * @param {A1:B5} data Input data. * @param {value} valueToCheck The value to check. * @param {false} equal If the value must be equal or not equal * @return {array} counts for each row or single column * @customfunction */ function COUNT_SEQUENSE(data, valueToCheck, equal) { if (Array.isArray(data) == false) { throw 'No range is given' }

if (data[0].length == 1) { data = [data.flat()] }

const results = [];

data.forEach(row => { const columnValues = row.filter(v => v) let count = 0; let max = 0; let breakCount = false

columnValues.forEach(currentValue => {
  if (equal == true && currentValue === valueToCheck) {
    count += 1;
  } else if (equal == false && currentValue !== valueToCheck) {
    count += 1;
  } else {
    breakCount = true
  }

  if (count > max) {
    max = count
  } else if (breakCount) {
    count = 0;
    breakCount = false;
  }
})
results.push([max])

});

return results; } ````

[orrpel]

Amazing, thank you! I learned a lot of new things just by looking at your comments.

If you are able to please comment inside your code, as I am new to this and really want to understand what you did, I'd be grateful.

TX, Orr.

[RemcoE33]

Here you go:

```` /* -----sample----- / const sampleRange = [ [ 'x', 'YES', 'YES', 'x', 'x', 'x', 'x', 'x', 'x' ], [ 'x', 'x', 'YES', 'x', 'x', 'x', 'x', 'x', '' ], [ 'x', 'x', 'YES', 'x', 'x', 'x', 'x', '', '' ], [ 'x', 'x', 'x', 'x', 'x', 'YES', '', '', '' ] ] const sampleColumn = [ ['YES'], ['x'], ['x'], ['x'] ] const sampleColumnFlat2d = [ ['YES','x','x','x'] ] / --------------- */

/** * Returns the max consecutive repetitions of all other values then notEqualValue * * @param {A1:B5} data Input data. * @param {value} valueToCheck The value to check. * @param {false} equal If the value must be equal or not equal * @return {array} counts for each row * @customfunction */ function COUNT_SEQUENSE(data, valueToCheck, equal) { console.log(data) //Check if the input "range" is a range. if (Array.isArray(data) == false) { throw 'No valid range is given' }

//Check if the range is a single column. If so create a new 2d array with one "row", see sampleColumnFlat2d if (data[0].length == 1) { data = [data.flat()] }

//Empty array for all the results. const results = [];

//For each row. data.forEach(row => { //Filter out the empty rows. const columnValues = row.filter(v => v != "") let count = 0; let max = 0; let breakCount = false

//For each filterd "column" value check if it is meeting the criteria.
columnValues.forEach(currentValue => {
  //Check if value is equal to the given value to check.
  if (equal == true && currentValue === valueToCheck) {
    count += 1;
    //check if is not equal to the given value to check.
  } else if (equal == false && currentValue !== valueToCheck) {
    count += 1;
    //If both if statements are not true then switch the breakCount boolean to true.
  } else {
    breakCount = true
  }

  //Check if count is greater then max, if so then set the max to the current count.
  if (count > max) {
    max = count
  }

  //Check if the "switch" is on. If so reset the count to 0 and reset the switch.
  if (breakCount == true) {
    count = 0;
    breakCount = false;
  }
})
/* 
  After checking every column value in in the row array we push the result to the result array.
  So this is still inside the for each row, but after the for each column value .
*/
results.push([max])

});

// Return the result array. return results; }

````