These articles are abridged from my book on Office to Apps migration. Going GAS, from VBA to Google Apps Script. Now available for directly from O’Reilly or Amazon.
Many applications have their own names for colors in their palettes. This example features an Apps Script library to retrieve and manipulate colors according to their names in their home palettes. The known palettes currently are Material colors, HTML named colors and the Google Apps Palette. The Google Apps Palette looks like this in the GUI. I’ve generated the material palette using this library, along with the closest matching colors in the Apps and html palette. Here’s the html palette, showing the closest matches in the other palettes.style=”border:1px solid #000000;”
and the google palette The idea is to be able to get a color by its palette and name, or to find its name from its color codes. You can use the library by including this in your project.MBo5MKxo06_g-0n5MOckL0ai_d-phDA33
It’s also available on Github if you prefer, and the test spreadsheet is here if you’d like to make a copy.
Some examples (if you are using the library you’ll need to prefix ColorList with the library identifier eg cColorList.ColorList.getColor()) function test() { // get a color properties Logger.log(JSON.stringify(cColorList.ColorList.getColor(‘google’,’light red berry 1′))); // get a color properties by hex code Logger.log(JSON.stringify(cColorList.ColorList.getColorName(‘google’,’#f3f3f3′))); // get a color properties by rgb number Logger.log(JSON.stringify(cColorList.ColorList.getColorName(‘html’,255))); // get closest Logger.log(JSON.stringify(cColorList.ColorList.getClosest (‘html’ , ‘#ff9900’))); Logger.log(JSON.stringify(cColorList.ColorList.getClosest (‘material’ , ‘#ff9900’))); // check grey/gray Logger.log(JSON.stringify(cColorList.ColorList.getColor(‘material’,’grey 600′))); Logger.log(JSON.stringify(cColorList.ColorList.getColor(‘google’,’light gray 2′))); // get closest var lister = cColorList.ColorList; Logger.log(JSON.stringify(lister.getClosest (‘material’ , lister.getColor(‘google’,’red berry’).value))); } The result is not only the code matching the name and palette, but loads of additional color related information (see Color Arranger for more information on these properties).{
"name": "light red berry 1",
"value": "#ca4126",
"palette": "google",
"status": "ok",
"properties": {
"rgb": 2507210,
"red": 202,
"green": 65,
"blue": 38,
"htmlHex": "#ca4126",
"luminance": 0.16378548548538505,
"textColor": "#ffffff",
"contrastRatio": 4.91146532991259,
"black": 0.207843137254902,
"yellow": 0.8118811881188118,
"magenta": 0.6782178217821783,
"cyan": 0,
"hue": 9.878048780487813,
"saturation": 68.33333333333333,
"lightness": 47.05882352941176,
"value": 0.792156862745098,
"x": 26.597269352335072,
"y": 16.47709067915203,
"z": 3.612286684447369,
"LStar": 47.594296368851595,
"aStar": 52.928166803487386,
"bStar": 45.38090169447055,
"cStar": 69.71956023800627,
"hStar": 40.609968669570854
}
}
You can then use this in your Apps Script code to set background colors and avoid worrying about hex codes. (spacing and case are ignored in the color names) – for example.
ColorList.getColor('google','cornflower blue').value
Text Color
A very useful property is the the textColor, which will be set to either black or white, depending on which is the best font color to use with the given background color. This property can be used to set the font color appropriately if you change the background color of a cell.ColorList.getColor('google','light red berry 1').properties.textColor
Matching Colors
This library also provides a useful way to get the closest match in a palette for some desired color.
This snip shows the beginning of the result of finding the nearest match between the html palette and the google palette. and visa versa The material palette versus google apps. and so on .. the full palette matches and details can be found in this sheet.The Code for matching colors.
Finding the nearest match for a given color code in a palette is simple. Here’s we’re looking for the closest color in the html palette to #ff9900 (orange in the google palette).Logger.log(JSON.stringify(ColorList.getClosest ('html' , '#ff9900')));
The query could also be written like this.
Logger.log(JSON.stringify(ColorList.getClosest ('html' , ColorList.getColor('google' , 'orange' ))));
It gives a result like this, where diff is a value between 0-100. The lower, the better the match.
{
"diff": 4.2351903633318795,
"member": {
"name": "Orange",
"value": "#ffa500"
}
}
Code for comparing to complete color schemes and dumping the result
// get the best match between html and google and material palettes function match () { var ss = SpreadsheetApp.getActiveSpreadsheet(); var lister = cColorList.ColorList; // compare combinations of palettes comparePalettes_ (‘google’,’html’); comparePalettes_ (‘google’,’material’); comparePalettes_ (‘html’,’google’); comparePalettes_ (‘html’,’material’); comparePalettes_ (‘material’,’html’); comparePalettes_ (‘material’,’google’); /** * @param {string} p1 palette1 * @param {string} p2 palette2 */ function comparePalettes_ (p1, p2) { // do comparison and write to sheet var sheet = ss.getSheetByName(p1+’_’+ p2) || ss.insertSheet(p1+’_’+ p2); // clear the sheet sheet.clearContents(); // get each of the palettes var list1 = lister.getList(p1); // add the properties to the sheet var heads = {}; heads[p1+’Name’] = Object.keys(heads).length; heads[p1+’Value’] = Object.keys(heads).length; heads[p2+’Name’] = Object.keys(heads).length; heads[p2+’Value’] = Object.keys(heads).length; heads.difference = Object.keys(heads).length; // find the nearest matches var values = Object.keys(list1).map(function(d) { // get the nearest match in the other list var best = lister.getClosest (p2 , list1[d].value); // make this row var row = []; row[heads[p1+’Name’]] = list1[d].name; row[heads[p1+’Value’]] = list1[d].value; row[heads[p2+’Name’]] = best.member.name; row[heads[p2+’Value’]] = best.member.value; row[heads.difference] = best.diff; return row; }); // now write the whole thing out sheet.getRange (1,1,values.length+1 , Object.keys(heads).length) .setValues([Object.keys(heads)].concat(values)); // colorize the columns sheet.getRange (2,1+heads[p1+’Name’],values.length , 2) .setBackgrounds(values.map (function(row) { return row.slice(0,2).map(function(col) { return row[heads[p1+’Value’]]; }); })); sheet.getRange (2,1+heads[p2+’Name’],values.length , 2) .setBackgrounds(values.map (function(row) { return row.slice(0,2).map(function(col) { return row[heads[p2+’Value’]]; }); })); // choose best font color sheet.getRange (2,1+heads[p1+’Name’],values.length , 2) .setFontColors(values.map (function(row) { return row.slice(0,2).map(function(col) { return lister.getColor(p2,row[heads[p2+’Name’]]).properties.textColor; }); })); sheet.getRange (2,1+heads[p2+’Name’],values.length , 2) .setFontColors(values.map (function(row) { return row.slice(0,2).map(function(col) { return lister.getColor(p2,row[heads[p2+’Name’]]).properties.textColor; }); })); } }
Adding palettes
It is possible to add your own palettes with a simple pattern. To demonstrate, I’ll use the material color design principle as demonstrated in the material palette site, where you can create something like this given a primary and an accent color. By providing a primary and an accent color, here’s the same thing applied to a sheet. In this case using Amber and Blue. Abstracting the actual color selection means that colors can be referred to as dark, light, primary, icons, accent, secondaryText and primaryText and changing the color is just a matter of picking a new primary and accent color, with no changes of code required. This library can be used for client side color manipulation too of course. A new Material palette combination is created like thisvar theCombination = new cColorList.MaterialPalette('pink','blue');
and components accessed like this, which allows complete abstraction from the color scheme used.
var accent = lister.getColor (theCombination.getName(), 'accent').value;
Here’s the code for creating a selection of combinations, each on its own sheet.
// generate various material palette combinations
function generateMaterial() {
var lister = cColorList.ColorList;
var ss = SpreadsheetApp.getActiveSpreadsheet();
// here's a list of palette combinations to try
[['pink','lime'],['amber','blue'],['purple','deeporange'],['deeporange','purple']].forEach(function (combination) {
// create the material palette
var mp = new cColorList.MaterialPalette(combination[0],combination[1]);
// a sheet for each known combination or create one
var sheet = ss.getSheetByName(mp.getName()) || ss.insertSheet(mp.getName());
// clear the sheet
sheet.clear();
//now we can use that with the normal lister stuff show all the props on a sheet
var members = lister.getList(mp.getName());
sheet.getRange(1,1,1,1).setValues ([['combination:' + mp.getName()]]);
Object.keys(members).forEach(function(d,i) {
// get the details on the material palette
var color = lister.getColorName ('material', members[d].value);
// write the details
sheet.getRange(i+2,1,1,3).setValues ([[members[d].name,color.name,members[d].value]]);
// set the background to show the color
sheet.getRange(i+2,1,1,3).setBackgrounds ([[members[d].value,members[d].value,members[d].value]]);
// use a contrasting text color if it exists in the material list, otherwise use the material text color
var textColor = color.properties ? color.properties.textColor : members.primaryText;
sheet.getRange(i+2,1,1,3).setFontColors ([[textColor,textColor,textColor]]);
// specific colors can now be called out abstractly like this - allowing easy refactoring.
Logger.log( lister.getColor(mp.getName() , 'primary').value);
Logger.log( lister.getColor(mp.getName() , 'dark').value);
Logger.log( lister.getColor(mp.getName() , 'light').value);
Logger.log( lister.getColor(mp.getName() , 'accent').value);
Logger.log( lister.getColor(mp.getName() , 'primaryText').value);
Logger.log( lister.getColor(mp.getName() , 'secondaryText').value);
});
});
}
Creating your own.
The material combination code is in the ColorList library but it’s straightforward to create your own by adapting this pattern. In this case, the colors are picked up from the material palette, but they can be anything you want them to be, as can the properties like dark, light and so on.'use strict';
var MaterialPalette = function (primary, accent) {
var self = this;
var primary_ = (primary || 'Indigo').toLowerCase();
var accent_ = (accent || 'Amber').toLowerCase();
self.getName = function () {
return 'material' + primary_ + accent_;
};
var gc = ColorList.getColor;
// add to known palettes
KnownPalettes.palettes[self.getName()] = {
"dark":gc('material ' , primary_ + " 700").value,
"light":gc('material ' ,primary_ + " 100").value,
"primary":gc('material ' ,primary_ + " 500").value,
"icons":gc('material ' ,"white").value,
"accent":gc('material ' ,accent_ + (["pink","blue"].indexOf(accent_) === -1 ? " 500" : "a200")).value,
"primaryText":gc('material ' ,"gray 900").value,
"secondaryText":"#727272",
"divider":"#B6B6B6"
};
// update the keys.
ColorList.reset();
}
Dumping the color properties.
Here’s an snip of dumping all the color properties for each of these palettes to a Sheet.
The google palette.
The html palette
The material palette
The code for dumping the palettes
// show all palettes, 1 sheet per palette
function showPalettes() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var lister = cColorList.ColorList;
lister.getPalettes().forEach(function(pal) {
// a sheet for each known palette or create one
var sheet = ss.getSheetByName(pal) || ss.insertSheet(pal);
// clear the sheet
sheet.clearContents();
// get the colors in each palette
var palette = lister.getList(pal);
// add the properties to the sheet
var heads,values = Object.keys(palette).map (function (color) {
var data = lister.getColor(pal, color);
if (!heads) {
heads = {};
heads.name = 0, heads.value = 1;
// and the color properties
Object.keys(data.properties).reduce(function(p,c) {
p = Object.keys(p).length;
return p;
},heads);
}
// add the data
return Object.keys(data.properties).reduce(function(p,c) {
p[heads] = data.properties;
return p;
},[data.name,data.value]);
});
// now write the whole thing out
sheet.getRange (1,1,values.length+1 , Object.keys(heads).length)
.setValues([Object.keys(heads)].concat(values));
// now to color the lines
sheet.getRange (2,1,values.length , Object.keys(heads).length)
.setBackgrounds(values.map (function(row) {
return row.map(function(col) {
return row[heads.htmlHex];
});
}));
// choose best font color
sheet.getRange (2,1,values.length , Object.keys(heads).length)
.setFontColors(values.map (function(row) {
return row.map(function(col) {
return row[heads.textColor];
});
}))
});
}
Code to generate the palettes with comparison notes.
function generatePalette() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var lister = cColorList.ColorList; var SIZE = 16, SEP=SIZE/6; // layout of palette var layout = { “material”:{ “rows”:15 }, “google”: { “rows”:8, }, “html”: { “rows”:12 } }; // get all the palettes lister.getPalettes().forEach (function (pal) { // open a palette sheet var sheet = ss.getSheetByName(pal+’Palette’) || ss.insertSheet(pal+’Palette’); // clear the sheet sheet.clear(); sheet.clearNotes(); // these are the ones to compare against var compareAgainst = lister.getPalettes().filter(function(d) { return d !== pal; }); // get the palette contents var palette = lister.getList(pal); // make the shape for (var i = 0, values = [], height = layout[pal].rows , width = Math.ceil((Object.keys(palette).length)/height) ; i < height*2 ;i++ ) { values.push([]); for (var j =0 ; j < width *2 ; j++, values[i].push(”)); } // fill up the values Object.keys(palette).forEach(function(d,idx) { values[1+2*(idx % height)][1+ 2*Math.floor(idx / height)] = palette[d].value; // set the note var note = palette[d].name + ‘ (‘ + palette[d].value +’)n’ + compareAgainst.map(function(e) { var result = lister.getClosest (e, palette[d].value); return e + ‘:’ + result.member.name + ‘ (‘ + result.member.value +’)’ }).join(‘n’); sheet.getRange(1+2*(idx % height)+1,1+ 2*Math.floor(idx / height)+1,1,1).setNote(note); // output the picture sheet.getRange (1,1,height*2, width*2).setBackgrounds(values); // set the width and height for (var i=0; i < height *2 ; sheet.setRowHeight (++i , i % 2 ? SEP : SIZE )); for (var i=0; i < width *2; sheet.setColumnWidth (++i , i % 2 ? SEP : SIZE )); }); }); } You can use the library by including this in your project.MBo5MKxo06_g-0n5MOckL0ai_d-phDA33
It’s also available on Github if you prefer, and the test spreadsheet is here if you’d like to make a copy.
Material color list adapted from https://github.com/danlevan/google-material-color
For more like this, see Color Arranger.