Search API Connector Documentation

Print

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

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.
    pipedrive-connect
  3. You will see ascreen asking you to log in and approve the connection. Click Allow and Install.
    pipedrive-oauth
  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
    pipedrive-url
  3. Under Authentication, choose Pipedrive from the dropdown menu. You should see a “Connected” badge.
    pipedrive-connected
  4. Under Headers, enter Key = Accept, Value = application/json
    pipedrive-headers
  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).
    pipedrive-jmes
  7. Click Run to see data in your sheet:
    pipedrive-results1

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 typeoffset-limit
  • Offset parameterstart
  • Limit parameterlimit
  • Limit value500
  • Run untilchoose when to stop fetching data
    pipedrive-pagination-offsetlimit

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:

This section is provided as a totally free alternative to the method described above. Instead of clicking Connect you will retrieve your token yourself.
  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.
    pipedrive-img1
  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.
Previous Import Pinterest Ads Data to Google Sheets
Next Import Positionstack Data to Google Sheets

16 thoughts on “Import Pipedrive Data to Google Sheets”

  1. 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?)

    Reply
    • 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

      Reply
  2. 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

    Reply
    • 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

      Reply
  3. 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!

    Reply
    • 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.

      Reply
    • 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.

      Reply

Leave a Comment

Table of Contents