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

This comment has been minimized.

Copy link

@UmitKayabas UmitKayabas commented Oct 17, 2016

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

@AlexFolland

This comment has been minimized.

Copy link

@AlexFolland 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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.