Hi All,
I’m working on integrating Pipedrive with Google Sheets and other google apps, but I’m having trouble with the JSON.parse function and its outputs. The short story is I can see a lot of the basic endpoints from Pipedrive and am able to specify which of those objects I want to copy over to my Google Sheet, but some of them I can’t seem to access. I don’t know if Google just can’t access them or if I’m doing it wrong.
The API Reference page at Pipedrive API v1 Reference shows me some additional endpoints like “stay_in_pipeline_stages” and nested under that, “times_in_stages,” but Google scripts gives me an error saying those aren’t valid properties.
Here’s the key part of my code to this point:
var url = “https://[company].pipedrive.com/v1/deals?&start=1532&limit=10&api_token=XXXXXXXXXXXXXX”;
var response = UrlFetchApp.fetch(url);
var dataSet = JSON.parse(response.getContentText());
var data;
for (var i = 0; i < dataSet.data.length; i++) {
data = dataSet.data[i];
sheet.appendRow([data.title, data.org_name, data.owner_name, data.add_time, data.stage_change_time, data.update_time, data.formatted_value, data.activities_count,
data.done_activities_count, data.person_name, data.stage_id]);
}
}
I have tried using appendRow and just writing [data], to get all the data, regardless of the objects like those specified above, and I get a number of pieces of data, but I can’t seem to get all the JSON endpoints, again like “stay_in_pipeline_stages” and others that the API Reference page shows as endpoint objects with valid data.
Example copied from the ‘Get details of a deal’ ‘run endpoint’ tool:
},
“stay_in_pipeline_stages”: {
“times_in_stages”: {
“7”: 0,
“11”: 200861,
“12”: 0,
“13”: 3010983,
“15”: 0,
“17”: 31366410,
“21”: 2416024,
“22”: 0
},
“order_of_stages”: [
12,
7,
15,
13,
11,
22,
21,
17
]
},
“last_activity”: {
“id”: 19230,
“company_id”: 43449,
“user_id”: 1200050,
“done”: true,
“type”: “email”,
“reference_type”: null,
“reference_id”: null,
“due_date”: “2019-10-23”,
“due_time”: “”,
“duration”: “”,
“busy_flag”: false,
Specifying “data.stay_in_pipeline_stages” results in a blank output for the specified output range, and “data.stay_in_pipeline_stages.times_in_stages” results in “TypeError: Cannot read property ‘times_in_stages’ of undefined.”
Can anyone see what is missing, or suggest a change to the JSON.parse method? I would be fine with running two different scripts, one for the basic data, and one for the more nested objects!