将关联数组转换为数字以在Apps脚本中写入工作表

博观而约取,厚积而薄发。这篇文章主要讲述将关联数组转换为数字以在Apps脚本中写入工作表相关的知识,希望能为你提供帮助。
我正在尝试将JSON中提供的目录写入Google电子表格。
我能够获得JSON并编写字段但我仍然坚持将员工数据写入工作表。 JSON提供了一个关联数组。从我的理解使用Range.setValues()你需要有一个数值阵列。
【将关联数组转换为数字以在Apps脚本中写入工作表】

将关联数组转换为数字以在Apps脚本中写入工作表

文章图片

我可以将字段标题写入Google表格的第一行。
我用来编写员工数据的代码是:
function writeJSONtoSheet(json, sheetId) {var sheet = SpreadsheetApp.openById(sheetId).getSheets()[0]; var header = sheet.getRange(1, 1, 1, json['fields'].length+1); //A1 to O1 var headerVals = header.getValues(); var newColsTitles = ["ID #"]; var newColsIndex = ["id"]; for (var i = 0; i < json['fields'].length; i++){ newColsTitles.push(json['fields'][i]['name']); newColsIndex.push(json['fields'][i]['id']) }header.setValues([newColsTitles]); var employeeRows = sheet.getRange(2,1,json['employees'].length,json['fields'].length+1); var employees = []; var empArr = []; var emps = []; for (var e = 0; e < json['employees'].length; e++) { employees.push(json['employees'][e]); } for (var y = 0; y < employees.length; y++){ for (var x = 0; x < newColsIndex.length; x++){ empArr[y] = []; empArr[y][x] = employees[y][newColsIndex[x]]; console.log(employees[y][newColsIndex[x]]) } } employeeRows.setValues(empArr); }

当我在循环中logemployees[y][newColsIndex[x]]时,它正确地在每个员工字段上循环。
JSON格式如下:
{ "fields": [ { "id": "displayName", "type": "text", "name": "Display Name" }, { "id": "firstName", "type": "text", "name": "First Name" }, { "id": "lastName", "type": "text", "name": "Last Name" }, { "id": "gender", "type": "text", "name": "Gender" }, { "id": "jobTitle", "type": "list", "name": "Job Title" }, { "id": "workPhone", "type": "text", "name": "Work Phone" }, { "id": "workPhoneExtension", "type": "text", "name": "Work Extension" }, { "id": "skypeUsername", "type": "text", "name": "Skype Username" }, { "id": "facebook", "type": "text", "name": "Facebook URL" } ], "employees": [ { "id":123, "displayName":"John Doe", "firstName":"John", "lastName":"Doe", "gender":"Male", "jobTitle":"Customer Service Representative", "workPhone":"555-555-5555", "workPhoneExtension":null, "skypeUsername":"JohnDoe", "facebook":"JohnDoeFacebook" } ]

}
答案更改
for (var y = 0; y < employees.length; y++){ for (var x = 0; x < newColsIndex.length; x++){ empArr[y] = []; empArr[y][x] = employees[y][newColsIndex[x]]; console.log(employees[y][newColsIndex[x]]) }

到(见内联评论)
for (var y = 0; y < employees.length; y++){ empArr.push([]); // inserts a new row for each employee for (var x = 0; x < newColsIndex.length; x++){ //empArr[y] = []; // this was reseting the array on each pass empArr[y][x] = employees[y][newColsIndex[x]]; console.log(employees[y][newColsIndex[x]]) }


    推荐阅读