# R1 — lead capture: SETUP Read-date 2026-11-10. Written by JARVIS for a person who is not a programmer. You are installing one recipe: when a new lead arrives, its details go on a sheet and a reply is written for you. You read the reply and decide. Nothing in this recipe sends anything, on any of the three members below. That is the product, not a limitation - `RECIPE.md` says why. Pick ONE of the three members. They do the same job for three different places your leads might live. | Member | For | You need | |---|---|---| | **A. The plain files** — `lead_capture_to_sheets.py` | a folder you can drop files into | python 3.12, nothing else, no accounts | | **B. n8n** — `lead_capture.n8n.json` | an n8n you run (your own or your company's) | an n8n install, a Google account | | **C. Google Apps Script** — `lead_capture.apps-script.gs` | leads that already land in a Google Sheet | the Google account that owns that sheet | Member A works today with no account and no bill. If you are only trying the pack out, start there and take 5 minutes. --- ## Member A — the plain files ### What you need * Python 3.12 or newer (`python3 --version`). Nothing to install - it uses only what comes with Python. * A folder your form can write into, or a folder where you save exports by hand. ### Step 1 — see it work before you change anything (2 minutes) From inside the `r1` folder: ``` python3 lead_capture_to_sheets.py ``` It reads the two sample files in `samples/incoming/`, and prints a summary counting every row it saw. It writes two things: * `data/leads_sheet.csv` — one row per new lead, header row included; * `outbox/leads_drafts/0001-….txt`, `0002-….txt`, … — one draft per new lead. Open one of the `.txt` files. The first line says `DRAFT`. Nothing has left your machine: there is no mail code in this program to leave by. Run it a second time. The summary now says `appended : 0` and counts every address as a duplicate, and it writes no new draft. That is the guarantee: the same lead twice does not double-up. ### Step 2 — point it at your real leads ``` python3 lead_capture_to_sheets.py \ --incoming /path/to/where/your/form/drops/files \ --sheet /path/to/my_leads.csv \ --outbox /path/to/reply_drafts \ --signature "Your Name - Your Business" \ --subject "Your enquiry" ``` * `--incoming` the folder that holds new lead files. `.json`, `.csv`, `.tsv`. * `--sheet` the CSV that plays the part of a Google Sheet. Created if absent. * `--outbox` where drafts are written. Each file name starts with a number. * `--subject` and `--signature` the words at the top and bottom of the draft. * `--body-template my_wording.txt` optional: a file of your own holding the whole body. It may use `{name}`, `{source_clause}`, `{message_block}` and `{signature_block}` where it wants the details dropped in. * `--archive used` optional: move each file out of `--incoming` once it has been read, into `used/`. Useful if your form re-uses file names. * `--now` puts a fixed stamp on the run. You will not need it. * `--quiet` writes the files and prints nothing, for when it runs on a schedule. Exit codes: `0` done, `2` a named problem and **nothing was written**. A half unreadable folder cannot leave a half-written sheet - it reads and checks everything first, and only then writes. ### Step 3 — what your file's columns can be called You do not have to rename anything to `email`. Headers are matched after stripping case and punctuation, so `Email Address`, `email_address` and `E-MAIL` all count as the email. The names it knows: | It fills | From columns named (a few of them) | |---|---| | `email` | email, Email Address, lead email, contact email, work email, reply to | | `name` | name, Full Name, contact name, first name, lead name | | `phone` | phone, Phone Number, mobile, cell, telephone | | `source` | source, lead source, utm_source, channel, medium, campaign | | `message` | message, comments, notes, inquiry, details, description, brief | | `lead_date` | date, received at, submitted at, timestamp, created at | A column it does not know is ignored, not guessed. Your own extra column: add its name to the `ALIASES` table at the top of `lead_capture_to_sheets.py` - one line, and the rest of the file needs nothing. ### Step 4 — run it on a schedule It is one command that finishes, which is all a scheduler needs. Every 15 minutes on Linux or macOS, in `crontab -e`: ``` */15 * * * * cd /path/to/r1 && /usr/bin/python3 lead_capture_to_sheets.py --incoming /path/to/leads --sheet /path/to/my_leads.csv --outbox /path/to/reply_drafts --quiet >> /path/to/lead_capture.log 2>&1 ``` Windows: Task Scheduler, "Start a program", with the arguments above. Twice a day is usually enough. The lead's details are safe in the sheet the moment the file lands; the schedule only decides how soon the draft appears. --- ## Member B — n8n **Read this caveat first, in honesty:** n8n is not installed on the machine this pack was built on, so this workflow was **never executed by n8n**. It was assembled against n8n's documented node shapes and checked offline by the tests in `test_lead_capture.py` (it loads, it chains in one direction, the last node is a draft). You are the first execution. Step 3 tells you the five things to look at. ### Step 1 — the two credentials you add (this is what "the buyer adds" means) In n8n: **Credentials** ▸ **New**, and add these two. | Where it is used | Credential you pick in n8n | What it needs to be able to do | |---|---|---| | node "Add the lead row" | **Google Sheets** (OAuth2, or a service account) | write to one spreadsheet | | node "Create the reply draft" | **Gmail** (OAuth2) | create drafts in one mailbox | The workflow file arrives with both credential fields **empty** - deliberately, so it cannot be holding anyone's access but the person who owns it. ### Step 2 — import it **Workflows** ▸ **Import from File** ▸ choose `lead_capture.n8n.json`. ### Step 3 — the five checks before you turn it on 1. **All four nodes loaded.** The chain is `New lead arrives` → `Normalize and check` → `Add the lead row` → `Create the reply draft`. If n8n says it cannot load a node or a version, open that node and re-select it from its own picker - the settings it needs are named in this file, and re-entering them takes about a minute. 2. **The last node says Draft.** Open `Create the reply draft` and confirm the resource is **Draft**. *Do not swap it for the message/send operation to make it "work better".* That one change is the whole reason this recipe is written the way it is (`RECIPE.md`). 3. **Pick your spreadsheet and tab** in `Add the lead row` - two fields: the document, and the sheet inside it. Give the tab a header row of `receivedAt, leadDate, name, email, phone, source, message` so the columns line up (n8n will add missing ones if you do not). 4. **The webhook has a path.** It ships as `back-office-lead`. Your form posts to n8n's webhook URL for that path (the production URL is shown on the node). 5. **No credentials left empty** on the two Google nodes. ### Step 4 — test it without touching a real lead Open `New lead arrives` ▸ **Test workflow**, and post one synthetic lead: ``` curl -X POST -H 'Content-Type: application/json' \ -d '{"Full Name":"Test One","Email Address":"test@example.com","Source":"manual test","Message":"Just checking this works."}' ``` You should get: one row on the sheet, and **one draft in the Gmail account's Drafts folder**. Click "Execute workflow" again with the same JSON: the Code node holds a seen-list, so the second one is refused and nothing is added. ### Step 5 — turn it on Switch the workflow to Active. Your form's submissions now start landing. **One honest limit.** n8n clears a workflow's static data when the workflow is re-published. If a duplicate lead arrives after a re-publish, the worst that can happen is one extra *draft* waiting unopened. It cannot produce a second row without you noticing, and it cannot send a second message, because nothing in this workflow can send. Member A has no such hole: it checks the sheet itself. --- ## Member C — Google Apps Script ### Step 1 — put it in the right place Open the spreadsheet that will hold your leads ▸ **Extensions** ▸ **Apps Script**. Delete what is in `Code.gs`, paste the whole of `lead_capture.apps-script.gs`, save. (Or keep it as a standalone script project and set `CONFIG.spreadsheetId` to your spreadsheet's id.) ### Step 2 — set the five values at the top In `CONFIG`: `sheetName` (the tab, created if missing), `signature`, `subject`, `spreadsheetId` (only if standalone), and `webhookSecret` — **set this before you publish the web app**, because the URL it prints is otherwise open to anyone. It is not a password to anything you care about; it only stops a stranger posting junk into your sheet. ### Step 3 — prove it works, on your real sheet, with nobody involved In the Apps Script editor, pick `verifyR1` in the function picker and press **Run**. It authorises itself the first time (Google will ask once; it wants Gmail and Sheets). It then: * appends one test row, reads it back, confirms the duplicate check finds it, and **deletes the test row again**; * refuses a lead with no address; * creates **one draft** on the reserved `.invalid` address, which no mail system can ever deliver to. You get a line-by-line pass/fail log. Look in your Gmail **Drafts** for that one draft, read it, delete it. ### Step 4 — let your form post to it **Deploy** ▸ **New deployment** ▸ the gear ▸ **Web app**. Execute as *Me*. Who has access: *Anyone* (that is what lets your form post; add `?secret=` to the URL your form posts to). Copy the URL it gives you into your form's integration, and submit a test entry. --- ## What to do when it errors Member A prints one line beginning `ERROR:` and writes nothing. The lines it can print, and what each means: | It says | What to do | |---|---| | `no such folder. Pass --incoming ` | the `--incoming` path is wrong or unmade | | `has no header row, so no column can be named` | your CSV's first line must name the columns | | `no email column. Its columns are [...]` | your file has no column it can read as an email. It lists what it did find; add the name to `ALIASES` | | `not valid JSON` | the `.json` file is not valid JSON; it prints what is wrong and where | | `suffix .xlsx is not one of .json, .csv, .tsv` | export it as CSV, or leave the file out of that folder | | `its header is [...] but this recipe writes [...]` | that CSV is somebody else's file. Point `--sheet` at a new one | A single bad *row* is not an error: it is counted under `skipped` with its reason and the file and record number it came from. Bad addresses are never guessed at. ## What it costs you Nothing from me. Your own running costs depend on the member: Member A is a script on a machine you already own. n8n or a hosted workflow tool is your bill — the posted band I measured for platform cost is **$30–120/mo**, and for AI usage **$20–200/mo per automation** (brothersautomate.com, read 2026-11-10; your own plan may be cheaper, and for this recipe you may need neither).