API requests Json array to data spreadsheet on google scripts

Question

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


}

Cooper · Accepted Answer · 2022-01-06 19:50:40Z

1

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

Domain	packageId	orderNumber	sku	quantity	country	customerName	shippingMethod	trackingNumber	orderDate	cost	euCustomsFee	totalCost
test-shop	XXX12345	123456-7890		1	DENMARK	JamesAndersen	UPS	UP2100421266111769	2021-01-0321:44:36	9.32	0	9.32

Object.keys()

Object.values()

edited Jan 6, 2022 at 19:50

answered Jan 6, 2022 at 19:44

Cooper

64.2k7 gold badges28 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Juan Over a year ago

Would it be possible to have let response be generated rather than hard coding it?

Cooper Over a year ago

Sure. I was just working with what you gave me

Collectives™ on Stack Overflow

API requests Json array to data spreadsheet on google scripts

1 Answer 1

Response to Spreadsheet

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Response to Spreadsheet

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related