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.

1. Open up Google Sheets and click Extensions > API Connector > Manage Connections.
2. In the list of available connections, find Pipedrive and click Connect.
   
   
3. You will see ascreen asking you to log in and approve the connection. Click Allow and Install.
   
   
4. 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.

1. Open up Google Sheets and click Extensions > API Connector > Open.
2. In the Create screen, enter the Request URL we just created
   
3. Under Authentication, choose Pipedrive from the dropdown menu. You should see a “Connected” badge.
   
4. Under Headers, enter Key = Accept, Value = application/json
   
5. Create a new tab and click Set current to use that tab as your data destination.
6. 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).
   
7. 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:

1. 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)
2. 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
3. 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.
4. 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:

1. While logged into your Pipedrive account, navigate to Settings > (Personal) > Personal Preferences > API, or just click here.
2. You’ll now see a page containing your Access Token. Copy this to your clipboard.
   
3. 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.