Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save kuyazee/40708a1c405f15df4af6b283db413996 to your computer and use it in GitHub Desktop.
Save kuyazee/40708a1c405f15df4af6b283db413996 to your computer and use it in GitHub Desktop.
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]{6}$)|(^#[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 data = range.getValues(); // read all the data up front, rather than reading per cell, for an enormous speed boost
var rowCount = range.getNumRows();
var colCount = range.getNumColumns();
for(var r = 0; r < rowCount; ++r) {
for(var c = 0; c < colCount; ++c) {
var value = data[r][c];
if(isValidHex(value)) {
var cell = range.getCell(r + 1, c + 1);
cell.setBackground(value);
cell.setFontColor(getContrastYIQ(value));
}
}
}
};
/*********
** Helpers
*********/
/**
* Get whether a value is a valid hex colour code.
*/
function isValidHex(hex) {
return colourPattern.test(hex);
};
/**
* Get a hex colour 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 colour
hex = hex.replace(/^#(.)(.)(.)$/, '#$1$1$2$2$3$3');
// convert hex to decimal value
var inverse = parseInt(hex.substring(1), 16);
// invert colour
inverse = 0xFFFFFF ^ inverse;
// convert back to hex notation
return '#' + ('000000' + inverse.toString(16)).slice(-6);
};
/**
* 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';
};
@Colter-Hammer
Copy link

I added the option to put in colors with RGB values. There's the option to change it to hex, or just use the RGB just for kicks and giggles. I'm really bad at RegEx, so please correct/fix/optimize. Enjoy!!

@JoedePass
Copy link

This seems to recolour cells regardless of whether they contain a valid hex or not, is it supposed to? I'm also getting an issue where I can't paste into more than one cell allthough this might have nothing to do with the script.

@kuyazee
Copy link
Author

kuyazee commented Jun 24, 2019

Hmmm I'm not sure, let me review I haven't worked on this for a long time @JoedePass

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