Skip to content

Instantly share code, notes, and snippets.

@al-codaio
Created February 27, 2023 03:09
Show Gist options
  • Save al-codaio/7ce8b4f79962627c468f3a472b2056d6 to your computer and use it in GitHub Desktop.
Save al-codaio/7ce8b4f79962627c468f3a472b2056d6 to your computer and use it in GitHub Desktop.
Count the number of cells with a background color in Google Sheets (Google Apps Script)
function CountFormattedCells() {
// Output the number of formatted cells somewhere in your spreadsheet
var outputNumberOfFormattedCells = 'C52'
// Cell that contains the color you want to count. Default is blank.
var cellWithFormatToCount = ''
var spreadsheet = SpreadsheetApp.getActive();
var currentRangeColors = spreadsheet.getActiveRange().getBackgrounds();
if (cellWithFormatToCount !== '') { var cellWithFormat = spreadsheet.getRange(cellWithFormatToCount).getBackground(); }
var formattedCellCount = 0
for (var i in currentRangeColors) {
for (var j in currentRangeColors[i]) {
if (currentRangeColors[i][j] !== '#ffffff' && cellWithFormatToCount == '') {
formattedCellCount++
} else if (cellWithFormatToCount !== '' && currentRangeColors[i][j] == cellWithFormat) {
formattedCellCount++
}
}
}
if (outputNumberOfFormattedCells != '') {
spreadsheet.getRange(outputNumberOfFormattedCells).setValue(formattedCellCount)
}
};
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment