topic Re: Integrating 'Hubspot Lists' to Google Spreadsheets using Appsscript. in APIs & Integrations

Integrating 'Hubspot Lists' to Google Spreadsheets using Appsscript.

MChavan — Tue, 02 Nov 2021 13:39:54 GMT

Hi Everyone,

I'm trying to integrate my only 'Hubspot Lists' to google spreadsheets. I want all the contacts from the 'Lists', by writing code in the AppsScript. Can anyone please help me with us. Thankyou.

Re: Integrating 'Hubspot Lists' to Google Spreadsheets using Appsscript.

dennisedson — Wed, 03 Nov 2021 15:13:38 GMT

@miljkovicmisa , maybe you can help? 😀

Re: Integrating 'Hubspot Lists' to Google Spreadsheets using Appsscript.

miljkovicmisa — Wed, 03 Nov 2021 22:15:15 GMT

Hello @MChavan , hi @dennisedson .
This is possible by utilizing the UrlFetch service in appscript, you can find more info here in this link.
The call can be made to the according list that would return json data with all the contacts, the api endpoint documentation page is here in this link.

Hope I gave you some guidance regarding this implementation, unfortunately I haven't worked heavily with the Apps Script so I don't know the ins and outs, but it seems straightforward so if you need further assistance regarding code, don't hesitate to write here with your progress and someone might be of help.

If my answer was helpful please mark it as a solution.

Re: Integrating 'Hubspot Lists' to Google Spreadsheets using Appsscript.

MChavan — Fri, 05 Nov 2021 10:17:49 GMT

Hi, @dennisedson , Hi @miljkovicmisa. thank you so much for the reply, this means a lot to me. By the way, i succeded with getting the 'Hubspot Lists' contacts into my 'Google spreadsheets' using Appsscript. Now i have one more challenge to do i.e. "I want to get all the 'Companies' contacts into Google spreadsheets using Appsscript". I have written a Appsscript code for this, but the problem is, if i try to run this code, it's kept on running without output. I'm attaching my code for the reference. Please, can you guys help me with this, like, where i'm making mistake. Thanking you. Below is the code, i'm running.

function getCompanies() {

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","Company name"]);

// 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)

{

var url_query = "https://api.hubapi.com/companies/v2/companies/paged?hapikey=YOUR_HUBSPOT_API&property=name";

if (hasMore)

{

url_query += "&vidOffset="+offset;

}

var response = UrlFetchApp.fetch(url_query).getContentText();

response = JSON.parse(response);

Logger.log(response);

hasMore = response['has-more'];

offset = response['vid-offset'];

if (!hasMore)

{

go = false;

}

response.companies.forEach(function(companies) {

var vid = companies.vid;

var name = (companies.properties.hasOwnProperty('name')) ? companies.properties.name.value : "";

data.push([vid, name]);

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

sheet.getRange('A1:X40000').clearContent();

var setRange = sheet.getRange(1,1,results.length,results[0].length);

setRange.setValues(results);

}

Re: Integrating 'Hubspot Lists' to Google Spreadsheets using Appsscript.

MChavan — Fri, 05 Nov 2021 10:30:09 GMT

@miljkovicmisa , @dennisedson , and this is the JSON response i'm receiving. 'Properties' is a empty object.

Re: Integrating 'Hubspot Lists' to Google Spreadsheets using Appsscript.

dennisedson — Fri, 05 Nov 2021 14:03:35 GMT

Hey @MChavan

The contacts for a company are not considered properties of the company. They are associations.

Using the associations endpoint will return the IDs of the contacts. You can then use the contacts endpoint to read a batch of contacts by internal ID

Re: Integrating 'Hubspot Lists' to Google Spreadsheets using Appsscript.

MChavan — Mon, 08 Nov 2021 08:24:56 GMT

@dennisedson thanks for the information.