How to Create an Automatic Invoice Tracker in Google Sheets

The problem

You have invoices to track. Client name, amount, due date, status. Right now they live in a spreadsheet, a folder of PDFs, your email inbox, or some combination of all three. When a payment is late, you find out by accident. When your bookkeeper asks for a summary, you spend 20 minutes pulling it together.

Most freelancers and small business owners start with a basic Google Sheet. A row per invoice, a few columns, maybe some conditional formatting. That works until you have 30 or 40 open invoices and no reliable way to see what's overdue, who needs a follow-up, or what your total outstanding balance is.

This guide builds an invoice tracker in Google Sheets that actually works at scale. It starts as a clean spreadsheet with the right structure. Then it adds automation: overdue alerts, status updates, email reminders, and optional PDF export. You can use as much or as little of the automation as you want.

What we're building

A Google Sheet with these capabilities:

1. A structured invoice log with client name, invoice number, amount, issue date, due date, status, and payment date
2. Automatic overdue detection (status turns red when past due)
3. A dashboard row showing total invoiced, total paid, total outstanding, and total overdue
4. Optional: automatic email reminders for invoices approaching their due date
5. Optional: PDF generation from invoice data (covered in a linked tutorial)

The base spreadsheet takes about 10 minutes to set up. The automation layers add another 10 to 15 minutes each.

Step 1: Build the invoice log

Create a new Google Sheet. Name the first tab "Invoices."

Set up these column headers in row 1:

• A: Invoice Number
• B: Client Name
• C: Client Email
• D: Amount
• E: Issue Date
• F: Due Date
• G: Status
• H: Payment Date
• I: Days Until Due
• J: Notes

Format column D as currency. Format columns E, F, and H as dates.

For the Status column (G), set up data validation. Click the column header, go to Data > Data validation, and add a dropdown with these options:

• Draft
• Sent
• Paid
• Overdue
• Cancelled

This gives you a clean dropdown on every row instead of free-text entry.

Step 2: Add the "Days Until Due" formula

In cell I2, enter this formula:

=IF(G2="Paid", "", IF(F2="", "", F2 - TODAY()))

This shows how many days until the invoice is due. Negative numbers mean it's overdue. If the invoice is already paid, the cell stays blank. Drag this formula down to cover your data range, or use ARRAYFORMULA to fill it automatically:

=ARRAYFORMULA(IF(G2:G="Paid", "", IF(F2:F="", "", F2:F - TODAY())))

Put that in I2 and it fills every row automatically.

Step 3: Auto-detect overdue invoices

You want the Status column to flip to "Overdue" automatically when an invoice passes its due date without being paid.

This requires a small script. Open Extensions > Apps Script and paste this:

function checkOverdue() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Invoices');
  var lastRow = sheet.getLastRow();
  if (lastRow < 2) return;

  var data = sheet.getRange(2, 1, lastRow - 1, 8).getValues();
  var today = new Date();
  today.setHours(0, 0, 0, 0);

  for (var i = 0; i < data.length; i++) {
var status = data[i][6];  // column G
var dueDate = data[i][5]; // column F

if (status === 'Sent' && dueDate instanceof Date) {
  var due = new Date(dueDate);
  due.setHours(0, 0, 0, 0);
  if (due < today) {
    sheet.getRange(i + 2, 7).setValue('Overdue');
  }
}
  }
}

function onOpen() {
  SpreadsheetApp.getUi()
.createMenu('Invoice Tools')
.addItem('Check for overdue invoices', 'checkOverdue')
.addToUi();
}

Save the script. Run onOpen once to authorize it. Now you have an "Invoice Tools" menu in your Sheet.

To make it automatic, set up a daily trigger: click the clock icon in the Apps Script sidebar, add a trigger for checkOverdue, set it to "Time-driven" > "Day timer" > pick a time (early morning works well). Every day, the script scans your invoices and marks any past-due items as Overdue.

Step 4: Add a dashboard summary

Create a row at the top of your sheet (or a separate "Dashboard" tab) with these formulas:

Total Invoiced:

=SUMIF(G:G,"<>Cancelled",D:D)

Total Paid:

=SUMIF(G:G,"Paid",D:D)

Total Outstanding:

=SUMIF(G:G,"Sent",D:D)

Total Overdue:

=SUMIF(G:G,"Overdue",D:D)

Number of Open Invoices:

=COUNTIFS(G:G,"<>Paid",G:G,"<>Cancelled",G:G,"<>Draft",G:G,"<>")

These update in real time as you add invoices and change statuses.

Step 5: Add conditional formatting

Make overdue invoices visually obvious. Select the Status column (G), go to Format > Conditional formatting, and add these rules:

• If text is "Overdue" > set background to light red
• If text is "Paid" > set background to light green
• If text is "Sent" > set background to light blue

You can also apply conditional formatting to the entire row based on the Status column. Use a custom formula rule on the full data range: =($G2="Overdue") with a light red background.

Step 6 (optional): Automatic email reminders

This is the part that saves real time. Instead of manually checking which invoices are almost due and sending follow-ups, the script does it for you.

Add this function to your Apps Script:

function sendReminders() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Invoices');
  var lastRow = sheet.getLastRow();
  if (lastRow < 2) return;

  var data = sheet.getRange(2, 1, lastRow - 1, 10).getValues();
  var today = new Date();
  today.setHours(0, 0, 0, 0);

  var reminderDays = 3; // send reminder this many days before due date

  for (var i = 0; i < data.length; i++) {
var invoiceNum = data[i][0];  // column A
var clientName = data[i][1];  // column B
var email      = data[i][2];  // column C
var amount     = data[i][3];  // column D
var dueDate    = data[i][5];  // column F
var status     = data[i][6];  // column G
var notes      = data[i][9];  // column J

if (status !== 'Sent' || !(dueDate instanceof Date)) continue;
if (!email || !email.toString().includes('@')) continue;

var due = new Date(dueDate);
due.setHours(0, 0, 0, 0);
var daysLeft = Math.floor((due - today) / (1000 * 60 * 60 * 24));

if (daysLeft <= reminderDays && daysLeft >= 0) {
  var subject = 'Payment Reminder: Invoice ' + invoiceNum;
  var body = 'Hi ' + clientName + ',\n\n'
    + 'This is a friendly reminder that invoice ' + invoiceNum
    + ' for $' + Number(amount).toLocaleString('en-US', {minimumFractionDigits: 2})
    + ' is due on ' + Utilities.formatDate(due, Session.getScriptTimeZone(), 'MMMM d, yyyy') + '.\n\n'
    + 'If you have already sent payment, please disregard this message.\n\n'
    + 'Thank you,\n[Your Name]';

  MailApp.sendEmail(email, subject, body);

  // Mark that a reminder was sent
  sheet.getRange(i + 2, 10).setValue(
    (notes ? notes + '\n' : '') + 'Reminder sent ' + new Date().toLocaleDateString()
  );
}
  }
}

Set reminderDays to however many days before the due date you want the reminder sent. Add a daily trigger for sendReminders the same way you did for checkOverdue.

Update your onOpen function to include the new menu item:

function onOpen() {
  SpreadsheetApp.getUi()
.createMenu('Invoice Tools')
.addItem('Check for overdue invoices', 'checkOverdue')
.addItem('Send payment reminders', 'sendReminders')
.addToUi();
}

How to extend this

Add PDF export. Generate a formatted PDF invoice from each row and email it to the client. This uses the same pattern covered in our Sheets-to-PDF tutorial. Combine it with this tracker and you have a complete invoicing system inside Google Sheets.

Connect to Google Forms. If clients submit project requests through a form, you can auto-create an invoice row from each form submission. The form-to-doc tutorial covers this pattern.

Add a client summary view. Create a second tab that uses QUERY to show all invoices grouped by client, with a running total per client. Useful for seeing your biggest accounts at a glance.

Track partial payments. Add a "Paid Amount" column next to Amount. Change the Paid status logic to check if Paid Amount equals Amount, and add a "Partial" status for invoices where some but not all of the balance has been received.

Common issues

Overdue detection doesn't work. Make sure your Due Date column contains actual dates, not text that looks like dates. Click a cell and check the formula bar. If it shows 2026-03-15 without quotes, it's a date. If it shows '2026-03-15 with a leading apostrophe, it's text. Reformat the column as Date.

Email reminders aren't sending. Check the Apps Script executions log (click Executions in the sidebar). Common causes: the email address column is empty, the status isn't exactly "Sent" (check for extra spaces), or you've hit Google's daily email limit.

Dashboard formulas show wrong numbers. SUMIF and COUNTIFS are case-insensitive, but they need exact text matches. If your dropdown says "Paid" but someone typed "paid" manually, the formulas will miss it. Stick to the dropdown.

Conditional formatting isn't showing. Make sure the rules are applied to the correct range and that you're using "Text is exactly" rather than "Text contains" for status matching.