Build a UK Client Payment Dashboard with Apps Script & Google Sheets

Why Bother with a Custom Payment Dashboard?

Running a small business or working as a freelancer in the UK means juggling a lot of plates. Chasing invoices, tracking payments, and trying to predict cash flow can feel like a constant battle, especially when you're also focused on delivering great work. I've found that one of the biggest headaches is just knowing where you stand with client payments at any given moment.

Sure, you might have an accounting package, and that's essential for your official books, but sometimes you need a more immediate, flexible, and utterly customisable view of your incoming funds. You want to see who owes you what, when it was due, and how long it's been outstanding – without having to run complex reports or click through multiple screens. That's where a bespoke UK client payment dashboard comes into its own. It's about bringing all that crucial information to the forefront, giving you a clear, concise picture of your financial health.

The common pain points are pretty universal, aren't they? You might be:

• Spending too much time on manual tracking, flicking between spreadsheets or email threads.
• Struggling with a lack of real-time overview, meaning you don't spot overdue payments until they're really overdue.
• Feeling stressed about chasing late payments because you're not sure which ones to prioritise.
• Wishing you had a better handle on your cash flow predictions for upcoming months.

While off-the-shelf accounting software provides a fantastic backbone for your finances, it doesn't always offer the highly specific, custom-built dashboard views that some freelancers or small businesses need for day-to-day operational clarity. Sometimes, you just need a spreadsheet that works exactly the way you think.

Your Toolkit: Google Sheets & Google Apps Script

When it comes to building something flexible and powerful without breaking the bank, Google's ecosystem is incredibly handy. For this project, we're relying on two key components:

Google Sheets: Think of it as a supercharged spreadsheet. It's free, cloud-based, and fantastic for organising data. Because it lives online, you can access it from anywhere, collaborate with others if you need to, and never worry about losing your work to a crashed hard drive. Sheets will be the heart of our payment dashboard, holding all your client invoice data in an organised fashion.

Google Apps Script: This is where the magic happens. Apps Script is a JavaScript-based platform that lets you extend the functionality of Google applications like Sheets, Docs, and Gmail. Essentially, it allows you to write little programs (scripts) that automate tasks within your spreadsheet. For our payment dashboard, Apps Script will be doing the heavy lifting: automatically updating payment statuses, calculating overdue days, and keeping your dashboard fresh without you lifting a finger.

The beauty is in their synergy. Sheets stores your raw data, and Apps Script transforms that data into actionable insights, keeping your freelance payment tracking or small business payment tracking effortlessly up-to-date.

Setting Up Your Basic Google Sheet (The Data Engine)

First things first, let's get your data organised. Open a new Google Sheet and name it something clear, like "Client Payments UK". You'll want one main tab (let's call it "Invoices") to record all your invoice details. Here are the columns I'd recommend:

• Client Name: Who owes you money?
• Invoice Number: Unique identifier for each invoice.
• Invoice Date: When you issued the invoice.
• Due Date: When the payment is expected.
• Amount Due (£): The total amount of the invoice.
• Amount Paid (£): How much has actually been paid so far.
• Payment Date: The date you received the payment.
• Status: This is where Apps Script will come in, but for now, you can leave it blank or manually set it. (e.g., Paid, Due, Overdue).
• Payment Method: How the client paid (e.g., BACS, Faster Payment, Direct Debit). You could use data validation here to create a dropdown list, which is a neat touch for consistency.
• Notes: Any extra details you need to remember.

Populate this sheet with some of your existing invoice data. Make sure your dates are in a consistent UK format (e.g., DD/MM/YYYY) and that your monetary values are clearly marked as pounds sterling. You can apply some basic conditional formatting to the 'Status' column now if you like – for example, making 'Overdue' rows red, 'Due' rows amber, and 'Paid' rows green. It makes the sheet much easier to read at a glance, even before we get to the dashboard view.

Building Your Dashboard View

Now, let's create a separate sheet for your actual dashboard. Add a new tab and call it "Dashboard". This is where you'll summarise your data and get those high-level insights. Here are some key metrics you might want to display, using simple Sheets formulas to pull data from your "Invoices" tab:

• Total Outstanding: =SUMIF(Invoices!G:G, "", Invoices!E:E) (Sums 'Amount Due' where 'Payment Date' is blank)
• Total Paid (This Month/Year): You'll need `SUMIFS` and date criteria for this. For example, for total paid in the current month: =SUMIFS(Invoices!F:F, Invoices!H:H, "<>Paid", Invoices!G:G, ">="&EOMONTH(TODAY(),-1)+1, Invoices!G:G, "<="&EOMONTH(TODAY(),0))
• Total Overdue: =SUMIFS(Invoices!E:E, Invoices!H:H, "Overdue") (This will rely on our Apps Script later)
• Number of Outstanding Invoices: =COUNTIF(Invoices!G:G, "")
• Number of Overdue Invoices: =COUNTIF(Invoices!H:H, "Overdue")
• Average Payment Days: (This requires a bit more calculation, but essentially, it's `Payment Date - Invoice Date` for paid invoices. We can refine this with Apps Script too, or use an `ARRAYFORMULA` in a helper column).

You can also add simple charts to visualise this data. A pie chart showing the breakdown of 'Paid', 'Due', and 'Overdue' invoices is always effective. Google Sheets' built-in charting tools are quite intuitive – just select your data and click the chart icon.

Automating with Google Apps Script: The UK Payment Tracker Script

Here's the exciting part: making your spreadsheet automation UK-centric and truly automatic. Our main goal for Apps Script is to automatically update the 'Status' column based on payment dates and due dates, ensuring your dashboard is always current. We'll also briefly touch on how to get AI to help you with the scripting part.

Accessing Apps Script

1. Open your Google Sheet.
2. Go to Extensions > Apps Script. This will open a new browser tab with the Apps Script editor.

Writing Your First Script (Status Updater)

You'll see a default function called `myFunction()`. You can rename this. Let's create a function to update our invoice statuses:

Here’s a basic structure for your script. You don't need to be a coding expert; even a fundamental understanding helps, and remember, AI can assist!

1. Get the spreadsheet and the 'Invoices' sheet: You need to tell the script where to look.

2. Read all the data: Get all the values from your 'Invoices' tab.

3. Loop through each row: For every invoice, check its details.

4. Apply logic:

• If `Amount Paid` is greater than 0 AND `Payment Date` is filled, the status should be 'Paid'.
• Else if `Due Date` is in the past AND `Amount Paid` is 0 (or less than `Amount Due`), the status should be 'Overdue'.
• Otherwise, the status is simply 'Due'.

5. Update the 'Status' column: Write the new status back to the sheet.

A very simplified example of what the script might look like (you'd need to adapt column indices to your sheet):

function updatePaymentStatuses() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Invoices");
var range = sheet.getDataRange();
var values = range.getValues();
var headers = values[0];

// Find column indices dynamically (safer than hardcoding)
var invoiceDateCol = headers.indexOf("Invoice Date");
var dueDateCol = headers.indexOf("Due Date");
var amountDueCol = headers.indexOf("Amount Due (£)");
var amountPaidCol = headers.indexOf("Amount Paid (£)");
var paymentDateCol = headers.indexOf("Payment Date");
var statusCol = headers.indexOf("Status");

if (invoiceDateCol === -1 || dueDateCol === -1 || amountDueCol === -1 || amountPaidCol === -1 || paymentDateCol === -1 || statusCol === -1) {
Logger.log("One or more required columns not found. Please check headers.");
return;
}

for (var i = 1; i < values.length; i++) { // Start from second row to skip headers
var row = values[i];
var dueDate = new Date(row[dueDateCol]);
var amountDue = parseFloat(row[amountDueCol]);
var amountPaid = parseFloat(row[amountPaidCol]);
var paymentDate = row[paymentDateCol] ? new Date(row[paymentDateCol]) : null;

var status = "";

if (paymentDate && amountPaid >= amountDue && amountPaid > 0) { // Check if actually paid in full or overpaid
status = "Paid";
} else if (dueDate < new Date() && amountPaid < amountDue) {
status = "Overdue";
} else {
status = "Due";
}
row[statusCol] = status;
}
sheet.getRange(1, 1, values.length, values[0].length).setValues(values); // Write all values back
}

This basic script will read your data, update the status column based on your criteria, and write it back. You might also want to add functions to calculate days overdue or days until due – again, the logic is similar: compare dates and store the result. I've found that iterating through the data this way is far more robust than relying on complex `ARRAYFORMULA` constructs for every scenario.

Setting Up a Time-Driven Trigger

To make this truly automated, you need to set up a trigger. In the Apps Script editor, look for the clock icon on the left sidebar (it's the 'Triggers' menu). Click '+ Add Trigger' and set it up:

• Choose which function to run: `updatePaymentStatuses`
• Choose event source: Time-driven
• Select type of time-based trigger: Day timer (or `Hour timer` if you need more frequent updates)
• Select time of day: Pick a time when you're unlikely to be actively editing the sheet, e.g., midnight to 1 am.

Now, your Apps Script finance automation will run automatically, keeping your payment dashboard constantly updated!

Real-World Application & UK Nuances

Your new dashboard isn't just a pretty face; it's a powerful operational tool. Here’s how it helps with typical UK business practices:

• Identifying Late Payments Quickly: With an auto-updating 'Overdue' status, you can see at a glance which clients need a polite nudge. This is crucial for maintaining healthy cash flow.
• Late Payment Interest: The UK's Late Payment of Commercial Debts (Interest) Act 1998 allows businesses to claim interest on overdue invoices and reasonable compensation for recovery costs. Your dashboard helps you identify these invoices immediately, making it easier to decide when to apply these charges. You can even add a column to calculate the potential interest on overdue amounts automatically.
• Payment Methods: Recording payment methods like BACS, Faster Payments, or Direct Debit helps you understand client behaviour and reconcile your bank statements more efficiently.
• Integration with Reminders: Once your dashboard highlights overdue payments, you can use that information to feed into an automated reminder system. We've even discussed how to automate invoice reminders with AI and Google Sheets – and this dashboard provides the perfect data source for that!

Using AI to Help with Apps Script

Feeling a bit daunted by writing the script yourself? You're not alone! This is where modern AI models really shine. You can describe what you want the script to do in plain English, and models like ChatGPT, Claude, or Gemini can generate the code for you. Provide them with details like your sheet name, column headers, and the logic you want to apply. For example, you might prompt:

"Write a Google Apps Script for a sheet named 'Invoices'. I have columns 'Invoice Date', 'Due Date', 'Amount Due (£)', 'Amount Paid (£)', 'Payment Date', and 'Status'. I want the script to iterate through rows, and if 'Payment Date' is filled and 'Amount Paid' is equal to 'Amount Due', set 'Status' to 'Paid'. If 'Due Date' is past the current date and 'Amount Paid' is less than 'Amount Due', set 'Status' to 'Overdue'. Otherwise, set 'Status' to 'Due'."

You’ll likely need to do a bit of tweaking and testing, but AI can give you a significant head start. It’s an invaluable tool for spreadsheet automation UK users who might not have a coding background. Remember, these AI models are like incredibly knowledgeable assistants – they won't do *everything* perfectly, but they’ll get you 80% of the way there, letting you focus on the final 20% of customisation and refinement. If you're looking for more general AI assistants for your financial tasks, there are also excellent resources on essential AI prompts for small business bookkeeping.

Tips for Optimising Your Dashboard

To get the most out of your UK client payment dashboard, keep these tips in mind:

• Keep it Clean and Focused: Don't try to cram too much information onto your dashboard sheet. It should provide a quick snapshot. Detailed reports can live on other tabs.
• Use Clear Colour Coding: Beyond just red for overdue, consider using a gradient or different shades to indicate levels of urgency.
• Consider Data Visualisations: Sheets has excellent built-in charts. A simple bar chart showing payments received by month can be incredibly insightful for long-term trends.
• Regularly Review Your Data: Automation is great, but rubbish in still means rubbish out. Make sure the initial data you enter on your 'Invoices' tab is accurate.
• Use Google Sheets' Version History: Don't forget that Google Sheets automatically saves your changes and keeps a version history. If you make a mistake, you can always revert to an earlier version.

Building this dashboard is a proactive step towards better financial management. It's about empowering yourself with clarity, reducing manual effort, and making more informed business decisions. Couple this with good expense tracking (you might find our guide on HMRC-Ready AI Expense Tracking for UK Freelancers helpful) and you’ll have a comprehensive view of your finances.

Creating a UK client payment dashboard with Google Sheets and Apps Script might seem like a bit of a project initially, but the long-term benefits in saved time and reduced stress are immense. It provides a real-time, personalised view of your financial pipeline, helping you stay on top of your income and maintain healthy cash flow. Give it a go – you'll be surprised at how much more organised and in control you'll feel.