Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save Colter-Hammer/ad910bccca7491c22e31281c65a7a8cd to your computer and use it in GitHub Desktop.
Save Colter-Hammer/ad910bccca7491c22e31281c65a7a8cd 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;
// reI = "\\s*([+-]?\\d+)\\s*"
var rgbPattern = /rgb\((?:([0-9]{1,2}|1[0-9]{1,2}|2[0-4][0-9]|25[0-5]),??\s?){3}\)/gi;
/*********
** 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].replace(/\s/g, '');
if (isValidRgb(value)) {
value = value.match(/\((.+?)\)/i)[1].split(',');
var cell = range.getCell(r + 1, c + 1);
cell.setBackgroundRGB.apply(cell, value);
cell.setFontColor(getContrastYIQ(value, 'rgb'));
// // If you want to change everthing to hex instead of using the RGB values, that's possible too
// let hex = rgbToHex(value);
// cell.setBackground(hex.hex);
// cell.setFontColor(getContrastYIQ(hex.rgb, 'rgb'));
}
else if (isValidHex(value)) {
var cell = range.getCell(r + 1, c + 1);
cell.setBackground(value);
cell.setFontColor(getContrastYIQ(value, 'hex'));
}
}
}
}
/*********
** Helpers
*********/
/**
* Get whether a value is a valid hex colour code.
*/
function isValidHex(input) {
return colourPattern.test(input);
}
/**
* Get whether a value is a valid rgb value
*/
function isValidRgb(input) {
return rgbPattern.test(input);
}
/**
* Change RGB to hex
* Leaves the rgb numbers in there for background color
*/
function rgbToHex(nums) {
return nums.reduce(function (acc, num, i) {
acc.rgb.push(num);
num = parseInt(num).toString(16);
if (num.length === 1)
num = 0 + num;
acc.hex += num;
return acc;
}, {
hex: '#',
rgb: []
});
}
/**
* 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(color, type) {
var r, g, b;
if (type === 'hex') {
r = parseInt(color.substr(1, 2), 16);
g = parseInt(color.substr(3, 2), 16);
b = parseInt(color.substr(5, 2), 16);
}
else if (type === 'rgb') {
r = color[0];
g = color[1];
b = color[2];
}
var yiq = ((r * 299) + (g * 587) + (b * 114)) / 1000;
return (yiq >= 128) ? 'black' : 'white';
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment