Google-sheets – Google Spreadsheet: Use cell referencing in “Change Color Based on Rules”

Please see if my answer to a similar question helps.

Update:

My submission for the Google Docs Script gallery was accepted and you can install it to get the functionality you wish.

The steps:

• Open your spreadsheet
• In the menu go to Tools -> Script gallery...
• Search for Sum by color
• Click Install
• Click the Authorize button if you trust the script and then Close
• go back to the spreadsheet

Now you have three additional functions you can use in your spreadsheet formula:

• sumWhereBackgroundColorIs(color, rangeSpecification)
• sumWhereBackgroundColorIsNot(color, rangeSpecification)
• getBackgroundColor(rangeSpecification)

Please from that the rangeSpecification parameters require quotes (") around them.

For example:

=sumWhereBackgroundColorIs("white", "A1:C4")

sums the values of all cells in the A1:C4 range that have a white background.

Please note that if you don't know the color of a certain cell you can use the provided getBackgroundColor function to find out what the color is. This function is necessary, because some colors are expressed as RGB codes (for example, #00ff00 instead of green).

I've just had a go at this, and here's a more flexible function. The previous example couldn't properly handle ranges of selected cells, this one can (but you could also add some better error checking for empty ranges!) Could easily be customized to have other ranges of brightness, hues, auto-calculation of max value, etc.:

/**
 * Adds a custom menu to the active spreadsheet, containing a single menu item
 * for invoking the readRows() function specified above.
 * The onOpen() function, when defined, is automatically invoked whenever the
 * spreadsheet is opened.
 * For more information on using the Spreadsheet API, see
 * https://developers.google.com/apps-script/service_spreadsheet
 */

function onOpen() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  var menu = [({name: "colorize", functionName: "colorize"})];
  spreadsheet.addMenu("HSV Colors", menu);
}

/*
 * Change saturation of cell background colors based on their values
 */

function colorize() {

  // Prompt the user for a max value number.
  var numberRange = Browser.inputBox('Colorize Range',
      'Please enter the maximum number of your range' +
      ' (for example, "2"):',
      Browser.Buttons.OK_CANCEL);
  if (numberRange == 'cancel') {
    return;
  }

  // Prompt the user for a hue number.
  var hue = Browser.inputBox('Colorize Range',
      'Please enter the hue (0-359)' +
      ' (for example, "128"):',
      Browser.Buttons.OK_CANCEL);
  if (hue == 'cancel') {
    return;
  }


  var range = SpreadsheetApp.getActiveRange();
  Logger.log('range:' + range);

  var values = range.getValues(); // [][]

  Logger.log('values:' + values);

  var backgrounds = [];

  for (var row = 0; row < range.getNumRows(); row++) 
  {
    var rowBackgrounds = [];
    for (var column = 0; column < range.getNumColumns(); column++) 
    {
      var val = Number(values[row][column]);
      if (isNaN(val)) 
      {
        val = 0;
      }
      var newColor = HSVtoHEX(hue,255*val/numberRange,200);
      rowBackgrounds.push(newColor);
    }
    backgrounds.push(rowBackgrounds);
  }
  range.setBackgrounds(backgrounds);
}


// http://stackoverflow.com/a/17243070/1536038
function HSVtoHEX(h, s, v) {

  Logger.log('h:'+h+' s:'+s + ' v:' + v);

    var r, g, b, i, f, p, q, t;

    // turn variables into degrees and percentages
    h=h/360, s=s/255, v=v/255;

    if (h && s === undefined && v === undefined) {
        s = h.s, v = h.v, h = h.h;
    }
    i = Math.floor(h * 6);
    f = h * 6 - i;
    p = v * (1 - s);
    q = v * (1 - f * s);
    t = v * (1 - (1 - f) * s);
    switch (i % 6) {
        case 0: r = v, g = t, b = p; break;
        case 1: r = q, g = v, b = p; break;
        case 2: r = p, g = v, b = t; break;
        case 3: r = p, g = q, b = v; break;
        case 4: r = t, g = p, b = v; break;
        case 5: r = v, g = p, b = q; break;
    }

    //http://stackoverflow.com/a/5624139/1536038
  var result = "#" + ((1 << 24) + (Math.floor(r * 255) << 16) + 
      (Math.floor(g * 255) << 8) + Math.floor(b * 255))
      .toString(16).slice(1);
  Logger.log(result);
    return result;
}