Search API Connector Documentation
Import Pipedrive Data to Google Sheets
In this guide, we’ll walk through how to pull data from the Pipedrive API directly into Google Sheets, using the API Connector add-on for Sheets.
There are 2 ways to connect to the Pipedrive API:
- Preset “Connect” button (OAuth) premium
- Personal API token. Please check the appendix for detailed instructions to retrieve your token.
Contents
- Before You Begin
- Part 1: Connect to the Pipedrive API
- Part 2: Create your API Request URL
- Part 3: Pull Pipedrive API Data into Sheets
- Part 4: More Example API URLs
- Part 5: Handle Filtering
- Part 6: Handle Pagination
- Part 7: API Documentation
- Appendix: Connect with a Pipedrive API Token
Before You Begin
Click here to install the API Connector add-on from the Google Marketplace.
Part 1: Connect to the Pipedrive API
If you haven’t connected to the Pipedrive API before, you’ll first need to initiate the connection.
- Open up Google Sheets and click Extensions > API Connector > Manage Connections.
- In the list of available connections, find Pipedrive and click Connect.
- You will see ascreen asking you to log in and approve the connection. Click Allow and Install.
- You’ll then be returned to your Google Sheet, and can verify that your Pipedrive connection is active in the Connections screen.
Part 2: Create Your Pipedrive API Request URL
For our first request, we’ll get a list of deals.
- API root:
https://your_domain.pipedrive.com
- Endpoint:
/api/v1/deals
- Fields:
:(id,title,active,add_time,update_time,stage_id,currency,value,status,pipeline_id)
Putting it together, we get the full API Request URL:
https://company.pipedrive.com/api/v1/deals:(id,title,active,add_time,update_time,stage_id,currency,value,status,pipeline_id)
Note: the Fields piece is optional, but we’re including it because Pipedrive sends back a massive data set otherwise. If you’d like to leave it out, I suggest adding &limit=2
to your first request, so you can add filters before retrieving data for your full set of records.
Part 3: Pull Pipedrive API Data into Sheets
We can now enter that URL into API Connector.
- Open up Google Sheets and click Extensions > API Connector > Open.
- In the Create screen, enter the Request URL we just created
- Under Authentication, choose Pipedrive from the dropdown menu. You should see a “Connected” badge.
- Under Headers, enter Key = Accept, Value = application/json
- Create a new tab and click Set current to use that tab as your data destination.
- Under Output options, find the JMESPath section and enter the word
data
. This limits the response to just the fields listed above. (Without this setting, Pipedrive sends back some additional, deeply nested data that may cause your request to time out). - Click Run to see data in your sheet:
Part 4: More Example API URLs
Experiment with endpoints and query strings as described in the documentation to see other types of Pipedrive data. For example, this URL will return a list of organizations:
https://your_domain.pipedrive.com/v1/organizations
Part 5: Handle Filtering
By default, some of the Pipedrive API endpoints return a very large number of rows and columns. This can be hard to manage and may cause your requests to time out. To address this you can use any or all of the following filter types:
- Pipedrive field selector: Pipedrive’s documentation shows how to use the field selector to list fields you wish to see in your request.
https://company.pipedrive.com/api/v1/deals:(id,title,value,currency)
- Pipedrive query parameters: For each endpoint, Pipedrive’s documentation lists filters you can apply as query parameters, e.g.
https://company.pipedrive.com/api/v1/deals:(id,title,value,currency)?status=open
- Field editor: API Connector’s visual field editor lets you simply click to select or filter the columns you want to see in your report.
- JMESPath: Use JMESPath filters to precisely filter for specific data elements or conditions.
All filter types can be used together without any issue. Pipedrive’s parameters will speed up the response from the server, while API Connector’s field editor and JMESPath options will speed up the process of printing data into the sheet.
Part 6: Handle Pagination
Many Pipedrive endpoints will return a limited set of data per page. To retrieve additional data, you’ll need to follow the pagination links in the response, as described here. With API Connector, you can run these request URLs manually, or loop through them automatically using pagination handling (paid feature). Pipedrive uses ‘start’ and ‘limit’ parameters, so they would get entered like this:
- Pagination type:
offset-limit
- Offset parameter:
start
- Limit parameter:
limit
- Limit value:
500
- Run until: choose when to stop fetching data
Part 7: API Documentation
Official API documentation: https://developers.pipedrive.com/docs/api/v1/
Field selector documentation: https://pipedrive.readme.io/docs/core-api-concepts-requests#field-selector
Appendix: Connect with a Pipedrive API Token
Pipedrive requires that all API requests are authenticated. This is the process to authenticate with a Pipedrive API token:
- While logged into your Pipedrive account, navigate to Settings > (Personal) > Personal Preferences > API, or just click here.
- You’ll now see a page containing your Access Token. Copy this to your clipboard.
- That’s it, you now have access to the Pipedrive API! You can use this token by appending
api_token=YOUR_TOKEN
to your URLs, e.g.https://company.pipedrive.com/api/v1/deals:(id,title,active,add_time,update_time,stage_id,currency,value,status,pipeline_id)?api_token=1234567&status=open
. Since you’re manually including an API token, leave OAuth2 authentication set to None.
Excellent, 5 starts!
FIVE STARS 🙂
Thank you!
Very helpful. Thanks!
Any quick ways to export only filtered results?
There’s a section on filtering results but it seems you might be referring to something else. What do you mean by exporting only filtered results?
How do you pull up goals?
I think this is the right place: https://developers.pipedrive.com/docs/api/v1/Goals#getGoals
Based on that you’d make a request URL like
https://YOUR_DOMAIN.pipedrive.com/v1/goals/find
(you can add the various parameters listed in that article, too).Hi Ana thanks for the reply. I think my issue is that Im not sure how the URL looks like.
Having the details below, what will be the url format.Given that I only have one goal set up.
Assignee: Everyone
Goal Type: Deals Won
Goal Interval: Quarterly
Goal Duration: 098/01/2021 – 08/31/2021
Expected Outcome:80,0000
https://YOUR_DOMAIN.pipedrive.com/v1/goals/find
(whats next?)Oh, I didn’t think you needed to add anything after that since they didn’t mention any parameters were required. But if you do, you would add parameters like this:
https://YOUR_DOMAIN.pipedrive.com/v1/goals/find?type.name=deals_won
Another example would be
https://YOUR_DOMAIN.pipedrive.com/v1/goals/find?is_active=true
Hi Ana,
Thanks for this. Apologies I should have clarified that the first time, nonetheless I tried the below and its not working, I am getting errors.
https://YOUR_DOMAIN.pipedrive.com/v1/goals/find?type.name=deals_won/api_token=xxxxxxx
https://YOUR_DOMAIN.pipedrive.com/v1/goals/find?type.name=deals_won
https://YOUR_DOMAIN.pipedrive.com/v1/goals/find?type.name=deals_won?api_token=xxxxxxx
Multiple parameters get separated with an &, can you please try this?
https://YOUR_DOMAIN.pipedrive.com/v1/goals/find?type.name=deals_won&api_token=xxxxxxx
Hi Ana,
First of all, thank you so much for this.
I’m trying to use these steps to get All Deals from Pipedrive. And it’s working to an extent.
My issue is:
The first time I called the API, it returned some nicely formatted columns for Stage, Labels. In other words, I had text values in there with the Stage Name, Label name.
But now I’m trying to call the API again and it’s returning the ID (numerical values) for the Stage Name and Label Name.
Do you have any idea why this is happening?
Thank you so much!
It sounds like something has changed in your request but I can’t really say without seeing it. Have you modified field names in the field editor, or maybe added/removed a JMESPath expression? If you’d like me to take a look please feel free to send a message through support.
Hi Ana!
Can i export an certain range of data? like i want the from january first to today?
Someone asked this in their forum here. Based on the response, via the API it’s possible but takes two steps. You need to first create a date filter, and then can apply that filter to your request like this:
https://company.pipedrive.com/api/v1/deals?api_token=1234567&filter_id=ABCDE
That seems a bit complicated, so I’d suggest using a JMESPath filter instead. To filter with JMESPath, enter an expression like this:
data[?add_time>'2021-11-29'&&add_time<'2021-12-01']
. Just substitute in the dates you're interested in, and copy/paste that snippet into the JMESPath field.