将JSON填充并格式化为Google工作表

Question

提前感谢任何能够帮助我的人。几天来，我一直在努力让它发挥作用，只是对它的了解不够，无法通过例子来弄清楚。

我开发了一款安卓应用程序，可以在实时数据库中下单。每个订单都有自己的ID，由应用程序生成。当我运行代码并根据列标题进入正确的列时，我希望每个订单都显示在一行中。我发现这个例子似乎完全符合我的需求(相同的JSON结构(，但当我尝试重新调整用途时，它不起作用。这是我所拥有的：

function chunkArray(myArray, chunk_size){
var index = 0;
var arrayLength = myArray.length;
var tempArray = [];

for (index = 0; index < arrayLength; index += chunk_size) {
myChunk = myArray.slice(index, index+chunk_size);
// Do something if you want with the group
tempArray.push(myChunk);
}
return tempArray;
}
function flatten(arrayOfArrays){
return [].concat.apply([], arrayOfArrays);
}

function insertJSON(){

// this puts the keys in the correct place
var aOneName = "id";
var ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName("orders");
var fileURL = "<-RT DB Link ->";
var res = UrlFetchApp.fetch(fileURL,{'muteHttpExceptions': true});
var content = res.getContentText();
var json = JSON.parse(content);
var rows = [Object.keys(json)]; // Retrieve entry column.
var toRows = rows[0].length;
var toRows = toRows + 1;
var rowsflate = flatten(rows);
var rowstocols  = chunkArray(rowsflate, 1);
sheet.getRange("A2:A" + toRows).setValues(rowstocols); // sets column of IDs
sheet.getRange(1, 1).setValue(aOneName); // A1 name
var headers = json[rows[0][2]];
var getHeaders = [Object.keys(headers)];
sheet.getRange(1,2,getHeaders.length,getHeaders[0].length).setValues(getHeaders); // Put values to Spreadsheet.
content
var content = [];
var temp = [];
for (var i = 0; i < rows[0].length; i++) {
var temp2 = [Object.values(json[rows[0][i]])];// Retrieve values to be split.
Logger.log(temp2);
content.push(flatten(temp2));
}
var columncount1= rows[0].length;
var columncount2 = getHeaders[0].length;
sheet.getRange(2,2,columncount1,columncount2).setValues(content); // Put values to Spreadsheet.
}

该代码用于提取JSON数据，并将每个订单的键正确地放在从A2开始的列A中。然而，在那之后，使用此代码时有两个问题：

1. 标题被覆盖，它们的顺序被更改(按字母顺序排列(-这不是一个破坏交易的因素，如果需要，可以围绕它重新排序，但理想情况下，当将值放置在单元格中时，它会查看列标题(与JSON中的值标题相同(，并在单元格中放置正确的值。预期：电子表格应该是什么样子实际：代码运行时重新排序

2. Object.values抛出错误：TypeError: Cannot find function values in object function Object() { [native code for Object.Object, arity=1] }. (line 152, file "Code").我已将其替换为.keys，并尝试使用.map，但随后在sheet.getRange(2,2,columncount1,columncount2).setValues(content);:"The number of columns in the data does not match the number of columns in the range. The data has 1 but the range has 19. (line 161, file "Code")"行中收到错误消息

这是JSON

{
"12":{
"orderAddress":"address 1",
"orderDelivered":"No",
"orderDent":2,
"orderDish":0,
"orderEmail":"Not Provided",
"orderId":"12",
"orderLatLong":"Not Captured",
"orderLend":1,
"orderName":"Name 1",
"orderPaid":"No",
"orderPhone":"Phone 1",
"orderSans":0,
"orderSign":8,
"orderText":6,
"orderTint":0,
"orderTotal":17,
"orderTotalCost":200,
"orderTrain":0,
"orderTree":0
},
"13":{
"orderAddress":"address 2",
"orderDelivered":"No",
"orderDent":0,
"orderDish":1,
"orderEmail":"Not Provided",
"orderId":"13",
"orderLatLong":"Not Captured",
"orderLend":1,
"orderName":"Name 2",
"orderPaid":"Yes",
"orderPhone":"Phone 2",
"orderSans":0,
"orderSign":0,
"orderText":0,
"orderTint":0,
"orderTotal":27,
"orderTotalCost":40,
"orderTrain":0,
"orderTree":0
},
"-MMIu9hQrxlZNs91nl2M":{
"orderAddress":"address 3",
"orderDelivered":"Yes",
"orderDent":0,
"orderDish":0,
"orderEmail":"Not Provided",
"orderId":"-MMIu9hQrxlZNs91nl2M",
"orderLatLong":"Not Captured",
"orderLend":1,
"orderName":"Name 3",
"orderPaid":"Yes",
"orderPhone":"Phone 3",
"orderSans":0,
"orderSign":0,
"orderText":0,
"orderTint":0,
"orderTotal":1,
"orderTotalCost":12,
"orderTrain":0,
"orderTree":0
}
}

再次强调，如有任何指示，我们将不胜感激！

Answer 1

我相信你的目标如下。

• 您希望从json中检索值，并将解析后的值按"id", "orderId", "orderName", "orderAddress", "orderPhone", "orderEmail", "orderLatLong", "orderDent", "orderLend", "orderTrain", "orderDish", "orderSans", "orderTint", "orderText", "orderSign", "orderTree", "orderTotal", "orderTotalCost", "orderPaid", "orderDelivered"的标头顺序放置
• 你想使用谷歌应用程序脚本来实现这一点

修改点：

• 在JSON对象中，密钥的顺序不受保证。因此，在您的情况下，我认为给出有序标头值是合适的
• 通过将头值声明为数组，我想提出以下流程。
  1. 声明标头值
  2. 创建一个用于放入电子表格的数组
  3. 将创建的数组放入电子表格

当上面的点反映到脚本中时，它变成如下。

修改的脚本：

请按如下方式修改您的脚本。

发件人：

var rows = [Object.keys(json)]; // Retrieve entry column.
var toRows = rows[0].length;
var toRows = toRows + 1;
var rowsflate = flatten(rows);
var rowstocols  = chunkArray(rowsflate, 1);
sheet.getRange("A2:A" + toRows).setValues(rowstocols); // sets column of IDs
sheet.getRange(1, 1).setValue(aOneName); // A1 name
var headers = json[rows[0][2]];
var getHeaders = [Object.keys(headers)];
sheet.getRange(1,2,getHeaders.length,getHeaders[0].length).setValues(getHeaders); // Put values to Spreadsheet.
content
var content = [];
var temp = [];
for (var i = 0; i < rows[0].length; i++) {
var temp2 = [Object.values(json[rows[0][i]])];// Retrieve values to be split.
Logger.log(temp2);
content.push(flatten(temp2));
}
var columncount1= rows[0].length;
var columncount2 = getHeaders[0].length;
sheet.getRange(2,2,columncount1,columncount2).setValues(content); // Put values to Spreadsheet.

收件人：

// 1. Declare the header values.
const header = ["id", "orderId", "orderName", "orderAddress", "orderPhone", "orderEmail", "orderLatLong", "orderDent", "orderLend", "orderTrain", "orderDish", "orderSans", "orderTint", "orderText", "orderSign", "orderTree", "orderTotal", "orderTotalCost", "orderPaid", "orderDelivered"];
// 2. Create an array for putting to Spreadsheet.
const values = Object.entries(json).map(([k, v]) => {
v.id = k;
return header.map(h => v[h]);
});
values.unshift(header);  // When you want to add the header, please use this.
// 3. Put the created array to Spreadsheet.
sheet.getRange(1, 1, values.length, values[0].length).setValues(values);

注：

• 使用此脚本时，请启用V8运行时

参考：

• map((