Generate itemized invoices as Docs

Northwind invoices are not complicated, but they are fiddly. Each one carries a client name, an invoice number, a date, and a line-item table that has to total correctly to the penny. Doing it in Docs by hand means copy-paste mistakes; doing it in a spreadsheet means the layout looks like a spreadsheet. The compromise most studios settle on is a templated Doc — keep the brand, keep the maths — and a script that fills it in.

This script takes an invoice number, finds every matching row in the Invoices sheet, copies the template Doc, swaps the placeholders for real values, and replaces the {{lineItems}} paragraph with a proper Docs table whose totals are calculated as it goes. The result is a clean, branded invoice you can download as a PDF and send.

What you’ll need

• A Doc template with placeholders {{client}}, {{number}}, {{date}}, {{total}}, and a paragraph containing only {{lineItems}} where the table should appear. The script replaces that paragraph with the table.
• An Invoices Google Sheet with these columns in the first row: client, invoiceNumber, date, description, qty, unitPrice. One row per line item; group rows by invoiceNumber.
• Edit access to both files for the script account, and a Drive folder (anywhere) where the generated copies can land. By default the copy is saved into the script user’s My Drive.

The script

// IDs of the template Doc and the Invoices sheet.
const TEMPLATE = '1abcInvoiceTemplateId';
const INVOICES = '1abcInvoicesId';

// Currency settings. Swap once if Northwind changes billing currency.
const CURRENCY_SYMBOL = '£';
const DATE_FORMAT = 'd MMM yyyy';
const DATE_TZ = 'GMT';

/**
 * Builds a branded invoice Doc for a single invoice number.
 *
 * @param {string} invoiceNumber  The invoiceNumber to filter rows on.
 * @returns {GoogleAppsScript.Drive.File} The generated Doc as a Drive file.
 */
function generateInvoiceDoc(invoiceNumber) {
  // 1. Pull every line item for this invoice number out of the sheet.
  const rows = readSheet(INVOICES).filter((r) => r.invoiceNumber === invoiceNumber);
  if (!rows.length) {
    throw new Error('No rows found for invoice "' + invoiceNumber + '".');
  }
  const inv = rows[0];

  // 2. Copy the template. The filename includes the number and client
  //    so it sorts and searches well in Drive.
  const copy = DriveApp.getFileById(TEMPLATE)
    .makeCopy('Invoice ' + invoiceNumber + ' — ' + inv.client);
  const doc = DocumentApp.openById(copy.getId());
  const body = doc.getBody();

  // 3. Simple placeholders. `replaceText` is regex-aware but a literal
  //    string works fine here because `{{}}` is rare in real prose.
  body.replaceText('{{client}}', inv.client);
  body.replaceText('{{number}}', invoiceNumber);
  body.replaceText('{{date}}', Utilities.formatDate(inv.date, DATE_TZ, DATE_FORMAT));

  // 4. Build the line-item table with a running total.
  let total = 0;
  const items = [['Description', 'Qty', 'Unit', 'Total']];
  for (const r of rows) {
    const line = Number(r.qty) * Number(r.unitPrice);
    total += line;
    items.push([
      r.description,
      String(r.qty),
      CURRENCY_SYMBOL + Number(r.unitPrice).toFixed(2),
      CURRENCY_SYMBOL + line.toFixed(2),
    ]);
  }

  // 5. Swap the {{lineItems}} paragraph for the real table.
  replaceParagraphWithTable(body, '{{lineItems}}', items);

  // 6. Final total. Use toLocaleString so big numbers get thousands separators.
  body.replaceText('{{total}}', CURRENCY_SYMBOL + total.toLocaleString(undefined, {
    minimumFractionDigits: 2,
    maximumFractionDigits: 2,
  }));

  doc.saveAndClose();
  Logger.log('Generated ' + copy.getName() + ' (total ' + CURRENCY_SYMBOL + total.toFixed(2) + ').');
  return copy;
}

/**
 * Finds the paragraph that contains `marker`, removes it, and inserts a
 * table built from `rows` at the same position.
 */
function replaceParagraphWithTable(body, marker, rows) {
  const search = body.findText(marker);
  if (!search) return;
  const para = search.getElement().getParent();
  const idx = body.getChildIndex(para);
  body.removeChild(para);
  body.insertTable(idx, rows);
}

/**
 * Reads a sheet's first tab into an array of objects keyed by the header row.
 */
function readSheet(id) {
  const [h, ...rows] = SpreadsheetApp.openById(id)
    .getSheets()[0]
    .getDataRange()
    .getValues();
  return rows.map((r) => Object.fromEntries(h.map((k, i) => [k, r[i]])));
}

How it works

1. generateInvoiceDoc opens the Invoices sheet, reads it into objects via readSheet, and filters down to the rows whose invoiceNumber matches the one passed in. Each kept row is one line item.
2. If nothing matches, it throws — better a loud error than a blank invoice. Otherwise it takes the first row to pull the shared fields (client, date) — every line item on the same invoice carries the same client.
3. It copies the template Doc with a filename that includes the invoice number and client, so the file is searchable in Drive.
4. replaceText swaps the simple placeholders. Utilities.formatDate formats the date with the constants at the top of the file, so a different style or timezone is a one-line change.
5. It walks the rows, multiplies qty by unitPrice for each line, and builds a 2D array starting with the header row. The running total is summed in the same loop, so the invoice’s footer always matches the table.
6. replaceParagraphWithTable finds the {{lineItems}} paragraph, notes its position in the body, removes it, and inserts a real Docs table at the same index. That preserves the layout of everything above and below.
7. The {{total}} placeholder gets the final figure, formatted with thousand separators and two decimal places. saveAndClose flushes the buffered edits so the Doc is consistent before the function returns the file handle.

Example run

The Invoices sheet contains:

client	invoiceNumber	date	description	qty	unitPrice
Acme Studio	NW-2025-014	2025-05-20	Branding workshop (half day)	1	950
Acme Studio	NW-2025-014	2025-05-20	Logo concept sketches	6	75
Acme Studio	NW-2025-014	2025-05-20	Final logo files (vector + raster)	1	400

Calling generateInvoiceDoc('NW-2025-014') writes Invoice NW-2025-014 — Acme Studio to Drive, with {{client}} filled in as Acme Studio, {{date}} as 20 May 2025, a four-column line-item table where the rows total £950, £450, and £400, and {{total}} showing £1,800.00. The log line reads Generated Invoice NW-2025-014 — Acme Studio (total £1800.00).

Run it

This is an on-demand job — you generate a Doc when an invoice is ready, not on a schedule.

1. In the Apps Script editor, select generateInvoiceDoc, click the parameters dropdown, and pass the invoice number (or call it from a small wrapper for the editor).
2. Approve the Drive, Docs, and Sheets scopes the first time it runs.
3. Open the generated Doc from My Drive, give it a once-over, and download it as a PDF to send.

To make it one click for non-developers, wrap the call in a custom menu on the Invoices sheet: read the invoice number from the currently selected row and pass it in.

Watch out for

• qty and unitPrice arrive from the sheet typed as numbers, but a stray text cell (a footnote, a hyphen) makes the multiplication return NaN. Number(...) is defensive here; cell formatting should still be set to Number.
• Floating-point arithmetic is fine for invoices up to the thousands but starts drifting at six decimal places. For high-precision billing, work in pennies (integers) and divide by 100 only when rendering.
• body.replaceText matches every occurrence. If {{client}} appears in a footer as well as the header, both are replaced — usually what you want. If you ever need to preserve one, wrap the placeholder differently.
• replaceParagraphWithTable removes the entire paragraph that holds {{lineItems}}. Put the marker on its own line in the template, never inline with other prose, or you will lose surrounding text.
• The script does not set the new Doc’s sharing. By default it inherits My Drive permissions for the script user. Move the file or call setSharing if clients need to view it directly rather than receive a PDF.