Optimizing sheet formatting with Apps Script

Apps Script (intermediate level)posted on 13th November 2018

If you are using fiddler to format your sheet, as described in Formatting sheet column data with fiddler you probably don't need to bother with this article, as fiddler already does it behind the scenes, but if if you are formatting sheets manually with Apps Script - here' how you can cut down significantly in sheet traffic using rangelists and collecting like formats together. 

What are rangelists

These are a relatively new addition to Apps Script, and are essentially a set of disconnected ranges. This allows you an efficient way of, for example applying the same formatting options to a whole set of ranges in one go.

The example
I usually set formats using an object like this. 
var formats = [ {
    range: sheet.getRange("a2:a7"),
    format: {
      backgrounds:"red",
      fontColors:"white",
      fontStyles:"normal"
    }
  },{ 
  range: sheet.getRange("b2:b7"),
    format: {
      backgrounds:"yellow",
      fontColors:"black",
      fontStyles:"normal"
    }
  },{ 
    range: sheet.getRange("c2:c7"),
    format: {
      backgrounds:"red",
      fontColors:"white",
      fontStyles:"normal"
    }
  },{ 
    range: sheet.getRange("d2:f7"),
    format: {
      backgrounds:"darkgray",
      fontColors:"white",
      fontStyles:"italic"
    }
  }, {
    range: sheet.getRange("a1:g1"),
    format: {
      wraps:true,
      backgrounds:"yellow",
      fontWeights:'bold',
      fontColors:"black"
    }
  }];  

along with a function like this.
function setFormats (range, format) {
  // if there's anything to do
  var atr = range.getNumRows();
  var atc = range.getNumColumns();
  if(atc && atr){
    // for every format mentioned
    Object.keys(format).forEach (function (f) {
      // check method exists and apply it
      var method = 'set'+f.slice(0,1).toUpperCase()+f.slice(1).replace (/s$/,"").replace(/ies$/,"y");
      if (typeof range[method] !== "function") throw 'unknown format ' + method;
      range[method](format[f]);
    });
  } 
};

executed like this
  formats.forEach (function (f) {
    setFormats (f.range, f.format);
  });

That gives this

But looking at the execution log, we get these set format calls. Not a huge problem but with lots of columns and formats, this could mount up

Range.setBackground([red]) [0.058 seconds]
Range.setFontColor([white]) [0.001 seconds]
Range.setFontStyle([normal]) [0 seconds]

Range.setBackground([yellow]) [0 seconds]
Range.setFontColor([black]) [0 seconds]
Range.setFontStyle([normal]) [0 seconds]

Range.setBackground([red]) [0 seconds]
Range.setFontColor([white]) [0 seconds]
Range.setFontStyle([normal]) [0 seconds]

Range.setBackground([darkgray]) [0 seconds]
Range.setFontColor([white]) [0 seconds]
Range.setFontStyle([italic]) [0 seconds]

Range.setWrap([true]) [0 seconds]
Range.setBackground([yellow]) [0 seconds]
Range.setFontWeight([bold]) [0 seconds]
Range.setFontColor([black]) [0 seconds]


A better way though would be if all the formats like each other could be set at the same time. Rangelists allow us to work with disconnected ranges. It's more intuitive to think in terms of which formats apply to a range, rather than to think if terms of ranges apply to a format. Here's a function that inverts the settings above and applies them to a group of ranges in one go.  There's also a fallback there to the other method for any set format methods are not supported by rangeLists (either now or in the future).

function setFormatsRangeList (formats) {
  
  // optimize the formatting by collecting like formats together
  // and organizing by format rather than by range
  var formatOrgs = formats.reduce (function (p,c) {
    var sheet = c.range.getSheet();
    var sheetId = sheet.getSheetId();
    Object.keys(c.format).forEach (function (f) {
      // make a unique key for the combination of sheet/format/value
      var key = f+"_"+c.format[f]+"_"+sheetId;
      // initialize if we didn't see it before
      p[key] = p[key] || {
        value:c.format[f],
        format:f,
        ranges:[],
        sheet:sheet
      };
      // collect the ranges this same format applies to
      p[key].ranges.push (c.range);
    });
    return p;
  },{});
  
  // now we can apply formats using rangelists
  Object.keys (formatOrgs).forEach (function (d) {
    var o = formatOrgs[d];
    // make a rangelist of each range to which this applies
    var rangeList = o.sheet.getRangeList (o.ranges.map(function (e) { return e.getA1Notation(); }));
    // get rid of plural (this makes format object compatible between both methods)
    var method = "set"+o.format.slice(0,1).toUpperCase()+o.format.slice(1).replace (/s$/,"").replace(/ies$/,"y");
    // ideally we'll use a range list
    if (typeof rangeList[method] === 'function') {
      rangeList[method](o.value);
    } 
    // so there wasnt a rangelist version - lets try the other way
    else if (typeof o.ranges[0][method] === 'function') {
      // reconstruct a format object
      var t = {};
      t[o.format] = o.value;
      o.ranges.forEach (function (r) { setFormats (r , t) });
    }
    else {
      throw 'unknown format/method ' + o.format + '/' + method +'/'+ methods;;
    }
    
  });
  
  // return what we made
  return formatOrgs;

}

executed like this, using the same formats  definition as before
setFormatsRangeList( formats );

The inverted format it generates looks like this
//The inverted format it applies looks like this
[{
"value": "red",
"format": "backgrounds",
"ranges": ["A2:A7", "C2:C7"],
"sheet": "test"
}, {
"value": "white",
"format": "fontColors",
"ranges": ["A2:A7", "C2:C7", "D2:F7"],
"sheet": "test"
}, {
"value": "normal",
"format": "fontStyles",
"ranges": ["A2:A7", "B2:B7", "C2:C7"],
"sheet": "test"
}, {
"value": "yellow",
"format": "backgrounds",
"ranges": ["B2:B7", "A1:H1"],
"sheet": "test"
}, {
"value": "black",
"format": "fontColors",
"ranges": ["B2:B7", "A1:H1"],
"sheet": "test"
}, {
"value": "darkgray",
"format": "backgrounds",
"ranges": ["D2:F7"],
"sheet": "test"
}, {
"value": "italic",
"format": "fontStyles",
"ranges": ["D2:F7"],
"sheet": "test"
}, {
"value": true,
"format": "wraps",
"ranges": ["A1:H1"],
"sheet": "test"
}, {
"value": "bold",
"format": "fontWeights",
"ranges": ["A1:H1"],
"sheet": "test"
}]

And the execution log (about half the number of set formats  as previously)

RangeList.setBackground([red]) [0.064 seconds]
RangeList.setFontColor([white]) [0 seconds]
RangeList.setFontStyle([normal]) [0 seconds]
RangeList.setBackground([yellow]) [0 seconds]
RangeList.setFontColor([black]) [0 seconds]
RangeList.setBackground([darkgray]) [0 seconds]
[RangeList.setFontStyle([italic]) [0 seconds]
RangeList.setWrap([true]) [0 seconds]
RangeList.setFontWeight([bold]) [0 seconds]

Write values
You can also stamp values using this method. Can be handy for adding timestamps for example. Just add something like to the format definition. 
{
    range: sheet.getRange("g2:g7"),
    format: {
      background:"orange",
      fontWeights:'normal',
      fontColors:"white",
      values:new Date().getTime()
    }
  }

And here's what happens



If you want to use Fiddler, which already has all this implemented - it's here.
This class - Fiddler can be found in my cUseful library.

Here's the key, and it's also on github

1EbLSESpiGkI3PYmJqWh3-rmLkYKAtCNPi1L2YCtMgo2Ut8xMThfJ41Ex


There's more fiddler stuff here
For more like this, see Google Apps Scripts snippets. Why not join our forumfollow the blog or follow me on twitter to ensure you get updates when they are available. 

You want to learn Google Apps Script?

Learning Apps Script, (and transitioning from VBA) are covered 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

If you prefer Video style learning I also have two courses available. also published by O'Reilly.
Google Apps Script for Developers and Google Apps Script for Beginners.






Comments