Skip to content

Instantly share code, notes, and snippets.

@WaffleSouffle
Forked from clupasq/subByColor.js
Last active May 30, 2022 07:20
Show Gist options
  • Save WaffleSouffle/7675480 to your computer and use it in GitHub Desktop.
Save WaffleSouffle/7675480 to your computer and use it in GitHub Desktop.
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 countWhereBackgroundColorIs(color, rangeSpecification) {
var condition = function (cell) { return cell.getBackground() == color; };
return countByCondition(color, rangeSpecification, condition);
}
function countWhereBackgroundColorIsNot(color, rangeSpecification) {
var condition = function (cell) { return cell.getBackground() != color; };
return countByCondition(color, rangeSpecification, condition);
}
function countWhereForegroundColorIs(color, rangeSpecification) {
var condition = function (cell) { return cell.getFontColor() == color; };
return countByCondition(color, rangeSpecification, condition);
}
function countWhereForegroundColorIsNot(color, rangeSpecification) {
var condition = function (cell) { return cell.getFontColor() != color; };
return countByCondition(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;
}
function countByCondition(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 += 1;
}
}
return x;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment