How to create this Google Sheets formula?

Question

I want a formula that calculates the number of green cells in a column C like in the picture, counting result will be in column D and when encountering 2 consecutive green cells,it stop counting and write "-" and than reset and start counting again

question from:https://stackoverflow.com/questions/65906128/how-to-create-this-google-sheets-formula

Answer 1

In order to achieve this you will need to iterate over your range in column C and check each individual cell background color for a color match. As you did not specify the color, I have set the function below to detect any color that is not whithe which from your case scenario works well (but if you want you can also modify it to check for a specific HEX color).

The following piece of code has self explanatory comments. It will only print the count number in D if there are two consecutive colored cells and it will reset the count when this happens :

function myFunction() {
  // Get sheet
  var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  // Get range (column C until the last row with a value in the sheet) and 
  // get the background color of each cell on that range
  var rangeBkg = sheet.getRange(1,3,sheet.getLastRow(),1).getBackgrounds();

  // Set flag to check if there are two consecutive colored cells
  var consequents = false;
  // Initialise the count variable
  var count = 0;

  // Iterate over each row
  for (i=0;i<rangeBkg.length;i++) {
    // Iterate over each column
    for (var j in rangeBkg[i]) {
      // If background is not whithe (i.e it's your desired color)
      if(rangeBkg[i][j]!=='#ffffff'){
        // Increase the count by 1
        count++;
        // If the previous cell was also colored
        if(consequents){
          // Reset consequent colored cells flag
          consequents = false;
          // Set the value of the last colored cell to the count
          sheet.getRange(i+1,4).setValue(count);
          // Reset count
          count = 0;
        }
        // If the previous cell was whithe set the consequent to true
        // i.e as this cell is colored be aware for the next cell
        else{
          consequents = true;    
        }

      }
      // If it isn't colored set the consequent flag to false
      // i.e the previous cell was not colored
      else{
        consequents = false;
      }
    }
  }
}