CODEWITHSUNDEEP
arraysjsongoogle-apps-scriptgoogle-sheets
Kal El
Kal El

Reputation: 73

How to add multiple arrays from JSON into Google Sheets using Google Sheets app script

How can I add the following fields:

  1. weightTierInformation>name
  2. weightTierInformation>gramAmount

To this code:

function test(e) {

var ss = SpreadsheetApp.getActiveSpreadsheet();
var targetsheet = ss.getSheetByName("test");

var options = {
     //"async": true,
     //"crossDomain": true,
     "method" : "GET",
     "headers" : {
       "clientId" : "1",
       "key": "1",
       "Prefer": "code=200",
       "Prefer":"dynamic=true"
       
       }}




  var text = UrlFetchApp.fetch("https://stoplight.io/mocks/flowhub/public-developer-portal/24055485/v0/locations/1/inventory",options).getContentText();
  var json = JSON.parse(text);




var values = json.data.flatMap(({ productId,cannabinoidInformation }) =>  cannabinoidInformation.map(({lowerRange,name}) => [productId,lowerRange,name])
);



targetsheet.getRange(2, 1,values.length, values[0].length).setValues(values);

}

At the moment the output populate A-C columns, I want to populate D-E with the subfields above

Upvotes: 0

Views: 53

Answers (1)

Tanaike
Tanaike

Reputation: 201683

In this case, how about the following modification?

From:

var values = json.data.flatMap(({ productId,cannabinoidInformation }) =>  cannabinoidInformation.map(({lowerRange,name}) => [productId,lowerRange,name])

);

To:

var values = json.data.flatMap(({ productId, cannabinoidInformation, weightTierInformation }) => cannabinoidInformation.map(({ lowerRange, name }, i) => [productId, lowerRange, name, weightTierInformation[i].name, weightTierInformation[i].gramAmount]));
  • By this, modification, the values of productId, cannabinoidInformation, weightTierInformation are put to the column "A", the columns "B" to "C", the columns "D" to "E".

Upvotes: 2

Related Questions