Two custom Google Sheet menu functions built as a Google App Script.
I wrote this project in 2015 and back then, my build process was too complex and my deployment step was cut-and-paste. Since I recently discovered @google/clasp, I consolidated the original code, integrated webpack and employed clasp to deploy. So much better!
For nostalgia, I've included the original code in the
/legacyfolder.
The Art Enrichment(AE) program at a local private elementary school offers art courses for each grade (K-8) throughout the school year. Prior to the start of the school year, AE teachers submit their course schedule to the AE chairperson, who is tasked to identify and correct scheduling conflicts.
- Provides a consistent interface to control the quality of course schedule data submitted to the AE chairperson.
- Provide a repository to capture and store submitted schedules.
- Automate the process of identifying and reporting schedule conflicts.
A scheduled art course has three data points - the date scheduled, the time scheduled and the art equipment required.
Art equipment is shared among all staff.
Schedule conflicts exist when all the following criteria are met.
- 1+ courses occur on the same course date.
- 1+ courses are offered at overlapping times.
- 1+ courses have at least one tool in common.
To solve the first requirement, I created a Google Form. The AE chairperson sends the form to all AE teachers, who complete and submit.
Submitted forms are captured in a Google Sheet - thus, solving the second requirement.
To solve the third requirement, I wrote a Google App Script called 'Show Conflicts'. It is bound to the Google Sheet that stores all submitted forms. The script adds a custom menu option for the AE chairperson to easily run the conflict reporting program.
The Google Sheet that stores form responses, holds 3 worksheets - listed below by name.
- Data
- Tools
- Configuration
This is the default worksheet and must be named Data. It is the destination for all schedule form responses and has the following columns.
- Timestamp
- Course Date
- Start Time
- Duration
- Class
- Art Project
- End Time (computed)
- Tools (computed)
All fields are required.
Before the school year starts, the AE chairperson designs all art projects to be taught for the year. Each art project requires a lists of art supplies - or tools. This worksheet must be named Tools and has the following columns.
- Projects
- Tools
The Projects column is the master list of all art projects for the year. The projects in this column are programmatically added to the Art Project drop-down field on the schedule form.
The values in the Tools column are comma separated values that list the art equipment required for the cooresponding project.
The second menu function is Update Projects. It allows the AE coordinator to add and remove projects from the Projects column on the Tools worksheet and then easily sync the project list to the Art Project field choices on the schedule form.
The Update Projects program uses the Configuration worksheet to complete its work. The worksheet has the following columns.
- Spreadsheet ID
- Sheet
- Columns
- Form ID
- Field
The ID of the spreadsheet that holds the worksheet with the list of projects.
The name of the worksheet in the spreadsheet mentioned above.
The column letter of the column in the worksheet above that holds the list of projects.
The program assumes this worksheet has a header row, so the program will pull column values starting at row 2.
The ID of the target form in this column.
The field to update.
Google calls the field name, Question Title. Put the complete value of the Question Title, including spaces and punctuation, in this column.
From the menu, select Apps -> Show Conflicts.
Rows with the same highlight color are in conflict.
From the menu, select Apps -> Run Update.
