How to Automate Invoice Emails from Google Sheets (Free Script)

Updated October 2026 · 7 minute read

If you already keep invoices in Google Sheets, you don’t need new software to email them. A short Apps Script can email every invoice you mark as ready, log the date it went out and chase overdue ones automatically. Here is the full script, copy and paste, with setup steps and the limits to know about.

Set up the sheet

Name a tab Invoices and use these columns, with headers in row 1:

A drop-down for Status (Data → Data validation) stops typos like “ready ” that the script would skip.

The script

Open Extensions → Apps Script, delete the empty function and paste this. Change Your Business Name to yours.

function sendInvoiceEmails() {
  const sh = SpreadsheetApp.getActive().getSheetByName('Invoices');
  const last = sh.getLastRow();
  if (last < 2) return;                       // no invoices yet
  const rows = sh.getRange(2, 1, last - 1, 7).getValues();
  const tz = Session.getScriptTimeZone();

  rows.forEach((r, i) => {
    const [inv, client, email, amount, due, status] = r;
    if (status !== 'Ready' || !email) return;  // only rows marked Ready
    if (MailApp.getRemainingDailyQuota() < 1) return;

    const dueTxt = Utilities.formatDate(new Date(due), tz, 'MMM d, yyyy');
    const amt = Number(amount).toFixed(2);
    const body = '<p>Hi ' + client + ',</p>' +
      '<p>Invoice <b>' + inv + '</b> for <b>$' + amt + '</b> is due on <b>' + dueTxt + '</b>.</p>' +
      '<p>Thank you for your business.</p>';

    MailApp.sendEmail({
      to: email,
      subject: 'Invoice ' + inv + ' - $' + amt + ' due ' + dueTxt,
      htmlBody: body,
      name: 'Your Business Name'
    });
    sh.getRange(i + 2, 6, 1, 2).setValues([['Sent', new Date()]]);
  });
}

It reads every row, emails only rows with Status Ready and an email address, and writes Sent plus the date into columns F and G straight away so the invoice is never sent twice. It also stops if your daily email quota runs out, using MailApp.getRemainingDailyQuota().¹

Test it first: put your own email address on one row, set it to Ready and click Run. Google will ask you to authorise the script to send email as you; approve it for your own account.

Add overdue reminders

Paste this second function under the first. It emails one reminder for any invoice still marked Sent after its due date, then writes the date into column H. When a client pays, change their Status to Paid and they drop out.

function sendOverdueReminders() {
  const sh = SpreadsheetApp.getActive().getSheetByName('Invoices');
  const last = sh.getLastRow();
  if (last < 2) return;
  const rows = sh.getRange(2, 1, last - 1, 8).getValues();
  const today = new Date(); today.setHours(0, 0, 0, 0);

  rows.forEach((r, i) => {
    const [inv, client, email, amount, due, status, sentOn, reminded] = r;
    if (status !== 'Sent' || reminded || !email) return;
    if (new Date(due) >= today) return;          // not overdue yet
    if (MailApp.getRemainingDailyQuota() < 1) return;

    MailApp.sendEmail({
      to: email,
      subject: 'Reminder: invoice ' + inv + ' is past due',
      htmlBody: '<p>Hi ' + client + ',</p><p>A quick reminder that invoice <b>' + inv +
        '</b> for <b>$' + Number(amount).toFixed(2) + '</b> is now past due.</p>',
      name: 'Your Business Name'
    });
    sh.getRange(i + 2, 8).setValue(new Date());   // column H: reminder sent
  });
}

Run it every day

1. In the Apps Script editor, click the clock icon (Triggers) on the left.
2. Click Add Trigger.
3. Choose sendInvoiceEmails, event source Time-driven, type Day timer, and a time such as 8 to 9am.
4. Save, then add a second trigger for sendOverdueReminders.

Now you only fill in a row and pick Ready; the email goes out the next morning. Google allows 20 triggers per user per script and limits total trigger runtime to 90 minutes a day on gmail.com accounts, far more than this script needs.²

Limits and good habits

Source: Google Apps Script quotas, accessed October 2026.²

• Send from a business address with Google Workspace if you can; invoices from a personal gmail.com address look less professional.
• Include a payment link in the email body so clients can pay straight away.
• Keep the Status column honest: Ready, Sent, Paid. It doubles as your accounts receivable list.
• Back up the sheet before editing the script, and test changes on your own email first.

Questions

Related guides

Sources

1. Google Apps Script: Class MailApp
2. Google Apps Script: Quotas for Google Services, accessed October 2026