Exporting Data via API

[taran42]

I am looking to have an automated script that I will export some of my data from Hubspot (Companies, Contacts and Deals) via the API and save that data in a separate Excel file for each. I looked into the API tutorials that Hubspot had to offer and it looks like using the API will export the data I want into a JSON format. How then can I take that data and transfer it into an Excel document?

I have seen a similar extract performed in PowerShell and Python, but I am not versed enough in either of those to write the script for myself.

[ytulsi]

Certain types of exports can now be done via API. This feature is in public beta. More information here: https://developers.hubspot.com/docs/api/crm/exports 

[DduPlessis]

Hi @Mike_Eastwood 

Is there a way to pull relevant data from HubSpot to an Adobe integration to fill in a Certificate template? Like for example your First and Last name

[johnelmer]

There is now an app in the HubSpot App Marketplace called Ultimate Data Export for HubSpot. You can export the following data tables and their associations directly to Microsoft Excel:

• Contacts
• Web Analytics
• Companies
• Deals
• Deal History
• Owners
• Campaigns
• Engagements
• Emails
• Products
• Tickets
• Forms
• and more related subdata such as Pipelines and Associations.

Please mark this as a solution if it helps address your needs.

John

[dreamerdad]

Can someone clarify the API query parameters I should use to pull utm source data as shown below? Alternatively, I'd like to schedule weekly reports to my email address with the report being sent in the form of a CSV attachment and then auto-pull that csv attachment into a google sheet (via google apps script) and then use that google sheet as a data source in our Looker data studio report. Is there any way to do the former? Any way to do the latter using Ultimate Data Export for HubSpot? I would prefer to use the Hubspot API directly rather than via Ultimate Data Export for HubSpot

[shoes2334]

Hi John. 

Thanks so much for this awesome product.

Is there a way to use your product directly through an API for automation?

Thanks!

[johnelmer]

@shoes2334  There are a couple of options in the HubSpot App Marketplace that can meet your needs.  One option is the Excel Integration for HubSpot.  Using this app, your HubSpot data can be set up to refresh in Excel on a regularly scheduled basis. 

[Mike_Eastwood · Accepted solution]

Hi @taran42 

You'd need a script to convert the JSON data to CSV or Excel – it's not rocket surgery but it would require custom code.

Our team builds Custom HubSpot Integrations, please contact me if you'd like to know more.

Cheers

Mike

[taran42]

Mike, thanks for the reply. Do you know if there is a way to pull Hubspot data directly into a CSV? Or is the only output from Hubspot JSON (and then converting that)?

[DduPlessis]

Hi @Mike_Eastwood 

Is there a way to pull relevant data from HubSpot to an Adobe integration to fill in a Certificate template? Like for example your First and Last name

[Mike_Eastwood]

Hi @taran42 

You can manually export a spreadsheet from HubSpot.

Or use the APIs to get JSON.

Working with CSVs programatically is flakey so JSON is a much better format.

Have fun

Mike

[taran42]

Mike, thank you for your help. I was able to pull the JSON from Hubspit via the API in Python and then converted it to a CSV with the Pandas module.

[GKasireddy]

Hi taran42,

How to pull contacts list data from HubSpot into Excel file via API. Please provide any API you are using?

I am using the below API for fetching contacts list.

https://api.hubapi.com/crm/v3/objects/contacts

Thank you.

[taran42]

@GKasireddy The API you linked should work for getting the data out of Hubspot (I used v2). Once you get the JSON file file, you can use Python to convert that into an Excel file or another tool. Ultimately I brought the JSON data into Alteryx as a BLOB and converted it there.

[GKasireddy]

Thanks taran42. It is working.