How to retrieve your contacts from Hubspot in Google Spreadsheet using App Script

5 min readOct 27, 2018

Using Google App Script to retrieve data from Hubspot and write it to a Google Spreadsheet

Introduction

I wrote this other article a while ago “Create an automated Hubspot custom dashboard with Google Spreadsheet and Data Studio”. Some people asked me to write another one to help them retrieve the list of contacts from Hubspot using its API.

My goal is to make it as easy as possible for managers without a technical background to make them achieve this in a MVP kinda way.

I am Alex, one of the founders at Make it Group. We build startups in IoT. Check us out here: makeit-group.com

My commitment:

It’s free
It’s straightforward
It gets the job done

Let’s dig in

In order to achieve our mission we’ll need to do the following:

Create a development account on Hubspot
Create an application within Hubspot with the right settings
Use a Google Spreadsheet to authenticate to Hubspot using OAuth 2.0
Retrieve the contacts and their properties from Hubspot using its APIs

We’ll focus on the last step, as the three first ones are already detailed in my other article. If you need more info about those steps, make sure you start by reading the following : Create an automated Hubspot custom dashboard with Google Spreadsheet and Data Studio

Let’s go.

Retrieve the contacts from Hubspot using its APIs

If you are here, it means you have successfully created a developer’s account on Hubspot, created an application within Hubspot to retrieved your Client ID and Client Secret and connected your Google Spreadsheet to Hubspot using an OAuth 2.0 library. If that’s not the case, please read the detailed information from my other article as explained above.

The following function uses Hubspot’s contacts API to retrieve the contacts from Hubspot. In my example, we only retrieve the unique ID, first name, last name and e-mail if they exist in Hubspot. If you need to retrieve more info from the contacts, you might need to check out this API documentation. The routine goes as follows:

First we authenticate to Hubspot
Secondly, we prepare a two-dimensional array where we will save the date from the contacts
Hubspot’s contacts API uses pagination, so we setup a loop that will retrieve the data until we have retrieved all the contacts
Each time we enter the loop, we go through the found contacts and retrieve the requested data. All properties of the contact might not be affected, that’s why we make sure the property exists with the javascript function hasOwnProperty().
Finally, when the two-dimensional array is filled in, we use the writeResults() function to write the data into the appropriate sheet.

You can copy & paste the following code into your Google App Script:

function getContacts() {
  // Authentication to Hubspot
  var service = getService();
  var options = {headers: {'Authorization': 'Bearer ' +   service.getAccessToken()}};  var numResults = 0;
  
  // We are going to put all the date into that array, starting with the header of our sheet
  var data = new Array();
  data.push(["VID","FIRSTNAME","LASTNAME","EMAIL"]);
  
  // Hubspot only let's you get 100 contacts per API request, we need therefore to enable pagination
  var go = true;
  var hasMore = false;
  var offset = 0;
  
  while (go)
  {
    // More info about contacts api in the link below
    var url_query = API_URL + "/contacts/v1/lists/all/contacts/all";
    if (hasMore)
    {
      url_query += "?vidOffset="+offset;
    }
    var response = UrlFetchApp.fetch(url_query, options).getContentText();
    response = JSON.parse(response);
    hasMore = response['has-more'];
    offset = response['vid-offset'];    if (!hasMore)
    {
      go = false;
    }   response.contacts.forEach(function(item) {
      var vid = item.vid;
      
      // Those properties are not defined by default, so it might be that your contact does not have firstname or lastname defined
      var firstName = (item.properties.hasOwnProperty('firstname')) ? item.properties.firstname.value : "NA";
      var lastName = (item.properties.hasOwnProperty('lastname')) ? item.properties.lastname.value : "NA";
      var email = "NA";
      
      // Not sure why, but a contact might have multiple identity-profiles, we take the firstone
      item['identity-profiles'][0].identities.forEach(function(identity) {
        if (identity.type == "EMAIL") {
          email = identity.value;
        }
      });
      data.push([vid,firstName,lastName,email]);
      numResults++;
    });
  }  
  
  Logger.log(numResults);
  
  // This function will clear and fill in the sheet with the data
  writeResults("CONTACTS",data);}

The writeResults() function goes as follows:

function writeResults(sheetName,results)
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetName);
  sheet.clear();
  
  var setRange = sheet.getRange(1,1,results.length,results[0].length);
  setRange.setValues(results);
}

Don’t forget to add a sheet called CONTACTS in order to make the script work.

Fetch specific properties from each contact

Now you might need to request specific properties from the contacts. You can get those by adding the requested properties in url_query as such:

var url_query = API_URL + "/contacts/v1/lists/all/contacts/all?property=firstname&property=lastname&property=leadsource";

In this example I am asking for the First Name, Last Name and Lead Source. You can find the right name for each property in your Hubspot configuration or use the script below to fetch the properties from Hubspot and save them into your spreadsheet.

In order to make your script work, you will need to change the following lines as well:

if (hasMore)
{
   url_query += "&vidOffset="+offset;
}

The same way as before you will need to check whether those properties exist for each contact you go through in the loop.

var firstName = (item.properties.hasOwnProperty('firstname')) ? item.properties.firstname.value : "NA";
      
var lastName = (item.properties.hasOwnProperty('lastname')) ? item.properties.lastname.value : "NA";
      
var leadsource = (item.properties.hasOwnProperty('leadsource')) ? item.properties.leadsource.value : "NA";

Optional: fetch the properties directly from Hubspot and save them into your sheets.

You can use the following function to get the list of all the properties of your contacts you have set up in your Hubspot account. Those properties can then be used and added to your above url_query.

function getProperties() {
  // Authentication to Hubspot
  var service = getService();
  var options = {headers: {'Authorization': 'Bearer ' + service.getAccessToken()}};  var numResults = 0;
  
  // We are going to put all the date into that array, starting with   the header of our sheet
  var data = new Array();
  data.push(["NAME"]);// More info about contacts api here: 
// https://developers.hubspot.com/docs/methods/contacts/v2/get_contacts_properties
  var url_query = API_URL + "/properties/v1/contacts/properties";
  var response = UrlFetchApp.fetch(url_query, options).getContentText();
  response = JSON.parse(response);  response.forEach(function(item) {
    data.push([item.name]);
    numResults++;
  });  Logger.log(numResults);
  
  // This function will clear and fill in the sheet with the data
  writeResults("PROPERTIES",data);}

Don’t forget to add a sheet called PROPERTIES in order to make the script work.

That’s it

You can now sync and retrieve your contacts from Hubspot and get them into a Google Spreadsheet. Make sure to read the other more detailed article I wrote about building an automated Hubspot custom dashboard with Google Spreadsheet and Data Studio. The latter has more information and examples on how to play with Google App Script, Spreadsheet and Data Studio.

Hope that helps, if you have questions or if you have suggestions about other great examples to help you automate stuff in Google Spreadsheet please comment below.