Free YouTube Tutorial Resource
Google Sheets Payment Reminder Automation Script
Never chase unpaid invoices manually again. Follow our YouTube tutorial, copy the free Google Apps Script code below, clone our pre-built Google Sheet template, and deploy your custom automatic email chaser in minutes.
Google Sheet Template
Pre-formatted invoice tracking sheetWe pre-built a dashboard structure with correctly labeled sheets ('Client_Emails' and 'Invoice_Tracking') and column systems so the automated script works instantly.
Setup Video Walkthrough
Watch YouTube Tutorial
PaymentReminder.gs
// CHANGE THIS TO YOUR BOUND SPREADSHEET ID IF NEEDED, OR LEAVE AS IS IF RUNNING CONTAINER-BOUND "color: #569cd6; font-weight: bold;">const SPREADSHEET_ID = SpreadsheetApp.getActiveSpreadsheet().getId(); // 1. PHASE 1: DISPATCH INITIAL INVOICES FROM DRIVE "color: #569cd6; font-weight: bold;">function sendInitialInvoices() { "color: #569cd6; font-weight: bold;">const sourceFolder = DriveApp.getFoldersByName("Invoices to Send").next(); "color: #569cd6; font-weight: bold;">const targetFolder = DriveApp.getFoldersByName("Sent Invoices").next(); "color: #569cd6; font-weight: bold;">const files = sourceFolder.getFiles(); "color: #569cd6; font-weight: bold;">const ss = SpreadsheetApp.openById(SPREADSHEET_ID); "color: #569cd6; font-weight: bold;">const customerSheet = ss.getSheetByName("Client_Emails"); "color: #569cd6; font-weight: bold;">const trackingSheet = ss.getSheetByName("Invoice_Tracking"); "color: #569cd6; font-weight: bold;">const customerData = customerSheet.getDataRange().getValues(); "color: #569cd6; font-weight: bold;">while (files.hasNext()) { "color: #569cd6; font-weight: bold;">const file = files.next(); "color: #569cd6; font-weight: bold;">const fileName = file.getName(); // Split filename by underscore. Example: "INV-1001_Acme Corp.xlsx" "color: #569cd6; font-weight: bold;">const parts = fileName.split("_"); "color: #569cd6; font-weight: bold;">if (parts.length < 2) continue; "color: #569cd6; font-weight: bold;">const invoiceNumber = parts[0].trim(); // Dynamically isolate and strip out any file extension extension (e.g. .pdf, .xlsx, .docx) "color: #569cd6; font-weight: bold;">const rawCustomerPart = parts[1]; "color: #569cd6; font-weight: bold;">const customer = rawCustomerPart.substring(0, rawCustomerPart.lastIndexOf('.')).trim(); "color: #569cd6; font-weight: bold;">let toEmails = ""; "color: #569cd6; font-weight: bold;">let ccEmails = ""; // Lookup customer emails from Client_Emails directory "color: #569cd6; font-weight: bold;">for ("color: #569cd6; font-weight: bold;">let i = 1; i < customerData.length; i++) { "color: #569cd6; font-weight: bold;">if (customerData[i][0].trim().toLowerCase() === customer.toLowerCase()) { toEmails = customerData[i][1]; ccEmails = customerData[i][2]; break; } } "color: #569cd6; font-weight: bold;">if (toEmails) { "color: #569cd6; font-weight: bold;">const subject = "Tax Invoice Issued - " + invoiceNumber; "color: #569cd6; font-weight: bold;">const body = "Dear Customer,\n\n" + "Please find attached your digital invoice " + invoiceNumber + " ">for services/goods rendered.\n\n" + "We kindly request that you review the statement and arrange ">for payment according to the agreed credit terms.\n\n" + "Thank you ">for your business!\n\n" + "Best regards,\nAccounts Receivable Team"; // Dispatch via Gmail API GmailApp.sendEmail(toEmails, subject, body, { attachments: [file.getBlob()], cc: ccEmails }); // Append row initialization into the Invoice_Tracking database log trackingSheet.appendRow([ invoiceNumber, customer, "color: #569cd6; font-weight: bold;">new Date(), // Current timestamp as Date Sent "Pending", 0, // Days Pending starts at 0 0 // Reminder Level starts at 0 (Initial sent) ]); // Archive file into the storage destination folder file.moveTo(targetFolder); } } } // 2. MAINTENANCE PHASE: DYNAMIC ENGINE DAY UPDATES "color: #569cd6; font-weight: bold;">function updateInvoicePendingDays() { "color: #569cd6; font-weight: bold;">const sheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName("Invoice_Tracking"); "color: #569cd6; font-weight: bold;">const data = sheet.getDataRange().getValues(); "color: #569cd6; font-weight: bold;">for ("color: #569cd6; font-weight: bold;">let i = 1; i < data.length; i++) { "color: #569cd6; font-weight: bold;">const dateSent = "color: #569cd6; font-weight: bold;">new Date(data[i][2]); "color: #569cd6; font-weight: bold;">const status = data[i][3]; // Only track time dimensions "color: #569cd6; font-weight: bold;">if payment has not landed yet "color: #569cd6; font-weight: bold;">if (status === "Pending") { "color: #569cd6; font-weight: bold;">const differenceInMs = "color: #569cd6; font-weight: bold;">new Date() - dateSent; "color: #569cd6; font-weight: bold;">const daysPending = Math.floor(differenceInMs / (1000 * 60 * 60 * 24)); // Write the updated days metric back to Column 5 (Column E) sheet.getRange(i + 1, 5).setValue(daysPending); } } } // 3. LIFECYCLE PHASE: AUTOMATED ESCALATION CHASER REMINDERS "color: #569cd6; font-weight: bold;">function processInvoiceReminders() { "color: #569cd6; font-weight: bold;">const ss = SpreadsheetApp.openById(SPREADSHEET_ID); "color: #569cd6; font-weight: bold;">const trackingSheet = ss.getSheetByName("Invoice_Tracking"); "color: #569cd6; font-weight: bold;">const customerSheet = ss.getSheetByName("Client_Emails"); "color: #569cd6; font-weight: bold;">const trackingData = trackingSheet.getDataRange().getValues(); "color: #569cd6; font-weight: bold;">const customerData = customerSheet.getDataRange().getValues(); "color: #569cd6; font-weight: bold;">for ("color: #569cd6; font-weight: bold;">let i = 1; i < trackingData.length; i++) { "color: #569cd6; font-weight: bold;">const invoiceNumber = trackingData[i][0]; "color: #569cd6; font-weight: bold;">const customer = trackingData[i][1]; "color: #569cd6; font-weight: bold;">const dateSent = "color: #569cd6; font-weight: bold;">new Date(trackingData[i][2]); "color: #569cd6; font-weight: bold;">const status = trackingData[i][3]; "color: #569cd6; font-weight: bold;">const currentLevel = trackingData[i][5]; // Column F // Safeguard: Stop tracking execution immediately "color: #569cd6; font-weight: bold;">if invoice status is paid "color: #569cd6; font-weight: bold;">if (status !== "Pending") continue; "color: #569cd6; font-weight: bold;">const daysPending = Math.floor(("color: #569cd6; font-weight: bold;">new Date() - dateSent) / (1000 * 60 * 60 * 24)); "color: #569cd6; font-weight: bold;">let toEmails = ""; "color: #569cd6; font-weight: bold;">let ccEmails = ""; // Lookup contact matrix info "color: #569cd6; font-weight: bold;">for ("color: #569cd6; font-weight: bold;">let j = 1; j < customerData.length; j++) { "color: #569cd6; font-weight: bold;">if (customerData[j][0].trim().toLowerCase() === customer.toLowerCase()) { toEmails = customerData[j][1]; ccEmails = customerData[j][2]; break; } } "color: #569cd6; font-weight: bold;">if (!toEmails) continue; "color: #569cd6; font-weight: bold;">let subject = ""; "color: #569cd6; font-weight: bold;">let body = ""; "color: #569cd6; font-weight: bold;">let systemNextLevel = currentLevel; // STAGE 1: GENTLE REMINDER (3 Days after initial drop) "color: #569cd6; font-weight: bold;">if (daysPending >= 3 && currentLevel == 0) { subject = "Friendly Reminder: Invoice Payment Due Notice - " + invoiceNumber; body = "Dear Customer,\n\n" + "This is a gentle notification regarding outstanding Invoice #" + invoiceNumber + " sent to you a few days ago.\n\n" + "Kindly verify with your accounting department ">if this item has been scheduled ">for settlement.\n\n" + "Warm regards,\nFinance Division"; systemNextLevel = 1; } // STAGE 2: FORMAL OVERDUE REMINDER (7 Days after initial drop) "color: #569cd6; font-weight: bold;">else "color: #569cd6; font-weight: bold;">if (daysPending >= 7 && currentLevel == 1) { subject = "Second Notice: Overdue Balance Invoice #" + invoiceNumber; body = "Dear Customer,\n\n" + "We are writing to remind you that your payment ">for invoice #" + invoiceNumber + " is currently overdue.\n\n" + "Please look into this account status immediately and confirm back with remittance proof.\n\n" + "Regards,\nAccounts Team"; systemNextLevel = 2; } // STAGE 3: FINAL COLLECTION ESCALATION (14 Days after initial drop) "color: #569cd6; font-weight: bold;">else "color: #569cd6; font-weight: bold;">if (daysPending >= 14 && currentLevel == 2) { subject = "FINAL REMINDER: Urgent Settlement Account Action Required - " + invoiceNumber; body = "Dear Customer,\n\n" + "Despite previous reminders, your account balance ">for invoice #" + invoiceNumber + " remains unpaid.\n\n" + "Please take note that ">if payment details are not reconciled within 48 hours, late service processing measures may apply.\n\n" + "We request your immediate intervention.\n\n" + "Respectfully,\nFinance Management Office"; systemNextLevel = 3; } // If an action matches a target time boundary, distribute the email alert "color: #569cd6; font-weight: bold;">if (subject) { GmailApp.sendEmail(toEmails, subject, body, { cc: ccEmails }); // Update Column 6 (Column F) tracking marker level value trackingSheet.getRange(i + 1, 6).setValue(systemNextLevel); } } }
How to Deploy the Script
2
Way 2: Setting Up From a Fresh Google Sheet
1. Create a brand new Google Sheet. Rename the first sheet tab to Client_Emails and create a second tab named Invoice_Tracking.
2. In the Client_Emails tab, create headers in Row 1: Column A = Client Name, Column B = Client Email, and Column C = CC Email.
3. In the Invoice_Tracking tab, create headers in Row 1: Column A = Invoice Number, Column B = Client Name, Column C = Date Sent, Column D = Status, Column E = Days Pending, and Column F = Reminder Level.
4. Open your Google Drive root folder and create two new folders named: Invoices to Send and Sent Invoices.
5. Open your sheet, click Extensions > Apps Script. Delete any starter template code, paste the code copied from this page, and click the Save icon (Ctrl + S).
6. Select sendInitialInvoices from the dropdown, click Run, and complete the authorization dialog permissions.
3
Set Up Automated Time-Driven Triggers
To automate the whole workflow, click on the **alarm clock icon (Triggers)** on the left sidebar of the Apps Script dashboard. Add the following three triggers:
1. Dispatching New Invoices:
- Function to run: sendInitialInvoices
- Event source: Time-driven
- Trigger type: Hour timer / Every hour (checks for new files in your Drive folder and emails them automatically).
2. Updating Days Elapsed:
- Function to run: updateInvoicePendingDays
- Event source: Time-driven
- Trigger type: Day timer / Midnight to 1am (increments the days counter in the tracking sheet every night).
3. Sending Escalation Reminders:
- Function to run: processInvoiceReminders
- Event source: Time-driven
- Trigger type: Day timer / 9am to 10am (checks overdue invoices daily and automatically dispatches escalation reminder emails).
Click **Save** on each trigger, and your reminder system is fully automated on autopilot!