In Playing around with GAS color and Playing around with colors in VBA we looked at how various color schemes could be stored and retrieved by Looking up color table.

The trouble is that this table can get pretty big if you are storing various color schemes such as Pantone, Dulux paint and so on, and you don’t want the overhead of them being in any particular sheet. Ideally you’d also like to be able to access the same table and their attributes from any source – such as javaScript, Google App Script, Excel, a browser, curl, PHP .. in fact anything that can do a rest API query.

To make that possible we’ll mash up all that color stuff with Google Apps ScriptDB, and serve up color attributes as jSon or jsonp using Google Apps Script as described in Let Google Apps Script do the work. That way, we can get various palettes and their attributes by executing a simple rest query.

Get the colortable into ScriptDB

The colortable referenced in Playing around with GAS color is in a spreadsheet in Google Apps Script. Here’s how to load scriptDB with the data. We only need the name, key, scheme, hex code and reference code. All the other attributes will be computed at query time. 

How to query

These examples will look up the color table and return the color characteristics. 

  • We can deal with multiple requests simultaneously – just separate them by a comma. 
  • Adding scheme= is optional, but can help if there is a duplication of code or name between schemes. 
  • You can use code= and name= and key= in the same query. 
  • If the same color is referred to multiple times, it will be repeated multiple times.
  • Everything is case insensitive, and will anyway be converted to lower case
?key=pfh-blue fox,pfh-surf spray?name=blue fox,surf spray?scheme=pfh&name=surf spray,silver?code=17-4811 TCX&scheme=pfh?scheme=dulux&name=five star,fizz&code=P39G2
These will simply return the color characteristics of the given codes?rgb=213-210-220,219-0-12?hex=#fa1230,#1a2300

The response

Regardless of the query, the response format is always the same

Beautiful JSON

If you want beautfied JSON just add &beautiful


By default, JSON will be returned. If you want JSONP just add &callback=callbackfunction

Transitioning is covered more comprehensively in my my book, Going Gas.

Continue reading about VBA to Google Apps Script here