I am having custom function that counts number of colored cells within a A1 notation range.
function countByColor(color, inputA1) {
let count = 0
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange(inputA1);
range.getBackgrounds().forEach(function(value) {
if(value[0] != undefined) {
if(value[0] == color) {
count=count+1
}
}
});
return count
}
I am coloring the cells using conditional forwarding, so basically the argument of the calling function is not changed. I need to refresh the results within the sheet on change, but If I create Trigger event (based time) it tries to call that function, but failing as I am not providing (within the declaration in Apps Script) any arguments In Cells the function looks like this -
=countByColor("#00ff00", "I7:I70")*1
The function works find, but if another cell within that range changes color, it doesn't recalculate. Since I am using that function in 20 cells, I need to quickly recalculate all of them
- Time Triggers
- On Change Triggers