google-sheetsgoogle-sheets-formulagoogle-sheets-apigoogle-sheets-macros

How to count a cell which is green in color in google sheet


I have a case where i need to count if a cell has color of "green" in google sheet. I tried few adons but they didn't work. I am looking for a solution via a script or a formula.

I have around 4 green values as shown in the attachment below

enter image description here

Tried this but it returns zero

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  //define here range of interest
  var range = sheet.getRange("BG4:BO");
  var backgrounds = range.getBackgrounds();
  var counter = 0;
  var green = "00ff00"
  for (var i = 0; i < backgrounds.length; i++){
    if(backgrounds[i]== "#00ff00")
      counter++;
  }
  Logger.log(counter); 
  sheet.getRange(3, 59).setValue(counter);
}

Thanks


Solution

  • You can count the green cells with Google Apps Script using the method getBackgrounds()

    Sample:

    function myFunction() {
      var sheet = SpreadsheetApp.getActiveSheet();
      //define here range of interest
      var range = sheet.getRange("A1:A10");
      var backgrounds = range.getBackgrounds();
      var counter = 0;
      var green = "#00ff00";
      for (var i = 0; i < backgrounds.length; i++){
        if(backgrounds[i]== green)
          counter++;
      }
      Logger.log(counter);  
    }
    

    UPDATE

    If you want to run the function on more than one column, you need to convert the 2D backgrounds array into a 1D one in order to run the function above. This can be accomplished with flat(). So:

    ...
    var backgrounds = range.getBackgrounds();
    backgrounds = backgrounds.flat();
    var counter = 0;
    ...