Automating Staff Rotations with Google Apps Script and AI: A Beginner-Friendly Guide

Katie Stokes
Aug 28
4 min read

If you’ve ever managed shift rotations or training schedules, you know the pain of endless spreadsheets, email reminders, and sticky notes. What if you could build a system that tracks your rotations, emails the right people at the right time, and grows with your needs, all without spending a dime?

That’s exactly what I did for a nurse manager, using Google Apps Script (GAS) and a little help from generative AI (like ChatGPT). Here’s how you can do it too, whether you’re managing nurses, volunteers, or project teams.

Why Google Apps Script Is Perfect for Beginners

Google Apps Script is the secret engine under the hood of Google Sheets, Docs, and Gmail. It’s free, cloud-based, and, for small organizations on Google Workspace, already part of your subscription.

Why I love it for small businesses:

No servers, no installs: Everything runs inside your Google account.
Beginner-friendly security: Scripts inherit your Workspace permissions, so you don’t have to manage complex API tokens or external servers.
Free (up to limits): Plenty of quota for most small teams, you’ll only hit caps if you’re emailing hundreds per day.
A nice starter limit of 50 projects to get your feet wet.
Integrates with what you already use: Gmail, Sheets, Calendar, and even Forms.

If you’ve ever tinkered with a spreadsheet formula, you can start learning Apps Script. And with today’s AI tools, the learning curve is smaller than ever.

Step 1: Define Your Goal Clearly

Before you open a script editor, get crystal clear on what you want.

Here’s what we needed:

Track nurses rotating through ER, Burns, Anesthesia, and Ortho.
Each rotation runs Tuesday to Friday for a set number of weeks.
When one rotation ends, the next should be scheduled automatically.
A manager gets a digest email with buttons to send assignment emails manually.
Keep it simple, secure, and free.

Generative AI thrives on specificity. I started by telling ChatGPT:

“Help me build a Google Apps Script that logs rotation schedules in a sheet, emails the manager a weekly digest, and lets the manager click a link to send assignment emails.”

Then I iterated, clarifying things like:

How many areas there are.
How long each rotation lasts.
Who should receive emails and when.

That back-and-forth is key - AI is a collaborator, not a mind reader.

Step 2: Use AI as Your Coding Buddy

If you’re new to coding, AI is like having a friendly senior developer by your side. Here’s how I worked with ChatGPT:

Start simple: Ask for the basic “skeleton” script.
Test in Sheets: Paste the code, see what breaks, and copy the exact error message back to the AI.
Iterate in small steps: Fix one issue at a time.
Ask for explanations: Have it explain what a function does so you can tweak it later.

This iterative loop keeps the project moving without feeling overwhelming.

Step 3: Build and Test in Google Sheets

Here’s what my system looks like in practice:

People Sheet: List of staff names and emails.
Tracks Sheet: ER, Burns, Anesthesia, Ortho, each with durations.
Log Sheet: Auto-populated records of who’s assigned where and when.
Daily Trigger: Runs every morning, advancing rotations and preparing links.
Manager Digest Email: Arrives Sunday and Monday, with “Send” buttons to trigger assignment emails.

And the best part? All of this is free inside Google Workspace.

Step 4: Understand the Limits

While Apps Script is fantastic, it’s not magic. Here are some real-world limits:

Email quotas: Free accounts get ~100 emails/day; Workspace accounts ~1,500/day.
Runtime limits: Long scripts may time out at 6 minutes.
Project limits: ~50 projects total
Security prompts: First-time authorizations look scary (“Read and delete your emails”), but the code only uses what you tell it to.
No built-in user interface: Custom dialogs are simple but not fancy.

For small businesses, these limits are rarely a problem. But if you need heavy-duty automation, you can always migrate to something more robust later.

Why This Approach Works

Cost-effective: Zero spend if you’re already in Google Workspace.
Customizable: You can adjust rotations, add roles, or tweak email templates anytime.
Empowers the team: The manager stays in control, and no IT ticket needed to change a schedule.
Scalable: Need to handle more areas or more people? Just add rows to your Sheets.

Beyond the Basics

Once you’re comfortable, here are ways to grow your system:

Add a Google Form for new staff enrollment.
Use conditional formatting in the Log sheet to flag upcoming rotations.
Create summary dashboards with charts.
Trigger calendar invites alongside assignment emails.
Integrate Slack or Teams notifications.

Generative AI makes these enhancements far easier to explore. Instead of hiring a developer for every tweak, you can experiment yourself.

Final Thoughts

Automation doesn’t have to be expensive or intimidating. With Google Apps Script and generative AI, you can create custom tools that save hours of work, improve accuracy, and fit the exact way your team operates.

This rotation scheduler is just a starting point. Whether you’re managing shifts, onboarding new hires, or tracking projects, the same principles apply:

Define your needs clearly.
Use AI to draft and refine your solution.
Start small, test often, and build from there.

Once you see how much time you save, you’ll start spotting opportunities to automate everywhere,

and that’s when things get really fun.