QuickBooks Online chart of accounts in a live spreadsheet
Add the Chart of accounts table to a sheet as a data source named chart_of_accounts and its 15 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.chart_of_accounts.sum(balance) Total balance. Adds up the Balance column over every row of Chart of accounts.
=data.chart_of_accounts.sum(balance, account_no, A2) Balance per account no.. With a account no. in A2, the total for that one. Fill it down next to a list of them.
=list.chart_of_accounts(account_no) Every account no., once. Spills the distinct account no. values down the column: the row labels for the formula above.
=data.chart_of_accounts.count(id, account_no, "<>") Rows with a account no.. Counts the rows whose Account no. is not empty.
=data.chart_of_accounts.avg(balance_with_sub_accounts) Average balance with sub-accounts. 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 |
| Full name | full_name | Text |
| Account no. | account_no | Text |
| Classification | classification | Text |
| Type | type | Text |
| Detail type | detail_type | Text |
| Balance | balance | Number |
| Balance with sub-accounts | balance_with_sub_accounts | Number |
| Sub-account | sub_account | Yes / no |
| Parent ID | parent_id | Text |
| Active | active | Yes / no |
| Currency | currency | Text |
| Created at | created_at | Date and time |
| Updated at | updated_at | Date and time |
Other accounting tables
Your chart of accounts, 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.