Skip to content

Instantly share code, notes, and snippets.

@clupasq
Created September 10, 2013 06:52
Show Gist options
  • Star 2 You must be signed in to star a gist
  • Fork 3 You must be signed in to fork a gist
  • Save clupasq/6505837 to your computer and use it in GitHub Desktop.
Save clupasq/6505837 to your computer and use it in GitHub Desktop.
SumByColor v1.1 - Added methods for Foreground Color - replaced the deprecated getBackgroundColor() with getBackground()
function getBackgroundColor(rangeSpecification) {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
return sheet.getRange(rangeSpecification).getBackground();
}
function getForegroundColor(rangeSpecification) {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
return sheet.getRange(rangeSpecification).getFontColor();
}
function sumWhereBackgroundColorIs(color, rangeSpecification) {
var condition = function (cell) { return cell.getBackground() == color; };
return sumByCondition(color, rangeSpecification, condition);
}
function sumWhereBackgroundColorIsNot(color, rangeSpecification) {
var condition = function (cell) { return cell.getBackground() != color; };
return sumByCondition(color, rangeSpecification, condition);
}
function sumWhereForegroundColorIs(color, rangeSpecification) {
var condition = function (cell) { return cell.getFontColor() == color; };
return sumByCondition(color, rangeSpecification, condition);
}
function sumWhereForegroundColorIsNot(color, rangeSpecification) {
var condition = function (cell) { return cell.getFontColor() != color; };
return sumByCondition(color, rangeSpecification, condition);
}
function sumByCondition(color, rangeSpecification, condition) {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var range = sheet.getRange(rangeSpecification);
var x = 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))
x += parseFloat(cell.getValue());
}
}
return x;
}
@WaffleSouffle
Copy link

Added "count" functions in this fork: https://gist.github.com/WaffleSouffle/7675480

@jeffdgr8
Copy link

Added skip empty cells in this fork: https://gist.github.com/jeffdgr8/1553faa6360ad04e9c17

@DarrenCattle
Copy link

If you want to do multiple lines filtered by color you can use this instead of "A1:A2" types of strings which you cannot drag in spreadsheets:

=getBackgroundColor(CONCAT("G", ROW(G2))) for a column, vice versa for a row

@jwdinkel
Copy link

Thank you all!
I combined WaffleSouffle's work and jeffdgr8.
Everything is here in this fork: https://gist.github.com/jwdinkel/6e4051f057d62204907b

Anybody know how to cause the functions to update if the color of a cell in the range is changed?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment