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