Daily AI blog posts from a Google Sheets idea queue

Build me a daily AI blogging agent that drains an idea queue in Google Sheets and ships a finished post to my Ghost site. Trigger: cron, every weekday (Mon to Fri) at 7am in my local timezone.

Sheet shape: my Google Sheet has columns Title, Angle, Target Keywords, Status, and Post URL. A row is ready to publish when Status is empty or set to Queued.

On each run, the agent should:

1. Call Google Sheets Get Values on the queue range and pick the first row whose Status is empty or Queued. If none exist, log that the queue is empty and exit cleanly.

2. Draft a 700 to 900 word article based on that row's Title, Angle, and Target Keywords. Write in a clear, helpful, lightly opinionated voice. Structure it with an intro, 3 to 5 H2 sections, and a short conclusion. Output clean HTML (no Markdown), with proper paragraph tags, headings, and lists.

3. Derive one or two short image search terms from the Title and Angle, then call Pexels Search Photos with orientation=landscape and size=large. Pick the first result that looks topical. Capture the photo URL (use the large or landscape size from the src object), the photographer's name, the photographer URL, and the photo's Pexels page URL.

4. Download the Pexels image, then call Ghost Upload Image (Admin API) to upload it. Keep the returned image URL to use as the feature image.

5. Append a small attribution block to the bottom of the article HTML: "Photo by <photographer name> on Pexels" with links to the photographer's Pexels profile and to pexels.com. This satisfies Pexels' attribution requirement.

6. Call Ghost Create Post (Admin API) with ?source=html. Set title to the row's Title, html to the full article, feature_image to the uploaded image URL, feature_image_caption to the photo credit, tags inferred from Target Keywords, and status to draft by default (make this a single toggle so I can switch it to published later). Capture the returned post URL.

7. Call Google Sheets Update Values to write the Ghost post URL into the row's Post URL cell and set Status to Published (or Drafted if status=draft).

Constraints and notes: only process one row per run. If Pexels returns zero matches, retry once with a broader search term derived from just the Title; if still nothing, fall back to leaving feature_image unset rather than failing the run. Stay well under the Pexels free tier limit of 200 requests per hour (we only need a handful of calls per run). If any step fails, do not flip the row's Status, so the same idea is picked up on the next run.

Inputs the agent should ask me for at setup time: the Google Sheet ID and tab name for the queue, my Ghost site URL, my preferred posting time, and whether new posts should land as draft or published.