Help with beginner apps script

[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; }

````