title: Visualize Nested JSON Objects in Spreadsheet
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>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:</p>
  
  <pre><code>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 &gt; 0) { // has nested child
  
          looper(obj);
  
        } else { // no child
  
          counter--;
  
          sheet.appendRow(rowArr);
  
          rowArr = [];
  
          for(var i = 0; i &lt; counter -1; i++) {
            rowArr.push('');
          }
  
        }
  
      });
  
    }
  
    looper(data);
  }
  </code></pre>
  
  <p>Sample data: 
  <div class="snippet" data-lang="js" data-hide="true" data-console="true" data-babel="false">
  <div class="snippet-code snippet-currently-hidden">
  <pre class="snippet-code-js lang-js prettyprint-override"><code>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"
    }
  }</code></pre>
  </div>
  </div>
  </p>
  
  <p>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.</p>
  
  <p>I've also <a href="https://docs.google.com/spreadsheets/d/1iizjUXEBMV0QqZP0GFp0jWKG-SYQb8U2ELfoI_jtM6U/edit?usp=sharing" rel="nofollow noreferrer">created a Google Sheet that anyone can access</a> 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.</p>
  
  <p>This is what I'm hoping the data will look like when complete:</p>
  
  <p><a href="https://i.stack.imgur.com/vMntl.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/vMntl.png" alt="Sample Table"></a></p>
  
  <p>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.</p>
  
  <p>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.</p>