How to transform the JSON response into a table in sheets

user_11708394 0
Viewed 1717

I'm sending a request to an API (in a Google Scripts), and I'm getting the response as a JSON text that looks like this: 

[{"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"},

I want to paste this response as a table in my spreadsheet.

My script actually looks like this (where response is the response I get from the API request):

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);
   
}

I assume it will be really easy but I can't find the solution.

google-apps-script google-sheets json.net json
1 Answers

You can cycle through your JSON structure and push each key and value to a specified row using the code below.

  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);    
  }
user_115514680
Related