Google Apps Script is free, runs in the cloud, and can replace hours of manual finance work every week. If your team is still sending payment reminders by hand or building aging reports from scratch — this is the fastest path to getting that time back.
I build these scripts for clients regularly. They're not glamorous. They don't require a developer. And they save 5–15 hours of manual work every month — sometimes more. The best part: Google Apps Script is already inside every Google Workspace account your team is using right now. You just haven't used it yet.
Here are five automations I'd deploy first, in order of impact and ease of implementation.
How to open Apps Script: In any Google Sheet, go to Extensions → Apps Script. Paste the code, save, and set a time-based trigger under Triggers → Add Trigger. That's it.
These automations assume your AR/AP data lives in a Google Sheet — even a basic one. Columns you'll need: Invoice Number, Client Name, Invoice Date, Due Date, Amount, Payment Status. If you have that, you can run all five automations below. If you're still on Excel locally, read this first.
The problem it solves: Someone on your team opens the AR sheet every morning, finds overdue invoices, and manually drafts reminder emails. This takes 30–60 minutes daily and is deeply inconsistent — tone, timing, and follow-up all vary by who's doing it.
This script checks your AR sheet every morning, identifies invoices overdue by 30, 60, or 90+ days, and automatically sends customised emails with the invoice details. Set it once, and it runs every day at 8am.
function sendPaymentReminders() {
const sheet = SpreadsheetApp.getActiveSpreadsheet()
.getSheetByName('AR');
const data = sheet.getDataRange().getValues();
const today = new Date();
// Skip header row (row 0)
for (let i = 1; i < data.length; i++) {
const [invoiceNo, client, , dueDate, amount, status, email] = data[i];
if (status === 'Paid') continue;
const due = new Date(dueDate);
const daysOver = Math.floor((today - due) / (1000*60*60*24));
if (daysOver <= 0) continue; // not yet due
const subject = `Payment Reminder: Invoice ${invoiceNo} — ${daysOver}d overdue`;
const body = `Dear ${client},\n\nThis is a reminder that Invoice `
+ `${invoiceNo} for ₹${amount.toLocaleString('en-IN')} was due on `
+ `${due.toLocaleDateString('en-IN')}.\n\nPlease arrange payment at `
+ `your earliest convenience.\n\nRegards,\nFinance Team`;
GmailApp.sendEmail(email, subject, body);
Logger.log(`Reminder sent: ${invoiceNo} → ${client}`);
}
}Set the trigger: Add Trigger → sendPaymentReminders → Time-driven → Day timer → 8am–9am. Done. Your reminders now go out automatically every morning.
The problem it solves: Your founder, CFO, or management team wants a daily cash snapshot. Right now, someone prepares it manually — or nobody gets it and decisions are made on stale numbers.
This script reads your cash flow sheet (inflows, outflows, bank balance) and emails a clean summary to your leadership team every morning before their first meeting. No manual prep required.
function sendDailyCashSummary() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('CashFlow');
const balance = sheet.getRange('B2').getValue(); // Current bank balance
const arTotal = sheet.getRange('B3').getValue(); // Total AR outstanding
const apTotal = sheet.getRange('B4').getValue(); // Total AP due this week
const today = new Date().toLocaleDateString('en-IN');
const subject = `Daily Cash Summary — ${today}`;
const body = `Good morning,\n\n`
+ `📊 Cash Position as of ${today}\n`
+ `━━━━━━━━━━━━━━━━━━━━━━━━\n`
+ `Bank Balance: ₹${balance.toLocaleString('en-IN')}\n`
+ `AR Outstanding: ₹${arTotal.toLocaleString('en-IN')}\n`
+ `AP Due This Week: ₹${apTotal.toLocaleString('en-IN')}\n`
+ `━━━━━━━━━━━━━━━━━━━━━━━━\n\n`
+ `View full report: ${ss.getUrl()}\n\nFinance Team`;
GmailApp.sendEmail(
'[email protected], [email protected]',
subject, body
);
}"The best automation is one that runs invisibly in the background and gives your team back time they didn't know they were losing. Apps Script is the fastest way to get there without spending a rupee."
The problem it solves: AR aging reports are built manually — someone calculates days outstanding, sorts invoices into buckets, and formats a summary table. It takes 1–2 hours, gets done inconsistently, and is out of date by the time it's shared.
This script scans your AR sheet and automatically writes aging bucket totals to a summary sheet. Run it daily with a trigger and your aging report is always current.
function buildARAgingReport() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const arSheet = ss.getSheetByName('AR');
const data = arSheet.getDataRange().getValues();
const today = new Date();
const buckets = { current:0, d30:0, d60:0, d90:0 };
for (let i = 1; i < data.length; i++) {
const [, , , dueDate, amount, status] = data[i];
if (status === 'Paid') continue;
const days = Math.floor((today - new Date(dueDate)) / (86400000));
if (days <= 0) buckets.current += amount;
else if (days <= 30) buckets.d30 += amount;
else if (days <= 60) buckets.d60 += amount;
else buckets.d90 += amount;
}
// Write to Aging Summary sheet
const out = ss.getSheetByName('AR Aging')
|| ss.insertSheet('AR Aging');
out.clearContents();
out.getRange(1,1,5,2).setValues([
['Bucket', 'Amount (₹)'],
['Current (not due)', buckets.current],
['1–30 days', buckets.d30],
['31–60 days', buckets.d60],
['60+ days', buckets.d90],
]);
Logger.log('AR Aging updated');
}The problem it solves: Duplicate invoices are one of the most common — and most embarrassing — AP errors. A vendor re-sends an invoice, someone doesn't check, and you pay it twice. Recovery is awkward. Prevention is 10 lines of code.
This script scans your AP sheet on every edit and highlights any duplicate invoice numbers in red. You'll know before you process, not after.
function flagDuplicateInvoices() {
const sheet = SpreadsheetApp.getActiveSpreadsheet()
.getSheetByName('AP');
const range = sheet.getRange('A2:A' + sheet.getLastRow());
const values = range.getValues().flat();
const seen = {};
const dupIdx = [];
values.forEach((v, i) => {
if (!v) return;
if (seen[v] !== undefined) {
dupIdx.push(i); // current row
dupIdx.push(seen[v]); // first occurrence
}
seen[v] = i;
});
// Reset all backgrounds first
range.setBackground(null);
// Highlight duplicates in red
dupIdx.forEach(i => {
sheet.getRange(i + 2, 1).setBackground('#FCA5A5');
});
if (dupIdx.length)
SpreadsheetApp.getUi().alert(
`⚠️ ${dupIdx.length/2} duplicate invoice(s) flagged in red.`
);
}
// Attach to onEdit trigger for real-time detection
function onEdit(e) {
if (e.range.getSheet().getName() === 'AP')
flagDuplicateInvoices();
}The problem it solves: Month-end close tasks get missed. Someone forgets to reconcile the credit card statement. Nobody sent the GST data request. The checklist is in someone's head or buried in an old email. This script creates a fresh checklist in a dedicated sheet at the start of every month, emails it to your finance team, and tracks completion.
const CLOSE_TASKS = [
['Bank reconciliation — current account', 'Finance Lead'],
['Bank reconciliation — savings account', 'Finance Lead'],
['AP ageing review and payments', 'AP Manager'],
['AR ageing review and reminders', 'AR Manager'],
['Employee expense claims approved', 'HR / Finance'],
['GST data compiled and reviewed', 'Tax Lead'],
['Payroll verified and posted', 'HR'],
['Accruals and prepayments reviewed', 'Controller'],
['P&L and Balance Sheet preliminary review', 'CFO'],
['Final sign-off', 'CFO / Founder'],
];
function createMonthEndChecklist() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const month = new Date().toLocaleDateString('en-IN', {month:'long',year:'numeric'});
const name = `Close ${month}`;
let sheet = ss.getSheetByName(name);
if (!sheet) sheet = ss.insertSheet(name);
sheet.clearContents();
sheet.getRange(1,1,1,3)
.setValues([['Task', 'Owner', 'Status']])
.setFontWeight('bold');
CLOSE_TASKS.forEach(([task, owner], i) => {
sheet.getRange(i+2,1,1,3)
.setValues([[task, owner, 'Pending']]);
});
GmailApp.sendEmail(
'[email protected]',
`Month-End Close Checklist: ${month}`,
`Hi team,\n\nThe month-end close checklist for ${month} is ready:\n\n`
+ CLOSE_TASKS.map(([t,o]) => `☐ ${t} (${o})`).join('\n')
+ `\n\nView and update: ${ss.getUrl()}\n\nFinance Team`
);
}Set the trigger: Triggers → createMonthEndChecklist → Month timer → 1st of month → 7am. Your checklist appears in the sheet and lands in every team member's inbox on Day 1, automatically.
Apps Script is genuinely powerful for automating within Google Sheets. But it has hard limits. It can't integrate with Tally, GST APIs, or your bank feed natively. It doesn't give you audit trails, approval workflows, or multi-entity accounting. And like VBA, every script creates a dependency on whoever wrote it.
The honest ceiling: Use Apps Script to buy time and prove the ROI of automation. Use it as a bridge. But if you're running 5+ scripts to manage processes that a single ERP would handle natively, you've outgrown scripts — and the cost of the complexity is now higher than the cost of the system.
If you want to understand where that line is for your business — whether scripts are still the right tool or whether it's time to graduate to ERPNext — that's exactly what I help finance leaders figure out.
I can customise and deploy all five automations for your exact Google Sheets setup in a single session — or help you assess whether a full ERP is the right next move.
Book a Free Strategy Call