QuickBooks Online products and services in a live spreadsheet
Add the Products and services table to a sheet as a data source named products_and_services and its 16 columns become names your formulas, pivot tables and charts use. Sheets reads it from your QuickBooks Online company each time the sheet opens or refreshes.
Formulas on this table
=data.products_and_services.sum(sales_price) Total sales price. Adds up the Sales price column over every row of Products and services.
=data.products_and_services.sum(sales_price, type, A2) Sales price per type. With a type in A2, the total for that one. Fill it down next to a list of them.
=list.products_and_services(type) Every type, once. Spills the distinct type values down the column: the row labels for the formula above.
=data.products_and_services.count(id, type, "<>") Rows with a type. Counts the rows whose Type is not empty.
=data.products_and_services.avg(cost) Average cost. avg, min, max, count, countd (distinct) and first work the same way as sum.
Columns
| Column | In formulas | Type |
|---|---|---|
| ID | id | Text |
| Name | name | Text |
| SKU | sku | Text |
| Type | type | Text |
| Description | description | Text |
| Sales price | sales_price | Number |
| Cost | cost | Number |
| Quantity on hand | quantity_on_hand | Number |
| Income account | income_account | Text |
| Income account ID | income_account_id | Text |
| Expense account | expense_account | Text |
| Expense account ID | expense_account_id | Text |
| Taxable | taxable | Yes / no |
| Active | active | Yes / no |
| Created at | created_at | Date and time |
| Updated at | updated_at | Date and time |
Other lists tables
Your products and services, live
Invoices, bills, customers and the chart of accounts become tables that formulas, pivot tables and charts read directly. Your numbers stay current without exporting.