CODEWITHSUNDEEP
javascriptjsongoogle-apps-scriptstringify
davids
davids

Reputation: 5577

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.

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

Upvotes: 1

Views: 1430

Answers (1)

charsi
charsi

Reputation: 3847

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

Upvotes: 1

Related Questions