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

Thanks for this code, came in handy for a project. :) I did make a few tweaks though, which can be seen in my fork

• did a bit of tiddying up on the RegEx
• instead of using inverse of HEX color, switch to black or white text depending on the color contrast

Hi

Thank you so much for sharing this above. I found it very helpful for a project im doing. Please could I ask (I have no previous experience of writing code etc.) If i want the text or in this case hex codes to be invisible or same colour as background so that all you see is a coloured cell how would the code change? If you could show me as above i would really appreciate the help.

Thanks

This is handy! Thanks!
In my case it ran pretty slowly (1 second per cell is too slow at 300 cells) so I optimized it a bit. Now it will do an entire column almost instantly. :)

Thank you guys so much! Now we just need one that has wjmazza's improvements and XCompWiz' optimization!

Here's a new gist where wjmazza's improvements and XCompWiz's optimization were added to the script.

This is just what I need for I'm creating color diagrams with google sheets. BUT... Being a total non-coder I can't figure out a way to "trigger" this script to do the modifications to the spreadsheet. How do I actually run this script? The play button is grayed out and I don't seem to figure out any other way of triggering this... Would really appreciate a bit of little help. Thanks a mil!

The closest I could get is to run the onEdit() -function, but that returns: TypeError: Cannot read property "range" from undefined. (line 33, file "Set colour preview on edit"

I'm confused.

@wjmazza @XCompWiz Thanks! I added most of your changes to the script.

@JooWorks The script applies automatically when a cell is edited. To apply it to previous values, copy & paste them over themselves so they're marked edited.

@Pathoschild That's what I read online that the onEdit should trigger the function, but it doesn't. I found an article about 8 months ago from someone that had the same issue. Apparently it just stopped working for him. And for me, it never started. :-/ I wonder if there is anything that I could do to "restart" my google scripts to make them work again.

Edited:
Oh. It randomly started working now. I don't know why, but I'm happy.

[...] i want the text or in this case hex codes to be invisible or same colour as background [...]

@colourhome In case you still need that, you can change this part:

cell.setBackground(value);
cell.setFontColor(getInverseHex(value));

To this:

cell.setBackground(value);
cell.setFontColor(value);

I need the font color inversion to look like this script: https://gist.github.com/wjmazza/131c050b88bb2a595d6049707693ec13 but with the current script speed of this page. (sorry, my english is very bad but i trying)

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