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
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
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);
}
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;
...