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

Image for post
Image for post
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

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store