SheetFlow turns a spreadsheet into a small-business back office.

What it automates

Four workflows that come up in most small businesses. Each one can be used on its own.

The Leads sheet and the SheetFlow menu

Coloured status chips, overdue follow-ups in red and flagged duplicates. Everything in the menu is plain TypeScript compiled to Apps Script.

Pipeline rules

Invalid moves (for example New to Won) are reverted by onEdit, with a toast that explains why. Follow-ups skip weekends and holidays.

From orders to invoice PDF and Gmail draft

"Invoices > Generate from Orders" groups un-invoiced rows by customer, numbers them, renders the PDF and writes the invoice number back to each order. Emails are created as drafts, so nothing is sent without a human check.

Weekly KPI summary

The KPI tab holds formulas such as =COUNTIFS(Leads!F:F,"Won",Leads!I:I,">="&A2, ...), so it stays correct when someone edits data by hand. A time-driven trigger rebuilds the chart every Monday.

Pipeline today: New 4 · Contacted 2 · Qualified 6 · Proposal 10 · Won 10 · Lost 8

Form intake routing

An installable onFormSubmit trigger sends each response to a tab by the answer to "What do you need help with?". Answers are trimmed, capped, and protected against formula injection.

Architecture

Business rules never touch Google globals (a lint rule enforces this), so they run and are unit-tested in Node. Thin adapters do the Sheets, Docs, Drive and Gmail calls.

Triggers and permissions

Scopes are kept narrow: spreadsheets.currentonly, documents, drive.file (only files SheetFlow creates), gmail.compose (drafts, not send), script.scriptapp and script.container.ui.

How I'd set this up for you

1. 30-minute call

   We map your current sheet, statuses, tax rules, invoice look and who needs reminders.

2. Adapt the schema

   Columns, dropdowns and pipeline rules live in one schema file, so I change them there and the tests confirm nothing else broke.

3. Install in your Google account

   I push the script to a copy of your sheet with clasp. You approve the permissions; I never need your password.

4. Your branded invoice template

   Logo, fonts and payment details go in the Docs template. We generate a test batch together and check every number.

5. Hand-off

   A short video walkthrough, a README for your team, and triggers switched on. Typical turnaround: 3 to 5 days.

A taste of the code

// src/core/invoice.ts: pure, tested, no Google APIs
export function computeTotals(lines: InvoiceLine[], taxRatePct: number): InvoiceTotals {
  let subtotal = 0, net = 0, taxableBase = 0;
  for (const line of lines) {
    const { gross, amount } = lineAmount(line.quantity, line.unitPrice, line.discountPct);
    subtotal += gross;
    net += amount;
    if (line.taxable) taxableBase += amount;
  }
  const tax = roundHalfUp((taxableBase * taxRatePct) / 100);
  return { subtotal, discount: subtotal - net, taxableBase, tax, total: net + tax };
}