How can i get data from a JSON array in google app script?

Question

I'm doing a request to an API that is successful but i need to get the data of the array returned, below i will put how the array looks like so you can help me to extract the data

{ total_grand: 30600000,
  total_billable: null,
  total_currencies: [ { currency: null, amount: null } ],
  total_count: 5,
  per_page: 50,
  data: 
   [ { id: 13998122,
       pid: 1570982183,
       tid: null,
       uid: 5386231,
       description: 'Finish the first part of the RCP mockup',
       start: '2020-03-26T13:00:00-04:00',
       end: '2020-03-26T16:00:00-04:00',
       updated: '2020-04-02T13:25:15-04:00',
       dur: 10800000,
       user: 'Jose',
       use_stop: true,
       client: 'PLA',
       project: 'Training',
       project_color: '0',
       project_hex_color: '#3750b5',
       task: null,
       billable: null,
       is_billable: false,
       cur: null,
       tags: [] 
   } ]
}

I want to access to the user,project,tags,client,start,end and description so i can put it in my SpreadSheet. How can i do that?

This is how i do the request and how i try to access to the data in the array in my variable togglData

for (var i = 0; i < projects.length; i++) {
    var listProjects = projects[i];
    var reportURL = baseURL + '/reports/api/v2/details' + params;
    var reportFetch = UrlFetchApp.fetch(reportURL, options);
    var togglReport = JSON.parse(reportFetch.getContentText());
    var togglData = togglReport["data"]["user"];
    Logger.log(togglReport);
  }

1.Provide console.log(togglReport) or console.log(JSON.stringify(togglReport)) instead. Logger doesn't provide accurate logs(= is not valid json; strings should be quoted). 2. Make sure your json is valid. You can hide sensitive details by changing them instead of adding .... 3. Is there anything you've tried to mould the data to a 2D array, the format required by Google sheets' setValues? — TheMaster
– TheMaster, Commented Apr 11, 2020 at 16:30
What makes you think that? console.log has been supported in apps script for years now. — TheMaster
– TheMaster, Commented Apr 11, 2020 at 16:51
Because the console.log never printed anything when i put in my scripts so i don't know if i need to do something else to make it work, that's why when i'm programming in GAS i avoid the console.log in my scripts and use Logger.log instead @TheMaster — Rinzler21
– Rinzler21, Commented Apr 11, 2020 at 16:54
@TheMaster i only have one month programming in GAS i'm very new at this ,can you see my updated question? — Rinzler21
– Rinzler21, Commented Apr 11, 2020 at 16:58

TheMaster · Accepted Answer · 2020-04-11 17:28:46Z

Range.setValues() is used to the set data as a two dimensional array to the sheet. Using destructuring assignment and for...of loop, It's possible to mould the data to a 2D array.

const togglReport = {
  total_grand: 30600000,
  total_billable: null,
  total_currencies: [{ currency: null, amount: null }],
  total_count: 5,
  per_page: 50,
  data: [
    {
      id: 13998122,
      pid: 1570982183,
      tid: null,
      uid: 5386231,
      description: 'Finish the first part of the RCP mockup',
      start: '2020-03-26T13:00:00-04:00',
      end: '2020-03-26T16:00:00-04:00',
      updated: '2020-04-02T13:25:15-04:00',
      dur: 10800000,
      user: 'Jose',
      use_stop: true,
      client: 'PLA',
      project: 'Training',
      project_color: '0',
      project_hex_color: '#3750b5',
      task: null,
      billable: null,
      is_billable: false,
      cur: null,
      tags: [],
    },
  ],
};
const out = [];
for (const {
  user,
  project,
  tags,
  client,
  start,
  end,
  description,
} of togglReport.data) {
  //We're looping over togglReport.data and not togglReport
  out.push([user, project, tags.join(), client, start, end, description]);
}
console.log(out);
//SpreadsheetApp.getActive().getSheets[0].getRange(1,1, out.length, out[0].length).setValues(out);

Collectives™ on Stack Overflow

How can i get data from a JSON array in google app script?

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related