Why put the chart of accounts in Google Sheets
- Cleanups: sort and filter hundreds of accounts to spot duplicates, inactive accounts that still carry a balance, or accounts with no number.
- Reviews: send a client or partner a readable list of accounts and balances without giving them QuickBooks access.
- Mapping: add your own column that groups accounts into reporting lines, then use it with a Profit and Loss sheet.
- Migrations: keep a dated snapshot before you merge or rename accounts.
Method 1: export the chart of accounts from QuickBooks
- In QuickBooks Online, go to Settings (gear) > Chart of accounts. Some menus show it under Accounting > Chart of accounts.
- Click the Export icon above the list (or Run report to open the Account List report and export from there).
- Choose Excel, then in Google Sheets open File > Import > Upload and insert it as a new sheet.
- Remove the title rows at the top so the headers sit in row 1.
This is free and fine for a one-off. The copy does not update, so balances drift from QuickBooks as soon as new transactions post. Accounts that are made inactive are also left out of the default list.
Method 2: sync the chart of accounts with SheetsSync
- Install SheetsSync, open Extensions > SheetsSync > Open SheetsSync and connect QuickBooks Online.
- On an empty tab, open Configure This Sheet and pick Accounts.
- Tick the fields you want (see below). The defaults are Name, Account Type, Current Balance and Classification.
- To include inactive accounts too, tick the Active field and leave the list unfiltered; to hide them, add a filter on Active.
- Click Sync. Each account becomes one row.
Accounts counts as one data type, so it works on the Free plan if it is the type you pick. On Pro the tab can refresh on a schedule (hourly, every 6 hours, daily or weekly) so balances stay close to QuickBooks. Accounts are read-only in SheetsSync: you can't create or rename accounts from the sheet. Two-way sync covers customers, vendors, invoices, bills and items.
Fields you can include
| Field | What it holds |
|---|---|
| Name | The account name |
| Full Name | Name with parent accounts, for example Utilities:Gas and Electric |
| Account Type | QuickBooks account type, such as Bank, Accounts Receivable, Expense or Income |
| Account Sub-Type | The detail type QuickBooks shows under the type |
| Account Number | The account number, if you use numbers |
| Current Balance | Balance as of the sync |
| Classification | Asset, Liability, Equity, Revenue or Expense |
| Description | The account description |
| Active | Whether the account is active |
| ID, Created, Updated | QuickBooks ID and timestamps, useful for spotting recent changes |
Things to do with it
Assume the synced tab is called Accounts with Name in A, Account Type in B, Current Balance in C and Classification in D.
Total balance by classification (a quick sanity check of assets against liabilities and equity):
=QUERY(Accounts!A:D, "select D, sum(C) where D is not null group by D label sum(C) 'Balance'", 1)Accounts with no number (if you add Account Number in column E):
=FILTER(Accounts!A2:A, Accounts!E2:E="")Your own reporting groups. On a separate Mapping tab, list account names in column A and your group (for example "Payroll" or "Software") in column B. Then pull the group next to any account with =XLOOKUP(A2, Mapping!A:A, Mapping!B:B, "Unmapped"). Keep the mapping on its own tab, because each sync rewrites the Accounts tab.