Sorry for the probably simple question. My team set up an API on our server for us to get on google sheet.

After using Postman I can see my data comes properly like this

  {
    "msg": "success",
    "code": 0,
    "data": [
        {
            "Domain": "test-shop",
            "packageId": "XXX12345",
            "orderNumber": "123456-7890",
            "sku": null,
            "quantity": 1,
            "country": "DENMARK",
            "customerName": "James Andersen",
            "shippingMethod": "UPS",
            "trackingNumber": "UP2100421266111769",
            "orderDate": "2021-01-03 21:44:36",
            "cost": 9.32,
            "euCustomsFee": 0,
            "totalCost": 9.32
        }
    ]
}

I need to get this into a google sheet where each object is a column. Over time the arrays will have more than one response.

I have been tryng with this script but get an error that setValues([number]) doesnt match the required signature foor setValues.

Currently I am generating the HTTP request on a link on the spreadsheet to try to limit the calculations ons the script since I am expecting this function to get to the 5 min limit.

function importcogs() {
  Logger.log("import begin");
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var urlsheet = ss.getSheetByName("GetInfo");
  var request = urlsheet.getRange(5,2).getValue();
  Logger.log(request);

  var response = UrlFetchApp.fetch(request);
  Logger.log("download data finish");
  Logger.log(response.getContentText());

  
  var sheet = ss.getSheetByName("Data");
  var jsonData = JSON.parse(response);
  Logger.log("Split Data");
  Logger.log(jsonData);
  Logger.log(jsonData.data);
  
  sheet.getRange(1,1,1,13).setValues(jsonData.data);


}  

CodePudding user response：

Response to Spreadsheet

function importcogs() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  let response = '{"msg":"success","code":0,"data":[{"Domain":"test-shop","packageId":"XXX12345","orderNumber":"123456-7890","sku":null,"quantity":1,"country":"DENMARK","customerName":"JamesAndersen","shippingMethod":"UPS","trackingNumber":"UP2100421266111769","orderDate":"2021-01-0321:44:36","cost":9.32,"euCustomsFee":0,"totalCost":9.32}]}';
  let obj = JSON.parse(response);//create object
  let vs = obj.data.map(o => Object.values(o));//data
  vs.unshift(Object.keys(obj.data[0]));//add header
  sh.getRange(1,1,vs.length, vs[0].length).setValues(vs);//output to spreadsheet
}

Spreadsheet Output

Object.keys()

Object.values()