Monthly Volunteer Schedule Automation with Shift Matching

## Problem Statement

A nonprofit staff member manually coordinates a monthly volunteer schedule for approximately 20 volunteers. Each month, the staff member must poll volunteers for their availability, manually match responses to available shift slots, resolve conflicts, and communicate the final schedule. This process is time-consuming and error-prone. The goal is to automate availability collection, shift matching, conflict detection, and schedule distribution so the staff member's time investment is minimized.

---

## Current Process Steps

1. **Determine shift dates**: The staff member identifies all Saturdays and Sundays in the upcoming month and defines two shifts per day — morning and afternoon — resulting in approximately 8–10 shifts per month (varies by number of weekends).
2. **Send availability poll**: A group email is sent to all ~20 volunteers containing a link to a Google Form asking for their availability across those specific dates and times.
3. **Collect responses**: Volunteers submit their availability through Google Forms. The staff member waits for responses.
4. **Review responses**: The staff member manually reviews Google Forms responses and cross-references them with the existing Excel spreadsheet tracking volunteers and shift slots.
5. **Manual matching**: The staff member attempts to assign each volunteer to shifts based on their stated availability, trying to fill all slots and distribute shifts fairly.
6. **Conflict resolution**: Any shifts that cannot be filled, or volunteers who are over-assigned, are manually resolved.
7. **Record schedule**: The finalized schedule is entered into the Excel spreadsheet.
8. **Distribute schedule**: The finalized schedule is communicated to volunteers, presumably via email.

---

## Integrations & Tools

| Tool | Role |
|---|---|
| **Google Forms** | Existing tool used to collect volunteer availability |
| **Google Sheets** | Backend for Google Forms responses; can replace Excel as schedule store |
| **Gmail API or SendGrid** | Send availability poll emails and distribute final schedule |
| **Python or Node.js** | Core scheduling/matching logic |
| **Make (Integromat) or Zapier** | Optional: orchestrate triggers between Google Sheets and email |
| **Excel (current)** | May be migrated to Google Sheets or kept as export format |

---

## Data Sources

- **Volunteer roster**: A list of ~20 volunteers including names and email addresses. Source: existing Excel spreadsheet or to be migrated to Google Sheets.
- **Google Forms responses**: Volunteer availability submissions, linked to a Google Sheet via native Forms integration.
- **Shift schedule template**: Definition of which Saturdays and Sundays exist in a given month and their two shift periods (morning, afternoon). This may be auto-generated from a calendar or defined by the staff member at the start of each month.
- **Minimum staffing requirements per shift**: Unknown from transcript — needs clarification. Assumed to be at least 1 volunteer per shift; actual number must be confirmed with the staff member.

---

## Acceptance Criteria

1. **Automated poll generation**: At the start of each month (or on a staff-triggered action), the system automatically identifies all Saturday and Sunday dates in the upcoming month and generates a Google Form (or pre-populates an existing form template) with morning/afternoon checkboxes for each date.
2. **Automated poll distribution**: The system sends a group email to all volunteers on the roster with the availability form link, without manual composition by the staff member.
3. **Response ingestion**: Volunteer responses submitted via Google Forms are automatically read into the system (via Google Sheets sync or Forms API).
4. **Automated shift matching**: The system runs a matching algorithm that assigns volunteers to shifts based on their stated availability. The algorithm should:
   - Fill all defined shifts (or flag unfillable ones)
   - Avoid double-booking a volunteer in two shifts on the same day (unless explicitly allowed)
   - Distribute shifts as fairly as possible across volunteers
5. **Conflict and gap reporting**: If any shift cannot be filled or a constraint cannot be satisfied, the system alerts the staff member with a clear summary so they can intervene manually.
6. **Schedule output**: The finalized schedule is written to a Google Sheet (or exported to Excel) in a human-readable format organized by date and shift.
7. **Schedule distribution**: The system sends each volunteer a personalized email listing their assigned shifts for the month.
8. **Staff review step (optional)**: The staff member should have the ability to review and approve the auto-generated schedule before it is distributed to volunteers.
9. **Monthly trigger**: The full process runs reliably once per month, either on a fixed calendar date or initiated by the staff member with a single action.

---

## Out of Scope

- **Volunteer recruitment or onboarding**: The system assumes a static roster of ~20 volunteers is already maintained.
- **Non-weekend shifts**: Only Saturday and Sunday shifts (morning and afternoon) are in scope. Weekday shifts, if any exist, are not addressed.
- **Volunteer preference ranking or priority logic**: Unless specified, the matching algorithm will treat all available volunteers as equally eligible. Complex priority rules (e.g., seniority, role type) are out of scope unless confirmed as a requirement.
- **Real-time schedule changes or cancellations**: Mid-month shift swaps, cancellations, or volunteer drop-outs are not handled by this automation.
- **Mobile app or volunteer self-service portal**: No new interface beyond email and Google Forms is being built.
- **Payroll or compensation tracking**: This is a volunteer scheduling tool only; no financial data is involved.
- **Integration with external calendar systems** (e.g., Google Calendar invites): Not in scope unless requested.