Skip to content

Instantly share code, notes, and snippets.

@EthraZa
Last active March 15, 2021 14:22
Show Gist options
  • Save EthraZa/91d1bb9eaae935af03c01efe5e4168a2 to your computer and use it in GitHub Desktop.
Save EthraZa/91d1bb9eaae935af03c01efe5e4168a2 to your computer and use it in GitHub Desktop.
Google Sheets: For each cell in a range, with a given background color, count or sum it.
/**
* Google Sheets script: countCellsByBgColor(inputRange, colorReference, operation = 'count')
* For each cell in a range, with a given background color, count or sum it.
*
* @param {String} inputRange The range of cells to check for the background color.
* @param {String} colorReference The cell with the background color to count.
* @param {String} operation [count|sum] The operation to perform over selected range.
* @param {Int} eachRow The number of rows to jump to at each interaction loop.
* @param {Int} eachColumn The number of columns to jump to at each interaction loop.
* @return {Number} The number of cells with a matching background.
*/
function countCellsByBgColor(inputRange, colorReference, operation = 'count', eachRow = 1, eachColumn = 1) {
var sheet = SpreadsheetApp.getActiveSheet(),
range = sheet.getRange(inputRange),
color = sheet.getRange(colorReference).getBackground(),
numRows = range.getNumRows(),
numCols = range.getNumColumns(),
c = 0;
for (var i = 1; i <= numRows; i = i + eachRow) {
for (var j = 1; j <= numCols; j = j + eachColumn) {
var cell = range.getCell(i,j),
bgcolor = cell.getBackground();
if (bgcolor == color) {
switch (operation) {
case 'sum':
c += parseInt(cell.getValue());
break;
default: //count
++c;
}
}
}
}
if (c) {
SpreadsheetApp.flush();
}
return c;
};
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment