CODEWITHSUNDEEP
javascriptjsongoogle-apps-script
Robert William Millard
Robert William Millard

Reputation: 11

JSON.parse() with nested JSON

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?

Upvotes: 1

Views: 1131

Answers (2)

Barmar
Barmar

Reputation: 781098

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

Upvotes: 1

th3n3wguy
th3n3wguy

Reputation: 3747

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

Upvotes: 0

Related Questions