"Elternsprechtag" [ˈɛltɐnˌʃpʁɛçtaːk], German noun: parents' day, parent–teacher conference, parent–teacher interview, parent–teacher night, or parent teacher meeting.
This script helps schedule appointments based on peoples' availabilities.
It maximizes the amount of appointments using the Hopcroft–Karp algorithm, based on this implementation.
It can for example be used for a parent-teacher conference, to schedule meetings with as many parents as possible.
The code is intended to run on Apps Script within GoogleSheets, with Google Forms used for data collection.
The use of Google Forms is optional, the script also works with manual data entry. If you want to skip Google Forms, you can find instructions below on how to set up your Google Sheet.
If you choose to use a Google Form, set one up with only two questions: One for identification and one with a list of checkboxes for all available times.
(Note: For data protection reasons, I recommend using random pseudonymized codes instead of real names for identification.)
Then head to the "Responses" Tab and link it to Google Sheets. It will create a GoogleSheet with a "Form responses 1" tab for you, so you will only need to create the "Schedule" tab (see below).
You need a Spreadsheet with two sheets: One as the data source and one to display the results.
For best compatibility with GoogleForms as a data source, I chose "Form responses 1" as the default name for the source tab. The default name for the results tab is "Schedule".
You can of course choose different names for your sheets, just remember to also change the names in the script accordingly!
This sheet will be used as the data source. It has three columns: timestamp, name, and a comma-separated list of available times (with or without spaces in between)
This will be created automatically if you choose to use Google Forms for your data collection. If you don't want to use Google Forms, just create a sheet that looks like this and then enter your data manually.
The timestamp column will be ignored, it is only there for compatibility with Google Forms. Similarly, the text in the header row does not matter. Just make sure that you have a header row, because the first row will be ignored by the code.
This is where the result will be printed. The code will again ignore the first row, so feel free to use whatever header you want.
Once you have set up both sheets, you can connect to Apps Script. Do this by choosing "Apps Scripts" from the "Extensions" menu:
You will see something like this:
Replace all code with this script.
If you now enter data into "Form Responses 1" and then run the index function, you should see a result in your "Schedule" sheet.
