In Flattening an object with dot syntax I showed how to take an object of more than 1 level deep and flatten it so it could be represented in a two-dimensional object like a spreadsheet. Now here’s the opposite, unflattening an object created that way. I also provide a library you can use for flattening and unflattening, as well as processing objects to and from spreadsheets
Here’s the library reference
{a:1,b:2,"c.d":3,"c.e.f":25,"g.0":1,"g.1":2,"g.2":3})
which has been flattened from this original object
{a:1,b:2,c:{d:3,e:{f:25}},g:[1,2,3]}
Note how an array is handled using the index number as a part of the flattened property
var f = new cFlatten.Flattener(); Logger.log( f.flatten({a:1,b:2,c:{d:3,e:{f:25}},g:[1,2,3]}));
result
{g.2=3.0, b=2.0, g.1=2.0, c.d=3.0, g.0=1.0, a=1.0, c.e.f=25.0}
unflatten an object
var f = new cFlatten.Flattener(); Logger.log( f.unFlatten( {a:1,b:2,"c.d":3,"c.e.f":25,"g.0":1,"g.1":2,"g.2":3}));
result
{g=[1.0, 2.0, 3.0], b=2.0, c={d=3.0, e={f=25.0}}, a=1.0}
create spreadsheet representation of values from an object
var f = new cFlatten.Flattener(); var obs = [{a:1,b:2,c:{d:3,e:{f:25}},g:[1,2]}, {a:3,b:2,c:{d:2,e:{f:5}},g:[11,12,13]}]; values = f.getValues ( obs); Logger.log(JSON.stringify(values));
result
[["a","b","c.d","c.e.f","g.0","g.1","g.2"],[1,2,3,25,1,2,""],[3,2,2,5,11,12,13]]
create an unflattened object from spreadsheet values
var f = new cFlatten.Flattener(); var v = [["a","b","c.d","c.e.f","g.0","g.1","g.2"],[1,2,3,25,1,2,""],[3,2,2,5,11,12,13]]; Logger.log(JSON.stringify(f.getObjectsFromValues(v)));
result
[{"a":1,"b":2,"c":{"d":3,"e":{"f":25}},"g":[1,2,""]},{"a":3,"b":2,"c":{"d":2,"e":{"f":5}},"g":[11,12,13]}]
Putting it all together, start with an object, write it to a sheet
var f = new cFlatten.Flattener(); var obs = [{a:1,b:2,c:{d:3,e:{f:25}},g:[1,2]}, {a:3,b:2,c:{d:2,e:{f:5}},g:[11,12,13]}]; var ss = SpreadsheetApp.openById("12pTwh5Wzg0W4ZnGBiUI3yZY8QFoNI8NNx_oCPynjGYY"); var sheet = ss.getSheetByName("flatten"); var values = f.getValues(obs); sheet.getRange(1,1,values.length,values[0].length).setValues(values);
result
a | b | c.d | c.e.f | g.0 | g.1 | g.2 |
1 | 2 | 3 | 25 | 1 | 2 | |
3 | 2 | 2 | 5 | 11 | 12 | 13 |
create an unflattened object from a sheet
var ss = SpreadsheetApp.openById("12pTwh5Wzg0W4ZnGBiUI3yZY8QFoNI8NNx_oCPynjGYY"); var f = new cFlatten.Flattener(); var sheet = ss.getSheetByName("flatten"); var obs = f.getObjectsFromValues(sheet.getDataRange().getValues()); Logger.log(JSON.stringify(obs));
result
[{"a":1,"b":2,"c":{"d":3,"e":{"f":25}},"g":[1,2,""]},{"a":3,"b":2,"c":{"d":2,"e":{"f":5}},"g":[11,12,13]}]
The code
/** * @param {string} optObKeep if specified,objects of this key wont be flattened * @return {Flattener} self */ function getLibraryInfo () { return { info: { name:'cFlatten', version:'2.2.1', key:'MqxKdBrlw18FDd-X5zQLd7yz3TLx7pV4j', }, dependencies:[ ] }; } var Flattener = function(optObKeep) { var self = this; self.obKeep = optObKeep || null; self.keepDates = false; self.setKeepDates = function (keep) { self.keepDates = keep; return self; }; return self; }; /** get an array of objects from sheetvalues and unflatten them * @parameter {Array.object} values a 2 dim array of values return by spreadsheet.getValues() * @return {object} an unflatten object **/ Flattener.prototype.getObjectsFromValues = function (values) { var self = this; var obs = []; for (var i=1 ; i < values.length ; i++){ var k = 0; obs.push(self.unFlatten(values[i].reduce (function (p,c) { p[values[0][k++]] = c; return p; } , {}))); } return obs; }; /** get values from an array of objects by flattening and sorting all the keys found * @parameter {Array.object} obs an array of objects * @return {Array.object} a two dim array of values **/ Flattener.prototype.getValues = function(obs) { var self = this; var headings = self.getHeadingMap(obs); var headingValues = Object.keys(headings); var width = headingValues.length; return [headingValues].concat(obs.map ( function (row) { var v =[]; for (var i=0;i<width;i++)v.push(''); var o = self.flatten(row); Object.keys(o).forEach( function (k) { v[headings[k]] = o[k]; }); return v; })); }; /** get headings from an array of objects by flattening and sorting all the keys found * @parameter {Array.object} obs an array of objects * @return {object} a flattened object with a property for each key and its position **/ Flattener.prototype.getHeadingMap = function(obs) { var self = this; var headings = {},n=0; obs.forEach ( function (row) { headings = Object.keys(self.flatten(row)).reduce(function(p,c) { if (!p.hasOwnProperty(c)) { p = 0; } return p; },headings ); }); // sort the keys return Object.keys(headings).sort ( function (a,b) { return a > b ? 1 : ( a===b ? 0 : -1); }) .reduce(function (p,c) { p = n++; return p; },{}); }; /** unFlatten an ob * creates this {a:1,b:2,c:{d:3,e:{f:25}},g:[1,2,3]} * from this {a:1,b:2,"c.d":3,"c.e.f":25,"g.0":1,"g.1":2,"g.2":3} * @parameter {object} ob the object to be unflattened * @return {object} the unflattened object **/ Flattener.prototype.unFlatten = function (ob) { var self = this; return Object.keys(ob).reduce(function (p,c) { var pk=p, keys = c.split("."); for (var i=0; i < keys.length-1 ;i++) { if (!pk.hasOwnProperty(keys[i])) { pk[keys[i]] = self.isNumber(keys[i+1]) ? [] : {}; } pk = pk[keys[i]]; } var k = keys[keys.length-1]; pk[k] = ob; return p; },Array.isArray(ob) ? [] : {}); }; /** flatten an ob * turns this {a:1,b:2,c:{d:3,e:{f:25}},g:[1,2,3]} * into this {a:1,b:2,"c.d":3,"c.e.f":25,"g.0":1,"g.1":2,"g.2":3} * @parameter {object} ob the object to be flattened * @return {object} the flattened object **/ Flattener.prototype.flatten = function(ob) { var self = this; return self.objectDot (ob).reduce(function(p,c){ p = c.value; return p; },{}); }; Flattener.prototype.objectSplitKeys = function (ob,obArray,keyArray) { obArray = obArray || []; var self = this; //turns this {a:1,b:2,c:{d:3,e:{f:25}}} // into this, so that the keys can be joined to make dot syntax //[{key:[a], value:1},{key:[b], value:2} , {key:, value:3}, {key:, value:25}] if (self.isObject(ob)) { Object.keys(ob).forEach ( function (k) { var ka = keyArray ? keyArray.slice(0) : []; ka.push(k); if(self.isObject(ob[k]) && (!self.obKeep || !ob[k][self.obKeep]) && ( !self.keepDates || !self.isDateObject(ob[k]))) { self.objectSplitKeys (ob[k],obArray,ka); } else { obArray.push ( {key:ka, value:ob[k]} ); } }); } else { obArray.push(ob); } return obArray; }; Flattener.prototype.objectDot = function (ob) { var self = this; return self.objectSplitKeys (ob).map ( function (o) { return {key:o.key.join("."), value:o.value}; }); }; Flattener.prototype.isObject = function (obj) { return obj === Object(obj); }; Flattener.prototype.isNumber = function (s) { return !isNaN(parseInt(s,10)) ; }; Flattener.prototype.isDateObject = function (ob) { return this.isObject(ob) && ob.constructor && ob.constructor.name === "Date"; }; /** get headings from an array of objects by flattening and sorting all the keys found * @parameter {Array.object} obs an array of objects * @return {Array.object} an array of heading values **/ Flattener.prototype.getHeadings = function(obs) { return Object.keys(self.getHeadingMap(obs)); };