Color Ramp useful scripts - Desktop Liberation

Page Content hide

1 Color manipulation library scripts

2 Color Ramp Library

3 Code for test Example

4 Heatmap with random numbers

5 Picking a good contrast font color

6 The colorRamp library

7 The colorRamp function.

8 Utilities and full code.

9 Next Steps

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

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.
the use of rgbToHTMLHex which converts an RGB code to an HTML code as used by Google Script.
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.

// used by template
var eArgs;
 /** publish google apps script in various formats
  * all apply to e.parameter..
  * @param {String=} module the name of the module|scriptdb|gistfilename|filename entry to publish
  * @param {String=} library the name of the attached library|gistId that contains the module (blank/missing = local)
  * @param {String=} type the type of output required (json|html|js|jsonp|javascript) 
  * @param {String=} template the template to use - must exist in this script 
  * @param {String=} callback required for jsonp otherwise you get json
  * @param {String=} source where it comes from script|scriptdb|custom|gist|web
  * @param {String=} func comma seperated list of functions to include .. default is all
  * @param {String=} query json query to be used along with scriptdb
  * @return {object} depends on format selected
 */

function doGet(e) {

    //----------
    // need to put this in a global var for callback from template
    eArgs = e;
    // set the arg defaults
    mcpher.setGasPubDefaults (eArgs);
    // convert source to correct format
    var result = expressSource(eArgs);

    // publish as appropriate
    return mcpher.prepareGasPub(eArgs,result);
}

 /* convert source code to required format
  * @param {object} e arg to doGet(e)
  * @return {object} the source expressed according the format
  */
function expressSource(e) {
  // if a template is mentioned, we are going to do html
  if (mcpher.isNeedTemplate (e)) {
    // evaluate the template
    try {
      var template = HtmlService.createTemplateFromFile(e.parameter.template);
      return template.evaluate().getContent(); 
    }
    catch (err) {
      return  { error: 'error ' + err + 'reading template' + e.parameter.template};
    }
  }
  else {
    // this going to be Json or jSonP or javascript
    return getSource();
  }
}
 /** gets the appropriate source for the combination of requested module and requested library
  * @param {String} content the web content 
  * @return {String} the escaped content
  */
function getSource() {
  return getMySource(eArgs);
}

// -- write a getMySource() to retrieve any modules in THIS script file
/* Returns a modules source code
 * @param {parameters} e the argument to doGet(e). should have module parameter specified
 * @return {object} The result.
 */

function getMySource(e) {
  // probably doesnt need changed, except if you need a custom function
  // the purpose of all this is to execute the scriptdb/scriptapp in the correct project according to the library selected
    
  // maybe muliple modules - introduced this hack to speed up hosting javascript code
      var modules =[]; 
      if (e.parameter.module && e.parameter.type && e.parameter.type == "javascript") 
          modules = e.parameter.module.split(",");
      
      var r;
      if (modules.length > 1) {
        try {
          var cum = "";
          for (var i=0;i<modules.length;i++) {
            e.parameter.module=modules[i];
            r = getMyMod(e);
            if (r.error) break;
            cum +=r.results.data;
          }
          // subsititute the result with cumulative results
          if(r.results) r.results.data = cum;
          
       }
       catch(err) {
        return { error : err + " when trying to get module ", parameters : e.parameter };
       }
      }
      else {
        r = getMyMod(e);
      }
        
      return r;
}
// -- write a getMySource() to retrieve any modules in THIS script file
/* Returns a modules source code
 * @param {parameters} e the argument to doGet(e). should have module parameter specified
 * @return {object} The result.
 */
function getMyMod(e) {
  // probably doesnt need changed, except if you need a custom function
  // the purpose of all this is to execute the scriptdb/scriptapp in the correct project according to the library selected
    try {
      return mcpher.getGeneralSource(e,
              e.parameter.source == 'script' ? 
                eval(mcpher.addLibraryPrefix(e,"showMyScriptAppResource"))(e.parameter.module) :
                ( e.parameter.source == 'scriptdb' ? 
                  eval( mcpher.addLibraryPrefix(e,"showMyScriptDb"))() : null
                 ) ,
              function (e) { // this is a custom function if you need one 
                         return null;} 
            );
    }
    catch (err) {
      return { error : err + " when trying to get module ", parameters : e.parameter };
    }
}

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

function testHeatMapRandomRamp() {
  var rampList = [ 
          "heatmap",     
          "heatmaptowhite", 
          "blacktowhite",                 
          "whitetoblack", 
          "hotinthemiddle",  
          "candylime",  
          "heatcolorblind", 
          "gethotquick",
          "greensweep",
          "terrain", 
          "terrainnosea"]; 

  var nPoints = 10;
  var nRows = 20;
 
  
  var wr = mcpher.wholeSheet("heatmaprandom");
  
  mcpher.WorkSheet(wr).clearFormats();
  mcpher.WorkSheet(wr).clear();
  
 // since we know the dimensions
 // extending the cache at beginning will save a few cycles
  var cacheData = mcpher.sheetCache(wr).extend(nRows,nPoints);
  var cacheColor = mcpher.sheetCache(wr,"getBackgroundColors").extend(nRows,nPoints);
  var cacheFontColor = mcpher.sheetCache(wr,"getFontColors").extend(nRows,nPoints);
  
// create some random data
  
  for (var r = 0 ; r < nRows ; r++) {
    for (var c = 0; c < nPoints ; c++) {
      cacheData.setValue(mcpher.randBetween(0,100),r+1,c+1); 
    }
  }
  
// extremes of heatrange
  var mx = cacheData.max();
  var mn = cacheData.min();
  cacheData.commit();
  
// now apply the colors
   for (var l=0 ; l < rampList.length ; l++) {
    cacheData.forEach(
      function(v,r,c) {
        // the heatmap color and its properties
        var fillColor =  mcpher.makeColorProps(mcpher.rampLibraryRGB(rampList[l], mn, mx, v)) ;
        // set the fill
        cacheColor.setValue(fillColor.htmlHex,r,c);
        // get a friendly font color for this fill
        cacheFontColor.setValue(mcpher.rgbToHTMLHex(fillColor.textColor),r,c);
      }
    );
    
    cacheColor.commit();
    cacheFontColor.commit();
    mcpher.tryToast("painted color ramp "+rampList[l])
    mcpher.sleep(5000);
    
  }
  
// we're done
  cacheData.close();
  cacheFontColor.close();
  cacheColor.close();
}

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
cacheColor.setValue(fillColor.htmlHex,r,c);
// get a friendly font color for this fill
cacheFontColor.setValue(mcpher.rgbToHTMLHex(fillColor.textColor),r,c);

The code for that is here.

function w3Luminance (rgbColor) {
// this is based on
// http://en.wikipedia.org/wiki/Luma_(video)

  return (0.2126 * Math.pow((rgbRed(rgbColor)/255),2.2)) +
         (0.7152 * Math.pow((rgbGreen(rgbColor)/255),2.2)) +
         (0.0722 * Math.pow((rgbBlue(rgbColor)/255),2.2)) ;
}

function makeColorProps (rgbColor) {
  return new colorProps().populate(rgbColor);
}

function colorProps() {
  return this;
}

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

function rampLibraryRGB(ramp, min , max, value , optBrighten) {
  var brighten = fixOptional (optBrighten, 1);
  
  if (IsArray(ramp)) {
    // ramp colors have been passed here
       return colorRamp(min, max, value, ramp,undefined , brighten);
  }
  else {
  
    switch(Trim(LCase(ramp))) {
        case "heatmaptowhite":
            return  colorRamp(min, max, value, 
                            [VBCOLORS.vbBlue, VBCOLORS.vbGreen, VBCOLORS.vbYellow, 
                             VBCOLORS.vbRed, VBCOLORS.vbWhite],undefined , 
                            brighten);
        case "heatmap":
            return  colorRamp(min, max, value, 
                            [VBCOLORS.vbBlue, VBCOLORS.vbGreen, VBCOLORS.vbYellow, 
                             VBCOLORS.vbRed], undefined, 
                            brighten);  
        case "blacktowhite":
            return  colorRamp(min, max, value, 
                            [VBCOLORS.vbBlack, VBCOLORS.vbWhite], undefined, 
                            brighten);    
        case "whitetoblack":
            return  colorRamp(min, max, value, 
                            [VBCOLORS.vbWhite, VBCOLORS.vbBlack],undefined , 
                            brighten);      
        case "hotinthemiddle":
            return  colorRamp(min, max, value, 
                            [VBCOLORS.vbBlue, VBCOLORS.vbGreen, 
                             VBCOLORS.vbYellow,VBCOLORS.vbRed,VBCOLORS.vbGreen,
                             VBCOLORS.vbBlue], undefined, 
                             brighten);  
        case "candylime":
            return  colorRamp(min, max, value, 
                            [RGB(255, 77, 121), RGB(255, 121, 77), 
                                    RGB(255, 210, 77), RGB(210, 255, 77)], undefined, 
                            brighten);  
                            
        case "heatcolorblind":
            return  colorRamp(min, max, value, 
                            [VBCOLORS.vbBlack,VBCOLORS.vbBlue,  VBCOLORS.vbRed, 
                             VBCOLORS.vbWhite], undefined, 
                            brighten);  
        case "gethotquick":
            return  colorRamp(min, max, value, 
                            [VBCOLORS.vbBlue,VBCOLORS.vbGreen,  VBCOLORS.vbYellow, 
                             VBCOLORS.vbRed],[0, 0.1, 0.25, 1] , 
                            brighten);                           
        case "greensweep":
            return  colorRamp(min, max, value, 
                            [RGB(153, 204, 51), RGB(51, 204, 179)] 
                             ,undefined , 
                            brighten);   
        case "terrain":
            return  colorRamp(min, max, value, 
                            [VBCOLORS.vbBlack, RGB(0, 46, 184), RGB(0, 138, 184), 
                            RGB(0, 184, 138), 
                            RGB(138, 184, 0), RGB(184, 138, 0), 
                            RGB(138, 0, 184), VBCOLORS.vbWhite] 
                             ,undefined , 
                            brighten); 
        case "terrainnosea":
            return  colorRamp(min, max, value, 
                            [VBCOLORS.vbGreen, RGB(0, 184, 138), 
                            RGB(138, 184, 0), RGB(184, 138, 0), 
                            RGB(138, 0, 184), VBCOLORS.vbWhite] 
                             , undefined, 
                            brighten); 
                            
                            
                            
        case "greendollar":
           return colorRamp(min, max, value, 
                                [RGB(225, 255, 235), 
                                RGB(2, 202, 69)], undefined, 
                                brighten);
    
        case "lightblue":
          return colorRamp(min, max, value, 
                                [RGB(230, 237, 246), 
                                RGB(163, 189, 271)], undefined, 
                                brighten);
                                
         case "lightorange":
           return colorRamp(min, max, value, 
                                [rgb(253, 233, 217), 
                                rgb(244, 132, 40)], undefined, 
                                brighten);
                            
       default:
         DebugAssert(false,"Unknown library entry " + ramp) ;
    }
   }
   
}

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.

function colorRamp(min, max , value , mileStones , fractionStones, optBrighten) {
  // color ramp given or default
  var brighten = fixOptional ( optBrighten,1);
  var ms = IsMissing(mileStones) ? 
      [VBCOLORS.vbBlue,VBCOLORS.vbGreen,VBCOLORS.vbYellow, 
        VBCOLORS.vbRed,VBCOLORS.vbRed,VBCOLORS.vbWhite] :
        mileStones;
   DebugAssert( ms.length , "No milestone colors specified");
   // only 1 milestone - thats the color
    if (ms.length == 1) return ms[lb];
   // fractions of range at which to apply these colors
    var fs = [];
    if (!IsMissing(fractionStones)) {
        DebugAssert( fractionStones.length == ms.length, "no of fractions must equal number of steps" );
        fs = fractionStones;
    }
    else {
      // equal proportions
      fs[0]=0;
      for (var i = 1 ; i < ms.length ; i++ ) fs[i] = i/(ms.length-1) ;
    }
    // now calculate the color
    var spread = max - min;
    DebugAssert (spread >= 0 , "min is greater than max for color spread");
    var ratio = (value - min) / spread;
    DebugAssert (ratio >= 0 && ratio <= 1, "couldnt calculate ratio for color spread");
    //find which slot this value belongs in
    for (var i = 1; i  < ms.length;i++) {
      if (ratio <= fs[i]) {
        var r = (ratio - fs[i - 1]) / (fs[i] - fs[i - 1]);
        var red = rgbRed(ms[i - 1]) + (rgbRed(ms[i]) - rgbRed(ms[i - 1])) * r;
        var blue = rgbBlue(ms[i - 1]) + (rgbBlue(ms[i]) - rgbBlue(ms[i - 1])) * r;
        var green = rgbGreen(ms[i - 1]) + (rgbGreen(ms[i]) - rgbGreen(ms[i - 1])) * r;
        return RGB(lumRGB(red, brighten), 
                   lumRGB(green, brighten), 
                   lumRGB(blue, brighten));
      }
    }
        
    DebugAssert (false,"ColorRamp failed to work - dont know why");
}