A couple of my favorite topics – object proxies and fiddling with colors – in the same article today. We’ll look at extending the bmChroma library to use custom color schemes, including the material design color palette and create color scales based on these schemes.
I’m going to use my usual goto example spreadsheet which contains a list of airports. Take a copy and we’ll work on that. You’ll notice that each airport has lat/lon co-ordinates – the latitude will be used to create a color scale depending on how far north/south it is of the equator. You can use your imagination to apply it to one of your sheets that needs colorizing according to values in a given column.
For simplicity I’m using my bmPreFiddler library for everything connected with accessing sheets. You can do your own thing if you like of course, but it’s all pretty painless with fiddler.
Create a new standalone script (or container bound if you prefer), and add the bmPreFiddler (13JUFGY18RHfjjuKmIRRfvmGlCYrEkEtN6uUm-iLUcxOUFRJD-WBX-tkR) library and the bmChroma (1zjBPTX8meADK6W2tbw-sNB0479OMN2hhT1O5MGna7v5liAj7paj-W8QE) library.
…or you could take copy of the completed script from here and work on that.
Create an Exports script
Passing all your global and library references through an Exports object helps to avoid those annoying problems you sometimes get in Apps Script when you access something before Apps Script has defined it. I’ve covered Exports in a number of articles previously such as Fix Apps Script file order problems with Exports. Here’s what your Exports.gs should loook like. There’s also a bonus proxy checker in there to make sure you don’t accidently try to access properties that don’t exist.
Copy the source sheet data
We’ll use fiddler to copy the original sheet ‘airports’ to ‘color-airports’, and then play around with the copy. Subsitute the ids below with the spreadsheet id of the copy you made of my original airports spreadsheet.
From now on we’ll manipulate the data in the targetFiddler, leaving the source sheet intact.
The sheet intially looks like this
Add some extra columns
In this article we’ll be adding and colorizing extra columns to demonstrate different color scale methodologies – let’s add the first one ‘vanilla’. We can make it re-runnable by checking if the columns already exists first.
Create a scale function
Next we want to create a function that will return a suitablely scaled color based on latitude.
- The scale is going to go blue->yellow->blue, and the latitude (which will be in the range -90 to +90 – ie the domain) will determine how to mix the base colors in the scale
Add the scale
We’ll add the hex code for the chosen color in the newly created column ‘vanilla’
Sort by latitude
It’ll be more effective to see the scale if we sort the data by latitude
There are hundreds of airports in the sheet, so I’ve just snapped the first few – you can see that the southern hemisphere airports are now sorted to the tops, and we have a suggested color to use for them based on where their latitude lies between -90 and +90.
You could simply use these colors to color the entire row, but instead I’m going to use a function that examines an entire sheet and sets a background an contrasting font color in any cell that looks like a color. It examines the contents of a fiddler, and returns a function which will colorize the whole thing when executed. Always quite handy for articles involving sheets and colors.
Note: The plugin im using for rendering code has a problem so the code for isHex may not show up properly on your browser – it should read.
const isHex = (value) => value && value.toString().match(/^#[0-9abcdef]+$/i)
We can now apply the colorizer function. I’ll also set some formats for the header row just before dumping the fiddler data.
Here’s the beginning of the scale
You’ll may have noticed that we can use either a color name or a hex value to specify a color to bmChroma. In the background, bmChroma uses the excellent chroma node module which I’ve ported into Apps Script. Chroma has a list of known named colors it recognizes (I believe it’s all the named HTML colors)
But what if we want to use a different color scheme – for example Google’s material color palette? bmChroma now has the vuetify material color palette built in as a custom scheme, and uses a proxy to insert this list into chroma’s known list of colors. If you are interested you can see how it does that in the snippet below. When the proxied version of chroma is executed, it first checks to see if the color is known as part of any scheme, before returning control to chroma to validate and parse the color chosen.
The material color built in scheme.
The custom scheme for material colors looks like this – a small snippet below.
Referencing a custom scheme.
A color in a custom scheme is constructed as follows:
The scheme name of the built in material colors is mc, so mc-red, mc-amber-darken-2, mc-orange-accent-4 are all valid material colors
We’ll create a new scheme later, but first let’s update the code to create a scale that starts and ends on a material color rather than the standard html color.
Now we have this
Finding the nearest color
That looks a little nicer, but what if we want a scale that only features colors in a given scheme? Although our scheme currently starts and finishes on colors in the scheme, colors along the way will be a mix, so we need to find a way of finding the nearest scheme color to each intermediate scale value. You could use this same technique, for example, to convert a manually colorized sheet to only contain colors from a list – but choose the colors that are the nearest match. I’ve written a few articles on the different color spaces and how they calculate nearness – there are some links at the end of this article. For this example, we’ll use the ‘oklab’ color space which I find gives the most human match.
Finding the nearest match
Some more code updates, and a new column
Now we have airports at similar latitudes with the same color, and each color is strictly from the material color scheme rather than a calculated ‘inbetween’
Create your own scheme
You may want to create your own scheme – for example on status reports or for color matching to a range of paint or fabrics. For this example, I’ll create a simple scheme without any variants.
I’ve taken a snip round about the equator for this one.
The complete code
Heres’ the final version of the code – you can of course take a copy of the script or get it from github.
One more thing
The custom schemes are fully integrated into other bmChroma functions – for example you can desconstruct text that extracts things that look like colors – and these colors can now include any custom scheme names as below.
There are a number of articles about color mixing and schemes. Here’s a small selection
- Colorize sentiment in a Google document
- Content oriented color mixing with Apps Script
- Find nearest matches in color schemes with Apps Script
- Calculate contrasting font colors for Sheets.
- Detect fake news with Google Fact Check tools
- Use a proxy to catch invalid property access
And some more background on fiddlers – there are many others – try searching for fiddler
- Handly helper for fiddler
- Header formatting with fiddler
- Fiddler – A functional approach to fiddling with sheet data