OVERVIEW

1. Setup tracker sheet - 10 columns with formulas for days overdue and status

2. Install Apps Script - automated reminder emails at 30/60/90 days

3. Configure weekly trigger - runs automatically every Monday morning

RESOURCES

Good tools to make life easier. You’re welcome :)

• Coefficient - Live spreadsheet connections to 60+ business systems

• Lido - Automate your spreadsheets; accurately extract date from PDFs

• HubSpot - all-in-one CRM solutions for businesses of every size.

• Quickbooks - accounting software…still industry standard

• TransactionPro - quickly import, export or delete data in Quickbooks

• beehiiv - my choice for a newsletter operating system

• Carrd - free one-page website builder

• Notion - notetaking + project management + database

• Transistor - my favorite podcast host

• Fathom - AI-powered notetaking app

Automated Invoice Payment Reminder System

Welcome to Got Sheet! This week, we're building an automation together using Apps Script.

This Week's Build: Payment Reminders Automated

The Problem: Manual follow-ups for past due invoices.

What You'll Build: An automated system that tracks invoice status, sends payment reminders at 30/60/90 days, and maintains a complete payment history.

Business Impact:

• Save time every week on payment follow-ups

• Reduce payment delays through consistent reminders

• Never miss following up on overdue invoices

Time Investment: hour or two to set up

Before We Start

Prerequisites:

• Google Sheets access

• Gmail account (for sending reminders)

• Basic spreadsheet skills (SUM, IF functions)

Tools Needed:

• Google Sheets

• Apps Script (free Google tool)

What You'll Have by the End: A complete invoice tracking system that automatically sends professional payment reminders and updates client payment history.

Step 1: Create Your Invoice Tracker Sheet

Create a new Google Sheet with these columns:

Add this formula to cell H2 (Days Overdue):

=IF(G2="PAID", 0, IF(TODAY()>E2, TODAY()-E2, 0))

Add this formula to cell G2 (Status):

=IF(F2="", "", IF(J2>0, "OVERDUE", IF(TODAY()>E2, "OVERDUE", "SENT")))

Copy these formulas down to all rows where you'll track invoices.

Step 2: Set Up Automated Reminders

Access Apps Script:

1. In your sheet: Extensions → Apps Script

2. Delete default code

3. Paste this complete script:

function sendPaymentReminders() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const data = sheet.getDataRange().getValues();
  
  // Skip header row
  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    const invoiceNum = row[0];
    const clientName = row[1];
    const email = row[2];
    const dueDate = new Date(row[4]);
    const amount = row[5];
    const status = row[6];
    const daysOverdue = row[7];
    const lastReminder = row[8];
    const reminderCount = row[9] || 0;
    
    // Skip if paid or no email
    if (status === "PAID" || !email || !invoiceNum) continue;
    
    const today = new Date();
    const daysSinceLastReminder = lastReminder ? 
      Math.floor((today - new Date(lastReminder)) / (1000 * 60 * 60 * 24)) : 999;
    
    // Send reminder if 30, 60, or 90 days overdue and hasn't been reminded in 7 days
    if ((daysOverdue >= 30 && daysOverdue < 60 && reminderCount === 0) ||
        (daysOverdue >= 60 && daysOverdue < 90 && reminderCount === 1) ||
        (daysOverdue >= 90 && reminderCount === 2)) {
      
      if (daysSinceLastReminder >= 7) {
        sendReminderEmail(invoiceNum, clientName, email, amount, daysOverdue, reminderCount + 1);
        updateReminderData(i + 1, today, reminderCount + 1);
      }
    }
  }
}

function sendReminderEmail(invoiceNum, clientName, email, amount, daysOverdue, reminderLevel) {
  let subject, body;
  
  if (reminderLevel === 1) {
    subject = `Friendly Reminder: Invoice ${invoiceNum} - ${clientName}`;
    body = `Hi ${clientName},\n\nI hope you're doing well! This is a friendly reminder that invoice ${invoiceNum} for $${amount} is now ${daysOverdue} days past due.\n\nIf you've already sent payment, please disregard this message. If you have any questions about this invoice, please let me know.\n\nThank you!\n\nBest regards`;
  } else if (reminderLevel === 2) {
    subject = `Second Notice: Invoice ${invoiceNum} - ${clientName}`;
    body = `Hi ${clientName},\n\nThis is a second reminder that invoice ${invoiceNum} for $${amount} is now ${daysOverdue} days overdue.\n\nPlease prioritize this payment or contact me if there are any issues we need to discuss.\n\nThank you for your prompt attention to this matter.\n\nBest regards`;
  } else {
    subject = `URGENT: Invoice ${invoiceNum} - ${clientName}`;
    body = `Hi ${clientName},\n\nInvoice ${invoiceNum} for $${amount} is now ${daysOverdue} days overdue. This is our final automated reminder.\n\nPlease contact me immediately to resolve this matter.\n\nBest regards`;
  }
  
  GmailApp.sendEmail(email, subject, body);
}

function updateReminderData(rowNum, date, count) {
  const sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(rowNum, 9).setValue(date); // Last Reminder
  sheet.getRange(rowNum, 10).setValue(count); // Reminder Count
}

4. Save with Ctrl+S

5. Name your project "Invoice Reminder System"

Step 3: Set Up Automatic Scheduling

Create Time-Based Trigger:

1. In Apps Script: Triggers (clock icon in left sidebar)

2. Click "+ Add Trigger"

3. Configure:

   • Function: sendPaymentReminders

   • Event source: Time-driven

   • Type: Week timer

   • Day: Monday

   • Time: 9am-10am

4. Save trigger

5. Authorize permissions when prompted

Step 4: Test Your System

Manual Test:

1. Add a test invoice with due date 35 days ago

2. In Apps Script: Run → sendPaymentReminders

3. Check your sent folder for the reminder email

4. Verify the sheet updated "Last Reminder" and "Reminder Count"

Update Payment Status: When invoices are paid, simply change Status column to "PAID" - the system will stop sending reminders automatically.

Customization Ideas

For Service Businesses:

• Add "Project" column to track which project the invoice relates to

• Include project details in reminder emails

For Product Businesses:

• Add "Product" column for inventory tracking

• Link to your order management system

For Agencies:

• Add "Contract Value" to track total client relationship value

• Create separate reminder templates for different client tiers

Advanced Options:

• Add SMS reminders using Twilio integration

• Create a client portal where customers can view outstanding invoices

• Integrate with QuickBooks or other accounting software

Troubleshooting

Emails Not Sending:

• Check Gmail quotas (100 emails per day for free accounts)

• Verify email addresses are correctly formatted

• Check spam folder for test emails

Formulas Not Working:

• Ensure date columns are formatted as dates, not text

• Check for empty cells that might break calculations

• Use absolute references if copying formulas

Script Errors:

• Check trigger permissions in Apps Script

• Verify sheet column order matches the script

• Test with a small dataset first

Quick Implementation Tips

Pro Shortcut #1: Use data validation on the Status column to prevent typos. Set up a dropdown with options: SENT, PAID, OVERDUE. Add functionality in the Apps Script to set to PAID if you do this.

Pro Shortcut #2: Add conditional formatting to highlight overdue invoices in red. This gives you instant visual feedback on your sheet.

What's Next?

Well, I’ve got some ideas, but I’d love to hear yours as well.

Reply to this - I read every response. Let me know what project you would benefit from, and it may be included in a future walkthrough.

NEXT STEPS

Whenever you’re ready, here’s how I can help:

1. Work with me
   I am available for consulting projects. Reach out and let’s chat.

2. Business tech stack (FREE)
   My recommendations for software and tools I use to create content and run my online business.

3. Sponsor Got Sheet
   Got Sheet teaches business operators, teachers and entrepreneurs how to get good at spreadsheets. Interested in sponsoring an issue or a series of Got Sheet newsletters? Reach out to get more information.

4. Personal budget tool
   As a Got Sheet subscriber, I want you to have a 50% discount on the personal finance system I update every year.

5. YouTube

   If you aren’t subscribed yet, come on over to my YouTube channel where I make all my spreadsheet, coding and productivity tutorials

HAPPY SPREADSHEETING!

Enjoyed this issue?
Subscribe below or Leave a testimonial

Cheers, Eamonn
Connect on LinkedIn, YouTube & Twitter.