davids davids - 3 months ago 13
JSON Question

Convert Google Sheet as Multi-level JSON Array

I am attempting to use

Apps Script
to convert the values in a sheet to a multi-level
JSON
.

It is easy enough to convert the values to a single level
JSON
like this:

[{
"name": "Bob Jones",
"phone": "555-555-5555",
"street": "123 Somewhere St.",
"city": "Nowhere",
"state": "ID",
"postal": 45632,
"country": "USA"
}]


But, what I want, is this:

[{
"name": "Bob Jones",
"phone": "555-555-5555",
"address": {
"street": "123 Somewhere St.",
"city": "Nowhere",
"state": "ID",
"postal": 45632,
"country": "USA"
}
}]


Here is the code used to format the
JSON
:

function makeJSON_(object, options) {
if (options.format == FORMAT_PRETTY) {
var jsonString = JSON.stringify(object, null, 4);
} else if (options.format == FORMAT_MULTILINE) {
var jsonString = Utilities.jsonStringify(object);
jsonString = jsonString.replace(/},/gi, '},\n');
jsonString = prettyJSON.replace(/":\[{"/gi, '":\n[{"');
jsonString = prettyJSON.replace(/}\],/gi, '}],\n');
} else {
var jsonString = Utilities.jsonStringify(object);
}
return jsonString;
}


It would be easy enough to setup a "pre-conversion" sheet to create the
JSON
substring, but that isn't flexible and would be a beast to maintain.

I have setup a
Google Sheet
for testing and review here.

How do I
JSON.stringify()
the sheet data to automatically create the substrings?

Answer

To go from the version of json you have to the one you want to go to you can do the following --

var json = [{
  "name": "Bob Jones",
  "phone": "555-555-5555",
  "street": "123 Somewhere St.",
  "city": "Nowhere",
  "state": "ID",
  "postal": 45632,
  "country": "USA"
}]

for (var i=0; i < json.length; i++){
  var currentObj = json[i];
  // make a temporary address object
  var address = {};
  // copy all the attributes over to the temp object
  address.street = currentObj.street;
  address.city = currentObj.city;
  address.state = currentObj.state;
  address.postal = currentObj.postal;
  address.country = currentObj.country;
  // add address to the original object
  currentObj.address = address;
  // get rid of the following attributes from parent
  delete currentObj.street;
  delete currentObj.city;
  delete currentObj.state;
  delete currentObj.postal;
  delete currentObj.country;
}


console.log(json);

Much easier than replacing things in a string.

http://codepen.io/anon/pen/XKOrXa

Comments