Daily AI blog post generator: Sheets idea queue to Ghost

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.

Additional information

What does this prompt do?

Picks the next unpublished idea from a Google Sheets editorial queue and drafts a 700 to 900 word article in your voice.
Finds a high-resolution landscape stock photo on Pexels that matches the headline and angle, with photographer credit added to the post footer.
Publishes or saves the post as a draft in Ghost with the photo set as the featured image.
Writes the Ghost post URL back to the row and flips its status to Published so the queue stays current.

What do I need to use this?

A Google Sheets idea queue with columns for Title, Angle, Target Keywords, and Status.
A Ghost site you can publish to, with a custom integration created under Settings.
A free Pexels account so the agent can pull stock photos and credit the photographer.

How can I customize it?

Move the run time or switch from weekdays only to seven days a week.
Change whether the post is saved as a draft for review or published immediately.
Tune the word count, tone, or section structure of the article in the agent's instructions.

Frequently asked questions

Will it publish posts automatically, or can I review them first?

Either. By default we recommend saving each post as a draft in Ghost so you can review and tweak before publishing. Flip a single setting in the prompt to publish immediately when you are ready to let it run on autopilot.

What happens if my Google Sheet runs out of ideas?

The agent looks for the next row whose Status is empty or set to Queued. If it finds none, it skips the run and does nothing. Just add new ideas to the sheet whenever you want it to pick up again.

Do I have to credit Pexels photographers?

Yes. Pexels requires attribution when you use their images, so the workflow always appends a small credit line at the bottom of each post linking back to the photographer and to Pexels.

Can I use it with a self-hosted Ghost site as well as Ghost(Pro)?

Yes. The setup asks for your site URL, so it works with any Ghost site you can reach on the public web, whether it is hosted on Ghost(Pro) or your own server.

What if Pexels does not have a great photo for my topic?

The agent derives a couple of image search terms from your title and angle. You can edit those instructions to try broader or more visual keywords, or fall back to a default featured image you keep in Ghost.