javascript - 在电子表格中可视化嵌套的 JSON 对象

Question

我试图在电子表格中可视化一个嵌套的 JSON 对象——特别是 Google 表格。我在我的 Google 表格中使用脚本编辑器工具来编写我的代码和电子表格来输出数据。

我在这上面花了整整 3 天时间，但无法弄清楚如何正确构建数据。它开始时工作正常，但随着代码进一步遍历到 JSON 对象中而迅速退化。我无法控制 JSON 对象的结构。

由于它的大小，我不会在此处发布实际的 JSON 对象，但可以在我的 Google 表格中的一个文件中查看它。这是我想出的递归函数，用于循环遍历此对象并将数据写入 Google 表格:

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);
}

示例数据:

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"
  }
}

您会注意到我正在尝试将电子表格的每一行写入文件(因此使用 Google 表格特定语法)，但如果有更好的方法来执行此操作，我愿意接受。

我也created a Google Sheet that anyone can access查看我的完整代码并使用我已有的代码。要访问脚本编辑器，请转到“工具”>“脚本编辑器”，您会看到我的文件。

这是我希望数据完成后的样子:

您可以看到每个子对象都位于其父对象右侧的一列。所以我们可以看到 Purple 是 Green、Violet 和 Crimson 的父级，Violet 是 Indigo 的父级，Crimson 是 Rose 的父级，等等。

我遇到的主要问题是如何跟踪我需要添加的空白单元格的数量，以便数据在电子表格中的正确位置排列。更深入地遍历 JSON 对象很容易，但是一旦函数退出几个级别，我发现很难知道我在 JSON 对象中的位置。我意识到这很复杂，我尽量保持简短，所以如果我遗漏了什么，请问我澄清问题。

Answer 1

深度级别计数器必须在进入下一个级别之前增加，并在以下之后减少:

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') ); 

替代方法是将其作为参数 function looper(data, counter) { 并传递递增计数 looper(obj, counter + 1);