Cleaning up a document format

These articles are abridged from my book on Apps Script.
Going GAS, from 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.

Page Content hide

1 Formatting tables

2 The code

3 Share with your network

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);
        }
      });
    }
}

For more like this see Google Apps Scripts Snippets