Malou Dongelmans

Episode 04

Service account + campaign planning

Fourth post in “Tools I build with.” Planning this series in a Google Sheet, then letting code fill it in. Full setup below: service account, sheet sharing, credentials safely stashed in a .env file, and the 60-line Python script that did the actual writing.

Before you start

You need a Google account (the same one you use for Gmail or Drive) and a web browser. Everything in this post uses Google’s free tier, no paid plan is required. If you don’t have a Google account yet, create one at accounts.google.com.

Why a Google Sheet

Content plans need to live somewhere every column is visible at a glance. Notion tables, markdown tables, a plan.yaml file, each eventually felt like fighting the medium. Spreadsheets already work. They’re also the cleanest surface to share with an AI: you see the plan, Claude edits row by row, and iterating becomes a two-way conversation.

Why let Claude write to it

Once the columns existed, populating the rows of hooks was the boring part. Letting Claude fill in the drafts, with space to edit them afterwards, is the natural next step. That requires Claude to write to the sheet, not just read it.

Two ways to authenticate a Python script that needs to write to Google:

  1. OAuth (user flow). The script borrows your sign-in through a browser prompt. Clunky when the script is meant to run on its own without you there, needs re-authentication, and leaks sign-in prompts into the terminal.
  2. Service account. A Google login that belongs to code rather than a person, with its own credential file. Give the credential to the script; it writes directly. No browser, no re-authentication.

Service accounts are the right tool for any Python script that needs sustained access to a Google resource. The same pattern works for Drive, Calendar, Gmail and more.

Creating the service account

Head to Google Cloud Console. About 90 seconds of clicking.

  1. Create a project. Any short name works (something like sheet-writer or a codename for your series). The name shows up in dashboards, so pick something recognisable later.
  2. IAM & Admin, Service Accounts, Create. Give it a short name too, for example sheet-writer. Skip the optional role assignment.
  3. Keys, Add Key, Create key, JSON. A file downloads. That file is the credential. Treat it like a password.

Your service account now has an email address, something like sheet-writer@your-project.iam.gserviceaccount.com. That’s the identity the code will authenticate as.

Share the sheet with the service account

Open the Google Sheet you want to write to. Click Share. Paste the service account’s email address. Give it Editor. Send.

Easy to forget. Even with a perfect JSON key, if the sheet isn’t shared with the service account, the API returns a 403.

Enable the Sheets API

One more click in Google Cloud Console: APIs & Services, Library, search “Google Sheets API”, Enable. Five seconds. Without it, the API returns a clear error pointing you to this exact page.

Keep the JSON key out of your code

The downloaded JSON file is a secret. It can’t live in a folder that gets shared (a GitHub repo, an email attachment), and it shouldn’t be written directly into your script. That’s what .env files are for.

.env (never committed to a shared folder):

GOOGLE_APPLICATION_CREDENTIALS=/path/to/your/service-account.json
SHEET_ID=1pX9_pQQpg5hc8puu2NYJiqjEYElgzhXktkuuRQxjpQg

.env.example (same keys, placeholder values, safe to share):

GOOGLE_APPLICATION_CREDENTIALS=/path/to/your/service-account.json
SHEET_ID=your-sheet-id-here

.gitignore:

.env
*service-account*.json

Three files, zero secrets in version control.

The script that writes the sheet

Python plus python-dotenv plus Google’s official client. The skeleton:

import os
from pathlib import Path
from dotenv import load_dotenv
from google.oauth2 import service_account
from googleapiclient.discovery import build

load_dotenv(Path(__file__).parent / ".env")

creds = service_account.Credentials.from_service_account_file(
    os.getenv("GOOGLE_APPLICATION_CREDENTIALS"),
    scopes=["https://www.googleapis.com/auth/spreadsheets"],
)
svc = build("sheets", "v4", credentials=creds, cache_discovery=False)

HEADERS = ["#", "Episode", "LinkedIn hook", "Instagram hook", "TikTok hook",
           "Receipt", "Platform order", "Status", "Recorded", "Published"]

EPISODES = [
    [1, "Claude Code + Cursor setup", "...", "...", "...",
     "the editor you're watching", "LI → IG → TikTok", "Planned", "", ""],
    # ...13 more rows
]

svc.spreadsheets().values().update(
    spreadsheetId=os.getenv("SHEET_ID"),
    range="Sheet1!A1",
    valueInputOption="RAW",
    body={"values": [HEADERS] + EPISODES},
).execute()

Run it. The sheet fills in front of your eyes.

One sheet, one starting point

Running this script was the trigger for everything else in this series. Once the sheet held every row populated by code, the question shifted from “what goes in the plan” to “how do I build each piece.” The posts from here on answer that second question, one piece at a time.

What’s next in the series

  • The Lowphi rules: golden ratio and a nod to the avant-garde. The rule book behind the first project in this series. The palette, the suprematist shapes, the print-on-demand plan.
  • …and more tools ahead, each tied to a real project as its receipt.

Subscribe below for the next one.