Visualize Nested JSON Objects in Spreadsheet

Question

I'm attempting to visualize a nested JSON object in a spreadsheet – specifically Google Sheets. I'm using the Script Editor tool within my Google Sheet to write my code and the spreadsheet to output the data.

I've spent about 3 solid days on this and can't figure out how to get the data to be structured properly. It starts out working correctly but quickly devolves as the code traverses further into the JSON object. I have no control over the structure of the JSON object.

Because of it's size I won't be posting the actual JSON object here but it can be viewed in one of the files within my Google Sheet. Here's the recursive function I've come up with to loop through this object and write the data to the Google Sheet:

function createVisualization(data) {
  var sheet   = SpreadsheetApp.getActiveSheet();
  var rowArr  = [];
  var counter = 0;

  function looper(data) {

    var object = data.children;
    object.forEach(function(obj, index) {

      var name        = obj.name;
      var numChildren = obj.children.length;

      rowArr.push(name);

      counter = (counter - index) + 1;

      if(numChildren > 0) { // has nested child

        looper(obj);

      } else { // no child

        counter--;

        sheet.appendRow(rowArr);

        rowArr = [];

        for(var i = 0; i < counter -1; i++) {
          rowArr.push('');
        }

      }

    });

  }

  looper(data);
}

Sample data:

function getData() {
  return {
    "additionalParam": "value",
    "data": {
      "children": [
        {
          "name": "Purple",
          "children": [
            {
              "name": "Green",
              "children": [
                {
                  "name": "Pink",
                  "children": [],
                  "additionalParam": "value",
                  "additionalParam": "value",
                  "additionalParam": "value",
                  "additionalParam": "value"
                }
              ],
              "additionalParam": "value",
              "additionalParam": "value",
              "additionalParam": "value",
              "additionalParam": "value"
            },
            {
              "name": "Violet",
              "children": [
                {
                  "name": "Indigo",
                  "children": [
                    {
                      "name": "Chartreuse",
                      "children": [
                        {
                          "name": "Yellow",
                          "children": [
                            {
                              "name": "Red",
                              "children": [
                                {
                                  "name": "Blue",
                                  "children": [
                                    {
                                      "name": "Orange",
                                      "children": [
                                        {
                                          "name": "Turquoise",
                                          "children": [],
                                          "additionalParam": "value",
                                          "additionalParam": "value",
                                          "additionalParam": "value",
                                          "additionalParam": "value"
                                        }
                                      ],
                                      "additionalParam": "value",
                                      "additionalParam": "value",
                                      "additionalParam": "value",
                                      "additionalParam": "value"
                                    }
                                  ],
                                  "additionalParam": "value",
                                  "additionalParam": "value",
                                  "additionalParam": "value",
                                  "additionalParam": "value"
                                }
                              ],
                              "additionalParam": "value",
                              "additionalParam": "value",
                              "additionalParam": "value",
                              "additionalParam": "value"
                            }
                          ],
                          "additionalParam": "value",
                          "additionalParam": "value",
                          "additionalParam": "value",
                          "additionalParam": "value"
                        }
                      ],
                      "additionalParam": "value",
                      "additionalParam": "value",
                      "additionalParam": "value",
                      "additionalParam": "value"
                    }
                  ],
                  "additionalParam": "value",
                  "additionalParam": "value",
                  "additionalParam": "value",
                  "additionalParam": "value"
                }
              ],
              "additionalParam": "value",
              "additionalParam": "value",
              "additionalParam": "value",
              "additionalParam": "value"
            },
            {
              "name": "Crimson",
              "children": [
                {
                  "name": "Rose",
                  "children": [],
                  "additionalParam": "value",
                  "additionalParam": "value",
                  "additionalParam": "value",
                  "additionalParam": "value"
                }
              ],
              "additionalParam": "value",
              "additionalParam": "value",
              "additionalParam": "value",
              "additionalParam": "value"
            }
          ],
          "additionalParam": "value",
          "additionalParam": "value",
          "additionalParam": "value",
          "additionalParam": "value"
        },
        {
          "name": "Amethyst",
          "children": [
            {
              "name": "Silver",
              "children": [],
              "additionalParam": "value",
              "additionalParam": "value",
              "additionalParam": "value",
              "additionalParam": "value"
            }
          ],
          "additionalParam": "value",
          "additionalParam": "value",
          "additionalParam": "value",
          "additionalParam": "value"
        },
        {
          "name": "Gold",
          "children": [],
          "additionalParam": "value",
          "additionalParam": "value",
          "additionalParam": "value",
          "additionalParam": "value"
        }
      ],
      "additionalParam": "value",
      "additionalParam": "value",
      "additionalParam": "value",
      "additionalParam": "value"
    },
    "additionalParam": "value",
    "additionalParam": "value",
    "additionalParam": "value"
  }
}

You'll notice that I'm trying to write each row of the spreadsheet to the file (hence the Google Sheet specific syntax) but if there's a better way to do this I'm open to it.

I've also created a Google Sheet that anyone can access to see my full code and work with what I've got. To access the Script Editor go to Tools > Script Editor and you'll see my files.

This is what I'm hoping the data will look like when complete:

You can see that each child object is one column to the right of it's parent. So we can see that Purple is the parent of Green, Violet, and Crimson, Violet is the parent of Indigo, Crimson is the parent of Rose, etc.

The main issue I'm running into is how to keep track of the number of blank cells I need to add so that the data lines up in the right place within the spreadsheet. Traversing deeper into the JSON object is easy but once the function backs out a few levels I'm finding it difficult to know where in the JSON object I am. I realize this is complicated and I've tried to keep this short so ask me clarifying questions if I've missed anything.

I'm sorry for my poor English skill. Can I ask you about your question? I understand that there are Green, Violet and Crimson under Purple, because those are children of Purple. In this case, can I ask you about the meaning that there are Violet, Indigo, Chartreuse, Yellow, Red, Blue, Orange and Turquoise in one row? Although there are many children under Indigo, those are put in one row. And also I couldn't understand about "The main issue I'm running into is how to keep track of the number of blank cells I need to add so that the data lines up in the right place within the spreadsheet.". — Tanaike
– Tanaike, Commented Jun 28, 2018 at 23:06
Any cell to the right means it's a child of the previous cell (or previous cell that is higher). So yes, green, violet, and crimson are all children of purple, but indigo is a child of violet, chartreuse is a child of indigo, yellow is a child of chartreuse, and so on. If you take a look at the Google sheet I posted and look at the JSON object you'll see the structure. Regarding the blank cells, since I'm trying to output to a Google sheet I have to offset the data the proper number of cells to make it all line up. Hope this make sense. — jmp
– jmp, Commented Jun 29, 2018 at 0:32

Slai · Accepted Answer · 2018-06-29 00:55:31Z

The depth level counter has to be increased before going through the next level, and decreased after:

var data = {"children":[{"name":"Purple","children":[{"name":"Green","children":[{"name":"Pink","children":[]}]},{"name":"Violet","children":[{"name":"Indigo","children":[{"name":"Chartre","children":[{"name":"Yellow","children":[{"name":"Red","children":[{"name":"Blue","children":[{"name":"Orange","children":[{"name":"Turquoise","children":[]}]}]}]}]}]}]}]},{"name":"Crimson","children":[{"name":"Rose","children":[]}]}]},{"name":"Amethys","children":[{"name":"Silver","children":[]}]},{"name":"Gold","children":[]}]};

var rows = [];      // used just for the demo and not needed in your script
var rowArr  = [];
var counter = 0;

function looper(data) { 
  data.children.forEach(function(obj, index) { 
    while (rowArr.length < counter)
      rowArr.push('');

    rowArr.push(obj.name);

    counter++;
    looper(obj);
    counter--;

    if (rowArr.length > 0) { 
      rows.push(rowArr); // replace with sheet.appendRow(rowArr);
      rowArr = [];
    }
  });
}
looper(data); 

for (var row of rows) console.log( row.join('\t') );

Alternative is to have it as a parameter function looper(data, counter) { and pass incremented count looper(obj, counter + 1);

Collectives™ on Stack Overflow

Visualize Nested JSON Objects in Spreadsheet

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related