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

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

Introduction

Let’s dig in

Retrieve the contacts from Hubspot using its APIs

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);
}
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);
}

Fetch specific properties from each contact

var url_query = API_URL + "/contacts/v1/lists/all/contacts/all?property=firstname&property=lastname&property=leadsource";
if (hasMore)
{
url_query += "&vidOffset="+offset;
}
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.

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);
}

That’s it

I am Alex, one of the founders at Make it — Tech Startup Studio. We build startups. Check us out here: makeit-studio.com