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]}]

For help and more information join our forumfollow the blogfollow me on twitter

The code

Here's the library reference
MqxKdBrlw18FDd-X5zQLd7yz3TLx7pV4j



For help and more information join our forumfollow the blogfollow me on twitter

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