Weekly Kit newsletter draft from your content backlog

Every Thursday at 9am, draft next week's Kit (ConvertKit) newsletter for me from my content backlog so I only have to review and send. Trigger: cron, weekly on Thursday at 9am in my local timezone.

Step 1: read the backlog. Use Google Sheets Get Values against the Content backlog tab. The columns are title, link, notes, priority, and status. Parse the rows and pick the 3 to 5 highest-priority items whose status is still exactly 'ready'. If fewer than 3 ready items exist, use whatever is available and note the shortfall in the run summary. Preserve each picked row's index so we can update it later.

Step 2: study my on-brand voice. Use ConvertKit List Broadcasts to fetch my most recent broadcasts (roughly the last 5 to 10). Skim their subject lines, preview text, and opening lines so the new draft matches the pattern I already use, including sentence casing, emoji habits, punchiness, and any recurring sign-off.

Step 3: write the newsletter. Produce a short opener in my voice (2 to 4 sentences, warm but concise). Then for each picked backlog item write a compact blurb: a bolded title that links to the item's link, and one sentence that hooks the reader on why it is worth clicking. Close with a single clear call to action (reply to the email, share the newsletter, or check out the featured item, whichever fits best given the mix). Also propose a subject line that matches the recent style and a preview text of roughly 90 to 120 characters.

Step 4: save the draft in Kit. Call ConvertKit Create Broadcast with the composed subject line, preview text, and HTML body. Save it as a draft only, never scheduled and never sent. Include the broadcast id and the Kit URL in the run summary so I can click straight through to review.

Step 5: mark the sheet. Call Google Sheets Batch Update Values to flip the status column for every picked row from 'ready' to 'queued', in a single batched request. Do this only after Create Broadcast succeeds so the sheet never gets ahead of the draft.

Non-negotiables: draft-only, never auto-send. If Create Broadcast fails, do not touch the sheet. Never feature the same item twice, and never feature items whose status is not exactly 'ready'. Report back which items were picked, the proposed subject line, and the Kit draft link.