Payment reminder emails from a Google Sheet

# Payment reminders

## Purpose

This project is a Google Sheets tool built with Apps Script. A Sheet lists clients, invoice amounts, due dates and paid status. A daily script emails reminders to clients whose invoices are unpaid and past due. The tool also flags overdue clients, drafts reminder emails, and logs every send attempt.

## Layout

- `src/Code.gs`: all Apps Script logic (menu, sheet reading, overdue rules, email text, trigger, guarded sending)
- `src/appsscript.json`: manifest (time zone, OAuth scopes, runtime V8)
- `tests/`: Node tests for pure functions (`tests/logic.test.js`)
- `SPEC.md`: what the tool must do
- `PLAN.md`: phased build plan, each phase ends with a `/goal`
- `REVIEW.md`: review checklist

@REVIEW.md

## Commands

- `npm test`: runs the Node tests on pure logic. No network, no Gmail.
- `npx clasp open`: opens the Apps Script editor in the browser.
- `npx clasp push`: uploads `src/` to Apps Script. Ask me first.
- `npx clasp deploy` and `npx clasp run`: ask me first. Never run them to test sending.

## Gotchas

- Apps Script has no npm. Keep pure logic (date math, row parsing, email text) in functions that take plain objects. Tests load them through the guarded `module.exports` block, which is skipped inside Apps Script (`typeof module` check).
- Never send email from a test or a preview. `sendReminders` must honour the DRY_RUN flag from `Settings!B2` (Send live emails). Default is ON: blank or FALSE means dry run.
- Gmail quota applies (about 100 recipients per day on consumer accounts). Keep `MAX_SENDS_PER_RUN = 50` and the per-invoice cap of 3 reminders.
- Log every send attempt to the Sent Log tab, including errors.
- Write to sheets in one batch call where possible. Do not call `getRange` in a loop per cell.
- Column names come from SPEC.md exactly (Client Name, Email, Invoice ID, Amount, Due Date, Paid, Last Reminder Sent, Reminder Count). If a column must change, update SPEC.md first, then the code.
- `.clasp.json` holds a real script ID. Do not commit it if the repo is shared. In docs and examples, use `YOUR_SCRIPT_ID`.
- Never put real email addresses, script IDs or API keys in code, tests or docs. Use placeholders such as `you@example.com`.
- Dates use the script's time zone (set in `src/appsscript.json`). Compare due dates at start of day.

## Non-default conventions

- Plain English in menus and messages. No jargon in anything the user sees.
- The Sheet and the code stay in sync through the column names above. Do not rename a column in only one place.
- Functions named in SPEC.md wiring are the only ones that read or write sheets. Pure functions never touch `SpreadsheetApp`.

## Workflow

1. Explore the code and SPEC.md first.
2. Plan the phase in PLAN.md.
3. Implement one phase only.
4. Run the `verify` skill and read failures only.
5. Run `/clear` before starting the next phase.

Use `/code-review` before accepting a phase. It applies the rules in REVIEW.md.

{
  "model": "sonnet",
  "effort": "medium",
  "permissions": {
    "allow": [
      "Bash(npm test)",
      "Bash(npm run test:*)",
      "Bash(npx clasp open)",
      "Read(src/**)",
      "Edit(src/**)",
      "Read(tests/**)",
      "Edit(tests/**)"
    ],
    "ask": [
      "Bash(npx clasp push*)",
      "Bash(npx clasp deploy*)",
      "Bash(npx clasp run*)"
    ],
    "deny": [
      "Bash(rm -rf:*)",
      "Read(./.clasp.json)",
      "Read(./.env)"
    ]
  }
}

---
name: verify
description: Run after any change to src/ or tests/ in this Apps Script project to check tests, sheet access rules, dry-run default, and push status before reporting done.
allowed-tools: Bash(npm test), Bash(npx clasp push --force)
---

# Verify

Use this skill after any change to `src/` or `tests/`. It proves the change works without sending any email.

## Rules

- Never run `clasp run` or any function that sends mail.
- Run `npx clasp push --force` only after the user confirms in this chat. If they have not confirmed, skip step 3 and mark it SKIPPED.
- Do not edit files during verification. Report problems and wait for the user.

## Steps

1. **Tests.** Run `npm test`. Report only failing test names and their error lines, not the full log. If there are no tests yet, report PASS only when the command exits with code 0.
2. **Sheet access.** Search `src/Code.gs` for `SpreadsheetApp`. Every call must sit inside a function named as sheet wiring in SPEC.md. Pure logic functions must not call `SpreadsheetApp`. Report the function name of any violation.
3. **Push.** After user confirmation, run `npx clasp push --force` and report success or the first error line. Do not retry more than once.
4. **Dry-run default.** In `src/Code.gs`, confirm the fallback used when `Settings!B2` is blank or FALSE is dry run (true). Report the line number where the default is set. If it is missing or false, report FAIL.

## Report format

Print one line per step, in this shape:

```
1 Tests: PASS
2 Sheet access: FAIL - onOpen calls SpreadsheetApp outside wiring list
3 Push: SKIPPED - user did not confirm
4 Dry-run default: PASS (Code.gs line 12)
```

For test failures, add one line under step 1 for each failing test name:

```
1 Tests: FAIL
  - isOverdue: 3 days overdue exactly
```

End with one line: `OVERALL: PASS` only if steps 1, 2 and 4 pass and step 3 passed or was skipped by the user's choice. Otherwise `OVERALL: FAIL`.

# Payment reminders: setup guide

This guide sets up a tool that reads a Google Sheet of invoices, finds unpaid clients who are past due, and emails them a polite reminder. It takes about an hour the first time.

## What you are installing

- **Google Sheets** holds your client list. You already use it or can open it in a browser.
- **Apps Script** is Google's free scripting language. It runs inside your Google account and can read your Sheet and send Gmail. You write it in a web editor, and it is stored with the Sheet.
- **Claude Code** is an AI coding assistant that runs in a terminal. You describe what you want, and it writes and changes the script for you.
- **clasp** is a small tool that copies files from your computer up to Apps Script, so Claude can edit files on your computer instead of a web editor.
- **Node.js** runs the tests. Tests check the date rules and email text on your computer before anything reaches Gmail. The tests use no outside packages, so there is nothing else to install for them.

You do not need to write any code yourself.

## Before you start

- A Google account (a Workspace account has higher Gmail limits than a consumer account).
- Node.js version 20 or newer.
- Claude Code installed. Follow the official install instructions for your system first.

## Step 1: Install Node.js

1. Go to nodejs.org and download the LTS version.
2. Run the installer and accept the defaults.
3. Open a terminal. On Windows, open PowerShell. On Mac, open Terminal.
4. Type this and press Enter:

```
node --version
```

Expected output: a line such as `v20.11.0` or higher. If you see "command not found", close the terminal, open a new one, and try again.

## Step 2: Install clasp

In the same terminal, run:

```
npm install -g @google/clasp
```

This installs clasp for your whole computer. Expected output ends with a line showing the installed version and no red error text. Check it with:

```
clasp --version
```

## Step 3: Turn on the Apps Script API

1. Go to script.google.com/home/usersettings in your browser.
2. Find the switch named **Google Apps Script API**.
3. Turn it on.

clasp cannot upload code until this is on.

## Step 4: Sign in with clasp

Run:

```
clasp login
```

A browser window opens. Sign in with the Google account that owns the Sheet and approve the permissions. The terminal then prints a message such as `Authorization successful`. If the browser does not open, copy the link from the terminal into your browser.

## Step 5: Create the project folder and install Claude Code

1. Make a folder on your computer, for example `Documents/payment-reminders`.
2. Open a terminal inside that folder. On Windows, open PowerShell in the folder. On Mac, drag the folder onto the Terminal icon.
3. Start Claude Code by typing:

```
claude
```

The first time, it asks you to sign in. Follow the prompts, then type `/exit` to leave for now.

## Step 6: Copy the starter files into the folder

Copy these files and folders from the starter kit into your project folder, keeping the same names:

- CLAUDE.md, SPEC.md, PLAN.md, REVIEW.md, INSTALL.md
- .claude/settings.json and .claude/skills/verify/SKILL.md
- src/Code.gs and src/appsscript.json

Some systems hide folders that start with a dot. If you cannot see `.claude`, turn on "show hidden files" in your file manager.

Then, in the terminal inside the folder, set up the test runner:

```
npm init -y
```

Expected output: a short message ending with `Wrote to .../package.json`. This creates a file that tells npm how to run the tests. You do not need to install anything else.

## Step 7: Create the Sheet and link it to the script

Run this in the terminal:

```
npx clasp create --type sheets --title "Payment Reminders" --rootDir src
```

What it does: creates a new Google Sheet and a script attached to it, and writes a small file called `.clasp.json` that records the script's ID.

Expected output: a line saying the project was created, and a link to the new Sheet or script editor. Open the Sheet link and rename the Sheet to Payment Reminders if it is not already named that.

If you made an empty Sheet earlier, you can delete it. Use the new Sheet from this step.

**Privacy note:** `.clasp.json` contains your script ID. Do not share this folder or commit it to a shared repository. In shared examples, the ID appears as `YOUR_SCRIPT_ID`.

## Step 8: Add the Settings and Sent Log tabs

In the Sheet, make two more tabs. Use these exact names.

**Settings tab:**
- Cell A2: `Send live emails`
- Cell B2: `FALSE`

Leave FALSE for now. This is the safety switch. While it is FALSE, the tool only logs what it would send.

**Sent Log tab:**
- Row 1 headers: `Timestamp`, `Client`, `Invoice ID`, `Email`, `Status`

**Clients tab:** rename the first tab to `Clients` and add these headers in row 1, exactly as written:

`Client Name`, `Email`, `Invoice ID`, `Amount`, `Due Date`, `Paid`, `Last Reminder Sent`, `Reminder Count`

Add three or four sample rows. Use made-up names and an email address you control for at least one row.

## Step 9: Let Claude build the first phase

1. Start Claude Code in the project folder:

```
claude
```

2. Paste the kickoff prompt from the plan and press Enter.
3. Watch the work. Claude will ask before running `clasp push` or `clasp deploy`. Say no until you have reviewed the code.

When Claude says Phase 1 is done, type `/exit`.

## Step 10: Check the tests

In the terminal, run:

```
npm test
```

Expected output: a short summary with `pass` counts and no `fail` lines. Then check the exit code:

```
npm test; echo EXIT=$?
```

Expected: `EXIT=0`. Tests run only on your computer. They never send email.

## Step 11: Set your time zone

1. Open `src/appsscript.json` in any text editor.
2. Find the line with `"timeZone"` and change the value to your zone, for example `Europe/London` or `America/Chicago`. Use a name from the official time zone list.
3. Save the file.
4. Upload the change:

```
npx clasp push
```

Expected output: a list of files uploaded, ending with a line such as `Pushed 3 files.` Claude asks before running this during a build, but you run it yourself here.

The daily trigger runs at 9:00 in this time zone.

## Step 12: Preview reminders

1. Reload the Sheet. Wait a few seconds for the menu to appear.
2. Click **Payments**, then **Preview reminders**.
3. Google asks you to authorize the script the first time. Click through the prompts and choose your account.
4. A preview appears listing the clients who would get a reminder. Nothing is sent.

Check that the list matches your sample rows. Paid clients, clients not yet due, and clients who have had three reminders should not appear.

## Step 13: Send one test email to yourself

Do this with one client row that uses your own email address. Set its Due Date to at least four days ago and Paid to `FALSE`.

1. In the Sheet, go to **Settings** and change B2 to `TRUE`.
2. Open the script editor with this command in the terminal:

```
npx clasp open
```

3. In the editor, choose `sendReminders` from the function list and click **Run**.
4. Check your inbox for the reminder.
5. Check the **Sent Log** tab. It should show one row with status `sent`.
6. Change B2 back to `FALSE` until you are ready for real clients.

To confirm the daily trigger, click **Payments**, then **Install daily trigger**. The trigger runs once a day at 9:00.

## Troubleshooting

**"Gmail daily limit" or emails stop sending**
Consumer Gmail accounts can send about 100 emails a day to recipients. The tool stops at 50 per run and writes a note in Sent Log for anything it skipped. Wait until the next day, or use a Google Workspace account for higher limits.

**"Google hasn't verified this app" or other authorization prompts**
This appears because the script is your own and not published. Click **Advanced**, then **Go to Payment Reminders (unsafe)**, then **Allow**. This is safe for a script you wrote yourself. Only authorize scripts you trust.

**"clasp login" fails or the browser does not open**
- Run `clasp logout`, then `clasp login` again.
- Make sure the Apps Script API is on (Step 3).
- Copy the link from the terminal into your browser by hand.
- Try a different browser if pop-ups are blocked.

**"Could not find script" or push errors**
Check that `.clasp.json` exists in the project folder and that `rootDir` is `src`. Run `npx clasp push` again.

**Menu does not appear in the Sheet**
Reload the Sheet. If it is still missing, run `npx clasp push` and then reload again.

**Tests fail after a change**
Start Claude Code and ask it to explain the failing test name. Do not edit the tests to make them pass without checking the rule they test.

# Build plan

Work one phase at a time. After each phase:

1. Run the verify skill.
2. Review the change against REVIEW.md.
3. Run /clear before starting the next phase.

Each phase ends with a /goal prompt. Paste it into Claude Code when you start that phase.

## Phase 1: setup

Create the Apps Script project with clasp. Add src/appsscript.json, a sample Clients sheet and a Settings tab. Add the project files listed in CLAUDE.md.

Goal: clasp push succeeds and npm test runs, even with no tests yet.

```
/goal Phase 1 is complete when all of these are true. (1) These files exist: CLAUDE.md, SPEC.md, PLAN.md, REVIEW.md, .claude/settings.json, .claude/skills/verify/SKILL.md, src/Code.gs, src/appsscript.json, INSTALL.md. (2) npm test exits 0, even with zero tests. (3) npx clasp push reports success. Check: run `npm test; echo EXIT=$?` and confirm EXIT=0, then run `npx clasp push` and confirm success. Constraints: ask me before running clasp push. Do not send any email. Do not put real email addresses, script IDs or API keys in any file. Keep DRY_RUN default ON. Do not edit SPEC.md except to fix typos. Stop after 25 turns and report what is done and what is blocked.
```

## Phase 2: pure logic

Implement isOverdue, nextReminderDue, buildEmailText and row parsing in src/Code.gs. These functions take plain objects, not sheet objects, so Node can load them. Add Node tests in tests/logic.test.js.

Goal: npm test passes with at least 8 cases.

```
/goal Phase 2 is complete when npm test passes with at least 8 test cases in tests/logic.test.js. The cases must cover isOverdue, nextReminderDue, buildEmailText and row parsing, including the exactly-3-days edge case and blank email. Check: run `npm test; echo EXIT=$?` and confirm EXIT=0 and that the summary reports 8 or more tests. Constraints: pure functions must not call SpreadsheetApp, GmailApp or any Apps Script service. Do not send mail. Do not run clasp push or clasp run. Do not put real email addresses or secrets in tests. Stop after 25 turns and report what is done and what is blocked.
```

## Phase 3: sheet wiring

Read the Clients tab. Add the Payments menu with Preview reminders. Preview shows the rows that would be emailed. It does not send mail and does not write Sent Log rows.

Goal: preview lists the expected clients from the sample sheet without sending mail.

```
/goal Phase 3 is complete when Payments > Preview reminders lists the expected clients for the 10-row sample sheet described in SPEC.md Acceptance, and sends no mail. Check: run `npm test; echo EXIT=$?` and confirm EXIT=0. Then, in the sample Sheet, run Payments > Preview reminders and confirm the list matches the expected clients in SPEC.md, and that the Sent Log tab gained no rows. Constraints: do not call GmailApp.sendEmail or MailApp in any function reachable from preview. Do not run clasp run. Do not put real email addresses in any file. Stop after 25 turns and report what is done and what is blocked.
```

## Phase 4: sending

Add sendReminders with dry run as the default. Add the daily trigger installer, the 50-send cap per run, and the reminder counter updates. Writes to Sent Log go in one batch call.

Goal: dry run logs the intended sends. The live path is guarded by the Settings flag.

```
/goal Phase 4 is complete when sendReminders works in dry run and the live path is guarded by Settings!B2. Check: run `npm test; echo EXIT=$?` and confirm EXIT=0. Then confirm in code that sendReminders returns early with status "dry run" unless Settings!B2 is TRUE, and that MAX_SENDS_PER_RUN is 50. Confirm that a dry-run pass adds "dry run" rows to Sent Log and leaves Last Reminder Sent and Reminder Count unchanged. Constraints: do not set Settings!B2 to TRUE. Do not run sendReminders against live mail. Do not run clasp run. Do not put real email addresses or secrets in any file. Stop after 25 turns and report what is done and what is blocked.
```

## Phase 5: hardening

Handle bad dates and missing emails without stopping the run. Add duplicate-send protection so one invoice is not emailed twice in a day. Add a short README.md section for the user. Then run /code-review against REVIEW.md.

Goal: the code passes review against REVIEW.md and the tests cover the hardening cases.

```
/goal Phase 5 is complete when npm test exits 0 with cases for an unparseable date, a missing email and a duplicate send within the same day. README.md has a short section for the user. /code-review reports no open items against REVIEW.md. Check: run `npm test; echo EXIT=$?` and confirm EXIT=0. Run /code-review and confirm no open items. Constraints: do not send mail. Do not run clasp push or clasp run without my confirmation. Do not put real email addresses, script IDs or API keys in any file. Stop after 20 turns and report what is done and what is blocked.
```

# Review checklist

Apply this list before accepting any phase. Claude and the user both use it.

Note: CLAUDE.md imports this file with `@REVIEW.md` so `/code-review` applies these rules.

## Email safety

- [ ] No real email is sent during tests or previews.
- [ ] `DRY_RUN` still defaults to ON. Live sending needs both Settings!B2 = TRUE and the Script Property `DRY_RUN` = FALSE.
- [ ] `sendReminders` checks the dry-run flag before every `MailApp.sendEmail` call.
- [ ] Reminder counters change only on a real send, never on a dry run.

## Secrets and identifiers

- [ ] No hardcoded email addresses, script IDs or API keys in code, tests or docs.
- [ ] `.clasp.json` uses the YOUR_SCRIPT_ID placeholder in any shared example.

## Dates

- [ ] Dates use the script's time zone (set in src/appsscript.json).
- [ ] Due Date comparisons use start-of-day, so time of day never changes the result.
- [ ] "More than 3 days overdue" means 4 or more days after the due date. Exactly 3 is not overdue.

## Sheets

- [ ] Column names match SPEC.md exactly. Missing columns stop the run with a plain message.
- [ ] Writes use one batch call where possible. No loop calls `getRange` for each cell.
- [ ] Every send attempt, skip and error gets a line in the Sent Log.

## Errors

- [ ] Errors are caught per row, so one bad row does not stop the run.
- [ ] Bad dates and blank emails are logged as skipped, with the reason.

## Tests

- [ ] Tests cover: paid, due today, exactly 3 days overdue, reminder limit reached, blank email.
- [ ] Tests do not touch SpreadsheetApp, MailApp or ScriptApp.

## Wording

- [ ] Menus and messages use plain English with no jargon.
- [ ] Messages say what happened and what the user can do next.

# Payment reminders spec

## Purpose

A Google Sheet lists clients and their invoices. A daily script emails a polite reminder to each client whose invoice is unpaid and overdue. You can preview the list from the Payments menu before anything is sent.

## Clients tab

Use these column names exactly, in this order:

| Column | Type | Notes |
|---|---|---|
| Client Name | Text | Used in the email |
| Email | Text | Required to send. Blank rows are skipped and logged |
| Invoice ID | Text | One invoice per row |
| Amount | Number | Shown in the email |
| Due Date | Date | Start-of-day comparison |
| Paid | TRUE or FALSE | Set to TRUE when paid |
| Last Reminder Sent | Date | Written by the script |
| Reminder Count | Number | Written by the script |

## Overdue and reminder rules

- An invoice is overdue when Paid is FALSE and today is more than 3 days after Due Date.
- Exactly 3 days after Due Date is not yet overdue.
- Send at most one reminder every 7 days.
- Send at most 3 reminders per invoice. At Reminder Count 3, stop.
- Paid is TRUE: never send.
- Email is blank: skip the row and log it.

## Email template

Subject: Reminder: invoice {{invoiceId}} is {{daysOverdue}} days overdue

Body:

Hello {{clientName}},

I hope you are well. This is a friendly reminder that invoice {{invoiceId}} for {{amount}} was due on {{dueDate}} and is now {{daysOverdue}} days overdue.

If you have already paid, please reply with the payment date so we can update our records. If you have any questions, just reply to this email.

Thank you.

The sender name in Settings B3 is used as the From name and signature.

## Schedule and menu

- A daily trigger runs sendReminders() at 9:00 in your time zone.
- Payments > Preview reminders shows who would get which reminder. It sends nothing and writes no Sent Log rows.
- Payments > Install daily trigger creates the 9:00 trigger.

## Settings tab

- A2: Send live emails. B2: FALSE by default. Blank counts as FALSE.
- A3: Sender name. B3: the name shown on emails.
- Dry run is ON unless B2 is TRUE. In dry run, reminders are logged as "dry run" and no email is sent.
- Each run sends at most 50 emails. This limit is fixed in the code.

## Sent Log tab

Columns: Timestamp, Client Name, Invoice ID, Email, Status.

Every send attempt gets one row. Status is one of: sent, dry run, skipped (blank email), or error with a short message.

## Out of scope for v1

- Payment processing
- Invoice generation
- SMS
- Multiple currencies

## Acceptance

Preview lists the correct overdue clients for a sample sheet of 10 rows. The sample must include:

- one paid invoice
- one invoice not yet due
- one invoice exactly 3 days overdue
- one invoice at the 3-reminder limit
- one row with a blank email