HubSpot contacts in a live spreadsheet
Add the Contacts table to a sheet as a data source named contacts and its 41 columns become names your formulas, pivot tables and charts use. Sheets reads it from your HubSpot account each time the sheet opens or refreshes.
Each account's columns are its own HubSpot properties, custom ones included. The columns listed here are HubSpot's standard properties; custom objects are tables too (Enterprise).
Formulas on this table
=data.contacts.sum(number_of_associated_deals) Total number of associated deals. Adds up the Number of associated deals column over every row of Contacts.
=data.contacts.sum(number_of_associated_deals, contact_owner, A2) Number of associated deals per contact owner. With a contact owner in A2, the total for that one. Fill it down next to a list of them.
=list.contacts(contact_owner) Every contact owner, once. Spills the distinct contact owner values down the column: the row labels for the formula above.
=data.contacts.count(record_id, contact_owner, "<>") Rows with a contact owner. Counts the rows whose Contact owner is not empty.
=data.contacts.avg(recent_deal_amount) Average recent deal amount. avg, min, max, count, countd (distinct) and first work the same way as sum.
Reports that use it
=data.contacts.count(record_id, lifecycle_stage, A2) Contacts per lifecycle stage. How many contacts are subscribers, leads, customers and so on. Put a stage in A2.
Columns
| Column | In formulas | Type |
|---|---|---|
| Record ID | record_id | Text |
| City | city | Text |
| Company name | company_name | Text |
| Contact owner | contact_owner | Text |
| Country/Region | country_region | Text |
| Create date | create_date | Date and time |
email | Text | |
| First name | first_name | Text |
| Industry | industry | Text |
| Job title | job_title | Text |
| Last activity date | last_activity_date | Date and time |
| Last contacted | last_contacted | Date and time |
| Last modified date | last_modified_date | Date and time |
| Last name | last_name | Text |
| Lead status | lead_status | Text |
| Lifecycle stage | lifecycle_stage | Text |
| Marketing contact status | marketing_contact_status | Text |
| Mobile phone number | mobile_phone_number | Text |
| Number of associated deals | number_of_associated_deals | Number |
| Original traffic source | original_traffic_source | Text |
| Phone number | phone_number | Text |
| Recent deal amount | recent_deal_amount | Number |
| State/Region | state_region | Text |
| Total revenue | total_revenue | Number |
| Website URL | website_url | Text |
| Associated companies | associated_companies | Text |
| Associated company IDs | associated_company_ids | Text |
| Company ID | company_id | Text |
| Company: Company name | company_company_name | Text |
| Company: Company domain name | company_company_domain_name | Text |
| Company: Industry | company_industry | Text |
| Company: Country/Region | company_country_region | Text |
| Company: City | company_city | Text |
| Company: Number of employees | company_number_of_employees | Number |
| Company: Annual revenue | company_annual_revenue | Number |
| Company: Lifecycle stage | company_lifecycle_stage | Text |
| Company: Company owner | company_company_owner | Text |
| Associated deals | associated_deals | Text |
| Associated deal IDs | associated_deal_ids | Text |
| Associated tickets | associated_tickets | Text |
| Associated ticket IDs | associated_ticket_ids | Text |
Other crm tables
Your contacts, live
Contacts, companies, deals, tickets and activities become tables that formulas, pivot tables and charts read directly, opened inside HubSpot. Your numbers stay current without exporting.