Color Ramp useful scripts

Color manipulation library scripts

First off for this project we are going to need to figure out how to manipulate colors in Google Apps Script. As per the approach in Roadmapper migration my objective is to make the structure as much like the VBA version as possible so as to make dual maintenance a possibility.

Color Ramp Library

The approach will be the same as Create a heatmap in Excel, with an extensible library of named colorramps calling a general color generator.

Here is the result of applying various color ramps to a few lines of a Google Spreadsheet. 

Copy of heatMap

Code for test Example

This code produces the example above.  To test it,  go to the spreadsheet and press the button.

The main points here are

  1. the use of a predefined library along with maximum and minimum values for the ramp against the current value, which returns a composute RGB code as used by VBA.
  2. the use of rgbToHTMLHex which converts an RGB code to an HTML code as used by Google Script.  
  3. The use of caching to improve speed - otherwise it takes much longer to set the cells one by one.
This is all that's needed. The color ramp and caching utilities are provided in the mcpher script library.

Heatmap with random numbers

This second example, which also demonstrates the use of sheet caching, creates a random table of numbers then successively applies different heatmap entries to the output, taking a pause between each one.  To test it,  go to the random tab of the spreadsheet and press the button. Here's the code

Picking a good contrast font color

One of the problems with a heatmap is that your font color may need to be changed so that it is visible against the chosen color. In fact there are W3 org guidelines for contrast. I apply that algorithm to decide whether to use black or white for the text color. I initially played around with contrasting using HSL and so on, but certain color combinations were just plain ugly, so settled for flipping between black and white. Here's the whole section, where a ramp fill color is calculated, and then we use the calculated text color, based on the luminance of the fill color.

var fillColor = new mcpher.colorProps(mcpher.rampLibraryRGB(rampList[l], mn, mx, v)) ;
// set the fill
// get a friendly font color for this fill

The code for that is here.

The colorRamp library

The idea here is that you create your own entries as you find useful color ramps. Here are the predefined ones, some of which are used above

The colorRamp function.

This is called by the library to calculate the color for the given value and its place in its scale. If the color ramp you need is not predefined, you can call this directly to create your own. Arguments are as follows
  • min - the minimum value of the scale
  • max - the maximum value of the scale
  • value - the value being assessed
  • mileStones - these are a list of RGB codes for the main colors that make up the scales.
  • fractionStones - by default, these are evenly spaced - for example if there are 3 milestones - lets say blue, green, and yellow -  then the fractionSones used would be 0, .33 and .66 - meaning that any value up to .33 * (max-min) , would fall somewhere between blue and green.  This can be changed - let's say you wanted to spend longer on shades of yellow, you could set say , 0 , .2 and .4. That way, any value above .4 would fall somewhere between green and yellow.
  • optBrighten - this can be used to darken ( < 1  - for example .5 ) or brighten (>1 fpor example 1.5) the color tones. The default is 1.

Utilities and full code. 

Here is the full code of the usefulColors Module in the mcpher library

Next Steps

For more like this, see  From VBA to Google Apps Script . 

For help and more information join our forum,follow the blog or follow me on twitter .

Transitioning is covered more comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly.