Step 1: sync the Profit and Loss report
- Install SheetsSync, open Extensions > SheetsSync > Open SheetsSync and connect QuickBooks Online.
- Rename an empty tab to
PL This Yearand open Configure This Sheet. - Choose the Profit & Loss report.
- Set the date range to From Date to Today and pick January 1 of this year. The end date always stays today, so you never have to edit it.
- Choose the Accounting Method: Accrual (the default) or Cash.
- Click Sync.
The tab fills with one row per income and expense account and its amount for the period. SheetsSync writes the account rows and leaves out QuickBooks' subtotal lines (such as Total Income), so you can total things your own way in step 4.
Step 2: make it update itself
On the Free plan the tab refreshes when you click Sync, which is still much faster than exporting. To have it refresh on its own you need Pro:
- Open Configure This Sheet on the P&L tab and go to the sync settings.
- Turn on auto-sync and choose hourly, every 6 hours, daily at a set hour, or weekly on a set day.
Hourly is the fastest schedule, because Google lets add-ons run timed jobs at most once an hour. For a P&L, daily is usually plenty. Webhook sync does not apply to reports, so a schedule is the way to keep a report current.
Pro is $19.99/month or $159.99/year and starts with a 14-day free trial that does not ask for a card. If you don't add a card by the end, the account goes back to Free and the tab keeps its last synced numbers.
Step 3: this year vs last year
Add a second tab named PL Last Year, configure it with the same Profit & Loss report and a Custom Range covering the same dates last year (for a full year, January 1 to December 31). Both tabs use the same report, so this works on Free too (up to 3 configured sheets).
On a third tab, PL Compare, list every account from both periods and look up each amount:
A2: =SORT(UNIQUE(FILTER({'PL This Year'!A2:A; 'PL Last Year'!A2:A}, {'PL This Year'!A2:A; 'PL Last Year'!A2:A}<>"")))
B2: =IFERROR(XLOOKUP(A2, 'PL This Year'!A:A, 'PL This Year'!B:B), 0)
C2: =IFERROR(XLOOKUP(A2, 'PL Last Year'!A:A, 'PL Last Year'!B:B), 0)
D2: =B2-C2
E2: =IF(C2=0, "", B2/C2-1)Fill B2:E2 down, format E as a percentage, and you have a year-over-year P&L that refreshes with the source tabs.
Step 4: total income, expenses and net income
Because the synced tab has account rows only, you decide which accounts are income and which are expenses. Two ways to do it:
With a mapping tab (works on Free)
Create a PL Map tab with account names in column A and Income or Expense in column B. Then:
Total income: =SUMPRODUCT(('PL This Year'!B2:B) * (IFERROR(XLOOKUP('PL This Year'!A2:A, 'PL Map'!A:A, 'PL Map'!B:B), "")="Income"))
Total expenses: =SUMPRODUCT(('PL This Year'!B2:B) * (IFERROR(XLOOKUP('PL This Year'!A2:A, 'PL Map'!A:A, 'PL Map'!B:B), "")="Expense"))
Net income: =total income - total expensesWith the chart of accounts (Pro)
Sync the Accounts data type to a tab named Accounts with Name in A and Classification in D (see exporting the chart of accounts). Classification is Revenue for income accounts and Expense for expense accounts, so the lookup fills itself:
=SUMPRODUCT(('PL This Year'!B2:B) * (IFERROR(XLOOKUP('PL This Year'!A2:A, Accounts!A:A, Accounts!D:D), "")="Revenue"))Swap "Revenue" for "Expense" to total expenses. This needs two data types (the P&L report and Accounts), which is a Pro feature. If two accounts share a name under different parents, match on the mapping tab instead.
Check your numbers: compare net income with the Profit and Loss report in QuickBooks for the same dates and accounting method. If they differ, look for accounts missing from the mapping.
What to know
- Each P&L tab covers one date range. For monthly columns, add a tab per month or use the Transaction List report with a pivot table.
- The Balance Sheet report works the same way and pairs well with this sheet. Both reports are read-only.
- Keep formulas on separate tabs. Each sync rewrites the synced tab.
- SheetsSync works with every QuickBooks Online plan; QuickBooks Desktop is not supported.
Want charts on top? The QuickBooks dashboard guide shows how to build one from synced tabs.