javascript - 将 JSON 填充并格式化为 Google 表格
问题描述
提前感谢任何能够帮助我的人。我已经尝试了好几天来完成这项工作,只是对此知之甚少,无法通过示例来解决这个问题。
我开发了一个 Android 应用程序,可以在实时数据库中下订单。每个订单都有自己的 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 列中。之后,使用此代码时有两个问题:
标题被覆盖并且它们的顺序被改变(字母顺序) - 这不是一个交易破坏者,如果需要可以重新排序,但理想情况下,当将值放在单元格中时,它会查看列标题(与值相同JSON 中的标头)并在单元格中放置正确的值。预期: 电子表格应该是什么样子 实际: 运行代码时重新排序
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
}
}
再次,对此的任何方向将不胜感激!
解决方案
我相信你的目标如下。
- 您想从中检索值
json
并将解析后的值按"id", "orderId", "orderName", "orderAddress", "orderPhone", "orderEmail", "orderLatLong", "orderDent", "orderLend", "orderTrain", "orderDish", "orderSans", "orderTint", "orderText", "orderSign", "orderTree", "orderTotal", "orderTotalCost", "orderPaid", "orderDelivered"
. - 您想使用 Google Apps 脚本来实现这一点。
修改点:
- 在 JSON 对象中,不保证键的顺序。所以在你的情况下,我认为给出有序的标题值是合适的。
- 通过将标头值声明为数组,我想提出以下流程。
- 声明标题值。
- 创建一个用于放入电子表格的数组。
- 将创建的数组放入电子表格。
当以上几点反映到您的脚本中时,它变成如下。
修改后的脚本:
请按如下方式修改您的脚本。
从: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 运行时。
参考:
推荐阅读
- android - android.preference.Preference.setOnPreferenceClickListener() 不起作用
- html - 未捕获的 TypeError:Bootstrap 的 JavaScript 需要 jQuery。jQuery 必须在 Bootstrap 的 JavaScript 之前包含
- python - TypeError: agg() 得到了一个意外的关键字参数
- c# - 单元测试睡眠持续时间 polly 等待重试策略
- android - 我从谷歌开发者仪表板得到这个崩溃,但我不明白是什么原因造成的
- r - 如何计算数组的一个维度的总和并将其重新分配到其他维度?
- python-3.x - 输出预测图像 Tensorflow Lite
- swiftui - @State 变量以突出显示水平 ScrollView 菜单中的项目
- java - Selenium Webdriver 在 Web 元素中查找 Web 元素
- google-docs-api - 是否可以使用 Google Docs API 触发“设置我的默认样式”?