Send recurring invoice emails on the first

Northwind bills several clients on a monthly retainer. On the 1st of each month someone has to copy the invoice template, swap in the client name and amount, bump the invoice number, export a PDF, and email it. It is ten minutes per client of pure copy-paste — and the kind of job that is easy to forget.

This script does the whole round. On the 1st it reads the Clients sheet, generates an invoice from a Doc template for each active client, saves a PDF in a dated Drive folder, and emails it. Invoice numbers come from a counter in Script Properties, so they march upward without gaps or repeats.

What you’ll need

• A Clients Google Sheet with a header row and these columns: name, email, monthlyRetainer (a number), and status (only rows marked active are billed).
• A Google Doc invoice template containing the placeholders {{client}}, {{amount}}, {{number}}, and {{date}} — the script replaces these.
• An invoices/ root folder in Drive. The script creates a YYYY-MM subfolder inside it for each month’s PDFs.
• The IDs of the sheet, the template, and the root folder for the three config values at the top of the script.

The script

// The Clients sheet, the Doc invoice template, and the Drive folder
// that holds every month's invoices.
const CLIENTS_SHEET = '1abcClientsSheetId';
const INVOICE_TEMPLATE = '1abcInvoiceTemplateId';
const INVOICES_ROOT = '1abcInvoicesRootId';

// The first invoice number to use if no counter has been stored yet.
const STARTING_INVOICE_NUMBER = 1001;

/**
 * Generates and emails a retainer invoice for every active client.
 * Designed to run once a month on the 1st.
 */
function sendMonthlyInvoices() {
  // 1. Work out this month's folder name, e.g. "2025-08".
  const yearMonth = Utilities.formatDate(new Date(), 'GMT', 'yyyy-MM');

  // 2. Find or create the dated subfolder for this month's PDFs.
  const monthFolder = getOrCreate(
    DriveApp.getFolderById(INVOICES_ROOT), yearMonth);

  // 3. Read the next invoice number from the stored counter.
  const counter = nextInvoiceNumber();

  // 4. Read the Clients sheet and keep only the active rows.
  const clients = readSheet(CLIENTS_SHEET)
    .filter((c) => c.status === 'active');

  if (!clients.length) {
    Logger.log('No active clients — nothing to invoice.');
    return;
  }

  // 5. Render and email one invoice per client. Each gets the next
  //    sequential number (counter, counter + 1, ...).
  clients.forEach((c, i) => {
    const number = counter + i;
    const file = renderInvoice(c, number, monthFolder);
    GmailApp.sendEmail(
      c.email,
      `Invoice ${number} — Northwind`,
      `Hi ${c.name},\n\nAttached is your invoice for ${yearMonth}.\n\n— Northwind`,
      { attachments: [file.getAs('application/pdf')] });
  });

  // 6. Advance the stored counter past the numbers just used.
  saveInvoiceCounter(counter + clients.length);
  Logger.log('Sent ' + clients.length + ' invoice(s) for ' + yearMonth + '.');
}

/**
 * Copies the Doc template, fills in the placeholders for one client,
 * and returns the saved copy (used to attach a PDF).
 */
function renderInvoice(client, number, folder) {
  // Copy the template into the month folder with a descriptive name.
  const copy = DriveApp.getFileById(INVOICE_TEMPLATE)
    .makeCopy(`INV-${number} ${client.name}`, folder);

  // Open the copy and swap every placeholder for real values.
  const doc = DocumentApp.openById(copy.getId());
  const body = doc.getBody();
  body.replaceText('{{client}}', client.name);
  body.replaceText('{{amount}}', `$${client.monthlyRetainer.toLocaleString()}`);
  body.replaceText('{{number}}', `INV-${number}`);
  body.replaceText('{{date}}', Utilities.formatDate(new Date(), 'GMT', 'yyyy-MM-dd'));
  doc.saveAndClose();
  return copy;
}

/**
 * Reads the next invoice number from Script Properties, falling back
 * to STARTING_INVOICE_NUMBER on the very first run.
 */
function nextInvoiceNumber() {
  const props = PropertiesService.getScriptProperties();
  return parseInt(
    props.getProperty('NEXT_INVOICE') || String(STARTING_INVOICE_NUMBER));
}

/**
 * Stores the next invoice number so the sequence survives across runs.
 */
function saveInvoiceCounter(n) {
  PropertiesService.getScriptProperties().setProperty('NEXT_INVOICE', String(n));
}

/**
 * Returns the named subfolder of `parent`, creating it if it does not
 * already exist.
 */
function getOrCreate(parent, name) {
  const it = parent.getFoldersByName(name);
  return it.hasNext() ? it.next() : parent.createFolder(name);
}

/**
 * Reads a sheet by ID and returns its rows as objects keyed by the
 * header row.
 */
function readSheet(id) {
  const [header, ...rows] = SpreadsheetApp.openById(id)
    .getSheets()[0].getDataRange().getValues();
  return rows.map((r) => Object.fromEntries(header.map((h, i) => [h, r[i]])));
}

How it works

1. sendMonthlyInvoices formats today’s date as YYYY-MM — that string names the month’s invoice folder.
2. getOrCreate finds that subfolder inside the invoices/ root, or creates it the first time the script runs in a new month.
3. nextInvoiceNumber reads the stored counter from Script Properties, starting at STARTING_INVOICE_NUMBER on the first ever run.
4. readSheet loads the Clients sheet as objects, and the script keeps only rows whose status is active. If none are active it logs and stops.
5. For each client it calls renderInvoice — which copies the Doc template, replaces the four placeholders, and saves the copy — then emails the client with the rendered Doc attached as a PDF. Each client gets the next number in sequence.
6. saveInvoiceCounter advances the stored counter by the number of invoices sent, so next month’s run picks up exactly where this one left off.

Example run

The Clients sheet on 1 August, with the stored counter at 1004:

name	email	monthlyRetainer	status
Acme Co	[email protected]	2400	active
Bluefin Ltd	[email protected]	1800	active
Cedar Studio	[email protected]	3200	paused

Cedar Studio is paused, so it is skipped. The run produces:

• Drive › invoices/2025-08/INV-1004 Acme Co and an email to Acme with the PDF, subject Invoice 1004 — Northwind.
• Drive › invoices/2025-08/INV-1005 Bluefin Ltd and an email to Bluefin, subject Invoice 1005 — Northwind.
• The stored counter advances to 1006, ready for September.

The log reads Sent 2 invoice(s) for 2025-08.

Trigger it

This job must run once a month, unattended:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose sendMonthlyInvoices, event source Time-driven, type Month timer, day of month 1, and a time such as 7am to 8am.

It now fires on the 1st of every month without anyone touching it.

Watch out for

• Invoice numbers must never repeat. Storing the counter in Script Properties survives across runs and is concurrency-safe enough for a once-a-month job. Do not also edit NEXT_INVOICE by hand, or the sequence will jump.
• Gmail caps daily sends — 100 a day on a consumer account, 1,500 on Workspace. A monthly retainer batch is well inside that, but the same script run twice in one day counts twice.
• Re-running in the same month does not overwrite — it creates a second set of invoices with fresh numbers and emails them again. Run it once, or add a guard that checks whether this month’s folder already has invoices.
• monthlyRetainer must be a number. If a cell is text, toLocaleString will not format it and the amount will look wrong on the invoice.
• The template’s placeholders must match exactly, braces included. A stray space inside {{ client }} means replaceText finds nothing and the placeholder ships to the client.
• For high-frequency billing, see Auto-number invoices and POs without gaps.