Export in Google Sheet Contacts with Associated Company

[Espe]

These are strings of my Apps Script where I am trying to export in google sheet the contacts in the CRM. It works fine, but how can I add also the property of the Associated Company?

var url_query = API_URL + "/contacts/v1/lists/all/contacts/all";

var firstName = (item.properties.hasOwnProperty('firstname')) ? item.properties.firstname.value : "NA";
var lastName = (item.properties.hasOwnProperty('lastname')) ? item.properties.lastname.value : "NA";
var company = (item.properties.hasOwnProperty('company')) ? item.properties.company.value : "NA";
var email = "NA";

data.push([vid,firstName,lastName,company,email]);

[olga_rusnak]

Hi Espe, I see that you are asking for help with Apps script.

However, you can achieve the result by using Coupler.io (and no coding required).

Here is what you can do:

• Set up integration between HubSpot and Google Sheets
• Pull Contacts data and it will include the Associated company id and company as well.

You can configure your integration to run every hour, day, month (just set up a schedule that you need it to work).

[johnelmer]

If you are looking for a one-time solution, you can export all of your CRM data to Excel and open the file in Google Sheets. Here's a link to another community post about the free Ultimate Data Export app. NEW! Export raw web events associated with HubSpot CRM data 

[Espe]

This is a manual solution, not an auto integrator that auto update the data in google sheet. 

[quentin_lamamy]

There is no limitation on contact export / import on my side. @dennisedson 

Yes your export will be slow if you make it one request by one request. Use async request to speed up.

I don't understandd how you can have a limitation on number of execution of your own script, it make no sense

[Espe]

Hi @quentin_lamamy,

unfortunately not.

Hubspot is limited to exporting a maximum of 10,000 records and it is very slow by the way.

As if that were not enough, the script has a limited number of executions per day.

[quentin_lamamy]

@EspeDo you succeed to resolve your issue ?

[quentin_lamamy]

Like you do in you current code you have to loop over the result of an api request , but this time use

https://api.hubapi.com/companies/v2/companies/paged?hapikey={apiKEy}&properties=name

Here is what to do

1) Run the request to get all commpanies

2) Loop on each page and each result like you already do for contact

    For each company store the id and the name in an array

3) Make the request to get all the contact

4) Loop on each page and each contact

    Foreach contact build your array as usual

    To add the company name you just need to search in your company array the name corresponding to the associatedcompanieid of your contact

[Espe]

@quentin_lamamy as you said, the second one.

[quentin_lamamy]

Well, it depend on which solution you choose

[Espe]

@quentin_lamamy thanks for your reply. And so, how can I edit my code?

[quentin_lamamy]

If it's not a a property of the contact you can't get it through a contact request. This part of the hubspot ui is called an association and can be retrieve with this request on the new api:

https://api.hubapi.com/crm/v3/objects/contacts/{contactId}/associations/companies

or on the old one : https://api.hubapi.com/crm-associations/v1/associations/{contactId}/HUBSPOT_DEFINED/1

BUT

In your case you have 2 solution :

• Make for each contact a request of the company data according to the associatedcompanyid field and retrieve the name
• Make a request to get all companies, store in a table and in your contact loop pick in your table companie information according to the associatedcompanyid

The first choice need lot of request, the second one, manage pagination to get all company in a single array

make your choice 🙂

[Espe]

The field that you can see in the picture is not a property of the contacts. I thought that this field could be the associatedcompany property and not the company ID, but as I said, I need the name of the company for that value in the picture.

[quentin_lamamy]

I'm not sure that this data is a field of the contact object... I need to investigate more, but at the moment with the information you get with the associatedcompany id you can make a request to get the company information and his name

[Espe]

See the photo:

[quentin_lamamy]

@Espe It's associatedcompanyid, you can have a visualisation of the returned dataset with this url :

https://api.hubapi.com/contacts/v1/lists/all/contacts/all?hapikey=<API-KEY>&property=associatedcompa...

You will have to add in your url one "&property=<YOUR-FIELD>" parameters for each field you want to retrieve

[Espe]

Yes, but I don't want the ID of the company but this field:

[quentin_lamamy]

You question was about retrieving the Associated Company field, this field is just an id. What do you really want ?

[Espe]

Can you insert this string in my code?

[quentin_lamamy]

---

@Espe wrote:

Can you insert this string in my code?

---

Here is the code :

var url_query = API_URL + "/contacts/v1/lists/all/contacts/all&property=associatedcompanyid";
if (hasMore)
{
   url_query += "&vidOffset="+offset;
}

I added the new parameters in the url api, and now because the url already have a parameters i change in the following condition the "?" parameters by a "&"

[Espe]

Perfect, but now what is the name of the property of Associated Company to insert in the code?

var xxxxx = (item.properties.hasOwnProperty('xxxxxx')) ? item.properties.xxxxxx.value : "NA";