I have a Google Spreadsheet that contains a script to count a number of cells by color. The script works perfectly but seems like when ever I change the color of the cell, the count is not updated. How to update/refresh the cell count?
The script I'm using is from http://pastebin.com/4Yr095hV:
function getBackgroundColor(rangeSpecification) {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
return sheet.getRange(rangeSpecification).getBackgroundColor();
}
function sumWhereBackgroundColorIs(color, rangeSpecification) {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var range = sheet.getRange(rangeSpecification);
var x = 0;
for (var i = 1; i <= range.getNumRows(); i++) {
for (var j = 1; j <= range.getNumColumns(); j++) {
var cell = range.getCell(i, j);
if(cell.getBackgroundColor() == color) {
x += parseFloat(cell.getValue());
}
}
}
return x;
}
function countCellsWithBackgroundColor(color, rangeSpecification) {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var range = sheet.getRange(rangeSpecification);
var x = 0;
for (var i = 1; i <= range.getNumRows(); i++) {
for (var j = 1; j <= range.getNumColumns(); j++) {
var cell = range.getCell(i, j);
if(cell.getBackgroundColor() == color) {
x++;
}
}
}
return x;
}
Best Answer
The only way I know is to use a 'dummy' parameter to the function, that is not really used in the custom function, but will trigger the function to recalculate. So, where you would normally use:
you would now use
In F1 (or any other cell you may want to use) you can have value (e.g. 1) that when changed (e.g: incremented with 1) will force the recalculation of the custom function. This 'incrementing act' can also be scripted.