Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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)) {
var cellVal = parseFloat(cell.getValue());
if (isFinite(cellVal))
x += cellVal;
}
}
}
return x;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.