Populate Google Sheet data with Contacts/Companies property not works

[Espe]

I am trying to autopopulate google sheets with these 2 scripts, but they work only for the properties in the code: 

- name, city for the companies

- first name, last name, email for the contacts

When I try to add another property, the script don't work.

Why?

Contacts

var CLIENT_ID = 'xxx';
var CLIENT_SECRET = 'xxx';
var SCOPE = 'contacts';
var AUTH_URL = "https://app.hubspot.com/oauth/authorize";
var TOKEN_URL = "https://api.hubapi.com/oauth/v1/token";
var API_URL = "https://api.hubapi.com";

function getService() {
   return OAuth2.createService('hubspot')
      .setTokenUrl(TOKEN_URL)
      .setAuthorizationBaseUrl(AUTH_URL)
      .setClientId(CLIENT_ID)
      .setClientSecret(CLIENT_SECRET)
      .setCallbackFunction('authCallback')
      .setPropertyStore(PropertiesService.getUserProperties())
      .setScope(SCOPE);
}
function authCallback(request) {
   var service = getService();
   var authorized = service.handleCallback(request);
   if (authorized) {
      return HtmlService.createHtmlOutput('Success!');
   } else {
      return HtmlService.createHtmlOutput('Denied.');
   }
}

function authenticate() {
   var service = getService();
   if (service.hasAccess()) {
      // … whatever needs to be done here …
   } else {
      var authorizationUrl = service.getAuthorizationUrl();
      Logger.log('Open the following URL and re-run the script: %s',authorizationUrl);
   }
}

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

Companies

function getCompanies() {
// 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", "city"]);

var keep_going = true;
var hasMore = false;
var offset = 0;

while (keep_going)
{
var url_query = API_URL + "/companies/v2/companies/paged?properties=name&properties=city&limit=250&offset="+offset;
var response = UrlFetchApp.fetch(url_query, options);
var result = JSON.parse(response.getContentText());

keep_going = result.hasMore;
offset = result.offset;

result.companies.forEach(function(item) {
var name = (item.properties.hasOwnProperty('name')) ? item.properties.name.value : "NA";
var city = (item.properties.hasOwnProperty('city')) ? item.properties.city.value : "NA";
data.push([name, city]);
numResults++;
});
}

Logger.log(numResults);

// This function will clear and fill in the sheet with the data
writeCompanies("Comps",data);
}

function writeCompanies(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);
}

[Espe]

Hi @ellenrathburn,

Thanks but I would to use my script.

[ellenrathburn]

Hi @Espe, 

Have you considered using Zapier to link your Hubspot account and Google Sheet rather than coding out the individual scripts? It would be a much simpler way of converting your form submissions to your spreadsheet. This article is a good starting place if you're interested: https://knowledge.hubspot.com/integrations/how-to-use-zapier-and-hubspot 

[JessicaH]

Hi @Espe,

Thanks for reaching out.
I want to tag in some subject matter experts to see if they have any suggestions.
Hi @Ben_M @Trumeter @ellenrathburn, do you have any thoughts on this?

Thanks!
Jess  

[Espe]

Hi @JessicaH ,

thanks for your reply.

I hope there is a solution for this.