Setting background color of caller cell in Google Sheets script editor

Question

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

Answer 1

Alternative Solution [UPDATED]

Note: This script is intended to serve as a starting point or reference for your project. It's important to note that the community members do not provide coding services. While the logic outlined here has some limitations, it will help you get started with your approach to the issue at hand.

As an alternative solution, you could incorporate the usage of the onEdit() trigger which allows you to use the setBackground() 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 of onEdit() 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]

function onEdit() {
  var sheet = SpreadsheetApp.getActive().getActiveSheet();

  //Getting the current column & row
  var column = sheet.getActiveRange().getColumn();
  var row = sheet.getActiveRange().getRow();

  //getting the current RGB data from the selected cell & filter it just in case there are typos
  var rgb = sheet.getRange(row, column - 3, 1, 3).getValues()
    .filter(x => !x.join().match(/[a-zA-Z]/gm));

  //Do not run script if no conditions are met
  if (sheet.getActiveCell().getValue() == 'set') {
    var hex = rgb.map(x => [rgbToHex(x[0], x[1], x[2])]);

    //Set the hex value & BG color
    sheet.getRange(row, column).setValue(hex);
    sheet.getRange(row, column).setBackground(hex);

    //Adjusts font color accordingly for readibility if BG color is too dark or too light.
    isColorDark(rgb[0][0], rgb[0][1], rgb[0][2]) ? sheet.getRange(row, column).setFontColor('white') : sheet.getRange(row, column).setFontColor('black');
  }
}

function rgbToHex(red, green, blue) {
  var hex = "#" + ((1 << 24) + (red << 16) + (green << 8) + blue).toString(16).slice(1);
  return hex;
}

//Checks if BG dark or light
function isColorDark(r, g, b) {
  var luminance = (0.2126 * r + 0.7152 * g + 0.0722 * b) / 255;
  return luminance < 0.5;
}

Demo

• After placing the word "set" on the Hex column:

Reference

• setBackgrounds(color)
• onEdit(e)
• map()
• filter()
• rgbToHex() was derived from this existing post

Answer 2

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.

function onEdit(e){
  var column_number = e.source.getActiveSheet().getActiveRange().getColumn();
  //check if one of the first three columns (R, G, B) is edited
  if(column_number == 1 || column_number == 2 || column_number == 3){

    var s = SpreadsheetApp.getActiveSheet();
    var row_number = e.source.getActiveSheet().getActiveRange().getRowIndex();

    //Check if all the three values are filled (r,g,b)
    if( !s.getRange(row_number,1).isBlank() && !s.getRange(row_number,2).isBlank() && !s.getRange(row_number,3).isBlank()){
      
      var r = parseInt(s.getRange(row_number,1).getValue());
      var g = parseInt(s.getRange(row_number,2).getValue());
      var b = parseInt(s.getRange(row_number,3).getValue());

      s.getRange(row_number,4).setBackgroundRGB(r,g,b);
      s.getRange(row_number,4).setValue(s.getRange(row_number,4).getBackground());
    }
  }
}

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.