I have a database that has columns of RGB values and I wanted to display the color on anther column. I will have a custom function on the cell where I passed in the RGB values from the other columns.
=setBGColor(INDIRECT(ADDRESS(ROW(),COLUMN(F2)), INDIRECT(ADDRESS(ROW(),COLUMN(G2)), INDIRECT(ADDRESS(ROW(),COLUMN(H2)))
In the custom function I want to set the background color of the caller cell and also return the hex value. The first problem I ran into is that I couldn’t find a decimal to hex conversion function from the Google script. In the spreadsheet there is the handy DEC2HEX
function but I couldn’t find it in the script editor.
The second problem is that I don’t know how to get the cell that called the function. I tried this and it doesn’t work. I tested with some dummy hex value and got an error "You do not have permission to call setBackground".
function setBGColor()
{
//var rgb_hex = "#" + decToHexFunc(r) + decToHexFunc(g) + decToHexFunc(b);
SpreadsheetApp.getActiveSpreadsheet().getCurrentCell().setBackground("FFE4CE"); // test
//return rgb_hex;
}
2
Answers
Alternative Solution [UPDATED]
As an alternative solution, you could incorporate the usage of the
onEdit()
trigger which allows you to use thesetBackground()
function. Please review the script comments to understand how the script works. This script will automatically add the HEX value & background color in an instance ofonEdit()
function.This
onEdit()
function will only run once you enter the word ‘set‘ on a cell under the Hex column on any sheet in your Spreadsheet file. See demo below.Sample Script [UPDATED]
Demo
Hex
column:Reference
A custom function can not change the sheet format which is why you are getting the error, however I have an alternate solution for you where you wont have to use a custom function at all. By using an onEdit() function this can be solved.
This code assumes that your R,G,B values will be entered in First, Second and Third Columns respectively and you need the color and hex code in the 4th column. If that is not the case, you can just edit the numeric column values in the code or just use 3 variables.