Color scales, custom schemes and proxies with Apps Script

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.

Page Content hide

1 Getting started

1.1 Create an Exports script

1.2 Copy the source sheet data

1.3 Add some extra columns

1.4 Create a scale function

1.9 Initial colorization

2 Custom scheme

3 The material color built in scheme.

3.1 Referencing a custom scheme.

4 Finding the nearest color

4.1 Finding the nearest match

5 Create your own scheme

10 Share with your network

Getting started

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.

var Exports = {

  get PreFiddler () {
    return bmPreFiddler.PreFiddler
  },

  newPreFiddler(...args) {
    return this.guard(Exports.PreFiddler().getFiddler(...args))
  },


  get libExports () {
    return bmChroma.Exports
  },

  get chroma() {
    return this.libExports.chroma
  },

  get ColorWords() {
    return this.libExports.ColorWords
  },
  get Schemes() {
    return this.libExports.Schemes
  },


  // used to trap access to unknown properties
  guard(target) {
    return new Proxy(target, this.validateProperties)
  },

  /**
   * for validating attempts to access non existent properties
   */
  get validateProperties() {
    return {
      get(target, prop, receiver) {
        // typeof and console use the inspect prop
        if (
          typeof prop !== 'symbol' &&
          prop !== 'inspect' &&
          !Reflect.has(target, prop)
        ) throw `guard detected attempt to get non-existent property ${prop}`

        return Reflect.get(target, prop, receiver)
      },

      set(target, prop, value, receiver) {
        if (!Reflect.has(target, prop)) throw `guard attempt to set non-existent property ${prop}`
        return Reflect.set(target, prop, value, receiver)
      }
    }
  }

}

Exports.gs

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.

  // open the airports sheet
  const sourceFiddler = Exports.newPreFiddler({
    sheetName: "airports",
    id: 'id of your copy of airports'
  })

  const targetFiddler = Exports.newPreFiddler({
    sheetName: "color-airports",
    id: 'id of your copy of airports',
    createIfMissing: true
  })

  // copy the data
  targetFiddler.setData(sourceFiddler.getData())

set the source and target sheet

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.

  // add some more columns for the test if they are not already there
  const extraCols = ['vanilla']
  extraCols.filter(f => targetFiddler.getHeaders().indexOf(f) === -1)
    .forEach(f => targetFiddler.insertColumn(f))

add some extra columns

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

  // pick up the functions we'll need from the bmChroma library
  const { getChroma, getContrast, getColorPack } = Exports.ColorWords
  const chroma = getChroma()

  // Vanilla scale
  // the idea here is to colorize using a scale according to the absolute latitude
  // first of all using normal colors from blue (cold) thru yellow(warm) then thru blue again
  // make a copy to here
  const vanillaScale = chroma.scale(['blue', 'yellow', 'blue'].map(f => chroma(f))).domain([-90, 0, 90]);

scale function

Add the scale

We’ll add the hex code for the chosen color in the newly created column ‘vanilla’

  // now lets make a color for each airport
  targetFiddler.mapRows(row => {
    row.vanilla = vanillaScale(row.latitude_deg).hex()
    return row
  })

add vanilla scale

Sort by latitude

It’ll be more effective to see the scale if we sort the data by latitude

  // lets sort by latititude
  targetFiddler.setData(targetFiddler.sort('latitude_deg'))

  // and finally, write the fiddler data to the sheet
  targetFiddler.dumpValues()

sort by latitude

Initial colors

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.

Colorize

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)


  //see if avalues  is a hex code - naive test
  const isHex = (value) => value && value.toString().match(/^#[0-9abcdef] $/i)

  // this function will return a closure that will color the fiddler
  const colorize = (fiddler) => {
    const backgrounds = fiddler.getData().map(row => {
      return fiddler.getHeaders().map(col => isHex(row[col]) ? row[col] : null)
    })
    const fontColors = backgrounds.map(row => {
      return row.map(col => isHex(col) ? getContrast(col) : null)
    })
    const range = fiddler.getRange()
      .offset(1, 0, backgrounds.length, backgrounds[0].length)

    range
      .setBackgrounds(backgrounds)
      .setFontColors(fontColors)

    return {
      range,
      backgrounds,
      fontColors
    }
  }

Colorizer

We can now apply the colorizer function. I’ll also set some formats for the header row just before dumping the fiddler data.

  // getColorPack will give me a contrast to use for font color
  const headColor =  getColorPack('black')
  targetFiddler.setHeaderFormat({
    wraps: true,
    backgrounds: headColor.hex,
    fontWeights: 'bold',
    fontColors: headColor.contrast
  })

  // dump the results
  targetFiddler.dumpValues()

  // color anything that looks like a hex
  colorize(targetFiddler)

Colorizing

Initial colorization

Here’s the beginning of the scale

Custom scheme

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.

  const getChroma = () => {
    return new Proxy(Exports.chroma, {
      apply(target, thisArg, args) {
        // we can first check if the colors appear in any schemes before having chroma check
        if (args && args[0] && typeof args[0] === 'string') {
          const s = Exports.Schemes
          const color = s.getColor(args[0], false)
          return target.apply(thisArg, color ? [color].concat(args.slice(1)) : args)
        } else {
          return target.apply(thisArg, args)
        }
      }
    });
  }

a proxy for chroma

The material color built in scheme.

The custom scheme for material colors looks like this – a small snippet below.

{
    red: {
      base: '#f44336',
      variants: {
        "lighten-5": '#ffebee',
        "lighten-4": '#ffcdd2',
        "lighten-3": '#ef9a9a',
        "lighten-2": '#e57373',
        "lighten-1": '#ef5350',
        "darken-1": '#e53935',
        "darken-2": '#d32f2f',
        "darken-3": '#c62828',
        "darken-4": '#b71c1c',
        "accent-1": '#ff8a80',
        "accent-2": '#ff5252',
        "accent-3": '#ff1744',
        "accent-4": '#d50000'
      }
    },
    pink: {
      base: '#e91e63',
      variants: {
        "lighten-5": '#fce4ec', ... etc

material color scheme

Referencing a custom scheme.

A color in a custom scheme is constructed as follows:

schemename-basecolor-optionalvariants

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.

  // add some more columns for the test if they are not already there
  const extraCols = ['vanilla', 'material']
  extraCols.filter(f => targetFiddler.getHeaders().indexOf(f) === -1)
    .forEach(f => targetFiddler.insertColumn(f))

  ...

  // use material base colors
  const materialScale = chroma.scale([
    'mc-blue', 
    'mc-yellow', 
    'mc-blue'
  ].map(f => chroma(f))).domain([-90, 0, 90]);

  ...

    // now lets make a color for each airport
  targetFiddler.mapRows(row => {
    row.vanilla = vanillaScale(row.latitude_deg).hex()
    row.material = materialScale(row.latitude_deg).hex()
    return row
  })

extra code for a columns with a material scale

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

  // add some more columns for the test if they are not already there
  const extraCols = ['vanilla', 'material','material-strict']
  extraCols.filter(f => targetFiddler.getHeaders().indexOf(f) === -1)
    .forEach(f => targetFiddler.insertColumn(f))

  ...

  // function to caclculate nearest match
  // generalize this to take any mode for future reference
  // but we'll use the 'oklab' mode by default - it seems to give the best results
  const getDistance = (a, b, name = 'oklab') =>
    name === 'deltaE' ? chroma.deltaE(a, b) : chroma.distance(a, b, name)

  // we'll get the entire material color set
  const materialScheme = Exports.Schemes.listScheme('mc')
  const materialHexes = materialScheme.map(f => f.hex)

  // find the closest in the set
  const getNearest = (color, refSet = materialHexes) => refSet.reduce((p, c) => {
    const distance = getDistance(color, c)
    return distance < p.distance ? {
      distance,
      color,
      target: c
    } : p
  }, {
    distance: Infinity,
    color,
    target: null
  })

 ...

  // now lets make a color for each airport
  targetFiddler.mapRows(row => {
    row.vanilla = vanillaScale(row.latitude_deg).hex()
    row.material = materialScale(row.latitude_deg).hex()
    // how about limiting to the nearest material colors
    row['material-strict'] = getNearest(row.material).target
    return row
  })

nearest match from a scheme

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.

  // add some more columns for the test if they are not already there
  const extraCols = ['vanilla', 'material','material-strict', 'tropics'] 
  extraCols.filter(f => targetFiddler.getHeaders().indexOf(f) === -1)
    .forEach(f => targetFiddler.insertColumn(f))

.....

// create a custom scale
  Exports.Schemes.addScheme({
    name: 'tropics',
    scheme: {
      south: 'purple',
      capricorn: 'red',
      equator: 'yellow',
      cancer: 'orange',
      north: 'blue'
    }
  })
  const tropicsScale = chroma.scale([
    'tropics-south',
    'tropics-capricorn',
    'tropics-equator',
    'tropics-cancer',
    'tropics-north'
  ].map(f => chroma(f))).domain([-90, -23.43616, 0, 23.43616, 90]);

  ...

    // now lets make a color for each airport
  targetFiddler.mapRows(row => {
    row.vanilla = vanillaScale(row.latitude_deg).hex()
    row.material = materialScale(row.latitude_deg).hex()
    row['material-strict'] = getNearest(row.material).target
    row.tropics = tropicsScale (row.latitude_deg).hex()
    return row
  })

your own custom scheme

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.

const testCustomSchemes = () => {


  // open the airports sheet
  const sourceFiddler = Exports.newPreFiddler({
    sheetName: "airports",
    id: 'your copy of airports id'
  })

  const targetFiddler = Exports.newPreFiddler({
    sheetName: "color-airports",
    id: 'your copy of airports id',
    createIfMissing: true
  })


  targetFiddler.setData(sourceFiddler.getData())


  // add some more columns for the test if they are not already there
  const extraCols = ['vanilla', 'material','material-strict', 'tropics'] 
  extraCols.filter(f => targetFiddler.getHeaders().indexOf(f) === -1)
    .forEach(f => targetFiddler.insertColumn(f))


  // pick up the functions we'll need from the bmChroma library
  const { getChroma, getContrast, getColorPack } = Exports.ColorWords
  const chroma = getChroma()

  // Vanilla scale
  // the idea here is to colorize using a scale according to the absolute latitude
  // first of all using normal colors from blue (cold) thru yellow(warm) then thru blue again
  // make a copy to here
  const vanillaScale = chroma.scale(['blue', 'yellow', 'blue'].map(f => chroma(f))).domain([-90, 0, 90]);

  // then use material base colors
  const materialScale = chroma.scale([
    'mc-blue', 
    'mc-yellow', 
    'mc-blue'
  ].map(f => chroma(f))).domain([-90, 0, 90]);

  // create a custom scale
  Exports.Schemes.addScheme({
    name: 'tropics',
    scheme: {
      south: 'purple',
      capricorn: 'red',
      equator: 'yellow',
      cancer: 'orange',
      north: 'blue'
    }
  })
  const tropicsScale = chroma.scale([
    'tropics-south',
    'tropics-capricorn',
    'tropics-equator',
    'tropics-cancer',
    'tropics-north'
  ].map(f => chroma(f))).domain([-90, -23.43616, 0, 23.43616, 90]);

  // function to caclculate nearest match
  // generalize this to take any mode for future reference
  // but we'll use the 'oklab' mode by default - it seems to give the best results
  const getDistance = (a, b, name = 'oklab') =>
    name === 'deltaE' ? chroma.deltaE(a, b) : chroma.distance(a, b, name)

  // we'll get the entire material color set
  const materialScheme = Exports.Schemes.listScheme('mc')
  const materialHexes = materialScheme.map(f => f.hex)

  // find the closest in the set
  const getNearest = (color, refSet = materialHexes) => refSet.reduce((p, c) => {
    const distance = getDistance(color, c)
    return distance < p.distance ? {
      distance,
      color,
      target: c
    } : p
  }, {
    distance: Infinity,
    color,
    target: null
  })

  // now lets make a color for each airport
  targetFiddler.mapRows(row => {
    row.vanilla = vanillaScale(row.latitude_deg).hex()
    row.material = materialScale(row.latitude_deg).hex()
    // how about limiting to the nearest material colors
    row['material-strict'] = getNearest(row.material).target
    row.tropics = tropicsScale (row.latitude_deg).hex()
    return row
  })

  // lets sort by latititude
  targetFiddler.setData(targetFiddler.sort('latitude_deg'))

  //see if avalues  is a hex code - naive test
  const isHex = (value) => value && value.toString().match(/^#[0-9abcdef] $/i)

  // this function will return a closure that will color the fiddler
  const colorize = (fiddler) => {
    const backgrounds = fiddler.getData().map(row => {
      return fiddler.getHeaders().map(col => isHex(row[col]) ? row[col] : null)
    })
    const fontColors = backgrounds.map(row => {
      return row.map(col => isHex(col) ? getContrast(col) : null)
    })
    const range = fiddler.getRange()
      .offset(1, 0, backgrounds.length, backgrounds[0].length)

    range
      .setBackgrounds(backgrounds)
      .setFontColors(fontColors)

    return {
      range,
      backgrounds,
      fontColors
    }
  }

  // copy the source data and set basic header formatting
  
  const headColor =  getColorPack('black')
  targetFiddler.setHeaderFormat({
    wraps: true,
    backgrounds: headColor.hex,
    fontWeights: 'bold',
    fontColors: headColor.contrast
  })

  // dump the results
  targetFiddler.dumpValues()

  // color anything that looks like a hex
  colorize(targetFiddler)
  

}

Complete code

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.

  console.log(JSON.stringify(
    ColorWords.getColors(
      'theres a mc-red-lighten-2 house with mc-amber-accent-2 windows and a mc-purple and teal door'
    ))
  )

Colorwords

There are a number of articles about color mixing and schemes. Here’s a small selection

And some more background on fiddlers – there are many others – try searching for fiddler

Code

testBmChromaCustom script github
bmChroma script github
bmPreFiddler script github