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.

[zafarkahn]

Nice

[RobertYoung]

If you're aiming to export a lot of data regularly, building your own custom script can definitely work, but you'll need to manage pagination and error handling carefully.

Alternatively, using a third-party tool like Skyvia might save you some headaches—it handles those API quirks behind the scenes and lets you automate the exports easily.

[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

[JasonTisdall]

Hi John

We would like to be able to triggere regular exports of Deal dat from various pipelines. Other spreadsheets within our systems can then pick this data up automatically.

Is that something you can help with?

cheers Jason

[johnelmer]

@JasonTisdall Ultimate Data Export was developed to be a one-time manually triggered export of virtually all HubSpot data available via the APIs. It comes to you as an Excel file via email.  It's not the right long-terms solution regular data exports. 

I suggest looking at our app in the HubSpot App Marketplace called MS SQL Server Integration for HubSpot. Rather than exporting your data to Excel like UDE, this integration automatically extracts, transfers and loads your HubSpot data into your own secure SQL Server database. (The SQL Server data warehouse in Azure is included in the subscription cost and is automatically configured when you purchase a subscription. Subscriptions allow for unlimited users at a flat rate.)

Once your HubSpot data is synchronized with the SQL Server data warehouse, the SQL database automatically refreshes from HubSpot on a scheduled basis (several refresh rates are available). You can connect this SQL database to any other application that has a SQL connector (such as Microsoft Excel).  

[JasonTisdall]

John
Thanks. I'll look into it
Jason

[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.