Saturday Schedule
- Purpose
At my most recent library job, all staff members were required to work 4-5 Saturdays a semester. My manager sent Saturday schedule availability surveys through email every semester.
Staff would type the days they wanted to work and the days they weren’t available inside of a table built inside Outlook. The manager would then manually extract the information from more than 12 individual email responses and manually configure a schedule.
I enjoy creative problem solving, so in my spare time I developed a tool to reduce the complexity of this process and improve the efficiency.
I created a Google Form that would allow a more user-friendly interface and automatically populate answers into Google Sheets. With the Google Sheet, I was able to structure the data using advanced formulas to create an editable Saturday schedule tool.
Original Saturday Schedule Survey - Google Form
Calendar for staff to reference when selecting the Saturdays they are available
Data validation limits columns to a single response to prevent redundant entires
Staff members are sent a Google Form that requires them to sign in with their staff email. This form will only allow one response to protect the data integrity of the spreadsheet.
- Sheets Autofill
Email and Name scrubbed for privacy
This table is hard to read. The data can be filtered into a more graphical view to improve access.
- Schedule Widget
Sections Part 1: Staff names automatically populate into one of three sections (Not Available, Preferred, and Available) based on responses to the Google Form.
Sections Part 2: NOT AVAILABLE is the first section that appears. This allows for one to see a list of names of people that are not available for the day that corresponds to the column. By default, the selector cells validate against this table, but this presentation is there as a failsafe to ensure that staff aren’t made to work on Saturdays that they aren’t available.
Dropdowns Part 1: The dropdown menus are used to select the staff members for each desk/role that must be scheduled.
The roles for the staff member are defined in a separate (not pictured) table that lists the staff members under each role (Librarian, Circ, Ref)
Dropdowns Part 2: After a staff member is selected, a color is assigned to the staff member. Each staff member has their own color for easy visual tracking of the number of Saturday shifts per person for the semester.
At my most recent library job, all staff members were required to work 4-5 Saturdays a semester. My manager sent Saturday schedule availability surveys through email every semester.
Staff would type the days they wanted to work and the days they weren’t available inside of a table built inside Outlook. The manager would then manually extract the information from more than 12 individual email responses and manually configure a schedule.
I enjoy creative problem solving, so in my spare time I developed a tool to reduce the complexity of this process and improve the efficiency.
I created a Google Form that would allow a more user-friendly interface and automatically populate answers into Google Sheets. With the Google Sheet, I was able to structure the data using advanced formulas to create an editable Saturday schedule tool.
Calendar for staff to reference when selecting the Saturdays they are available
Data validation limits columns to a single response to prevent redundant entires
Staff members are sent a Google Form that requires them to sign in with their staff email. This form will only allow one response to protect the data integrity of the spreadsheet.
Email and Name scrubbed for privacy
This table is hard to read. The data can be filtered into a more graphical view to improve access.
Sections Part 1: Staff names automatically populate into one of three sections (Not Available, Preferred, and Available) based on responses to the Google Form.
Sections Part 2: NOT AVAILABLE is the first section that appears. This allows for one to see a list of names of people that are not available for the day that corresponds to the column. By default, the selector cells validate against this table, but this presentation is there as a failsafe to ensure that staff aren’t made to work on Saturdays that they aren’t available.
Dropdowns Part 1: The dropdown menus are used to select the staff members for each desk/role that must be scheduled.
The roles for the staff member are defined in a separate (not pictured) table that lists the staff members under each role (Librarian, Circ, Ref)
Dropdowns Part 2: After a staff member is selected, a color is assigned to the staff member. Each staff member has their own color for easy visual tracking of the number of Saturday shifts per person for the semester.
