How I Automated My Content Calendar with Claude in One Weekend

The honest version of how this started

I had a content calendar that was technically a Google Sheet but functionally a graveyard.

Colour-coded columns. A "status" dropdown nobody updated. A tab called "Ideas" with 31 rows of half-formed titles I'd never touched. Every Monday I'd open it, feel mildly guilty, close it, and write whatever came to mind instead.

The problem wasn't discipline. The problem was that maintaining a content calendar manually costs more mental energy than it saves — so I kept skipping it.

What I actually needed was a system that did the thinking for me: suggest what to write next, generate the brief, slot it into a schedule, and tell me what to do on any given day. Not a fancier spreadsheet. A system with a brain.

So I spent a weekend building one with Claude. Here's exactly what I built, what it cost, and what I'd recommend you do differently from the start.

What the system does

Before I explain the how, here's what the finished system produces:

Every Monday morning, I open a Google Sheet and find 2 post briefs waiting — each with a working title, a target keyword, a 3-sentence content angle, and 5 FAQ questions. I approve, edit, or swap them out, then mark them "approved". The calendar tracks status: suggested → approved → drafted → published.

The whole thing runs on Claude's API. Total monthly cost to generate 8 post briefs per month: under €0.02 — less than a cent. That is the actual number.

What I built it with

Claude API (claude-haiku-4-5 model — fast and cheap for structured tasks like this), Google Sheets as the calendar interface, and Google Apps Script — a free tool built into Google Sheets that lets you run code on a schedule without any server or hosting. No n8n, no Zapier, no subscriptions.

Step 1: Set up your Google Sheet

Create a new Google Sheet with these columns: Date Added, Publish Date, Title, Target Keyword, Content Angle, FAQ 1–5, Content Type, Difficulty, and Status. Name the tab "Content Calendar". This is the tab the script will write to.

Step 2: Get your Claude API key

Go to console.anthropic.com, create an account, and generate an API key. Anthropic gives you $5 in free credit when you sign up (~€4.60). At Haiku pricing, generating 8 post briefs costs roughly $0.01 per month. That $5 starter credit will last you approximately four years of running this script before you need to add more.

Step 3: Write the Apps Script

In your Google Sheet, go to Extensions → Apps Script. Delete any existing code and paste the script below. Replace YOUR_API_KEY_HERE with your actual Claude API key.

const CLAUDE_API_KEY = 'YOUR_API_KEY_HERE';
const SHEET_NAME = 'Content Calendar';
const BLOG_TOPIC = 'AI automation and AI marketing for solo marketers and consultants';

function generateWeeklyBriefs() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
const today = new Date();

const prompt = `You are a content strategist for a personal brand blog about ${BLOG_TOPIC}.

Generate exactly 2 blog post briefs. Each brief must target a longtail keyword (4–7 words) that a new domain can realistically rank for.

For each brief, output a JSON object with these exact fields:
- title: direct-answer format, max 60 characters
- targetKeyword: longtail, 4–7 words
- contentAngle: 2–3 sentences describing the unique angle
- faq1 through faq5: five questions a reader would ask
- contentType: one of "explainer", "tutorial", "comparison", "guide", "case-study"
- difficulty: one of "beginner", "intermediate", "advanced"

Return only a valid JSON array with 2 objects. No explanation, no markdown, just the JSON.`;

const response = UrlFetchApp.fetch('https://api.anthropic.com/v1/messages', {
method: 'post',
headers: {
'x-api-key': CLAUDE_API_KEY,
'anthropic-version': '2023-06-01',
'content-type': 'application/json',
},
payload: JSON.stringify({
model: 'claude-haiku-4-5-20251001',
max_tokens: 1500,
messages: [{ role: 'user', content: prompt }],
}),
});

const result = JSON.parse(response.getContentText());
const briefs = JSON.parse(result.content[0].text);

briefs.forEach((brief, i) => {
sheet.appendRow([
today.toISOString().split('T')[0],
'',
brief.title,
brief.targetKeyword,
brief.contentAngle,
brief.faq1, brief.faq2, brief.faq3, brief.faq4, brief.faq5,
brief.contentType,
brief.difficulty,
'suggested',
]);
});
}

Step 4: Tune the prompt for your niche

The script uses a generic BLOG_TOPIC variable. Swap it for something specific to your audience. The more specific you are, the more useful the briefs. Spend 30 minutes iterating on this before you run it for the first time.

What it actually cost me

Claude API (Haiku, 8 briefs/month): ~$0.01/month. Google Sheets and Apps Script: free. Anthropic signup credit: $5 free — lasts ~4 years at this usage. Time to build: ~3 hours. Time to tune the prompt: ~1 hour. Total ongoing cost: effectively €0.

What I'd recommend you do from the start

Add a topic constraint before your first run. The version shown above will occasionally suggest topics you've already covered — especially once you're a few months in. Before you run it for the first time, add a short list of topics or titles you've already written about to the prompt, under a line that says "Do not suggest topics similar to these."

Leave the publish date blank. In practice, most people move dates around manually anyway — client work comes up, a trending topic jumps the queue, life happens. The script above already does this (it appends an empty string for the date column), so you're set.

The part people always ask about

Doesn't this make your content feel generic? The briefs are a starting point, not the finished post. Claude tells me the title, the keyword, and the angle. I still write the post — in my own experience, with my own opinions, with specific examples from my own work. The system removes the blank-page problem and the scheduling overhead. The writing is still mine.

If you use a brief and publish it without adding your own perspective, it will read like every other AI blog post on the internet. That is not the system's fault.

What's next

This setup handles brief generation. The next step is connecting it to a full post drafting workflow — where an approved brief becomes a full draft via a more detailed Claude prompt, reviewed by me, then pushed into the CMS. I'll cover that in a separate post.

If you want to start smaller, just run the script once manually (click Run in Apps Script) and see what briefs it generates for your niche. You don't need the weekly trigger to get value from the first run.