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.

Note that this API requires authentication for access, through either your own API token or an OAuth2 integration. This article shows how to connect through API Connector’s paid OAuth2 Connection Manager, which handles authentication for you. If you’d prefer to manage the process manually for free, please skip to the Appendix for step by step instructions on connecting with an API 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 Add-ons > 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-img10
  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 Add-ons > 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.

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.
  • 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. By default, only 10 records will be returned unless you paginate through records as described in their documentation. This is why we set the limit=100 parameter in our example queries above.
hubspot-img7

To get more than 100 records, you need to use the cursor value returned in the paging.next.after property as shown above.

With API Connector you can either run those request URLs manually or loop through them automatically with pagination handling (paid feature), like this:

  • API URLenter your request URL as usual, including the limit=100 parameter
  • Pagination typecursor
  • Next token parameterafter
  • Next token fieldpaging.next.after
  • Number of pagesenter the number of pages you’d like to fetch
    hubspot-img8

APPENDIX: CONNECT WITH A HUBSPOT 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

Leave a Comment

Table of Contents