json - 如何将 JSON 响应转换为工作表中的表格

Question

我正在向 API 发送请求(在 Google 脚本中)，我收到的响应是 JSON 文本，如下所示:

[{"id":26319355,"name":"1. WAW -FIRST SESION","calendar":"Glovers
Click&Collect","duration":30,"isSeries":false,"slots":90,"slotsAvailable"
:89,"color":"#E3DE7D","price":"0.00","category":"WAW","description":"",
"calendarID":2978881,"serviceGroupID":2978881,"appointmentTypeID":10104780,
"calendarTimezone":"Europe\/Madrid","time":"2019-06-01T12:00:00+0200",
"localeTime":"June 1, 2019 12:00"},

{"id":26466803,"name":"1. WAW -FIRST SESION","calendar":"Glovers
Click&Collect","duration":30,"isSeries":false,"slots":90,"slotsAvailable"
:89,"color":"#E3DE7D","price":"0.00","category":"WAW","description":"",
"calendarID":2978881,"serviceGroupID":2978881,"appointmentTypeID":10104780,
"calendarTimezone":"Europe\/Madrid","time":"2019-06-07T14:00:00+0200",
"localeTime":"June 7, 2019 14:00"},

我想将此回复作为表格粘贴到我的电子表格中。

我的脚本实际上是这样的(其中 response 是我从 API 请求中获得的响应):

function CheckAv(row,acuityid,check,url,apiusername,apisecretkey,ss) {
 
 var header = {
        "contentType": "application/json",
        "headers":{"Authorization" : " Basic " + Utilities.base64Encode(apiusername + ":" + apisecretkey)},
        "method" : "GET"
      }
   
  muteHttpExceptions: true 
  
  var response = UrlFetchApp.fetch(url, header);
  var data = JSON.parse(response.getContentText());
  var text = response.getResponseCode();
   
  Logger.log(text);
   
}

我想这会很简单，但我找不到解决方案。

Answer 1

您可以循环遍历 JSON 结构，并使用以下代码将每个键和值推送到指定的行。

  json = [{your: "JSON", data: "goes"}, {in : "here"}]

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var sheet = ss.getActiveSheet();

  var rows = [],
      data;

    for (i = 0; i < json.length; i++) {
        for (j in json[i]) {   

          dataq = json[i][j];

          Logger.log(dataq);

          rows.push([j, dataq]);
    }
      dataRange = sheet.getRange(1, 1, rows.length, 2);
      dataRange.setValues(rows);    
  }