Cleaning up a document format

These articles are abridged from my  book on Apps Script.
Going GASfrom VBA to Google Apps Script. Now available from O’Reilly or Amazon

If you’ve ever had to go through and fix up formatting on a large Google Doc, you’ll know what a pain it can be. Here’s how to automate it.

Formatting tables

In this example, I wanted a parameter driven solution that searched through a document and set various attributes of all tables with just 1 cell in them – I use these to publish source code.
Additionally, the formats had to be slightly different depending on whether the code language was VBA or Apps Script.
Specifically, JavaScript would look like this
and VBA like this

The code

It’s pretty straightforward – simply set up the attributes that need to be set, the value to change them to, and which type of table (in my case I assigned the types VBA and JS) to apply the changes to. Note this example is specific for 1 cell tables, but that can me modified with a small adaptation.
// find all the one cell tables 
// set to a standard
function cleanUp() {
    var doc = DocumentApp.getActiveDocument();
    // these are the properties to set in selected tables
    var attribs = {
      cell: [{
        value: "#ffffff",
        attribute: "BackgroundColor",
        types: ["VBA"]
      }, {
        value: "#f3f3f3",
        attribute: "BackgroundColor",
        types: ["JS"]
      text: [{
        value: 9,
        attribute: "FontSize"
      }, {
        value: "Consolas",
        attribute: "FontFamily"
      }, {
        value: "#000000",
        attribute: "ForegroundColor",
        types: ["JS"]
      }, {
        value: "#434343",
        attribute: "ForegroundColor",
        types: ["VBA"]
      table: [{
        value: "#d9d9d9",
        attribute: "BorderColor",
        types: ["VBA", "JS"]
      }, ]
    // look at all the tables
    doc.getBody().getTables().forEach(function(d) {
      // im only doing 1 cell tables
      if (d.getNumChildren() === 1) {
        // single cell table
        var cell = d.getCell(0, 0);
        var text = cell.editAsText();
        // simple test for language is look for semicolons.. not perfect but it'll do
        var type = text.getText().indexOf(';') === -1 ? 'VBA' : 'JS';
        // now set the attributes at each level
        setAttributes(attribs.table, d, type);
        setAttributes(attribs.cell, cell, type);
        setAttributes(attribs.text, text, type);
    // set the attributes          
    function setAttributes(obAttribs, ob, type) {
      (obAttribs || []).forEach(function(d) {
        if (!d.types || !d.types.length || d.types.some(function(e) {
            return e === type;
          })) {
          ob['set' + d.attribute] ( d.value);

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.

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.