A Google Sheets script which adds color preview to cells. When you edit a cell containing a valid CSS hexadecimal color code (like #000 or #000000), the background color is changed to that color and the font color is changed to the inverse color for readability.

google-sheets-color-preview.js

/*

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

To use this script in a Google Sheets spreadsheet:
1. go to Tools » Script Editor;
2. replace everyting in the text editor with this code;
3. click File » Save;
4. set the title to "Set color preview on edit".

This will apply to cells when they're edited. If you already have values, you can force them to
update by cutting & pasting them over themselves.

Includes improvements from...
- wjmazza (tweaked regex);
- XCompWiz (optimised when editing larger ranges).
*/

/*********
** Properties
*********/
/**
 * A regex pattern matching a valid CSS hex color code.
 */
var colorPattern = /^#([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 color code like #FFF or #FFFFFF.
 */
function onEdit(e) {
  // iterate over cell range  
  var range = e.range;
  var data = range.getValues(); // read all the data up-front (much faster than reading each cell individually)
  var rowCount = range.getNumRows();
  var colCount = range.getNumColumns();
  for(var r = 0; r < rowCount; r++) {
    for(var c = 0; c < colCount; c++) {
      var cell = range.getCell(r + 1, c + 1);
      var value = data[r][c];
      
      if(isValidHex(value)) {
        cell.setBackground(value);
        cell.setFontColor(getInverseHex(value));
      }
      else {
        cell.setBackground(null);
        cell.setFontColor(null);
      }
    }
  }
};


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

/**
 * Get a hex color code that is the inverse of the provided code.
 * Derived from stackoverflow.com/questions/9600295 with added
 * support for CSS shorthand hex notation.
 */
function getInverseHex(hex) {
  // expand shorthand color
  hex = hex.replace(/^#(.)(.)(.)$/, '#$1$1$2$2$3$3');
  
  // convert hex to decimal value
  var inverse = parseInt(hex.substring(1), 16);
  
  // invert color
  inverse = 0xFFFFFF ^ inverse;
  
  // convert back to hex notation
  return '#' + ('000000' + inverse.toString(16)).slice(-6);
};

Amazing tool, this is what I was looking for. However, I need to apply this script only to a specific range of cells in my spreadsheet. It would avoid setting all other cells to white background overriding my other formatting (conditional or not). I'm new to coding and would like some help to add a range (sheet & range) as a parameter in the script to control where it's applied.

Hi !
I tried to update the script to restrict its actions on a namedRange. I'm too new for coding and can't debug fully.
Could someone have a look ?
Thanks!

function onEdit(e) {
// iterate over cell range

// check if edited cell is in colorList namedRange
var sheet = SpreadsheetApp.getActiveSheet();
if (sheet.getName() == "data"){
var range = e.range;
var editRow = range.getRow();
var editCol = range.getCol();
var rangeColor = sheet.getRange("colorList");
var rangeRowStart = rangeColor.getRow();
var rangeRowEnd = rangeRowStart + rangeColor.getHeight();
var rangeColStart = rangeColor.getColumn();
var rangeColEnd = rangeColStart + rangeColor.getWidth();
if (editRow >= rangeRowStart && editRow <= rangeRowEnd
&& editCol >= rangeColStart && editCol <= rangeColEnd)
{
var data = range.getValues(); // read all the data up-front (much faster than reading each cell individually)
var rowCount = range.getNumRows();
var colCount = range.getNumColumns();
for(var r = 0; r < rowCount; r++) {
for(var c = 0; c < colCount; c++) {
var cell = range.getCell(r + 1, c + 1);
var value = data[r][c];
if(isValidHex(value)) {
cell.setBackground(value);
cell.setFontColor(getInverseHex(value));
}
else {
cell.setBackground(null);
cell.setFontColor(null);
}
}
}
}
}
};

For some reason I can't get this working. Everytime it runs I see this error in the console:
Google Apps Script: TypeError: Cannot read property 'setBackground' of undefined