Set the background color to a cell based on a hex value

Question

I want to set the background color to a cell based on a hex value in another cell. What I have done so far:

function setColorHEX(hex) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var cell = ss.getActiveCell();
  cell.setBackground(hex);
}

Does anybody know what I am doing wrong?

Answer 1

I was trying to do the same and found this script. It works great, though I did have to highlight all the existing cells and copy/paste them back in to get it to work. Credit: https://gist.github.com/wjmazza/131c050b88bb2a595d6049707693ec13

/*

This script is meant to be used with a Google Sheets spreadsheet. When you edit a cell containing a
valid CSS hexadecimal colour code (like #000 or #000000), the background colour will be changed to
that colour and the font colour will be changed to the inverse colour for readability.

To use this script in a Google Sheets spreadsheet:
1. go to Tools » Script Editor » Spreadsheet;
2. erase everything in the text editor;
3. change the title to "Set colour preview on edit";
4. paste this code in;
5. click File » Save.
*/

/*********
** Properties
*********/
/**
 * A regex pattern matching a valid CSS hex colour code.
 */
var colourPattern = /^#([0-9a-f]{3})([0-9a-f]{3})?$/i;


/*********
** Event handlers
*********/
/**
 * Sets the foreground or background color of a cell based on its value.
 * This assumes a valid CSS hexadecimal colour code like #FFF or #FFFFFF.
 */
function onEdit(e){
  // iterate over cell range  
  var range = e.range;
  var rowCount = range.getNumRows();
  var colCount = range.getNumColumns();
  for(var r = 1; r <= rowCount; r++) {
    for(var c = 1; c <= colCount; c++) {
      var cell = range.getCell(r, c);
      var value = cell.getValue();

      if(isValidHex(value)) {
        cell.setBackground(value);
        cell.setFontColor(getContrastYIQ(value));
      }
      else {
        cell.setBackground('white');
        cell.setFontColor('black');
      }
    }
  }
};


/*********
** Helpers
*********/
/**
 * Get whether a value is a valid hex colour code.
 */
function isValidHex(hex) {
  return colourPattern.test(hex);
};

/**
 * Change text color to white or black depending on YIQ contrast
 * https://24ways.org/2010/calculating-color-contrast/
 */
function getContrastYIQ(hexcolor){
    var r = parseInt(hexcolor.substr(1,2),16);
    var g = parseInt(hexcolor.substr(3,2),16);
    var b = parseInt(hexcolor.substr(5,2),16);
    var yiq = ((r*299)+(g*587)+(b*114))/1000;
    return (yiq >= 128) ? 'black' : 'white';
}