Converting Google Docs table to JSON and back

It’s pretty common to convert spreadsheet values to an array of JSON objects using the header row as the property keys, and you’ll find many examples of that around this site. It’s a little less common, but equally useful, to convert Docs tables to JSON objects (and back). The object model of Docs is a little less friendly to structured data than that of sheets, but a simple trick can turn these less friendly elements into arrays. This article shows a couple of snippets for managing this as a very simple pattern.

Page Content hide

1 The example

2 Here’s how it works.

3 Back again

The example

Let’s start with a document with this table in it, which I want to convert into an array of objects

into this.

[{
 "title": "McDonalds, Beijing",
 "info": "china"
}, {
 "title": "McDonalds, Paris",
 "info": "france"
}, {
 "title": "McDonalds, London",
 "info": "london"
}, {
 "title": "McDonalds, New York",
 "info": "USA"
}, {
 "title": "McDonalds, Toronto",
 "info": "Canada"
}]

Here’s how it works.

The key to keeping this simple is to tranform the table and row objects into arrays which are more convenient to deal with.

/**
 * get the data from the table and objectify it
 * @return {[object]} the data
 */
function objectifyTable () {
  // open the document
  var doc = DocumentApp.getActiveDocument();
  var body= doc.getBody();
  // get the first table
  var table = body.getTables()[0]; 
  if (!table || !table.getNumRows()) {
    return [];
  }
  // turn the table rows into an array
  var rows = arrayify(table);
  // slice off the headers and turn into an array of values
  var heads = arrayify(rows.shift())
  .map(function(cell) {
    return cell.getText();
  });
  // now we can objectify   
  return rows.map (function (row) {
    // get the cells in this row
    var cells = arrayify (row);
    // use the header text to drive objectification
    return heads.reduce (function (p,c,i) {
      p[c] = cells[i].getText();
      return p;
    },{});
  });
}

using this small function

function arrayify (items) {
// items returned from api are not really arrays - this converts
for (var arr = [] ,i=0; i &lt; items.getNumChildren() ; i   ) {
arr.push (items.getChild (i));
}
return arr;
}

Back again

Here’s the opposite – takes a an array of JSON objects and creates a table for them. Let’s assume that the table already exists, so the first thing it needs to do is clear that table, then repopulate with the new data.

} data the data
 * @return {Table} the table
 */
function unObjectifyToTable (data) {
  // open the document
  var doc = DocumentApp.getActiveDocument();
  var body= doc.getBody();
  // get the first table
  var table = body.getTables()[0]; 
  // delete all the rows
  while ( table.getNumRows() > 0) {
    table.removeRow(table.getNumRows() -1);
  }
  if (!data.length) {
    return table;
  }
  // these are the heads
  var heads = Object.keys(data[0]);
  // append the headers
  var tr = table.appendTableRow();
  heads.forEach (function (d) {
    tr.appendTableCell(d);
  });
  // and the data
  data.forEach (function (row) {
    var tr = table.appendTableRow();
    heads.forEach (function (d) {
       tr.appendTableCell(row[d]);
    });
  });
  return table;
}

For more like this see Google Apps Scripts Snippets