P
AI for Activities Director

Automation: Auto-Draft Your Monthly Newsletter with Zapier + ChatGPT

Tools:Zapier, ChatGPT API, Google Sheets
Time to build:2 hours
Difficulty:Intermediate-Advanced
Prerequisites:Comfortable using ChatGPT for writing (see Level 3 guide: "Build Your Activity Theme Library with ChatGPT")
Time to build: 2 hoursChatGPTZapier

What This Builds

Instead of spending 3-4 hours drafting your monthly newsletter from scratch, you'll build an automation that runs on the last business day of each month, pulls your activity highlights from a Google Sheet, sends them to ChatGPT, and emails you a complete newsletter draft, ready for your review and editing in under 30 minutes. You do the setup once. After that, the newsletter starts itself.

Prerequisites

  • Comfortable using ChatGPT for basic writing tasks (Level 3)
  • A free Zapier account at zapier.com (free tier covers this workflow)
  • A Google account (free) for Google Sheets
  • A ChatGPT Plus account (for ChatGPT API access, ~$20/month or pay-per-use)
  • An email account for the newsletter draft to be delivered to

The Concept

Think of Zapier as a very simple robot that watches for things to happen and does other things in response. You set up a "recipe": every month on a specific date, the robot reads your activity log spreadsheet, sends it to ChatGPT with instructions, and emails you the draft. You read it, make your personal edits (add specific resident stories, check facts, add your photo), and send. Instead of a 3-hour task, it becomes 30 minutes of review and personalization.

Build It Step by Step

Part 1: Set up your Activity Log spreadsheet

This spreadsheet becomes the source of truth that feeds the automation.

  1. Open Google Sheets and create a new spreadsheet

  2. Name it: "Monthly Activity Log - [Facility Name]"

  3. Create these columns:

    • Column A: Month (e.g., "March 2026")
    • Column B: Activity Highlights (paste 3-5 bullet points of what happened this month)
    • Column C: Upcoming Events (list 3-5 events for next month)
    • Column D: Resident Spotlight (one sentence about a resident moment, first name only)
    • Column E: Announcements (any facility news to share)
    • Column F: Status (leave blank; the automation will fill this)

  4. Fill in this month's row as a test. Example:

    • Month: March 2026
    • Highlights: St. Patrick's Day party drew 45 residents; visiting choir from St. Joseph's performed; new garden program launched with 12 participants
    • Upcoming: Spring celebration April 15, visiting musician April 3, Easter activities April 20
    • Spotlight: Dorothy, 88, taught the whole room a new card game she invented
    • Announcements: Activities schedule now posted on the family portal

What you should see: A spreadsheet with one complete test row.

Part 2: Get ChatGPT API access

  1. Go to platform.openai.com
  2. Create an account or log in
  3. Go to "API keys" and create a new key
  4. Copy the key. You'll need it in Zapier. Keep it private.

Cost: ChatGPT API charges per use. One newsletter generation costs approximately $0.02-0.05, essentially free. You'll need to add a small credit to your account (minimum $5).

What you should see: An API key that looks like "sk-proj-...". Copy and save it somewhere safe.

Part 3: Create the Zapier workflow

  1. Go to zapier.com and create a free account
  2. Click "Create Zap" (a Zap is Zapier's name for an automation workflow)

Step 3a: Set the Trigger (what starts the automation)

  • Trigger app: Schedule by Zapier
  • Trigger event: Every Month
  • Set day: Last business day of the month (or a specific date like the 28th)
  • Set time: Whatever time works for you (e.g., 8:00 AM)

This trigger will fire once per month on your chosen date.

Step 3b: Add an action to read your spreadsheet

  • App: Google Sheets
  • Action: Get Spreadsheet Row
  • Connect your Google account
  • Select your "Monthly Activity Log" spreadsheet
  • Configure it to get the row where Column F (Status) is blank. This finds your current month's unpublished row.

Step 3c: Add an action to call ChatGPT

  • App: OpenAI (ChatGPT)
  • Action: Send Prompt
  • Authentication: Paste your API key
  • Model: gpt-4o (or gpt-4o-mini for lower cost)
  • Prompt: Paste this template, with Zapier data fields inserted:
Copy and paste this
Write a warm, friendly monthly newsletter for families of residents at [Your Facility Name] for {{Month from spreadsheet}}.

Activity highlights from this month: {{Highlights from spreadsheet}}

Upcoming events: {{Upcoming events from spreadsheet}}

Resident spotlight: {{Spotlight from spreadsheet}}

Announcements: {{Announcements from spreadsheet}}

Tone: Warm and personal, as if written by a care professional who genuinely loves the residents. Use first names only. Not corporate. 2-3 paragraphs total plus a brief intro and closing. Sign off as "[Your Name], Activities Director."

In Zapier, you'll click "+" to insert the spreadsheet data fields into the prompt where the {{variables}} are shown.

Step 3d: Add an action to email you the draft

  • App: Gmail (or your email provider)
  • Action: Send Email
  • To: your email address
  • Subject: "Newsletter Draft Ready - {{Month}}"
  • Body: The ChatGPT output from Step 3c

Step 3e: Add an action to mark the row as processed

  • App: Google Sheets
  • Action: Update Spreadsheet Row
  • Update Column F (Status) to "Sent to review"

This prevents the automation from processing the same month twice.

Part 4: Test the workflow

  1. Click "Test" in Zapier to run the workflow manually
  2. Check your email. You should receive a newsletter draft.
  3. Review the output against your newsletter standards
  4. If adjustments are needed, edit the prompt in Step 3c and re-test

Real Example: March Newsletter

Setup: Google Sheet filled with March highlights. Zapier workflow runs on March 28th at 8 AM.

Input:

  • Highlights: "St. Patrick's Day party, 45 residents; visiting choir; garden program launched"
  • Spotlight: "Dorothy taught everyone her card game"

Output (emailed to you at 8 AM March 28): A complete 3-paragraph newsletter draft arrives in your inbox. You add a personal touch in paragraph 2, attach a photo from the St. Patrick's Day party, and send to families by 9 AM. Total time: 25 minutes instead of 3.5 hours.

Time saved: 3+ hours per month, permanently.

What to Do When It Breaks

  • Zap doesn't trigger → Check that the Schedule trigger is set to the correct timezone; verify the Zap is turned "On"
  • Wrong spreadsheet row is pulled → Adjust the filter in Step 3b; add a clear "Current Month" marker
  • Newsletter draft is too generic → Add more specific instructions to the ChatGPT prompt in Step 3c; include your facility's actual newsletter examples as reference
  • API key errors → Go to platform.openai.com, check that your account has credit and the key is active; regenerate if needed

Variations

  • Simpler version: Skip Zapier entirely. On the last day of each month, manually paste your spreadsheet highlights into ChatGPT and ask it to write the newsletter. Same quality; you just trigger it yourself.
  • Extended version: Add a step that posts the finished newsletter to your facility's family portal (if it has an API) or sends it directly to families after your approval step.

What to Do Next

  • This week: Set up the Google Sheet and do one manual test with ChatGPT to see what the draft looks like
  • This month: Build the full Zapier workflow; test before relying on it
  • Advanced: Connect to your family email list tool (MailChimp, Constant Contact) to fully automate distribution after your approval

Advanced guide for Activities Director professionals. Requires Zapier account (free tier sufficient) and ChatGPT API access (~$5 initial credit).