How to Use Google Apps Script to Query the HubSpot API

When it comes to running a business, there’s nothing more important than your data. After all, your metrics provide essential information that may indicate the difference between success and failure. Unfortunately, even if you have great tools that can help you collect and track that data — like HubSpot dashboards — making sense of it all can be frustrating, even for experienced data analysts.

The good news is that a well-organized spreadsheet can help you make sense of all your data. Integrating HubSpot and Google Sheets can be invaluable to operations, marketing, and sales teams, as it enables them to quickly analyze robust data sets. As a result, they’re able to make better, more-informed business decisions. The fact that Google Sheets is free, cloud-based, and functional across various devices makes it a great way to generate quality HubSpot reports and gain deeper insight into existing data.

However, before you can start using Google Sheets for your HubSpot reporting, you need to import your data from HubSpot to Google Sheets. You can do so by manually exporting a HubSpot CSV file each time you want to analyze your data, but this eats up valuable time and energy. Or, you can use Google Apps script to query the HubSpot API and pull the data right into Google Sheets, saving you a great deal of time.

To that end, we’ve created a step-by-step guide for how to use the latter approach to import data on your HubSpot Contacts, Companies, Deals, and Owners. Here are the six steps for connecting Google Sheets to HubSpot with the HubSpot API that we will cover in detail below:

1. Create a new Google Sheet
2. Open Google Apps Script 
3. Paste the script below into the Google Apps Script code field
4. Replace the script’s placeholder API key with your HubSpot API key
5. Click “Save project”, then click “Run the selected function”
6. Navigate back to your Google Sheet

Connecting Google Sheets to HubSpot with the HubSpot API

The six-step process below walks you through how to connect Google Sheets to HubSpot by querying the HubSpot API. This will empower you to pull in any data you want from HubSpot, though we are going to focus on Contacts, Companies, Deals, and Owners. Ready to get started? Follow these steps:

1. Create a new Google Sheet (pro tip: try typing “sheet.new” into your address bar).

This Google Sheet is where your HubSpot data will end up after you use Google Apps Script to query the HubSpot API. You can name this Google Sheet whatever you wish, but we’d suggest something like “Test to Connect Google Sheets to HubSpot with the HubSpot API”.

2. Open Google Apps Script (from within Google Sheets).

In the menu of your Google Sheet, navigate to “Tools” > “Script editor.”

This will open a new window with the Google Apps Script interface. This interface is where you will paste (or, if you are a coding whiz, write) the script that you will use to query the HubSpot API.

Again, feel free to name this Google Apps Script whatever you wish — perhaps something like “Test Google Apps Script with the HubSpot API.”

3. Paste the script below into the Google Apps Script code field.

As a first run, let’s focus on importing a list of Contacts from your HubSpot. In a HubSpot context, your Contacts are anyone who has communicated with your organization in some way. Contact records often include information like individual names, company names, email addresses, and so forth. This information can help you optimize everything from content mapping to a range of marketing automation tasks. Now, back to the fun stuff...

If you see any code already in the code field, delete it. Then, paste the following script into the empty code field:

function callHsapi() {

var API_KEY = "YOUR_HUBSPOT_API_KEY"; // Replace YOUR_HUBSPOT_API_KEY with your API Key

var url = "https://api.hubapi.com/crm/v3/objects/contacts?limit=10&archived=false&hapikey=YOUR_HUBSPOT_API_KEY"; // Replace YOUR_HUBSPOT_API_KEY with your API Key

var response = UrlFetchApp.fetch(url);

var data = JSON.parse(response.getContentText());

var results = data['results'];

var sheet = SpreadsheetApp.getActiveSheet();

var header = ["Company", "Create Date", "Email", "First Name", "Last Modified Date", "Last Name", "Phone", "Website"];

var items = [header];

results.forEach(function (result) {

items.push([result['properties'].company, result['properties'].createdate, result['properties'].email, result['properties'].firstname, result['properties'].lastmodifieddate, result['properties'].lastname, result['properties'].phone, result['properties'].website]);

});

sheet.getRange(1,1,items.length,items[0].length).setValues(items);

results.forEach(function (result) {

Logger.log(result['properties']);

});

}

‍

4. Replace the script’s placeholder API key with your HubSpot API key.

Before running the script you just pasted, you will need to replace both appearances of “YOUR_HUBSPOT_API_KEY” (one appearance is in Line 2 of the script, the other is in Line 3) with, unsurprisingly, your unique HubSpot API key.

To find your HubSpot API key, follow these steps:

a. Paste this URL in your address bar: https://app.hubspot.com/portal-recommend/l?slug=personal-access-key.

b. Select the HubSpot account from which you would like to import data, then click “Continue with this account.”

c. Click the “Settings” icon (the gear) in the top-right corner of the page.

d. In the left sidebar, open the “Integrations” accordion, then click “API Key.”

e. On the “HubSpot API Key” page, you will see a field called “Active API Key.”

• If you have never generated a key for this HubSpot account, click “Generate API Key.” Then, click “Copy” to copy the newly created key to your device’s clipboard.
• If you have previously generated a key for this HubSpot account, click “Show,” then click “Copy” to copy the key to your device’s clipboard.

f. Now, return to your Google Apps Script interface and replace both appearances of “YOUR_HUBSPOT_API_KEY” with the API key on your device’s clipboard.

5. Click “Save project” (the floppy disk icon), then click “Run the selected function.”

• A window may pop up informing you that your script needs permission to access your data on Google. Click “Review Permissions.” Select the Google account you are using to run your script, then click “Allow.”

• An “Execution log” should automatically appear beneath your script. Assuming everything goes well, the final line of the log should read, “Execution completed.” 

6. Navigate back to your Google Sheet.

You will now see all the Contact data you just imported from HubSpot in your spreadsheet!

Import other types of HubSpot data to Google Sheets

Of course, Contacts are not the only type of HubSpot data you can pull into a Google Sheet using Google Apps Script. The process for importing Companies, Deals, and Owners is nearly identical to the process for importing Contacts. The only difference is the actual script you paste into the Google Apps Script interface (Step 3 above).

Below, you will find the unique scripts you can use to import these other data types to your Google Sheet. Do note that if you want all your data to exist in the same Google Sheet spreadsheet document, be sure to create a new tab for each import you perform. Otherwise, each successful import will override the previous one!

Data Type: Companies

Not unlike Contacts, in a HubSpot context, Companies include any organization that has communicated with your organization in some way. Multiple HubSpot Contacts can be tied to a single HubSpot Company, which can give you a good idea of the distinctive ways that clusters of Contacts who work for the same organization interact with your brand.

Here’s the script you’ll want to paste into the empty code field in the Google Apps Script interface in order to import your HubSpot Companies data into your Google Sheet (again, during Step 3 of the process outlined above):

function callHsapi() {

var API_KEY = "YOUR_HUBSPOT_API_KEY"; // Replace YOUR_HUBSPOT_API_KEY with your API Key

var url = "https://api.hubapi.com/crm/v3/objects/companies?limit=10&archived=false&hapikey=YOUR_HUBSPOT_API_KEY"; // Replace YOUR_HUBSPOT_API_KEY with your API Key

var response = UrlFetchApp.fetch(url);

var data = JSON.parse(response.getContentText());

var results = data['results'];

var sheet = SpreadsheetApp.getActiveSheet();

var header = ["City", "Create Date", "Domain", "HS Last Modified Date", "Industry", "Name", "Phone", "State"];

var items = [header];

results.forEach(function (result) {

items.push([result['properties'].city, result['properties'].createdate, result['properties'].domain, result['properties'].hs_lastmodifieddate, result['properties'].industry, result['properties'].name, result['properties'].phone, result['properties'].state]);

});

sheet.getRange(1,1,items.length,items[0].length).setValues(items);

results.forEach(function (result) {

Logger.log(result['properties']);

});

}

Data Type: Deals

In a HubSpot context, Deals include information on specific revenue opportunities, and are tracked through your HubSpot sales pipeline until they result in either a conversion or a lost deal.

Here’s the script you’ll want to paste into the empty code field in the Google Apps Script interface in order to import your HubSpot Deals data into your Google Sheet (again, during Step 3 of the process outlined above):

function callHsapi() {

var API_KEY = "YOUR_HUBSPOT_API_KEY"; // Replace YOUR_HUBSPOT_API_KEY by your API Key

var url = "https://api.hubapi.com/crm/v3/objects/deals?limit=10&archived=false&hapikey=YOUR_HUBSPOT_API_KEY"; // Replace YOUR_HUBSPOT_API_KEY by your API Key

var response = UrlFetchApp.fetch(url);

var data = JSON.parse(response.getContentText());

var results = data['results'];

var sheet = SpreadsheetApp.getActiveSheet();

var header = ["Amount", "Close Date", "Create Date", "Deal Name", "Deal Stage", "HS Last Modified Date", "Hubspot Owner ID", "Pipeline"];

var items = [header];

results.forEach(function (result) {

items.push([result['properties'].amount, result['properties'].closedate, result['properties'].createdate, result['properties'].dealname, result['properties'].dealstage, result['properties'].hs_lastmodifieddate, result['properties'].hubspot_owner_id, result['properties'].pipeline]);

});

sheet.getRange(1,1,items.length,items[0].length).setValues(items);

results.forEach(function (result) {

Logger.log(result['properties']);

});

}

‍

Data Type: Owners

Among our examples in this piece, a Hubspot Owner is a unique data type. In a HubSpot context, an Owner is a HubSpot user who is assigned to a specific Object — including a Contact, a Company, or a Deal. Owners are critical to, for instance, reporting on individual salespeople’s performance.

Here’s the script you’ll want to paste into the empty code field in the Google Apps Script interface in order to import your HubSpot Owners data into your Google Sheet (again, during Step 3 of the process outlined above):

function callHsapi() {

var API_KEY = "YOUR_HUBSPOT_API_KEY"; // Replace YOUR_HUBSPOT_API_KEY by your API Key

var url = "https://api.hubapi.com/crm/v3/objects/contacts?limit=10&archived=false&hapikey=YOUR_HUBSPOT_API_KEY"; // Replace YOUR_HUBSPOT_API_KEY by your API Key

var response = UrlFetchApp.fetch(url);

var data = JSON.parse(response.getContentText());

var results = data['results'];

var sheet = SpreadsheetApp.getActiveSheet();

var header = ["First Name", "Last Name", "Created At", "Archived", "ID", "User ID", "Email", "Updated At"];

var items = [header];

results.forEach(function (result) {

items.push([result['properties'].firstName, result['properties'].lastName, result['properties'].createdAt, result['properties'].archived, result['properties'].id, result['properties'].userId, result['properties'].email, result['properties'].updatedAt]);

});

sheet.getRange(1,1,items.length,items[0].length).setValues(items);

results.forEach(function (result) {

Logger.log(result['properties']);

});

}

Simplifying the process with a streamlined data integration solution

Clearly, using Google Apps Script to query the HubSpot API is not the simplest process in the world. And while there are a number of commercially-available third-party tools like Supermetrics, G-Accon, Zapier, and Automate.io that can help simplify the process, most of these platforms have serious pitfalls. First and foremost, many of them come with very high price tags. Plus, their capabilities are often limited: they may not allow you to import all your data, and they may only have a few use cases.

To make matters worse, many of these tools feature frustrating, unintuitive interfaces. Navigating complicated interfaces where the smallest mistake may have significant disruptive consequences can make you question whether paying for a tool is really better than just querying the HubSpot API on your own.

Luckily, the perfect solution does exist. Actiondesk is a cloud-based spreadsheet interface that makes it easy to stay on top of all your data. Import (and mix and match) data from HubSpot, SQL, Airtable, Salesforce, Intercom, Stripe, and more. With Actiondesk, you can eliminate CSV copy-pasting without needing to know any scripting languages or having any technical expertise.

With Actiondesk, you need only connect to HubSpot once and your data will automatically update in your spreadsheet when it changes in HubSpot. You can then manipulate your data as much as you want. Build dashboards, create graphs, dig deeper into your data, and more — all while using the Google Sheets and Excel shortcuts you already know and love.

Get all the benefits of the best third-party solutions while avoiding their pitfalls. Connect HubSpot (and many other data sources) with just a few clicks by trying Actiondesk for free today!