Creating a nested JSON object with Google Sheets

Question

I am trying to teach myself some AppScript/Javascript. As an exercise, I am want to generate the following JSON object

[
  {
    "config_type": "City Details",
    "config_data": [
      {
        "city_type": "MAJOR_CITY",
        "city_data": [
          {
            "city_name": "BIGFOOLA",
            "monetary_data": [
              {
                "currency_data": [
                  {
                    "dollars": 1000
                  }
                ]
              }
            ]
          }
        ]
      }
    ]
  }
]

I want to be able to enter only few details like "City Details", "MAJOR_CITY", and the dollar value - 1000 in a Google Sheet. The script should be able to generate the above JSON.

So I started by creating the names of all the Arrays and Objects in one row. In front of the the arrays, there was a blank cell and in front of the object the value. The Sheet looks like this

A              B 
config_type    City Details
config_data    
city_type      MAJOR_CITY
city_data
city_name      BIGFOOLA
monetary_data
currency_data
dollars        1000

I am able to get all of the details in a single object, but struggling to nest them under each other. How do I go about this?

Edit :Here is what I have for now

function doGet(){
  var result={}
  var rewardSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CITY")
  var reward_data = rewardSheet.getRange("A1:B13").getValues();

  result = getJsonArrayFromData(reward_data);
  return ContentService.createTextOutput(JSON.stringify(result))
  .setMimeType(ContentService.MimeType.JSON)
}


function getJsonArrayFromData(data)
{
  var column_headers = data[0];
  var col_len = column_headers.length;
  var row = [];
  var reward_obj = [];
  var config_obj = {};

  var config_type = {};
  var config_data = [];
  var reward_type = {};
  var reward_data = [];
  var reward_name = {};
  var reward_data_2 = [];
  var currency_data = [];
  var curreny_obj = {};
  var rewardSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CITY")
  var row_items = rewardSheet.getRange(1,1,data.length,1).getValues();
  //Logger.log(row_items);

  for(var i=0;i<data.length;i++){

    row = data [i];
    config_type ={};
    reward_type[config_type]={};
    reward_name[reward_type]={};

    //Logger.log(row);
    for(var r=0;r<row.length;r++)
    {
      config_type[row[r]] = row[r+1];
      reward_type[row[r]] = row[r+1];
      reward_name[row[r]] = row[r+1];

    }
    config_data.push(config_type,reward_type,reward_name);
    //reward_data.push(reward_name);


    reward_obj = config_data;
  } 
  Logger.log(reward_obj);


  return reward_obj;

}

Ps: I know the JSON is messy, but its just to understand and teach myself.

Try to write the code yourself, then if are not able to make it work, search/research about what are you trying to do, then if you don't find how to do this, make a question. If you already did this, include a minimal reproducible example. Reference How to Ask. — Wicket
– Wicket, Commented Nov 30, 2017 at 18:09

user6655984 · Accepted Answer · 2017-11-30 22:45:36Z

You hard-coded a bunch of property names as variables; this is not a good approach.

Here is how one can do this. The variable output holds the object we'll return at the end, while currentObject points to the object that is due to be filled next. When it comes to filling it, we either have a scalar value data[i][1] to put in, or we don't, in which case a new object is created and becomes the new currentObject.

function formJSON() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("CITY");
  var data = sheet.getDataRange().getValues();
  var currentObject = {};
  var output = [currentObject];                     // or just currentObject  
  for (var i = 0; i < data.length; i++) {
    if (data[i][1]) {
      currentObject[data[i][0]] = data[i][1];
    }
    else {
      var newObject = {};
      currentObject[data[i][0]] = [newObject];      // or just newObject 
      currentObject = newObject;
    }
  }
  Logger.log(JSON.stringify(output));
}

The output is

[{"config_type":"City Details","config_data":[{"city_type":"MAJOR_CITY","city_data":[{"city_name":"BIGFOOLA","monetary_data":[{"currency_data":[{"dollars":1000}]}]}]}]}]

or, in beautified form,

[{
  "config_type": "City Details",
  "config_data": [{
    "city_type": "MAJOR_CITY",
    "city_data": [{
      "city_name": "BIGFOOLA",
      "monetary_data": [{
        "currency_data": [{
          "dollars": 1000
        }]
      }]
    }]
  }]
}]

Incidentally, I don't see why you wanted to put every object in an array. A property can be another object. Removing square brackets on the commented lines we would get

{
  "config_type": "City Details",
  "config_data": {
    "city_type": "MAJOR_CITY",
    "city_data": {
      "city_name": "BIGFOOLA",
      "monetary_data": {
        "currency_data": {
          "dollars": 1000
        }
      }
    }
  }
}

Thanks @user6655984. Can you do the reverse? Which is to import JSON into Sheets. One problem is I am not sure the best way to have JSON in Sheets.

Collectives™ on Stack Overflow

Creating a nested JSON object with Google Sheets

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related