# Colorize a cell in Google Sheets based on cell data

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

``````function LinInt(x){
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.

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.

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.:

``````/**
* for invoking the readRows() function specified above.
* The onOpen() function, when defined, is automatically invoked whenever the
*/

function onOpen() {

var menu = [({name: "colorize", functionName: "colorize"})];
}

/*
* 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',
' (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;
}

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;
}
``````

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"})];
}

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

function LinRange() {
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.length; j<jLen; j++) {
colors[i][j] = HSVtoRGB_2(values[i][j],40,100);
}
}
range.setBackgrounds(colors);
}

// https://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;
}

//https://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. 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
• 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

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

Select range, Format > Conditional formatting > Colour scale, set Midpoint to Number and `50` and Done: 