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. We’ll first get an API key from HubSpot, and then set up a request to pull in HubSpot CRM data to your spreadsheet.
- Before You Begin
- Part 1: Get your HubSpot API Key
- Part 2: Create your API Request URL
- Part 3: Pull API data into Sheets
- Part 4: More Example API URLs
- Part 5: Handle Pagination
BEFORE YOU BEGIN
Click here to install the API Connector add-on from the Google Marketplace.
PART 1: GET YOUR HUBSPOT API KEY
- If you haven’t already, log into your HubSpot account. Click on the settings icon in the header bar.
- This will open the settings menu in the sidebar. Click Account Setup > Integrations > API Key.
- Click Create key to generate an API key.
- You’ll now see your API access key. Copy it and keep it safe, we’ll need it in a moment. Congrats, you now have access to the HubSpot API.
PART 2: CREATE YOUR API REQUEST URL
We’ll first access data about your contacts.
- API root: https://api.hubapi.com
- Endpoint: /crm/v3/objects/contacts
- Query strings: ?limit=100&hapikey=YOUR_API_KEY
Putting it together, we get the full API Request URL:
PART 3: PULL HUBSPOT DATA INTO GOOGLE SHEETS
Now let’s copy and paste that URL into API Connector.
- Open up Google Sheets and click Add-ons > API Connector > Open.
- In the Create tab, enter the API URL we just created.
- We don’t need any headers so just leave that section blank. We don’t need any extra authentication, either, so leave authentication set to None.
- Create a new tab and click ‘Set current’ to use that tab as your data destination.
- Name your request and click Run. A moment later you’ll see information about your CRM contacts in your sheet.
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
- Analytics API: metrics broken down by source
- CRM API: Companies
- CRM API: Deals
Marketing API: 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.
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 URL: enter your request URL as usual, including the limit=100 parameter
- Pagination type:
- Next token parameter:
- Next token field:
- Number of pages: enter the number of pages you’d like to fetch