Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I'd like to colorize a cell based on it's contents. 0 = red, 100 = green, and linearly interpolated between.

function LinInt(x){    
  var ss = SpreadsheetApp.getActiveSheet();
  var cell = ss.getActiveRange();
  var hue;
  hue = (x/100)*120;

  var color = HSVtoRGB(hue, 40, 100);
  cell.setBackground(color);

  return x;    
}   

function HSVtoRGB(h, s, v) {
    var r, g, b, i, f, p, q, t;
    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;
    }
  return '#'+((b | g << 8 | r << 16) / 0x1000000).toString(16).substring(2);
}

When I do this I get an error saying that I am not allowed to call setBackground. I assume this is because I'm not allowed to change the color of one cell from another because I'm screwing up getting the current cell that the function is running in.

My question is: How do I get the cell that the function is running in so that I can call setBackground() on it? For example, I'd like to type =LinInt(50) into cell A1 and have A1 be yellow and have the number 50 in it. Then when I type =LinInt(100) into cell A2 it is green and has the number 100 in it.

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

The way that you want it to work, is not possible. Changing a background color, involves an API call and these are not allowed in custom formulas. Therefore I compiled/created the following code:

Code

function onOpen() {
  var menu = [({name: "Cell", functionName: "LinInt"}),
    ({name: "Range", functionName: "LinRange"}),
    ({name: "Clear Formatting", functionName: "clearFormat"})];
  ss.addMenu("HSV Colors", menu);
}

function LinInt() {
  var cell = SpreadsheetApp.getActiveSheet().getActiveCell();
  var value = cell.getValue(), color = HSVtoHEX(value,40,100);
  cell.setBackground(color);
}   

function LinRange() {    
  var ss = SpreadsheetApp.getActiveSheet();
  var range = ss.getActiveRange(), values = range.getValues();
  var colors = new Array();  
  for(i in values) {
    colors[i] = new Array();
    for(var j=0, jLen=values[0].length; j<jLen; j++) {
      colors[i][j] = HSVtoRGB_2(values[i][j],40,100);
    }       
  }
  range.setBackgrounds(colors);
}   

// http://stackoverflow.com/a/17243070/1536038
function HSVtoHEX(h, s, v) {
    var r, g, b, i, f, p, q, t;

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

    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
    return "#" + ((1 << 24) + (Math.floor(r * 255) << 16) + 
      (Math.floor(g * 255) << 8) + Math.floor(b * 255))
      .toString(16).slice(1);
}

function clearFormat() {
  ss.getDataRange().clearFormat();
}

Explained

The onOpen function will create an extra menu item in the active spreadsheet, called HSV Colors having three entries:

  1. Cell: this will only work on one active cell (even if you selected a range).
  2. Range: this is meant for take on ranges (if will work on single cells, but less efficiently)
  3. Clear Formatting: clears all styling on the entire sheet (to play with the colors more easily)

The LinInt and the LinRange speak for themselves. The LinRange uses a batch operation to efficiently set the background colors.

The HSVtoHEX function will convert the HSV values into a RGB scheme, that is converted to a hexadecimal value.

Remarks

Originally you had me fooled, by your code. It said: HSVtoRGB but in fact you were trying to convert it to a hexadecimal color code. That's a good thing, because the setBackgroundRGB doesn't allow for batch operations like the setBackgrounds does.

Your code however doesn't return a correct hexadecimal code:
enter image description here

Type in the hex. value in colorizer.org and you will get the HSV code.

Add an onEdit trigger on the LinInt() function and every entry (integer) will give a change in background color.

Example

I've created an example file for you: HSV to HEX

References

  1. HSV & HSL, wiki page
  2. colorizer.org, gives a real time overview of the diferent color code schemes, like RGB, HEX, HSL, HSV/HSB and CMYK.
  3. HSVtoRGB, code to convert HSV to RGB
  4. RGBtoHEX, line of code to convert RGB to HEX
share|improve this answer
    
Hi Jan, it's Jan here too. :) That's a really helpful script you've put together. I know it's propably a small change, but I can't figure out, how to get the max and min value automatically from the selected range, instead of fixed 0 - 100 values. –  Underlines Oct 29 '13 at 12:58
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.