GoogleApps_SumByColor
GoogleApps_SumByColor copied to clipboard
Add equivalent functionality for count
This script works great and it would be lovely if it provided count functionality as well.
In a case where I needed that, I added the following two functions:
function countWhereBackgroundColorIs(color, rangeSpecification, key) {
var condition = function (cell) { return cell.getBackground().toLowerCase() == color.toLowerCase(); };
return countByCondition(rangeSpecification, condition, key);
}
/**
* Counts number of occurrences in the given range that have a
* specific condition.
*
* @param {rangeSpecification} rangeSpecification - the range to search against.
* @param {condition} condition - a function that determines if the cell should be
* counted or not.
* @param {key} key (optional) - the key of a remote spreadsheet. If provided the
* range lookup will be done remotely.
* @return A count of matching cells.
* @customfunction
*/
function countByCondition(rangeSpecification, condition, key) {
var sheet = _getSpreadsheet(key);
var range = sheet.getRange(rangeSpecification);
var count = 0;
for (var i = 1; i <= range.getNumRows(); i++) {
for (var j = 1; j <= range.getNumColumns(); j++) {
var cell = range.getCell(i, j);
if(condition(cell)) {
count++;
}
}
}
return count;
}
To add it properly the following is needed:
- Add other count variants
- Remove code duplication
- Update readme
- Rename repository