Build an AI-Powered Aged Debt Tracker in Google Sheets for UK SMBs

Why an Aged Debt Tracker is Essential for UK Small Businesses

Let's face it: getting paid on time is absolutely critical for any small business, especially here in the UK. Unpaid invoices, often called aged debt, aren't just an accounting nuisance; they can choke your cash flow, stifle growth, and cause a fair bit of sleepless nights. You've delivered the goods or services, and now you're waiting. It’s frustrating, isn't it?

That's where an effective UK aged debt tracker comes into its own. It gives you a clear, real-time picture of who owes you money, how much, and critically, for how long. Knowing this allows you to act proactively, not reactively, protecting your business's financial health. While many businesses use dedicated accounting software, I've found that for smaller operations or those just starting out, a custom solution built in a flexible tool like Google Sheets can be incredibly powerful and cost-effective. Add a dash of AI, and you've got yourself a surprisingly sophisticated system.

The Power Couple: Google Sheets and AI for UK Aged Debt Tracking

You might be thinking, "Google Sheets? Isn't that just for basic lists?" And you'd be wrong! Google Sheets is far more capable than many give it credit for, especially when paired with its scripting environment, Google Apps Script, and the intelligence of modern AI models. It's an accessible platform you probably already use, meaning zero extra software costs.

We're not just talking about a simple list of invoices here. We're building a dynamic system that can categorise your debts, flag urgent items, and even draft those awkward reminder emails for you. This combination truly helps you automate overdue invoices, freeing up your valuable time. The goal is straightforward: improve your cash flow automation and ensure you're chasing the right invoices at the right time, with minimal manual effort.

Setting Up Your Core Google Sheet: The Foundation

First things first, let's get your Google Sheet organised. Open a new sheet and set up some essential columns. I always recommend keeping things clear and consistent. Here's a suggested layout:

• Invoice Number: Your unique reference for each invoice.
• Client Name: Who owes you money.
• Client Email: For automated reminders.
• Invoice Date: When you issued the invoice.
• Due Date: When payment was expected. This is crucial for calculating aged debt.
• Amount Due (£): The outstanding balance.
• Status: (e.g., Unpaid, Paid, Partially Paid, Chasing).
• Days Overdue: This is where the automation starts.
• Aged Debt Bracket: (e.g., 0-30 days, 31-60 days, 61-90 days, 90+ days).
• Last Reminder Sent: Date of the last automated communication.
• Notes: Any manual observations or actions taken.

Once you have your headers, start populating with your current outstanding invoices. Accuracy here is key, of course.

Automating 'Days Overdue' and 'Aged Debt Bracket'

This is where Google Sheets begins to shine. In your 'Days Overdue' column (let's say it's column H), you'll want a formula that automatically updates based on today's date. Assuming your 'Due Date' is in column E, and 'Status' is in column F, you'd use something like this in H2 and drag down:

=IF(F2="Paid", 0, IF(TODAY()>E2, TODAY()-E2, 0))

This formula does a couple of things: if the invoice is 'Paid' (column F), it shows 0 days overdue. Otherwise, if today's date is past the due date, it calculates the difference. Simple, but effective!

For the 'Aged Debt Bracket' (say, column I), you can use a nested IF statement or a VLOOKUP against a lookup table, but a simple IF often suffices:

=IF(H2=0, "Not Due / Paid", IF(H2<=30, "0-30 Days Overdue", IF(H2<=60, "31-60 Days Overdue", IF(H2<=90, "61-90 Days Overdue", "90+ Days Overdue"))))

Now, you have a dynamic aged debt analysis that updates daily. Very handy for quick visual checks of your late payment landscape.

Bringing in AI: Drafting Smarter Reminder Emails with Apps Script

Here's where the "AI-powered" aspect truly comes into play for your spreadsheet automation. Instead of manually writing each reminder email, we'll use Google Apps Script to automate the process of sending them, and we'll tap into an AI model to help us draft those emails effectively. This is a huge time-saver and ensures consistency in your communications. For a deeper dive into this, you might find our article on How to Automate Invoice Reminders with AI and Google Sheets particularly useful.

Step 1: Drafting Your Email Templates with AI

You'll want different tones and urgency for different aged debt brackets. For instance, a friendly nudge for 7 days overdue is very different from a firm demand at 60+ days. This is an ideal task for an AI model. You can use tools like ChatGPT, Claude, or Gemini to generate these templates.

Here are some example prompts you might use:

• Prompt for 7-day overdue: "Draft a polite, friendly email reminder for an invoice 7 days overdue. Include invoice number, amount, and due date. Remind the client of payment methods. Keep it professional but light. Use a UK business tone."
• Prompt for 30-day overdue: "Create a slightly more urgent email for an invoice 30 days overdue. Reiterate the details, ask for an update on payment, and mention potential late payment fees in line with UK regulations if applicable. Maintain a professional tone."
• Prompt for 60+ day overdue: "Generate a firm but professional email for an invoice 60+ days overdue. State the outstanding amount, invoice number, and original due date clearly. Explain potential next steps (e.g., collection agency, legal action) if payment isn't received within X days. Emphasise the importance of immediate payment. UK business context."

Save these templates. You can store them directly in your Google Sheet on a separate tab (I'd call it "Email Templates") with columns for the 'Aged Debt Bracket' and the 'Email Body', or as variables within your Apps Script. I prefer storing them in the sheet; it's easier to edit them later without touching code, and gives you great control over your Apps Script reminders.

Step 2: The Google Apps Script for Reminders

Now for the exciting part: writing the script to send these reminders. Go to 'Extensions' > 'Apps Script' in your Google Sheet. This opens a new project. You'll write JavaScript code here.

Here’s a simplified outline of what your script will do:

1. Get Invoice Data: Read all rows from your active aged debt tracker sheet.
2. Identify Overdue Invoices: Loop through the data and check the 'Days Overdue' and 'Status' columns.
3. Select Appropriate Template: Based on the 'Aged Debt Bracket' and the 'Last Reminder Sent' date (to avoid spamming), choose the right AI-drafted email template.
4. Personalise the Email: Replace placeholders in your template (e.g., [Client Name], [Invoice Number], [Amount Due]) with actual data from the invoice row.
5. Send the Email: Use Google's MailApp.sendEmail() function.
6. Update Tracker: Mark the 'Last Reminder Sent' column with today's date.

A basic script structure might look something like this (this is conceptual code, not ready to run without significant setup and error handling; you'll want to test it thoroughly!):

function sendAgedDebtReminders() { const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Your Aged Debt Tracker Sheet Name"); const data = sheet.getDataRange().getValues(); // Get all data const headers = data[0]; // First row is headers const invoiceData = data.slice(1); // Actual invoice data // Find column indices - Adjust these based on your sheet setup! const clientNameCol = headers.indexOf("Client Name"); const clientEmailCol = headers.indexOf("Client Email"); const invoiceNumCol = headers.indexOf("Invoice Number"); const amountDueCol = headers.indexOf("Amount Due (£)"); const dueDateCol = headers.indexOf("Due Date"); const statusCol = headers.indexOf("Status"); const daysOverdueCol = headers.indexOf("Days Overdue"); const agedBracketCol = headers.indexOf("Aged Debt Bracket"); const lastReminderCol = headers.indexOf("Last Reminder Sent"); // Fetch AI-generated templates (assuming they are on a separate sheet named "Email Templates") // First column: Aged Debt Bracket, Second column: Email Body const templateSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email Templates"); const templates = templateSheet.getDataRange().getValues(); const templateMap = {}; templates.forEach(row => { if (row[0] && row[1]) { templateMap[row[0]] = row[1]; } }); invoiceData.forEach((row, index) => { const clientName = row[clientNameCol]; const clientEmail = row[clientEmailCol]; const invoiceNumber = row[invoiceNumCol]; const amountDue = row[amountDueCol]; const dueDate = new Date(row[dueDateCol]).toLocaleDateString('en-GB'); const status = row[statusCol]; const daysOverdue = row[daysOverdueCol]; const agedBracket = row[agedBracketCol]; const lastReminder = row[lastReminderCol] ? new Date(row[lastReminderCol]) : null; // Only send reminders for unpaid invoices that are actually overdue and have a valid email if (status !== "Paid" && daysOverdue > 0 && clientEmail && clientEmail.includes("@")) { let subject = ""; let body = ""; let sendThisReminder = false; // Flag to control sending const today = new Date(); const oneDay = 24 * 60 * 60 * 1000; // milliseconds in a day const daysSinceLastReminder = lastReminder ? Math.round(Math.abs((today.getTime() - lastReminder.getTime()) / oneDay)) : 999; // A large number if no reminder sent yet // Logic to send reminders at appropriate intervals and stages if (daysOverdue <= 7 && daysSinceLastReminder > 5) { // Send ~weekly for early stage subject = `Friendly Reminder: Invoice ${invoiceNumber} is 7 Days Overdue`; body = templateMap["0-30 Days Overdue"] || "Please find your invoice attached. Just a gentle reminder."; sendThisReminder = true; } else if (daysOverdue > 7 && daysOverdue <= 30 && daysSinceLastReminder > 10) { // Send ~fortnightly subject = `Action Required: Invoice ${invoiceNumber} is 30 Days Overdue`; body = templateMap["31-60 Days Overdue"] || "Your invoice is now 30 days overdue. Could you provide an update?"; sendThisReminder = true; } else if (daysOverdue > 30 && daysOverdue <= 60 && daysSinceLastReminder > 14) { // Send ~monthly subject = `URGENT: Invoice ${invoiceNumber} is 60 Days Overdue`; body = templateMap["61-90 Days Overdue"] || "Immediate payment required for your 60-day overdue invoice."; sendThisReminder = true; } else if (daysOverdue > 60 && daysSinceLastReminder > 20) { // Send less frequently but with greater urgency subject = `FINAL NOTICE: Overdue Invoice ${invoiceNumber} - Action Required`; body = templateMap["90+ Days Overdue"] || "This is a final reminder for your significantly overdue invoice. Please contact us immediately."; sendThisReminder = true; } if (sendThisReminder) { // Replace placeholders in the AI-generated body body = body.replace("[Client Name]", clientName) .replace("[Invoice Number]", invoiceNumber) .replace("[Amount Due]", `£${amountDue ? parseFloat(amountDue).toFixed(2) : '0.00'}`) // Handle potential non-numeric amount .replace("[Due Date]", dueDate); MailApp.sendEmail(clientEmail, subject, body); // Update the 'Last Reminder Sent' column in the sheet. Add 2 to index to account for headers (row 1 is 0-indexed) // and 1 to lastReminderCol because Apps Script getRange is 1-indexed for columns. sheet.getRange(index + 2, lastReminderCol + 1).setValue(new Date()); Utilities.sleep(1000); // Small delay to avoid hitting email sending limits } } }); } 

Remember, this is a simplified example. You'll need to adapt it for your specific column names and template storage. You might also want to include checks for valid email addresses and robust error handling. Getting your Google Sheets AI script just right takes a bit of testing.

Step 3: Setting Up Automation Triggers

Once your script is working, you don't want to run it manually every day. In the Apps Script editor, on the left-hand side, click the clock icon ('Triggers'). Add a new trigger:

• Choose which function to run: sendAgedDebtReminders (or whatever you've named your function).
• Choose event source: Time-driven.
• Select type of time-based trigger: Day timer.
• Select time of day: Pick a time when you want the reminders to go out, e.g., 'every day between 8 AM and 9 AM'.

Now, your Google Sheets AI-powered aged debt tracker will automatically check for overdue invoices and send reminders! This kind of automation for your UK small business finance can be a real boon.

Further Enhancements and Practical Observations for UK Businesses

This basic setup is a great start, but you can always build on it:

• Conditional Formatting: Add visual cues in your sheet. For example, highlight rows red if 'Days Overdue' is over 30, amber for 7-30 days, and green for those not yet due. This makes your tracker instantly readable.
• Payment Plans: For larger, older debts, you might negotiate a payment plan. You could add columns to track these agreements.
• Integration with Accounting Software: While this is a standalone tracker, you could explore Zapier or even more advanced Apps Script integrations to pull data directly from accounting software like Xero or QuickBooks Online, although that's a more advanced project. For now, manual entry or CSV imports are perfectly fine.
• AI for Follow-up Strategy: You could use an AI assistant to help brainstorm strategies for particularly difficult clients or to suggest appropriate next steps based on the age of the debt. Just ask it, "What's the best approach for a client 90 days overdue on a £5,000 invoice, who isn't responding?" You might be surprised by its suggestions.
• HMRC Compliance: Always ensure your invoicing and debt recovery practices align with UK regulations. Keeping clear, digital records in your tracker aids in this, particularly if you ever need to justify a bad debt write-off. For more on HMRC-ready automation, check out Mastering HMRC-Ready AI Expense Tracking for UK Freelancers.
• Better AI Prompts: Experiment with your prompts to get the best email templates. Remember that the quality of AI output depends on the quality of your input. For more general prompt ideas, our article on Essential AI Prompts for UK Small Business Bookkeeping might spark some ideas.

Wrapping Up Your UK Aged Debt Tracker Project

Building an AI-powered aged debt tracker in Google Sheets might seem like a bit of an undertaking initially, but the long-term benefits for your UK small business finance are substantial. You're moving from a reactive position to a proactive one, automating a tedious but crucial task, and ultimately safeguarding your cash flow. It's about working smarter, not harder.

By following these steps, you'll create a robust system that keeps an eye on your outstanding invoices, ensures timely reminders are sent, and gives you back precious time to focus on what you do best: running and growing your business. It's a pragmatic application of technology that genuinely makes a difference to your bottom line.