Search API Connector Documentation
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
- Part 1: Connect to the HubSpot API
- Part 2: Pull Data from Hubspot to Sheets
- Part 3: Create a Custom Request
- Part 4: Example API URLs
- Part 5: Handle Pagination
- Part 6: API Documentation
- Appendix: Connect with a HubSpot API Key
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.
- Select Hubspot CRM from the drop-down list of applications
- Under Authorization, click Connect to Hubspot CRM
- You’ll be asked to select an account and authorize the connection. Click Connect app.
- 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.
- Under Endpoint, choose the data you’d like to retrieve. The
/objects/contactsendpoint will return your contacts.
- Optionally set any parameters, such as archive status, record limit, and properties.
- Select a destination sheet, name your request, and click Run.
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 key).
To easily convert from a preset request to a custom API URL, tick the Add request URL box before running your preset request (under Output options). This will print out your complete API URL, which you can then copy/paste into the request URL field.
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 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
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.
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
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
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
- 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. Congrats 🙂 Copy it and keep it safe, we’ll need it in a moment.
- 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
This pagination method doesn’t work when fetching companies. I’m using
https://api.hubapi.com/companies/v2/companies/paged?hapikey=xxx&properties=namewith the cursor / after / paging.next.after settings for pagination, but I can’t get more than 100. Any ideas?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=250Then, 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!I was able to run
https://api.hubapi.com/crm/v3/objects/contacts?limit=100&after=3372282but when I ran thehttps://api.hubapi.com/marketing-emails/v1/emailsit 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!!
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.
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
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.
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/searchHeaders: 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.
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.
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?
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?
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=1234567where 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.