Search API Connector Documentation

Print

Import HubSpot Data to Google Sheets

In this guide, we’ll walk through how to pull data from the HubSpot API directly into Google Sheets, using the API Connector add-on for Sheets.

There are 2 ways to connect to the HubSpot API:

  • Preset “Connect” button (OAuth) premium
  • Personal API key. 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 HubSpot API

We’ll first initiate the connection to the HubSpot API.

  1. Open up Google Sheets and click Extensions > API Connector > Manage Connections.
  2. In the list of available connections, find HubSpot Contacts and click Connect.
    hubspot-img9
  3. You’ll be asked to select an account and authorize the connection. Click Connect app.
    hubspot-oauth
  4. Back on the connections screen, you can verify that the account is now connected.

Part 2: Create Your API Request URL

For this request we’ll access data about your contacts.

  • API root: https://api.hubapi.com
  • Endpoint: /crm/v3/objects/contacts
  • Query strings: ?limit=100

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

https://api.hubapi.com/crm/v3/objects/contacts?limit=100

Part 3: Pull HubSpot Data into Google Sheets

Now let’s copy and paste that URL into API Connector.

  1. Open up Google Sheets and click Extensions > API Connector > Open.
  2. In the Create tab, enter the API URL we just created.
    hubspot-img5
     
  3. Under OAuth, choose HubSpot.
    hubspot-img5b
  4. We don’t need any headers so just leave that section blank.
  5. Create a new tab and click Set current to use that tab as your data destination.
  6. Name your request and click Run. A moment later you’ll see information about your CRM contacts in your sheet.
    hubspot-img6

Part 4: More Example API URLs

You can experiment with endpoints and query strings as described in the documentation to see other types of HubSpot data, but if you just want to jump in and get a feel for it, try the following example URLs. HubSpot’s API is is split into separate APIs for CRM, Analytics, etc, so this is just a sampling of the different types of endpoints you can find.

  • Analytics API: high level metrics for May 2021
    https://api.hubapi.com/analytics/v2/reports/totals/total?limit=100&start=20210501&end=20210531
  • Analytics API: metrics broken down by source
    https://api.hubapi.com/analytics/v2/reports/sources/total?limit=100&start=20210501&end=20210531
  • CRM API: Companies
    https://api.hubapi.com/crm/v3/objects/companies?limit=100
  • CRM API: Deals
    https://api.hubapi.com/crm/v3/objects/deals?limit=100
  • Marketing API: Emails
    https://api.hubapi.com/marketing-emails/v1/emails

Part 5: Handle Pagination

HubSpot limits the number of records returned in each request, often to just 10 records unless you add parameters and paginate through the response. This is why we set the limit=100 parameter in our example queries above. In general, Hubspot uses cursor pagination for GET requests and offset-limit-body pagination for POST requests, so both types are shown below:

1) Cursor pagination

Endpoints that use cursor pagination require that we receive a token in the response and pass it into the URL of the next request.
hubspot-img7

You can loop through cursor-based pagination automatically with pagination handling (paid feature), like this:

  • API URLenter your request URL, including the limit=100 parameter
  • Pagination typecursor
  • Next token parameterafter
  • Next token parameterpaging.next.after
  • Run untilchoose when to stop fetching data
    hubspot-pagination-cursor

2) Offset-limit body pagination

Hubspot paginates endpoints that require POST requests, like CRM search, through the request body:

  • API URLenter your request URL
  • Pagination typeoffset-limit body
  • Offset body parameterafter
  • Limit body parameterlimit
  • Limit value100
  • Run untilchoose when to stop fetching data
    hubspot-pagination-offset-limit-body

Part 6: API Documentation

Official API documentation: https://developers.hubspot.com/docs/api/overview

Legacy API documentation: https://legacydocs.hubspot.com/docs/overview

HubSpot’s API is in the midst of a migration to v3 of their API. Not all endpoints have been moved over, so you may need to toggle between their new API docs and their legacy docs. In addition, not all endpoints are available on all plans.

Appendix: Connect with a HubSpot API Key

This section is provided as a free alternative to the method described above. Instead of clicking HubSpot in the Connections manager, you will retrieve your own API key.
  1. If you haven’t already, log into your HubSpot account. Click on the settings icon in the header bar.
    hubspot-img1
  2. This will open the settings menu in the sidebar. Click Account Setup > Integrations > API Key.
    hubspot-img2
  3. Click Create key to generate an API key.
    hubspot-img3
  4. You’ll now see your API access key. Congrats 🙂 Copy it and keep it safe, we’ll need it in a moment.
    hubspot-img4
  5. To make requests, append this value to the end of your URL like this: &hapikey=YOUR_API_KEY (since you’re using an API key, leave OAuth set to None). For example:
https://api.hubapi.com/crm/v3/objects/contacts?limit=100&hapikey=YOUR_API_KEY
Previous Import Harvest Data to Google Sheets
Next Import Hunter Data to Google Sheets

8 thoughts on “Import HubSpot Data to Google Sheets”

  1. This pagination method doesn’t work when fetching companies. I’m using https://api.hubapi.com/companies/v2/companies/paged?hapikey=xxx&properties=name with the cursor / after / paging.next.after settings for pagination, but I can’t get more than 100. Any ideas?

    Reply
    • Your URL is for HubSpot’s v2 API, while the instructions above are for v3. They’ve made significant changes between the 2 versions so the v3 pagination method doesn’t work for v2 (and vice versa).
      If you use v2, you’d enter your URL like https://api.hubapi.com/companies/v2/companies/paged?hapikey=xxx&properties=name&limit=250
      Then, under pagination, you’d enter pagination = offset-limit, offset parameter = offset, limit parameter = limit.

      If you use v3, your URL would instead look like https://api.hubapi.com/crm/v3/objects/companies?limit=100, and you should be able to use the pagination method as shown in the article. Let me know if that works for you!

      Reply
  2. I was able to run https://api.hubapi.com/crm/v3/objects/contacts?limit=100&after=3372282 but when I ran the https://api.hubapi.com/marketing-emails/v1/emails
    it gave this error message {“category”:”MISSING_SCOPES”,”correlationId”:”163890a9-4406-4cfa-966c-b77343b407cc”,”errors”:[{“context”:{“requiredScopes”:[“content”]},”message”:”One or more of the following scopes are required.”}],”links”:{“scopes”:”https://developers.hubspot.com/scopes”},”message”:”This app hasn’t been granted all required scopes to make this call. Read more about required scopes here: https://developers.hubspot.com/scopes.”,”status”:”error”}
    OK

    What should i do to fix this? Can anyone help thanks!!

    Reply
    • Hey Tang, sorry, our scopes (permissions) for Hubspot currently don’t include marketing emails. That means that for now you’ll need to create and use your own API key. The API key doesn’t require such granular permissions and will give you access to all the data in your account. In the future we’ll also add the contacts scope to our connector as well.

      Reply
  3. Thank you for your amazing post!
    I have a question: How can I get data from my custom reports using the HubSpot API?
    Suppose I have a custom report called: “Overdue tasks”
    Whats the API call to get that information.
    Regards

    Reply
    • Hi Adam, the HubSpot API doesn’t directly provide an endpoint to fetch data from your custom reports. You can see some discussion on this in their forum. So you would need to identify which endpoint contains the data you need and recreate your custom report accordingly. It sounds like you’d be looking for the /tasks endpoint, please check that out and see if it contains the info you need, or just reach out if you’d like help.

      Reply
      • Suppose I have the following report:
        1.- Companies Last Open Task Date is before 05/19/2022 (CEST)
        AND
        2.- Companies Válido is none of REV or DNC
        AND
        3.- Companies Sales Funnel Stage is any of Opportunity
        AND
        4.- Companies Company owner is none of XXX, YYY, ZZZ

        What’s the more efficient way to get this info through API?

        Thanks

      • I think you would use their search endpoint for this, which can be queried like this:
        URL POST https://api.hubapi.com/crm/v3/objects/companies/search
        Headers: key = content-type, value = application/json
        Request body: {"filterGroups":[{"filters":[{"value":"company.com","propertyName":"domain","operator":"EQ"}]}],"sorts":[{"propertyName":"company_id","direction":"ASCENDING"}],"limit":100,"after":0}
        I’m not really sure how each of your search elements would get entered, though. You may want to check their forums for more specific help, I see some related questions here and here.

Leave a Comment

Table of Contents