Skip to content

Instantly share code, notes, and snippets.

@Pathoschild
Last active August 1, 2024 21:43
Show Gist options
  • Save Pathoschild/29ad5ec92348164b1dea to your computer and use it in GitHub Desktop.
Save Pathoschild/29ad5ec92348164b1dea to your computer and use it in GitHub Desktop.
A Google Sheets script which adds color preview to cells. When you edit a cell containing a valid CSS hexadecimal color code (like #000 or #000000), the background color is changed to that color and the font color is changed to the inverse color for readability.
/*
This script is meant to be used with a Google Sheets spreadsheet. When you edit a cell containing a
valid CSS hexadecimal color code (like #000 or #000000), the background color will change to that
color and the font color will be changed to the inverse color for readability.
To use this script in a Google Sheets spreadsheet:
1. go to Tools » Script Editor;
2. replace everyting in the text editor with this code;
3. click File » Save;
4. set the title to "Set color preview on edit".
This will apply to cells when they're edited. If you already have values, you can force them to
update by cutting & pasting them over themselves.
Includes improvements from...
- wjmazza (tweaked regex);
- XCompWiz (optimised when editing larger ranges).
*/
/*********
** Properties
*********/
/**
* A regex pattern matching a valid CSS hex color code.
*/
var colorPattern = /^#([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 color 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 (much faster than reading each cell individually)
var rowCount = range.getNumRows();
var colCount = range.getNumColumns();
for(var r = 0; r < rowCount; r++) {
for(var c = 0; c < colCount; c++) {
var cell = range.getCell(r + 1, c + 1);
var value = data[r][c];
if(isValidHex(value)) {
cell.setBackground(value);
cell.setFontColor(getInverseHex(value));
}
else {
cell.setBackground(null);
cell.setFontColor(null);
}
}
}
};
/*********
** Helpers
*********/
/**
* Get whether a value is a valid hex color code.
*/
function isValidHex(hex) {
return colorPattern.test(hex);
};
/**
* Get a hex color 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 color
hex = hex.replace(/^#(.)(.)(.)$/, '#$1$1$2$2$3$3');
// convert hex to decimal value
var inverse = parseInt(hex.substring(1), 16);
// invert color
inverse = 0xFFFFFF ^ inverse;
// convert back to hex notation
return '#' + ('000000' + inverse.toString(16)).slice(-6);
};
@wjmazza
Copy link

wjmazza commented Aug 16, 2016

Thanks for this code, came in handy for a project. :) I did make a few tweaks though, which can be seen in my fork

  • did a bit of tiddying up on the RegEx
  • instead of using inverse of HEX color, switch to black or white text depending on the color contrast

@colourhome
Copy link

Hi

Thank you so much for sharing this above. I found it very helpful for a project im doing. Please could I ask (I have no previous experience of writing code etc.) If i want the text or in this case hex codes to be invisible or same colour as background so that all you see is a coloured cell how would the code change? If you could show me as above i would really appreciate the help.

Thanks

@XCompWiz
Copy link

XCompWiz commented Feb 27, 2018

This is handy! Thanks!
In my case it ran pretty slowly (1 second per cell is too slow at 300 cells) so I optimized it a bit. Now it will do an entire column almost instantly. :)

@AlexFolland
Copy link

Thank you guys so much! Now we just need one that has wjmazza's improvements and XCompWiz' optimization!

@kuyazee
Copy link

kuyazee commented Sep 20, 2018

Here's a new gist where wjmazza's improvements and XCompWiz's optimization were added to the script.

@JooWorks
Copy link

This is just what I need for I'm creating color diagrams with google sheets. BUT... Being a total non-coder I can't figure out a way to "trigger" this script to do the modifications to the spreadsheet. How do I actually run this script? The play button is grayed out and I don't seem to figure out any other way of triggering this... Would really appreciate a bit of little help. Thanks a mil!

The closest I could get is to run the onEdit() -function, but that returns: TypeError: Cannot read property "range" from undefined. (line 33, file "Set colour preview on edit"

I'm confused.

@Pathoschild
Copy link
Author

@wjmazza @XCompWiz Thanks! I added most of your changes to the script.

@JooWorks The script applies automatically when a cell is edited. To apply it to previous values, copy & paste them over themselves so they're marked edited.

@JooWorks
Copy link

JooWorks commented Sep 22, 2019

@Pathoschild That's what I read online that the onEdit should trigger the function, but it doesn't. I found an article about 8 months ago from someone that had the same issue. Apparently it just stopped working for him. And for me, it never started. :-/ I wonder if there is anything that I could do to "restart" my google scripts to make them work again.

Edited:
Oh. It randomly started working now. I don't know why, but I'm happy.

@Pathoschild
Copy link
Author

[...] i want the text or in this case hex codes to be invisible or same colour as background [...]

@colourhome In case you still need that, you can change this part:

cell.setBackground(value);
cell.setFontColor(getInverseHex(value));

To this:

cell.setBackground(value);
cell.setFontColor(value);

@mariolgr
Copy link

I need the font color inversion to look like this script: https://gist.github.com/wjmazza/131c050b88bb2a595d6049707693ec13 but with the current script speed of this page. (sorry, my english is very bad but i trying)

@arthurdarde
Copy link

Amazing tool, this is what I was looking for. However, I need to apply this script only to a specific range of cells in my spreadsheet. It would avoid setting all other cells to white background overriding my other formatting (conditional or not). I'm new to coding and would like some help to add a range (sheet & range) as a parameter in the script to control where it's applied.

@arthurdarde
Copy link

arthurdarde commented Jan 13, 2020

Hi !
I tried to update the script to restrict its actions on a namedRange. I'm too new for coding and can't debug fully.
Could someone have a look ?
Thanks!

function onEdit(e) {
// iterate over cell range

// check if edited cell is in colorList namedRange
var sheet = SpreadsheetApp.getActiveSheet();
if (sheet.getName() == "data"){
var range = e.range;
var editRow = range.getRow();
var editCol = range.getCol();
var rangeColor = sheet.getRange("colorList");
var rangeRowStart = rangeColor.getRow();
var rangeRowEnd = rangeRowStart + rangeColor.getHeight();
var rangeColStart = rangeColor.getColumn();
var rangeColEnd = rangeColStart + rangeColor.getWidth();
if (editRow >= rangeRowStart && editRow <= rangeRowEnd
&& editCol >= rangeColStart && editCol <= rangeColEnd)
{

var data = range.getValues(); // read all the data up-front (much faster than reading each cell individually)
var rowCount = range.getNumRows();
var colCount = range.getNumColumns();
for(var r = 0; r < rowCount; r++) {
for(var c = 0; c < colCount; c++) {
var cell = range.getCell(r + 1, c + 1);
var value = data[r][c];
if(isValidHex(value)) {
cell.setBackground(value);
cell.setFontColor(getInverseHex(value));
}
else {
cell.setBackground(null);
cell.setFontColor(null);
}
}
}
}
}
};

@micarner
Copy link

For some reason I can't get this working. Everytime it runs I see this error in the console:
Google Apps Script: TypeError: Cannot read property 'setBackground' of undefined

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