Automatically detects new Google Form submissions (via a linked Google Sheet), generates a blog post with AI, and pushes it to Substack as a draft.

Rendering mermaid diagram...

1. Poll Google Sheet — An interval val runs on a schedule (e.g. every 15 min). It fetches all rows from the unlisted Google Sheet via the public CSV/JSON export URL (no API key needed for unlisted sheets).
2. Deduplicate — Each row's timestamp (or row number) is tracked in SQLite so we never process the same submission twice.
3. Generate Blog Post — New submissions are sent to OpenAI (via Val Town's std/openai) to generate a full blog post (title + HTML body).
4. Push to Substack — The generated post is created as a draft on Substack via its internal API (POST /api/v1/drafts), authenticated with your substack.sid cookie.
5. Log & Review — You review and publish drafts in Substack's UI.

In the val's settings (left sidebar → Environment Variables), add these three:

The ID from your Google Sheet URL. For example, if the URL is:

https://docs.google.com/spreadsheets/d/1aBcDeFgHiJkLmNoPqRsTuVwXyZ/edit

then the ID is 1aBcDeFgHiJkLmNoPqRsTuVwXyZ.

⚠️ The sheet must be set to "Anyone with the link can view" (unlisted) for the pipeline to read it without Google API credentials.

Your substack.sid cookie, which authenticates API requests as you.

1. Log into substack.com in Chrome
2. Open DevTools: F12 (or right-click → Inspect)
3. Go to the Application tab → Cookies → https://substack.com
4. Find the cookie named substack.sid and copy its Value

⚠️ Treat this like a password — it gives full access to your Substack account. It stays valid for months as long as you don't log out.

Your Substack publication hostname, e.g. yourname.substack.com (no https://, just the hostname).

Run test-google-sheet.ts. You should see your column names and a preview of the first few rows. If it fails, double-check:

• The sheet ID is correct
• The sheet sharing is set to "Anyone with the link"

Run test-substack.ts. This will:

1. Verify your cookie authenticates successfully (prints your name/email)
2. Create a test draft post on your Substack

If it succeeds, check your Substack dashboard drafts — you should see a test draft. You can safely delete it.

Once both test scripts pass, the connections are working and we can build the main cron pipeline.

• README.md — This file
• test-google-sheet.ts — Script to test Google Sheet connectivity
• test-substack.ts — Script to test Substack API connectivity
• main.ts — (TODO) The main interval cron that ties it all together

• Project plan
• Test Google Sheet connection
• Test Substack connection
• AI blog post generation
• Main pipeline cron
• Dashboard (optional)