JSON.parse() with nested JSON

Question

I have a google-script that calls a CRM system, parses the JSON and appends it to the sheet. It works for some endpoints and not others. When it works, the values in the g-sheet, when it doesn't it just doesn't find any values. Code:

function thingy() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var sheet = ss.getActiveSheet();

  var url    = "https://api.pipedrive.com/v1/mailbox/mailMessages/17685?include_body=1";
  var token  = "&api_token=token"

  var response = UrlFetchApp.fetch(url+token); 
  var dataSet = JSON.parse(response.getContentText()); 
  var data;

  for (var i = 0; i < dataSet.data.length; i++) {
    data = dataSet.data[i];
    sheet.appendRow([data.user_id]); 
  }
}

Endpoint JSON that works, i can get title by using sheet.appendRow([data.title] :

{
"success": true,
"data": [
    {
        "stage_id": 6,
        "title": "Fakel",
        "value": 210,
        "currency": "EUR",
        "add_time": "2014-02-25 09:09:01"
    }

Endpoint JSON that does notworks, I can't get body with sheet.appendRow([data.body] :

{
"data": {
    "id": 17685,
    "from": [
        {
            "id": 1411,
        }
    ],
    "to": [
        {
            "id": 1739,
        }
    ],
    "cc": [
        {
            "id": 199,
     }
    ],
    "bcc": [],
    "body": "blahblahblah"

I would also like to get the child entities as well. So I want to be able to get the ID in From, TO, CC. I'm guessing there is something different in the structure of the JSON that's preventing me to do it?

Barmar · Accepted Answer · 2018-02-23 21:10:37Z

1

In the first example, dataSet.data is an array, so you can loop over the elements and access each element as dataSet.data[i] in the loop.

In the second example, dataSet.data is an object, not an array, so there are no array elements to loop over. The information you want is in dataSet.data.body.

sheet.appendRow([dataSet.data.body])

edited Feb 23, 2018 at 21:10

answered Feb 23, 2018 at 21:02

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

2 Comments

Robert William Millard Over a year ago

Thank you, so it should be: for (var i = 0; i < dataSet.data.body.length; i++) { data = dataSet.data.body[i]; sheet.appendRow([data.body]); } } ?

Barmar Over a year ago

dataSet.data.body is a string, not an array. Why would you loop over it?

th3n3wguy · Accepted Answer · 2018-02-23 20:59:32Z

function thingy() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var sheet = ss.getActiveSheet();

  var url    = "https://api.pipedrive.com/v1/mailbox/mailMessages/17685?include_body=1";
  var token  = "&api_token=token"

  var response = UrlFetchApp.fetch(url+token); 
  var dataSet = JSON.parse(response.getContentText()); 
  var data;

  // Right here, you are looping over the .data property, which doesn't exist and you are thinking that you can access it with the data.body property. The problem being that the second response .data property is an OBJECT and not an ARRAY (like it is in your first response example).
  for (var i = 0; i < dataSet.data.length; i++) {
    data = dataSet.data[i];
    sheet.appendRow([data.user_id]); 
  }
}

Collectives™ on Stack Overflow

JSON.parse() with nested JSON

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related