A Google Sheets script which adds colour preview to cells. 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.

google-sheets-colour-preview.js

/*

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

This is ace! How would I have this override a Conditional formatting item though?

The tweaks are awesome! XCompWiz optimized it so it can do an entire column very very quickly, linked in this comment: https://gist.github.com/Pathoschild/29ad5ec92348164b1dea#gistcomment-2364383 . Your improvements with XCompWiz' optimization would be extra awesome.

Can you please update this to recolor specific cells that are hex results generated by a formula? I need this to update colors based on formula results. Is that possible? It doesn't seem to work, or works inconsistently.

Also could you address the fact that we don't have script editor anymore? Google Sheets uses Apps Script now, which is a totally different interface.

@ophello hi there! I haven’t touched or used this in years sadly. I’ll look into it but I cannot guarantee anything

Eh, don't worry about it, I found a workaround which is much better (jQuery and HTML!)