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
1 |
{a:1,b:2,"c.d":3,"c.e.f":25,"g.0":1,"g.1":2,"g.2":3}) |
1 |
{a:1,b:2,c:{d:3,e:{f:25}},g:[1,2,3]} |
1 2 |
var f = new cFlatten.Flattener(); Logger.log( f.flatten({a:1,b:2,c:{d:3,e:{f:25}},g:[1,2,3]})); |
1 |
{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
1 2 |
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
1 |
{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
1 2 3 4 |
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
1 |
[["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
1 2 3 |
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
1 |
[{"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
1 2 3 4 5 6 |
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
1 2 3 4 5 |
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)); |
1 |
[{"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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 |
/** * @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[c] = 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[c] = 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[c]; 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.key] = 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:[c,d], value:3}, {key:[c,e,f], 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)); }; |