GoogleApps_SumByColor icon indicating copy to clipboard operation
GoogleApps_SumByColor copied to clipboard

Add equivalent functionality for count

Open grimsa opened this issue 7 years ago • 0 comments

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

grimsa avatar Nov 10 '18 12:11 grimsa