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

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.