Unable to export all companies with custom fields + propreties using ZappySys API Tool

[XBeau]

Hey there,

I don't know if any of you are familiar with using the ZappySys API export tool with Hub Spot?

So I followed the steps, everything seems to be working just fine, my connection is there and my app  (that I've created with a dev account on HubSpot) is connected with my main account where all the infos about the companies are.

The thing is, when using different endpoint URLs provided by HS, I'm able to retreive all the companies but only with the basic HubSpot propreties, not with all the custom propreties that the companies currently have in the CRM.

The guy linked them all into different proprety groups that we can actually see in the CRM, but I can't seem to find a way to export them all into my SQL DB.

So, big picture here:

-I am using ZappySys JSon extract data flow task on VS.

-The connection is made and is working.

I am looking for a way to export every companies I have in my CRM along with all the custom propreties they've created in different groups, all related to the "company" Object in HubSpot.

Anyone has an idea?

Thanks alot, I've been looking for a way for a long time now but I literally can't find anything anywhere...

XB

[XBeau · Accepted solution]

So, I wanted to export all companies along with all of their 20 ish custom propreties from my HubSpot CRM using the ZappySys "JSON REST API Source" into my SQL Table.

ZappySys does provide some documentation regarding HubSpot APIs but it's still very limited, and only cover the export with the basic companies properties.

Big Up to @dennisedson for this URL Path:

https://api.hubapi.com/companies/v2/companies/paged&includeAllProperties=true&allPropertiesFetchMode...

To be able to retreive any information from this endpoint, you will need to follow the instruction given in that documentation to connect your HubSpot app with the ZS JSON source tool:

https://zappysys.com/products/ssis-powerpack/ssis-json-file-source/

• After that, you will need to select " $.companies[*].properties " in the array filter box at the bottom of general settings.
• You will now go to the columns tab on the left, and select and lock each and every property.VALUE that you want to be return in your DB.
• Then close the JSON SOURCE and add an OLE DB task and create or associate the result in the table.

When you'll try and run the package, you will now see that it only returns 100 rows, that's normal, it's by default.
Hubspot has a limit of 250 exported rows per API calls, so, obviously, if you have more than 250 rows to export, you'll need to make multiple calls.

In order to limit and minimize the number I added the "limit" param in Denisse's Endpoint:

https://api.hubapi.com/companies/v2/companies/paged?limit=250&includeAllProperties=true&allPropertie...

By doing the same thing, you will now retrieve 250 rows.

BUT WHAT IF I HAVE MORE??!

Yes, it was my case too.

• Take the endpoint up in PostMan and run it there by doing a new request.
  If you have more than 250 rows, the response you'd be getting in the first row would be:

          [has-more: true, offset: XxXxxX]

• 
  So super simple, now you'll need to make a second call using the provided offset number # in your endpoint.

So your new endpoint will look like this:

https://api.hubapi.com/companies/v2/companies/paged?limit=250&offset=XxXxxX&includeAllProperties=tru...

By repeating the process I explained before in a new "JSON REST API" Task with this endpoint, you will now be able to retreive the next 250 rows.

And so on, and so on;

Until the response you're getting is:

[has-more: false]

This means you just retrieved everything you have.

-X.B.

[dennisedson]

@XBeau 

I am not familiar with ZappySys, but I read the doc on connecting with HubSpot.  Looks like they use contacts as an example.  If you replace that example url with this:

https://api.hubapi.com/companies/v2/companies/paged?includeAllProperties=true&includeMergeAudits=true&allPropertiesFetchMode=all_versions&archived=false

What do you get?

[XBeau]

Hey! @dennisedson 

Thanks for the quick answer,

It seems like I'm going to be able to do something with the URL endpoint that you gave me. I'm now able to retreive the value of those said custom propreties in the filter array now.

• First of, I will need to manually create a table in my SQL DB with the proper column names
• Then, by connecting my ZS JSON source tool with your endpoint, I'm going to create multiple "JSON Multicasts" for each and every one of my custom propreties.
• I will then connect all of those into an OLE DB data flow task and manually map every value with the proper column name in my SQL Table.

If you have a more efficient way to do it in mind, please let me know, I'd be a really happy guy! 

Here is the documentation I will use to try and do so : 

https://zappysys.com/blog/extract-read-multiple-arrays-from-json-data-file-rest-api-response/#Read_m...