Unnesting data to sheet values

Flattening deeply nested data for a sheet posted on 22nd Dec 2019


This is an Apps Script version of the code referred to in Flattening arrays for Elastic Search. The point of that article is to prepare data arriving from GraphQL for elastic search, but it also solves the problem of taking highly nested data with arrays that need to be exploded and arranging it for rendering in a sheet.
Lets' start with this data. 

[{
  "franchise": {
    "name": "rambo",
    "episodes": [{
      "name": "first blood",
      "year": 1982,
      "actors": [{
        "dob": "1946-07-06",
        "name": "Sylvester Stallone",
        "nicknames": ["sly"]
      }, {
        "name": "Jack Starrett"
      }, {
        "name": "Richard Crenna",
        "nationality": "American"
      }]
    }, {
      "name": "first blood part 2",
      "year": 1985,
      "actors": [{
        "dob": "1946-07-06",
        "name": "Sylvester Stallone",
        "nicknames": ["sly"]
      }, {
        "name": "Richard Crenna",
        "nationality": "American"
      }]
    }]
  }
}, {
  "franchise": {
    "name": "terminator",
    "episodes": [{
      "name": "the terminator",
      "year": 1984,
      "actors": [{
        "dob": "1947-07-30",
        "name": "Arnold Schwarzenegger",
        "nicknames": ["arnie"]
      }, {
        "name": "Linda Hamilton"
      }]
    }, {
      "name": "terminator 2: judgement day",
      "year": 1985,
      "actors": [{
        "dob": "1947-07-30",
        "name": "Arnold Schwarzenegger",
        "nicknames": ["arnie", "schwarzie"]
      }, {
        "name": "Michael Biehn"
      }]
    }]
  }
}];

To be able to render this in a sheet, the arrays need to be blown out, and the parent data repeated on each row - like this.

That's a little more complex than simple object flattening, as we need to create a duplicate row each time there is an array element, and of course these arrays might occur at any level.

The code

The Es6 version of this can be found at Flattening arrays for Elastic Search, but it needs a downgrade for Apps Script. The code can also be included in your Script from my cUseful library - library key 1EbLSESpiGkI3PYmJqWh3-rmLkYKAtCNPi1L2YCtMgo2Ut8xMThfJ41Ex or on github

var Unnest = (function (ns){

  /**
  * converts blowup into table
  *
  * @param {object[]} {blownup} the array from blowup
  * @param {function} {sorter} a function to sort the headers
  * @return {*[][]} the 2 dimensional array of values with the headers in the first row
  */
  ns.blownupToTable = function (options) {
    var blownup = options.blownup;
    var sorter = options.sorter || function(mentions) {
      return Object.keys(mentions).sort(function (a, b) { return a - b; });
    };
    
    // collect all the property names
    var mentions = blownup.reduce(function (p, c) {
      Object.keys(c).forEach(function (k, i) {
        p[k] = i;
      });
      return p;
    }, {});
    
    // make that into a header row
    var headerRow = sorter(mentions);
    // now add the rows after the header
    // & we dont really like undefined in sheets, so replace with null.
    return [headerRow].concat(blownup.map(function(row) {
      return headerRow.map(function (h) {
        return typeof row[h] === typeof undefined ? null : row[h]
      });
    }));
  };
  
  /**
  * an array of object(or an object of arrays) gets blown up into rows one row per array element
  * nested arrays are handled too so an array of 5 with 10 nested array would create 50 rows and so on
  * array members dont need to have the same properties, and can each contain separate nested arrays
  * each flattened property is given a property name reflecting the object tree preceding, so
  * {a:{b:{c:{name:'rambo'}}}} 
  * would be expressed as
  * {a_b_c_name: 'rambo'}
  * {a:{b:[{c:{name:'rambo'}}, {c:{name:'terminator'}}]}}
  * would be expressed as 
  * [{a_b_c_name: 'rambo'},[{a_b_c_name: 'terminator'}]
  * @param {object|object[]} {ob} the object to be blown up
  * @param {string} [{sep}] the separator to use betwenn propertyu name sections
  * @param {function} [{cloner}] a function to deep clone an obje
  */
  ns.blowup = function (options) {
    var ob = options.ob;
    var sep = options.sep || '_';
    var cloner = options.cloner || function(item) { return JSON.parse(JSON.stringify(item))};
    
    var isObject = function (sob) {
      return typeof (sob) === 'object' && !(sob instanceof Date);
    };
    
    // recursive piece
    var makeRows = function (sob, rows, currentKey, cob) {
      rows = rows || [];
      currentKey = currentKey || '';
      cob = cob || {};
      
      // ignore undefined or null items
      if (typeof sob === typeof undefined || sob === null) {
        return rows;
      } else if (Array.isArray(sob)) {
        // going to work through an array creating 1 row for each element
        // but without adding to the current key
        // make deep clone of current object
        sob.forEach(function(f, i) {
          // make clone of what we have so far to replicate across
          var clob = cloner(cob);
          // the first element updates an existing row
          // subsequent elements add to the number of rows
          if (i) {
            rows.push(clob);
          } else {
            rows[rows.length ? rows.length - 1 : 0] = clob;
          }
          // recurse for each element
          makeRows(f, rows, currentKey, clob);
        });
      } else if (isObject(sob)) {
      // deal with the non object children first so they get cloned too   
        Object.keys(sob).sort(function(a,b) { 
         return isObject(sob[a]) && isObject(sob[b]) ? 0 : (isObject(sob[b]) ? -1: 1);
        }).forEach(function (k, i) {
          // add to the key, but nothing to the accumulating object
          makeRows(sob[k], rows, currentKey ? currentKey + sep + k : k, cob);
        });
      } else {
        // its a natural value
        if (cob.hasOwnProperty(currentKey)) {
          // something has gone wrong here - show should probably be a throw
          Logger.log('attempt to to overwrite property', cob, currentKey, 'row', rows.length);
        } else {
          cob[currentKey] = sob;
        }
      }
      return rows;
    };
    
    // do the work - the input data should be an array of objects
    if(!Array.isArray(ob)) ob = [ob];
    return makeRows(ob);
  };
  
  ns.table = function (options) {
    var blownup = ns.blowup(options);
    return ns.blownupToTable ({ blownup: blownup, sorter: options.sorter });                       
  };
  return ns;
}) ({});



Which gives this result, with the header row in the first element
[
["franchise_name", "franchise_episodes_name", "franchise_episodes_year", "franchise_episodes_actors_dob", "franchise_episodes_actors_name", "franchise_episodes_actors_nicknames", "franchise_episodes_actors_nationality"],
["rambo", "first blood", 1982, "1946-07-06", "Sylvester Stallone", "sly", null],
["rambo", "first blood", 1982, null, "Jack Starrett", null, null],
["rambo", "first blood", 1982, null, "Richard Crenna", null, "American"],
["rambo", "first blood part 2", 1985, "1946-07-06", "Sylvester Stallone", "sly", null],
["rambo", "first blood part 2", 1985, null, "Richard Crenna", null, "American"],
["terminator", "the terminator", 1984, "1947-07-30", "Arnold Schwarzenegger", "arnie", null],
["terminator", "the terminator", 1984, null, "Linda Hamilton", null, null],
["terminator", "terminator 2: judgement day", 1985, "1947-07-30", "Arnold Schwarzenegger", "arnie", null],
["terminator", "terminator 2: judgement day", 1985, "1947-07-30", "Arnold Schwarzenegger", "schwarzie", null],
["terminator", "terminator 2: judgement day", 1985, null, "Michael Biehn", null, null]
]

Putting it together

Assuming the JSON data earlier in the article is in a variable called films, here's the whole thing, using the cUseful library.
function test() {
  // open a sheet and write this stuff to it
  const sheet = SpreadsheetApp.openById('xxxxxxxx').getSheetByName('films');
  const values = cUseful.Unnest.table({ ob: films });
  // clear it
  sheet.clear()
  // write the flattened data
  sheet.getRange(1, 1,values.length, values[0].length).setValues(values);

}




Since G+ is closed, you can now star and follow post announcements and discussions on github, here 

Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, All formats are available from O'ReillyAmazon 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