Google-sheets – Sum cells according to cell colour

google sheetsgoogle-apps-script

I want to sum cells only with a specific cell colour.

I'm using this code:

function sumWhereBackgroundColorIs(color, rangeSpecification) {
    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(cell.getBackgroundColor() == color)
               x += parseFloat(cell.getValue());
        }   
     }
     return x; 
}

Best Answer

The problem with your code is that your using the function getBackgroundColor() which doesn't exist. Use getBackground() instead.

And make sure you're passing both range and color code as strings (enclosed with "). Also make sure to pass color codes in lower case ("#ffffff", not "#FFFFFF").

Related Solutions

Google Sheets – Sum Cells from Another Row Based on a Cell Value

With the magic of matrix multiplication and this solution (credit where credit is due), I have managed to wrangle it to do what you want.

=ArrayFormula(IF( J4:J = "" ; "" ; MMULT((ROW(G4:G)>=TRANSPOSE(ROW(G3:G)))*((ROW(G4:G)+1-I4:I)<=TRANSPOSE(ROW(G3:G)));G3:G)* ( ( J4:J ) / 10 ) ))

My head hurts already, so I won't go into too much explanatory detail, except to say that the key magic happens in

="MMULT((ROW(Range1)>=TRANSPOSE(ROW(Range2)))*((ROW(Range1)+1-I4:I)<=TRANSPOSE(ROW(Range2)));Range2)))"

And I've added a second sheet to your public one with a rough equation builder, for understanding or alternative applications.

Google Sheets – Change Cell Color in a Range

Use the following code to accomplish your goal.

Code

function onOpen() {
  SpreadsheetApp.getUi().createMenu('Custom Format')
    .addItem('Max & Color', 'getmaxColor').addToUi();
}

function getmaxColor() {
  var ss = SpreadsheetApp.getActive(), aRange = ss.getActiveRange(); 
  var data = aRange.getValues(), colors = aRange.getBackgrounds();
  var dest = aRange.offset(0, data[0].length, 1, 1);
  var index = getmaxIndices(data);
  var max = data[index.i][index.j], color = colors[index.i][index.j];
  dest.setValue(max).setBackground(color);  
}

function onEdit() {
  var ss = SpreadsheetApp.getActive(), col = ss.getActiveCell().getColumn();
  if(col < 7) {
    var aRange = ss.getRange("A1:F7"), data = aRange.getValues();
    var colors = aRange.getBackgrounds();
    var dest = aRange.offset(0, data[0].length, 1, 1)
    var index = getmaxIndices(data);
    var max = data[index.i][index.j], color = colors[index.i][index.j];
    dest.setValue(max).setBackground(color); 
  }
}

function getmaxIndices(data) {
  var max = 0, indexI = 0, indexJ = 0;
  for(var i=0, iLen=data.length; i<iLen; i++) {
    for(var j=0, jLen=data[0].length; j<jLen; j++) {
      if(data[i][j] > max) {
        max = data[i][j], indexI = i, indexJ = j;
      }
    }
  }
  return {"i":indexI, "j":indexJ};
}

Explained

The onOpen() adds a menu item. Select the range you want to evaluate and select the menu option Max & Color. The values and backgrounds are collected as is an offset range. Then the indices (2d-array) of the max value are obtained, through the getmaxIndices(). Afterwards the corresponding max value and color is set.

The onEdit() does almost the same, but it has a fixed range. It will only fire if a cell is edited and if the edited cell belongs to the A1:F7 range.

Note

The reason for getting the error message, can be read in this answer: custom function

Example

I've added the sample code into your example file.