在电子表格中可视化嵌套的JSON对象 [英] Visualize Nested JSON Objects in Spreadsheet
问题描述
我正在尝试在电子表格(特别是Google表格)中可视化嵌套的JSON对象.我正在Google表格中使用脚本编辑器工具来编写代码，并使用电子表格来输出数据.
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.
我在此上花了大约3天的时间，无法弄清楚如何正确构造数据.它可以正常工作，但是随着代码深入JSON对象而迅速发展.我无法控制JSON对象的结构.
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.
由于它的大小，我不会在此处发布实际的JSON对象，但是可以在我的Google表格中的文件之一中查看它.这是我想出的递归函数，用于遍历此对象并将数据写入Google表格:
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);
}
样本数据:
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表格特定的语法)，但是如果有更好的方法可以使用，则可以使用它.
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.
我还创建了任何人都可以使用的Google表格访问以查看我的完整代码并使用现有功能.要访问脚本编辑器，请转到工具">脚本编辑器"，您将看到我的文件.
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:
您可以看到每个子对象在其父对象的右边一列.因此，我们可以看到Purple是Green，Violet和Crimson的父级，Violet是Indigo的父级，Crimson是Rose的父级，等等.
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.
我遇到的主要问题是如何跟踪需要添加的空白单元格的数量，以便数据在电子表格内的正确位置排列.深入到JSON对象很容易，但是一旦函数退出了几个级别，我就很难知道我在JSON对象中的位置.我意识到这很复杂，因此我尽量保持简短，因此请问我是否有任何遗漏以澄清问题.
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.
推荐答案
深度级别计数器必须在进行下一级之前增加，而在以下级别后必须减小:
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') );
替代方法是将其作为参数function looper(data, counter) {并传递递增的计数looper(obj, counter + 1);
Alternative is to have it as a parameter function looper(data, counter) { and pass incremented count looper(obj, counter + 1);
这篇关于在电子表格中可视化嵌套的JSON对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
