Search API Connector Documentation

Print

Import HubSpot Data to Google Sheets

Last Updated On
byAna
< Knowledge Base

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
  • Access token. Please check the appendix for detailed instructions to retrieve your token.
Starting November 30, 2022, HubSpot API keys can no longer be used to access HubSpot APIs (info). Instead, use either of the methods listed above.

Contents

Before You Begin

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

Part 1: Connect to the HubSpot API

The easiest way to get started with the Hubspot API is through API Connector’s built-in integration.

  1. In Sheets, open API Connector and create a new request (Extensions > API Connector > Open > Create request)
  2. Select Hubspot CRM from the drop-down list of applications
    hubspot-application
  3. Under Authorization, click Connect to Hubspot CRM
    hubspot-authorization
  4. You'll be asked to select an account and authorize the connection. Click Connect app.
    hubspot-oauth
  5. Back in Sheets, you can verify that the account is now connected.

Part 2: Pull Data from Hubspot to Sheets

Now that we’re connected, let’s pull some data into Sheets.

  1. Under Endpoint, choose the data you’d like to retrieve. The /objects/contacts endpoint will return your contacts.
    hubspot-endpoints
  2. Optionally set any parameters, such as archive status, record limit, and properties.
  3. Select a destination sheet, name your request, and click Run.
    hubspot-response

Part 3: Create a Custom Request

Alternatively, you can create a custom request instead of using API Connector’s built-in integration, using any of the endpoints and parameters shown in their API documentation. To create a custom request, add the complete URL into the API URL field and select Hubspot from the OAuth menu (or connect with an API token). Here's an example request setup:

  • Application: Custom
  • Method: GET
  • Request URL: https://api.hubapi.com/crm/v3/objects/contacts?limit=100
  • OAuth: Hubspothubspot-img6

Part 4: Example API URLs

You can experiment with endpoints and query strings as described in the documentation to see other types of HubSpot data. 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 2022
    https://api.hubapi.com/analytics/v2/reports/totals/total?limit=100&start=20220501&end=20220531
  • Analytics API: metrics broken down by source
    https://api.hubapi.com/analytics/v2/reports/sources/total?limit=100&start=20220501&end=20220531
  • 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

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:

  • Pagination type: cursor
  • Next token parameter: after
  • Next token parameter: paging.next.after
  • Run until: choose 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:

  • Pagination type: offset-limit body
  • Offset body parameter: after
  • Limit body parameter: limit
  • Limit value: 100
  • Run until: choose 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. Most but not all endpoints have been moved over, so you may still need to toggle between their new v3 API docs and their legacy v2 docs.

Appendix: Connect with a HubSpot API Token

This section is provided as a free alternative to the method described above. Instead of clicking Connect to Hubspot, you will retrieve your own API token.
  1. In your HubSpot account, click the settings icon in the main navigation bar.
  2. In the left sidebar menu, navigate to Integrations > Private Apps.
  3. Click Create private app.
  4. On the Basic Info tab, give your app a name (it can be anything)
  5. On the Scopes tab, select the scopes you'd like your app to have access to. If you aren't sure, check Hubspot's API documentation. Each endpoint shows the scope it requires, e.g. listing Contacts requires crm.objects.contacts.read
    hubspot-scopes
  6. Once you've finished selecting your scopes, click Create app in the top right corner. Don't worry if you missed anything; you can change these scopes later.
  7. In the pop-up modal, review your settings and click Continue creating.
  8. You'll now see your private app token. Click Show token and copy the value to your clipboard. This token will also be available in your Account settings.
    hubspot-token
  9. To use this token, enter your API request URL and a Header where the Key = Authorization, and the Value = Bearer your_token
    hubspot-token-request

If you were previously using API keys, you will need to remove those from your URL and replace them with the access token header.

14 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 token with the scopes you require.

      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.

  4. Hi,

    Loving the latest update 🙂

    I'm trying to connect to Hubspot Deals endpoint thought the new UI but getting the below error:

    Couldn’t complete the connection
    This account doesn't have access to some HubSpot features that are required by this app. Please contact the app developer.

    I'm a super admin on the account so not sure if there are additional permissions I can get.

    Reply

    • Thanks, Mark! Sorry you're getting an error. It's related to missing scopes, but I'm not yet sure what's causing it since our app should have access to the scope you need for deals (and it's working on my side). If you haven't already, can you please disconnect/reconnect the Hubspot connection in case it wasn't fully approved for all sccopes earlier?

      Reply

  5. Hi Ana,

    I'm a paid user. Just wondering on how to do a deals export to google sheet if the deal count is greater than 200K because it only gets me around 50K and the rest are timeouts. Possible to start getting after the last deal id?

    Reply

    • The Hubspot deals endpoint uses cursor pagination, in which you take the last cursor value and pass it into the next URL, e.g. https://api.hubapi.com/crm/v3/objects/deals?limit=10&after=1234567 where the 1234567 is provided in the response.
      So if you've fetched 50K rows, you can then create a second request that plugs in that final cursor value and fetches another 50K records, and so on. If you need to continuously refetch this data, you can probably automate this by creating 4 requests that each reference the cell containing the cursor, e.g. https://api.hubapi.com/crm/v3/objects/deals?limit=10&after=+++Sheet1!Z1000+++ The exact setup depends on the response output, so feel free to message support if you'd like more specific help.

      Reply

    • Hey Adam, from what I see you can add the propertiesWithHistory parameter to your request, e.g. https://api.hubapi.com/crm/v3/objects/companies?limit=10&propertiesWithHistory=city,createdate,name (info)

      Reply

Leave a Reply to Mark Cancel reply