Quick Links

Other stuff

Site owners

  • Bruce Mcpherson

Flattening and unflattening objects to spreadsheets

In Flattening an object with dot syntax I showed how to take a 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
MqxKdBrlw18FDd-X5zQLd7yz3TLx7pV4j

Consider this object,
  {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

Here's how to use.

flatten an object

 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

Here's the library reference
MqxKdBrlw18FDd-X5zQLd7yz3TLx7pV4j



For help and more information join our forum,follow the blog or follow me on twitter .

Comments