Last active
December 5, 2022 10:38
-
-
Save eschmar/53f9becc409ea3951dc00cc269b7387c to your computer and use it in GitHub Desktop.
Google sheets (Apps Script) custom function to count cells with a given background color.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** | |
* Counts cell values in a range if they have the given background color | |
* =countByColor("V16", "S12:W20") | |
* | |
* @param {String} Cell reference for color to compare as String. | |
* @param {String} Range to iterate through as String. | |
* @return {int} Count of all cells matching background color. | |
*/ | |
function countByColor(cellColorToCompare, rangeToCount) { | |
var sheet = SpreadsheetApp.getActiveSpreadsheet(); | |
let color = sheet.getRange(cellColorToCompare).getBackgroundColor(); | |
let rangeToCheck = sheet.getRange(rangeToCount); | |
var bgColors = rangeToCheck.getBackgrounds(); | |
let cols = rangeToCheck.getNumColumns(); | |
let rows = rangeToCheck.getNumRows(); | |
var cnt = 0; | |
for (var i = 0; i < rows; i++) { | |
for (var j = 0; j < cols; j++) { | |
if (bgColors[i][j] == color) cnt++; | |
} | |
} | |
return cnt; | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment