Import Pipedrive Data to Google Sheets

Pipedrive is a sales CRM platform. 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. We’ll first get an API key from Pipedrive, and then set up a request to pull in CRM data to your spreadsheet.

CONTENTS

• Before You Begin
• Part 1: Get your Pipedrive API Key
• 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 

BEFORE YOU BEGIN

Click here to install the API Connector add-on from the Google Marketplace.

PART 1: GET YOUR PIPEDRIVE API KEY

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 as you’ll need it in a moment. That’s it, you now have access to the Pipedrive API!
   

PART 2: CREATE YOUR PIPEDRIVE API REQUEST URL

We’ll follow the Pipedrive documentation to access a list of leads.

• API root: https://YOUR_DOMAIN.pipedrive.com
• Endpoint: /v1/deals
• Query String: ?api_token=YOUR_API_TOKEN

Putting it together, we get the full API Request URL:

https://YOUR_DOMAIN.pipedrive.com/v1/deals?api_token=YOUR_API_TOKEN

PART 3: PULL PIPEDRIVE API DATA INTO SHEETS

We can now enter our values into API Connector and import Pipedrive API data into Google Sheets.

1. Open up Google Sheets and click Add-ons > API Connector > Open.
2. In the Create screen, enter the Request URL we just created
   
3. Under Headers, enter two sets of key-value pairs, like this:
   

   Content-Type	application/json
   Accept	application/json

   
4. Create a new tab and click ‘Set current’ to use that tab as your data destination.
5. Name your request and click Run. A moment later you’ll see a list of your deals in your Google 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?api_token=YOUR_API_TOKEN

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 or run into Google Sheets’ 5 million cell limit. To address this you can use one or both of the following filter types:

1. Pipedrive parameters: Pipedrive’s documentation lists numerous parameters you can append to your requests. These parameters get included at the end of your request URL, like this:
   https://YOUR_DOMAIN.pipedrive.com/v1/deals?api_token=api_token&limit=10&status=open
2. [PRO FEATURE] JMESPath filtering. JMESPath filtering (paid feature) allows you to specify exactly which fields to display in your request. For example, the following JMESPath expression would cause your sheet to display just 12 fields from the deals endpoint, instead of 100+
   data[*].{creator_id:creator_user_id.id,creator_name:creator_user_id.name,creator_email:creator_user_id.email, user_id:user_id.id, user_name:user_id.name,title:title,value:value,currency:currency,add_time:add_time,update_time:update_time,active:active,email_count:email_messages_count}

Both filter types can be used together without any issue.

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:

• API URL: enter your request URL as usual, making sure to include limit=500
• Pagination type: offset-limit
• Offset parameter: start
• Limit parameter: limit
• Limit value: 500
• Number of pages: enter the number of pages you’d like to fetch