Integrating 'Hubspot Lists' to Google Spreadsheets using Appsscript.

[MChavan]

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.

[miljkovicmisa · Accepted solution]

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.

[MChavan]

@dennisedson thanks for the information.

[MChavan]

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

[dennisedson · Accepted solution]

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

[MChavan]

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

}

[miljkovicmisa · Accepted solution]

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.

[dennisedson]

@miljkovicmisa , maybe you can help?  😀