Nightly bulk content drafts from a Google Sheets backlog with Claude

PromptCreate

Every night at 2am, I want a code workflow that drains my content backlog spreadsheet through Anthropic's Claude Message Batches API. Batch mode is 50% of the standard price, so this is how I want to bulk-draft copy affordably instead of hitting rate limits during the day.

Step 1. Read from Google Sheets. Use the Get Values operation on the 'Content Queue' tab of a Google Sheet whose ID is configurable. Assume the header row includes columns for row number, topic, content type, brand voice, draft, and status. Only pick up rows where topic and content type are filled in but the draft column is empty.

Step 2. Build the batch. For each eligible row, up to a configurable per-run cap (default 100), build one Claude message request. The prompt should combine the row's topic, content type (blog intro, LinkedIn post, product description, etc.), and any brand voice notes from that row into a clear instruction. Set custom_id equal to the row number so results can be matched back to the correct row later.

Step 3. Submit and wait. Call Anthropic's Create Message Batch with all the requests in one payload. Then poll Retrieve Message Batch on an interval until processing_status is 'ended'. Once it has ended, pull the JSONL from Get Message Batch Results.

Step 4. Write drafts back. Parse each JSONL line and use Google Sheets Batch Update Values to write the generated text into the draft column of the row whose row number matches the custom_id. In the same batch update, flip that row's status column to 'drafted'. If Claude returned an error for a row, leave the draft empty and write a short error note to the status column so I can retry it on the next nightly run.

Make these easy to change from workflow settings: the spreadsheet ID, the tab name, the per-run cap, the trigger time, and which Claude model is used. Include a hard safety limit so a single night can never process more than the cap, even if the sheet grows.

Use a code workflow because the pipeline is fully deterministic: read the sheet, submit one batch, poll for completion, write the results back. No agent reasoning required.

What does this prompt do?

Reads your content backlog spreadsheet overnight and picks up every row that still needs a draft.
Sends the whole batch to Claude in one overnight run at 50% of the usual per-word cost, with no rate-limit headaches.
Writes each finished draft back to the correct row and flips a status column so your team can see what is ready for review.
Caps how many rows are processed per night so a single run cannot blow through your monthly AI budget.

What do I need to use this?

A Google account with edit access to the spreadsheet that holds your content backlog.
An Anthropic account (Geni prompts for an API key from your Anthropic console when you connect it).
A backlog tab with columns for topic, content type, brand voice, draft, and status.

How can I customize it?

Change the nightly kickoff time, or run it only on weekdays instead of every night.
Raise or lower the per-run cap to match your team's monthly AI budget.
Swap the Claude model (for example, use a lighter model for social posts and a heavier one for long-form pieces).
Add extra columns like target keyword, audience, or word count so the drafts come out more on-brand.

FAQs

How is this cheaper than drafting one row at a time?

Claude's overnight batch pricing is 50% of the standard rate. A backlog of 500 rows costs about half what it would if the same prompts were sent one by one during the day.

What if the batch is not finished by morning?

The workflow keeps checking on the batch and only writes drafts back once Claude has finished. Anything still in progress stays queued and picks up on the next nightly run.

Will this overwrite drafts my team has already edited?

No. The workflow only picks up rows where the draft column is empty. Anything you or a teammate has already written or tweaked is left alone.

Can I use this for things other than marketing copy?

Yes. It works just as well for product descriptions, article summaries, data enrichment notes, or backfilled classifications, anywhere you have hundreds of rows that each need a short piece of AI-generated text.

What happens to rows missing a topic or content type?

Those rows are skipped and left untouched. Only rows with the required inputs get sent to Claude, so nothing gets drafted from half-empty data.

Related templates

Call overdue Xero customers with an AI collections agent

Every weekday at 10am, an AI voice agent phones your most overdue Xero accounts, logs what each customer promised, and reports back to finance in Slack.