Optimizing sheet formatting with Apps Script

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 exampleI 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 valuesYou 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