HubSpot line items in a live spreadsheet
Add the Line items table to a sheet as a data source named line_items and its 24 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.line_items.sum(net_price) Total net price. Adds up the Net price column over every row of Line items.
=data.line_items.sum(net_price, billing_frequency, A2) Net price per billing frequency. With a billing frequency in A2, the total for that one. Fill it down next to a list of them.
=list.line_items(billing_frequency) Every billing frequency, once. Spills the distinct billing frequency values down the column: the row labels for the formula above.
=data.line_items.count(record_id, billing_frequency, "<>") Rows with a billing frequency. Counts the rows whose Billing frequency is not empty.
=data.line_items.avg(quantity) Average quantity. avg, min, max, count, countd (distinct) and first work the same way as sum.
Columns
| Column | In formulas | Type |
|---|---|---|
| Record ID | record_id | Text |
| Billing frequency | billing_frequency | Text |
| Create date | create_date | Date and time |
| Description | description | Text |
| Last modified date | last_modified_date | Date and time |
| Name | name | Text |
| Net price | net_price | Number |
| Product ID | product_id | Text |
| Quantity | quantity | Number |
| SKU | sku | Text |
| Term | term | Text |
| Total discount | total_discount | Number |
| Unit cost | unit_cost | Number |
| Unit discount | unit_discount | Number |
| Unit price | unit_price | Number |
| Associated deals | associated_deals | Text |
| Associated deal IDs | associated_deal_ids | Text |
| Deal ID | deal_id | Text |
| Deal: Deal name | deal_deal_name | Text |
| Deal: Deal stage | deal_deal_stage | Text |
| Deal: Pipeline | deal_pipeline | Text |
| Deal: Amount | deal_amount | Number |
| Deal: Close date | deal_close_date | Date and time |
| Deal: Deal owner | deal_deal_owner | Text |
Other commerce tables
Your line items, 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.