How to Sync QuickBooks Invoices to Google Sheets: Fields, Filters and AR Aging

Invoices are where most receivables questions start: who owes what, how late they are, and which customers need a reminder call. QuickBooks Online answers some of that in its own reports, but as soon as you want your own aging buckets, a collections list for the team, or numbers that people without a QuickBooks login can see, the invoice data needs to be in a spreadsheet.

This guide is specifically about invoices. It covers which QuickBooks invoice fields you can pull into Google Sheets with SheetsSync, how to filter for unpaid or overdue invoices, ready-to-paste formulas for AR aging, an overdue list and balances by customer, and how editing invoices from Sheets works. For a general comparison of export methods, see how to export QuickBooks to Google Sheets.

Short version: install SheetsSync, click Connect QuickBooks in the sidebar, choose Invoice as the data type, pick fields, set the date range to cover your open invoices, and click Sync. Each row is one invoice. On Pro, schedule it to refresh hourly, every 6 hours, daily or weekly.

Which QuickBooks Invoice Fields You Can Sync

Each row in the sheet is one invoice, and you choose which fields become columns. These are the invoice fields SheetsSync offers, with the column label you will see in the sheet:

A new invoice sheet starts with six fields selected: Invoice Number, Date, Customer, Total Amount, Balance and Due Date. That set already covers most receivables work. Add A/R Paid if you want an easy paid or unpaid flag, and ID if you plan to push edits back. You can drag fields to change the column order.

One thing to know up front: the invoice sheet is one row per invoice, not one row per product line. If you need line-by-line detail (items, quantities, rates), use a QuickBooks report or the Transaction List report instead.

Step-by-Step Setup

Tip: keep the synced Invoices sheet for data only and build your formulas on separate tabs. Because each sync rewrites the synced sheet, anything you type next to the data there can be overwritten.

Filter, Sort and Group Your Invoices

Date range

Each sheet has a date range: All Time, Last 7 Days, Last 30 Days, Last 90 Days, Last Year, From Date to Today, or a Custom Range. For invoices, the range applies to the invoice date, not the due date or the date a payment came in. The default is Last 30 Days, which is fine for recent activity but will leave out an unpaid invoice from three months ago. For receivables work, use All Time, Last Year, or From Date to Today with a start date before your oldest open invoice.

Filters

Filters narrow the invoices that are pulled. Text fields can be matched with "equals" or "contains", amounts with =, >, >=, < and <=, and dates with =, after, on or after, before and on or before. If you add several filters, an invoice must match all of them. Useful invoice filters:

• Unpaid invoices only: A/R Paid equals Unpaid, or Balance > 0
• Large open invoices: Balance >= 5000
• One customer: Customer equals the customer's name
• Not yet emailed: Email Status equals NeedToSend
• Due before a date: Due Date before a date you pick

Filter values are fixed, so "overdue as of today" is best handled with a formula on a separate tab (shown below) that uses TODAY(). A filter of Balance > 0 keeps the sheet small and focused on what is still owed.

Sorting and grouping by customer

Choose a sort field and direction for the sheet, for example Due Date ascending so the oldest due invoices come first, or Balance descending to put the biggest open amounts at the top. For totals per customer, sync the invoice rows and group them in Sheets with QUERY or a pivot table, as in the formulas below.

Invoice Workflows with Google Sheets Formulas

The formulas below assume the synced sheet is named Invoices and has these columns, which is the default order when you select these fields: A Invoice Number, B Date, C Customer, D Total Amount, E Balance, F A/R Paid, G Due Date. If your columns differ, change the letters. Put each formula on its own tab, not on the synced sheet.

1. AR aging buckets (0-30, 31-60, 61-90, 90+ days past due)

On a tab named AR Aging, type the bucket names in A2:A6 and these formulas in B2:B6. Each one totals the open Balance of invoices whose Due Date falls in that window, counted back from today:

Paid invoices have a Balance of zero, so they add nothing to any bucket. As a check, the five buckets should add up to =SUM(Invoices!E2:E); if they don't, some open invoices have no due date. Because the formulas use TODAY(), the buckets move forward each day even between syncs, while the balances update on each sync.

2. Overdue invoice list with FILTER

On a tab named Overdue, type headers in row 1 (Invoice, Customer, Due Date, Balance, Days Overdue) and put this in A2. It lists every invoice with money still owed and a due date in the past, most overdue first:

To label each row with its aging bucket, put this in F2 (header "Bucket" in F1):

Share this tab with whoever handles collections. They see exactly which invoices to chase without needing QuickBooks access. Format the Due Date column as a date and Days Overdue as a plain number.

3. Total outstanding by customer

With QUERY, one formula gives each customer's open balance and the number of open invoices, largest first:

If you prefer SUMIF, list the customers with an open balance in A2 and total them in B2:

4. A DSO-style collection check

Days sales outstanding (DSO) estimates how many days of sales are sitting unpaid. A common version is outstanding receivables divided by sales over a period, times the number of days. Using invoice totals for the last 90 days as "sales":

Treat this as a trend indicator rather than an audited figure: invoice totals can include sales tax, and the result only counts invoices inside the synced date range. Set the sheet's range to All Time or Last Year so older open invoices are included in the balance. Track it weekly, and if it climbs, look at the 61-90 and 90+ buckets first.

For charts and a one-page view of revenue, AR and top customers, continue with the QuickBooks dashboard in Google Sheets tutorial, which uses the same column layout.

Keeping Invoice Data Up to Date

How fresh the sheet is depends on how you sync:

• Manual sync (Free and Pro): click Sync in the sidebar, or use Sync This Sheet or Sync All Sheets from the menu, whenever you want current numbers.
• Scheduled auto-sync (Pro): hourly, every 6 hours, daily at an hour you choose, or weekly on a day you choose. It runs without the sheet being open. Hourly is the fastest schedule, because Google limits add-on timers to once an hour.
• Webhook sync (Pro): QuickBooks notifies SheetsSync when an invoice is created or changed. The sheet is flagged and refreshed on the next hourly check, so changed data typically arrives within the hour rather than at the next daily or weekly run.

For a daily collections routine, a daily schedule set to an early hour is usually enough. If your team works the overdue list during the day, use hourly or turn on webhook sync. You can see recent runs under View Sync History.

Editing and Creating Invoices from Sheets (Pro)

With two-way sync on Pro, the invoice sheet is not just a report: you can change rows in Sheets and push those changes to QuickBooks. Two-way sync covers invoices, customers, vendors, bills and items.

Editing existing invoices

Include the ID field so SheetsSync knows which QuickBooks invoice each row belongs to. Turn on two-way sync for the sheet, edit a cell such as Due Date or Memo, then push with Extensions > SheetsSync > Push to QuickBooks > Push This Sheet. A Sync Status column shows each row as Synced, Pending, Error or New, so you can see what went through. Bulk push lets you send many edited rows at once, for example after extending due dates for a group of invoices with a formula.

Creating new invoices

Use Push to QuickBooks > Create New Record... to open a form where you pick the customer, invoice number (or leave it blank to let QuickBooks number it), invoice date, due date, line items (description, quantity, rate) and memo. QuickBooks needs a customer and at least one line item to create an invoice. There is also Create from Selected Rows, and an optional setting to auto-create QuickBooks records from new rows you add to the sheet.

What is never sent back

Some invoice values are calculated by QuickBooks, and SheetsSync never sends them on create or update: Total Amount, Balance, A/R Paid, and the Created and Updated timestamps. Typing a new number in the Balance column does not record a payment; record payments in QuickBooks and the balance updates on the next sync. Payments themselves are pull-only in SheetsSync, as are purchases, accounts, journal entries and reports.

Two-way sync is a Pro feature. On the Free plan, data flows one way, from QuickBooks into Sheets.

Troubleshooting Invoice Syncs

Some invoices are missing

Check the date range first. It filters on the invoice date, and the default is Last 30 Days, so older invoices (including old unpaid ones) are left out. Widen it to All Time, Last Year, or From Date to Today. Then check your filters: several filters must all match, so a leftover filter such as one customer name will hide everything else.

My AR total doesn't match QuickBooks

Usually the same cause: open invoices dated before the start of your date range are not in the sheet. Also check that the sheet has synced since the latest payments were recorded, and that your aging formulas count from the same date as the QuickBooks report you are comparing with.

I can't add a second sheet or data type on Free

The Free plan allows up to 3 configured sheets and one data type (one entity, such as Invoice, or one report). If you want invoices plus payments or customers, that needs Pro, which also unlocks scheduled sync, webhook sync and two-way sync. You can try Pro free for 14 days with no credit card required; if the trial ends without a subscription, the account returns to Free and your sheet settings are kept.

The schedule isn't refreshing

Scheduled and webhook sync are Pro features, and hourly is the fastest interval, so a change made a few minutes ago may not be in the sheet yet. Open View Sync History to see when the last run happened and whether it reported an error. Click Sync to refresh right away.

The QuickBooks connection expired

If syncs fail with a connection or authorization error, open the sidebar, disconnect, and click Connect QuickBooks again to re-approve access.

Pushed edits show Error

Make sure the ID column is in the sheet and hasn't been edited, and that you changed an editable field rather than Total Amount or Balance. Error rows can be corrected and pushed again.

Still stuck? The SheetsSync help center has more fixes and a way to contact support.

Frequently Asked Questions

Related Guides