An interview scheduler using constraint satisfaction and Google Sheets.

Primarily a command-line tool for specifying people, their availabilities, and desired meetings, finding the best solution to this scheduling task, and a few other niceties, all directly in a Google Sheets spreadsheet so it's easy to edit collaboratively and view the results.

scheduling - interview management utility for CLSP recruitment weekend

This code base produces a command-line utility for managing the interview schedules of the CLSP recruitment weekend. This involves manipulating Google spreadsheets, and solving constraint-satisfaction problems based on faculty preferences, availability, and other considerations. Under the hood it employs the Microsoft Z3 theorem prover to find optimal solutions to problems formulated via the SBV library. The interactions with Google APIs are accomplished with the gogol-* libraries and heavy use of lenses.

Building and running the repository

After using ghcup to install and set a recent version (e.g. 9.X) of GHC and Cabal, the code can be built with:

$ cabal build

and the tool invoked with:

$ cabal exec -- scheduler OPTIONS

Preparing a spreadsheet and using the scheduler

The spreadsheet should allow "anyone with the link" to edit, and the key (the long random path component in the URL) gets passed to the scheduler tool with the --access switch. The tool has two primary modes, init and solve, that each rely on information being in particular tabs, and create (or overwrite) a handful of others: outside of these, it should not modify anything (though bugs are possible, and interactions with e.g. formulae have not been considered).

The basic workflow is to manually create a "Prospects" and "Interviewers" tab, each with "First", "Last", and "Email" columns, and interviewers with a "Zoom" column (in any order, and additional columns will be ignored), and invoke the tool with:

cabal exec -- scheduler init --access KEY

This generates the "Prospect Availability", "Interviewer Availability", and "Interviewer Preferences" tabs, which can then be filled in by the relevant parties. Note that running the command with the --force switch will overwrite the tabs, so it may be wise to edit-protect them once information has been entered. Then, invoke the tool with:

cabal exec -- scheduler solve --access KEY

The tool will attempt to satisfy the interviewer preferences using the availability of both groups, and write it's best solution to the tabs "Interviewer Schedule" and "Prospect Schedule".