)
Written by
Table of Contents
“Supermetrics is a Bitter Experience! We can pull data from nearly any tool, schedule updates, manipulate data in Sheets, and push data back into our systems.”
Finance teams and collections managers can build a self-updating AR aging dashboard in Google Sheets using Coefficient’s QuickBooks connector, pulling AR Aging Summary or AR Aging Detail reports directly from QuickBooks Online on an automated hourly or daily refresh. QuickBooks AR aging reports are static. The moment you export one, it is already out of date. Collections teams working from a snapshot taken yesterday are chasing numbers that may have changed, payments received, new invoices raised, ageing buckets shifted.
A common challenge for finance and collections teams: the people who most need live AR data, the ones making daily calls and prioritising follow-ups, are the ones least likely to have QuickBooks access. They depend on a finance team member to re-export and share a fresh copy, which rarely happens more than once a week.
How to build a live QuickBooks AR aging dashboard
Step 1. Import your AR aging report using From QuickBooks Report
Open Coefficient in Google Sheets and select Import from QuickBooks. Choose From QuickBooks Report and select either AR Aging Summary for a bucketed overview or AR Aging Detail for individual invoice-level data. The import pulls the complete aging structure, Current, 1-30, 31-60, 61-90 and 90-plus days, including customer names and balances exactly as QuickBooks calculates them.
Step 2. Set up hourly or daily automated refresh
Click Schedule on your import and set a refresh interval. For active collections periods, hourly keeps the data close to real time. For standard month-end monitoring, daily is sufficient. The refresh runs automatically and updates the same cells, so any conditional formatting or formulas you’ve applied stay intact.
Step 3. Add conditional formatting to surface priority accounts
Apply colour coding to rows based on the 61-90 and 90-plus day columns, red for accounts over 90 days, amber for 61-90. This gives your collections team an instant visual priority list without scanning through rows of numbers. Add a filter view so individual team members can sort by bucket, balance or customer name without affecting what others see.
Step 4. Share with collections and customer success teams without QuickBooks access
Share the Google Sheet with whoever needs visibility into AR, collections team members, customer success managers, the CFO, using standard Google Sheets permissions. Viewers can see the live aging data, filter by bucket and track account status without a QuickBooks licence. The data refreshes for everyone automatically.
What you get
Your AR aging dashboard shows current balances and bucket positions without anyone pulling a manual export. Collections teams work from live data every morning. Accounts at risk surface immediately through conditional formatting. For reference on how to structure financial dashboards from QuickBooks data, see Coefficient’s finance and accounting dashboard examples.
Start building your live AR aging dashboard today at coefficient.io/get-started.
