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.

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!
    pipedrive-img1

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
    pipedrive-img2
  3. Under Headers, enter two sets of key-value pairs, like this:
    Content-Typeapplication/json
    Acceptapplication/json
    pipedrive-img3
  4. Create a new tab. You can call it whatever you like, but here we’ll call it ‘Pipedrive_deals’. While still in that tab, click ‘Set’ to use that tab as your data destination.
  5. Name your request. Again we’ll call it ‘Pipedrive_deals’
  6. Click Run and a moment later you’ll see a list of your deals in your Google Sheet:
    pipedrive-img4

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 requests separately, or process them automatically using pagination handling (paid feature). Pipedrive uses ‘start’ and ‘limit’ parameters, so they would get entered like this:

pipedrive-img5

3 thoughts on “Import Pipedrive Data to Google Sheets”

Leave a Comment