If you search how to make an employee schedule in Google Sheets, you’ll find a hundred templates: names down the side, dates across the top, colored cells. That works fine for a small team on weekdays. It falls apart the moment you run 24/7, with nights, rest rules and people who can’t work certain days. Now you’re counting cells by hand at 11 PM. So I built a free Google Sheets add-on, Shift Optimizer, that fills the schedule for you with a real optimization solver, right inside your sheet.
Below: the manual way first (it’s worth knowing, and for some teams it’s all you need), then the add-on, how I built it, how it’s going so far, and what’s next.
How to make an employee schedule in Google Sheets by hand
For a team of five to ten people on a simple week, this is all you need:
- Put your people’s names down column A, one per row, and the dates across row 1.
- Select the grid and add a dropdown (Insert, then Dropdown) with your shift codes, for example D, N and OFF. Dropdowns stop typos like “n ” that break your counts later.
- Add conditional formatting so each code gets its own color. You’ll spot a run of nights from across the room.
- Under each date, add a count of who’s on each shift, like
=COUNTIF(B2:B11,"N"). Compare it to how many you need. - At the end of each row, count each person’s shifts and nights for the period, so you can see who’s getting more than their share.
- Freeze the first row and column (View, then Freeze) so the names and dates stay put when you scroll.
That’s a good schedule sheet. Notice what it does, though: it shows you the problems. It doesn’t fix them. You still have to move people around until every count matches, nobody works a day shift right after a night, and the nights are spread fairly. With 12 people over four weeks there are more ways to do that than you could try in a lifetime, and every fix you make tends to break something two columns over (whack-a-mole, just with nurses).
Letting a solver fill it in: the Shift Optimizer add-on
The add-on takes your people, your shifts and how many you need, and searches for the best schedule that breaks none of your rules: minimum rest between shifts, max days and nights in a row, no day shift after a night, skills, days off people asked for, and fixed shifts you already promised someone. When there just aren’t enough people, it still builds the schedule and lists exactly which posts are empty, so you know who to call.
How to use it:
Install it from the Google Workspace Marketplace (free). Open a new Google Sheet, go to Extensions, then Shift Optimizer, then “Optimize shifts / Optimizar turnos”. A sidebar opens. Pick an example close to your situation (hospital ward, 3-shift plant, security post, fire station or service desk) and press “Insert example tabs”. Change the example’s tabs to your own people, shifts and needs, then press “Optimize roster”. A minute or less later you get a new “Roster” tab with everyone’s shifts, colored.
It works in English and Spanish, it only touches the sheet you open it in, and your data is used to build the schedule and then thrown away. Limits per run: 60 people, 42 days, 12 shift types.
How I built it
The solver already existed; it runs my 24/7 shift schedule maker and a Claude connector. The add-on is a thin layer on top: a small Apps Script sidebar that reads your tabs, sends them to my server, and writes the result back as a new tab. I built it with Claude, like the rest of this year’s tools. I decided, Claude wrote most of the code, I tested it.
Why Google Sheets and not Excel? I wanted Excel first, honestly. My models have lived in Excel for twenty years. But publishing an Excel add-in needs a work or school Microsoft account and a business sign-up, even for a free one, and I’m one guy with a blog. Google charges nothing to list. I also looked at what was already there: the most popular scheduling add-on for Sheets has over 100,000 installs, but you still fill the grid by hand and it syncs to Calendar. Another one uses AI text to write the schedule, with no guarantee the rules hold. Nobody was actually solving the schedule. That was the gap.
One technical thing that shaped the design: Google stops an add-on call after 30 seconds, and a four-week roster can take longer than that. So the add-on starts the solve as a job on my server and checks back every few seconds until it’s done.
Most of the time didn’t go to code. It went to Google’s review, in two steps. First, because the add-on talks to an outside server, Google treats that as a sensitive permission: I needed a privacy policy on my domain, a written reason for each permission, and a demo video of the sign-in screen. Then the Marketplace review itself, where they asked for another video showing the whole thing working end to end. I sent my no-audio YouTube walkthrough (I don’t do voice). Google’s first review came back asking for a clearer data-protection section in my privacy policy, so I fixed that and waited again. All in, about a week of back and forth for about a day of building.
How it’s going
Short answer: quiet. Every solve the add-on runs writes one row to my usage log, and since it went live the log has exactly two add-on rows. One is a full hospital ward example (16 people, 216 shifts, optimal) on October 2, about half an hour after Google’s review email arrived and half an hour before the approval, so that was Google’s reviewer pressing the button. Google approved it that same afternoon. The other is a Spanish run that failed in one second without building anything, and whoever it was never tried again. My log only says “error”, not why, which is exactly the kind of blind spot I need to fix.
The log only sees solves. It can’t see installs, and Google doesn’t show install counts on the public listing, so I don’t know how many people installed it and never pressed the button. The Marketplace has two 5-star ratings, and one of them is mine (in my defense, I do love it).
A new add-on in the Marketplace starts with zero reviews and zero installs, and the store sorts by both. So this post is partly the plan: the people who search for how to build a schedule in Sheets are exactly the people who’d want it.
What’s next
First, the help page for the add-on was still saying “coming soon” after Google approved it. That’s fixed. Second, a failed run should log the reason (a missing tab, a time it couldn’t read) so I can turn the common ones into a clear message in the sidebar. Third, if the add-on gets real use, the same pattern works for routing: a delivery route planner inside Sheets, using the same engine as my route optimizer. That one has a lot more competition in the Marketplace, so it waits until this one earns its keep.
If you build schedules in Google Sheets, give it a try on a copy of your real schedule and tell me what it got wrong. If it saved you an evening, a review in the Marketplace helps more than you’d think.
Things that I use, like, and am affiliated with:
Mint Mobile offers great cell phone service for $15 flat, get $15 off using the link. Get discounted phones with service activation and no contract.
I never spend money before I check Mr Rebates or Rakuten to get cashbacks, rebates, discounts, coupons or cheaper gift cards.