Nightly bulk content drafts from a Google Sheets backlog

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.