Payment Follow-up Tracker in Google Sheets

What it looks like

Each bill is one row. The sheet works out what is pending and how old it is. When a follow-up date comes, a reminder goes out and the row is updated:

Step 1: Make the Payments sheet

Name the tab Payments and put these headers in row 1. Columns H, I, K and L are formulas — you only type the others.

Step 2: Add the formulas

Type these in row 2 and drag them down for all rows:

Tip: use Format → Conditional formatting with the custom formula =$K2="90+" (red) and =$L2="Overdue" (orange), so old and missed payments stand out.

Step 3: A Summary tab for the big picture

Add a second tab called Summary with these formulas:

Now one look tells you the total pending, how much is very old, who to call first, and whose follow-up is today.

Step 4: Send reminders on their own

Open Extensions → Apps Script, paste this script and change YOUR_NAME. It emails every customer whose follow-up date has come, skips anyone who has paid, and sets the next follow-up 7 days later.

// Sheet name: Payments. Row 1 = headers.
// Needed columns: Customer | Email | Invoice No | Balance | Next Follow-up | Last Reminder
const YOUR_NAME = 'Your Business Name';
const DAYS_BETWEEN_REMINDERS = 7;

function sendPaymentReminders() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Payments');
  const rows = sheet.getDataRange().getValues();
  const head = rows[0];
  const col = (name) => head.indexOf(name);
  const tz = Session.getScriptTimeZone();
  const day = (d) => Utilities.formatDate(d, tz, 'yyyy-MM-dd');
  const today = day(new Date());

  for (let i = 1; i < rows.length; i++) {
    const r = rows[i];
    const balance = Number(r[col('Balance')]) || 0;
    const email = String(r[col('Email')]).trim();
    const next = r[col('Next Follow-up')];
    const last = r[col('Last Reminder')];

    if (balance <= 0 || !email) continue;              // paid, or no email
    if (!(next instanceof Date) || day(next) > today) continue; // no date, or not due yet
    if (last instanceof Date && day(last) === today) continue;  // already sent today

    MailApp.sendEmail(
      email,
      'Payment reminder: invoice ' + r[col('Invoice No')],
      'Hello ' + r[col('Customer')] + ',\n\n' +
        'A payment of Rs. ' + inr(balance) + ' for invoice ' + r[col('Invoice No')] + ' is pending.\n' +
        'If you have already paid, please ignore this message.\n\n' +
        'Thank you,\n' + YOUR_NAME
    );

    // Note the reminder, and plan the next one
    const nextDate = new Date();
    nextDate.setDate(nextDate.getDate() + DAYS_BETWEEN_REMINDERS);
    sheet.getRange(i + 1, col('Last Reminder') + 1).setValue(new Date());
    sheet.getRange(i + 1, col('Next Follow-up') + 1).setValue(nextDate);
  }
}

// 125000 -> "1,25,000" (Indian style)
function inr(n) {
  const s = String(Math.round(n));
  const last3 = s.slice(-3);
  const rest = s.slice(0, -3);
  return rest ? rest.replace(/\B(?=(\d{2})+(?!\d))/g, ',') + ',' + last3 : last3;
}

• To run it every morning, add a trigger: Triggers → Add trigger → choose sendPaymentReminders, “Time-driven”, “Day timer”, 9am to 10am.
• Run it once by hand first and allow the permissions. Test with your own email address in a few rows.
• Want WhatsApp instead of email? See sending WhatsApp messages from Google Sheets.

Tips that bring the money back faster

• Call or remind the biggest and oldest amounts first — the Summary tab shows them.
• Always put a Next Follow-up date. A pending bill with no date is easy to forget.
• Update the Paid column the same day money comes in, so the customer is not reminded by mistake.
• Keep the message short and polite. You want the money and the customer.
• Look at the Summary tab once a week — it shows if old money is growing.

Making the bills too? The PDF invoices from Google Sheets guide shows how to make and email them from the same sheet.

Frequently asked questions