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.

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<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. 
/**
 * write the json object as table rows, after clearing it
 * @param {[object]} 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. 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.






Comments