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;
}
You were so close !!!!!!!!
Use the following formula.
Formula
=ARRAYFORMULA(
HYPERLINK(
FILTER(Data!D2:D40,Data!B2:B40=D2),
FILTER(Data!A2:A40,Data!B2:B40=D2)
)
)
Explained
The ARRAYFORMULA will take on the ranges, set in the filter, and return the matches.
Best Answer
Try "Insert Notes" or "Insert Comment" options. Right click on any cell and enter the note or comment. These two options should open a small window of text that you have entered when you click or move the mouse cursor on top of it.
You can also edit the 'Comment'. Please refer the screenshot below.