Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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.
/*
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';
}
@UmitKayabas
Copy link

UmitKayabas commented Oct 17, 2016

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

@AlexFolland
Copy link

AlexFolland commented May 30, 2018

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.

@ophello
Copy link

ophello commented Oct 26, 2022

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.

@wjmazza
Copy link
Author

wjmazza commented Oct 26, 2022

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

@ophello
Copy link

ophello commented Oct 26, 2022

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment