I'd like to colorize a cell based on its 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.
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.
Best Answer
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.: