Automate Shift Scheduling based on Employee Availability

Hey team, Darian here. Let’s talk about a process that used to be the bane of my existence: weekly shift scheduling. I’d spend hours every Sunday evening staring at a spreadsheet, cross-referencing availability from a dozen different emails, and trying to piece together a schedule that didn’t burn someone out. It was a tedious, error-prone mess. I finally automated it, and what used to take two hours now takes zero. It just… happens. This guide will walk you through setting up a similar system. It’s a real game-changer, freeing you up for the engineering work that actually matters.

Prerequisites

Before we dive in, make sure you have the following ready to go:

• Python 3 installed on the machine where this script will run.
• A Google Account with access to Google Sheets and the ability to create API credentials.
• Google API Service Account Credentials. You’ll need to go to the Google Cloud Console, enable the Google Drive and Google Sheets APIs, and download the JSON credentials file for a new service account.
• Python Libraries. We’ll be using gspread and oauth2client. You’ll want to install these with your preferred package manager, for instance, by running a ‘pip install’ command for ‘gspread’ and ‘oauth2client’.

The Step-by-Step Guide

Alright, let’s get this thing built. I’ll skip the standard virtual environment setup since you likely have your own workflow for that. We’ll jump straight into the logic.

Step 1: Prepare Your Google Sheet

First, create a new Google Sheet. This will be our single source of truth. I structure mine with two tabs: “Availability” and “GeneratedSchedule”.

In the “Availability” tab, set it up like this:

• Column A: EmployeeName
• Column B: Monday_Available
• Column C: Tuesday_Available
• …and so on for all the shifts you need to cover (e.g., ‘Mon_AM’, ‘Mon_PM’).
• Employees will fill this sheet with a “Y” for shifts they are available to work.

Crucial step: You must share this Google Sheet with the client_email address found in your service account’s JSON file. Give it “Editor” permissions so our script can read and write data.

Step 2: The Python Script – Authentication

Create a Python script, let’s call it scheduler.py. The first thing we need to do is authenticate with the Google Sheets API. This code looks for your credentials file (I’ve named mine service_account.json here) and authorizes the connection.

import gspread
from oauth2client.service_account import ServiceAccountCredentials
import random

def authenticate_and_get_sheet():
    """Connects to Google Sheets API and returns the worksheet object."""
    scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
    creds = ServiceAccountCredentials.from_json_keyfile_name('service_account.json', scope)
    client = gspread.authorize(creds)
    
    # Open the spreadsheet by its title. Make sure this matches yours.
    sheet = client.open("Weekly Shift Schedule").worksheet("Availability")
    return sheet

Step 3: The Core Logic – Fetching Data and Assigning Shifts

Now for the fun part. We need to define the shifts we need to fill, fetch all the employee availabilities, and then loop through to assign people. The logic here is straightforward: for each required shift, find all employees who marked “Y”, and pick one at random.

Pro Tip: A simple `random.choice` works for small teams. In my production setups, I’ve built a more complex “fairness” algorithm. It keeps track of who worked the last weekend or holiday shift and prioritizes those who haven’t worked one recently. You can build this out by adding another column to your sheet to track shift counts.

def generate_schedule(sheet):
    """Fetches availability, defines shifts, and assigns employees."""
    # Get all data from the availability sheet
    records = sheet.get_all_records()
    
    # Define the shifts you need to fill for the week
    required_shifts = {
        "Monday_AM": 1, "Monday_PM": 1,
        "Tuesday_AM": 1, "Tuesday_PM": 1,
        "Wednesday_AM": 1, "Wednesday_PM": 1,
        "Thursday_AM": 1, "Thursday_PM": 1,
        "Friday_AM": 1, "Friday_PM": 1,
        "Saturday_OnCall": 1,
        "Sunday_OnCall": 1
    }
    
    schedule = {}
    assigned_today = set()

    for shift, num_needed in required_shifts.items():
        day = shift.split('_')[0]
        
        # Reset the "assigned today" set for each new day
        if not schedule or day not in list(schedule.keys())[-1]:
            assigned_today.clear()

        # Find all available employees for this specific shift
        available_employees = [
            record['EmployeeName'] for record in records
            if record.get(shift) == 'Y' and record['EmployeeName'] not in assigned_today
        ]
        
        if len(available_employees) < num_needed:
            schedule[shift] = ["UNFILLED"]
            print(f"Warning: Not enough available staff for {shift}")
            continue

        # Assign employees
        chosen_ones = random.sample(available_employees, num_needed)
        schedule[shift] = chosen_ones
        
        # Add assigned employees to a temporary set to avoid double-booking on the same day
        for person in chosen_ones:
            assigned_today.add(person)
            
    return schedule

Step 4: Writing the Schedule Back to the Sheet

Once the schedule is generated in memory, we need to write it back to our “GeneratedSchedule” tab for everyone to see.

def update_schedule_sheet(schedule):
    """Writes the generated schedule to the second tab in the Google Sheet."""
    scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
    creds = ServiceAccountCredentials.from_json_keyfile_name('service_account.json', scope)
    client = gspread.authorize(creds)
    
    # Get the target worksheet
    schedule_sheet = client.open("Weekly Shift Schedule").worksheet("GeneratedSchedule")
    
    # Clear the old schedule before writing the new one
    schedule_sheet.clear()
    
    # Write headers
    schedule_sheet.append_row(["Shift", "Assigned_Employee"])
    
    # Write the new schedule data
    for shift, employees in schedule.items():
        employee_str = ', '.join(employees)
        schedule_sheet.append_row([shift, employee_str])
    print("Schedule successfully written to Google Sheet.")

if __name__ == "__main__":
    availability_sheet = authenticate_and_get_sheet()
    if availability_sheet:
        new_schedule = generate_schedule(availability_sheet)
        update_schedule_sheet(new_schedule)

Step 5: Automating the Script

The final piece is to make this run automatically. I use a simple cron job on a utility server for this. You’ll want to schedule it to run at the beginning of the week, maybe early Monday morning.

A cron command for this would look something like this:

0 2 * * 1 python3 scheduler.py

This tells the system to run our Python script every Monday at 2:00 AM. No more Sunday night spreadsheet sessions.

Common Pitfalls (And How I’ve Learned to Avoid Them)

Here’s where I usually mess this up, so you don’t have to:

• API Permissions: The number one error is forgetting to share the Google Sheet with the service account’s email address. The script will fail with an authorization error. Always double-check this.
• Mismatched Names: Ensure your sheet titles, tab names, and column headers in the Python script perfectly match what’s in your Google Sheet. A typo will break the whole thing.
• Handling “Unfilled” Shifts: My first version would crash if no one was available. Now, I have it explicitly mark the shift as “UNFILLED” and print a warning. This is much more robust.
• Timezones: Remember that cron jobs run on the server’s timezone. If your server is in UTC and your team is in PST, schedule the job accordingly.

Conclusion

And that’s it. With one Google Sheet and a Python script, you’ve automated a time-consuming administrative task. This system is simple, but it’s a solid foundation. You can easily expand it to send email notifications, handle time-off requests, or implement more sophisticated assignment logic. The goal is to let the machines handle the rote work so we can focus on bigger challenges.

Happy automating!

– Darian

Darian Vance

Lead Cloud Architect & DevOps Strategist

With over 12 years in system architecture and automation, Darian specializes in simplifying complex cloud infrastructures. An advocate for open-source solutions, he founded TechResolve to provide engineers with actionable, battle-tested troubleshooting guides and robust software alternatives.

🤖 Frequently Asked Questions