2021-12-31
About two years ago, I noticed my German teacher expressing frustration while entering data into an Excel spreadsheet. He was managing the registrations for a local German contest, Houstonfest. As the director of the competition, he had devised a system for teachers to register their students by filling out a template spreadsheet and emailing him the result. I asked him about the process and noticed that a lot of his work involved copying data from the teacher-submitted spreadsheets into a single master spreadsheet. After that, he would prepare documents formatted and organized in various ways, such as a list of participants competing in each event to distribute to judges. While executing the process didn't take extraordinarily long, making modifications to the data later on would require that almost the whole process be repeated. Given the standardization and rigid format the system already followed, this seemed like a great candidate for automation.
My original idea was to completely revamp the registration system. I planned to create a web app where teachers would be able to sign in and enter their student data. I made some progress towards building the site, but eventually realized that for this kind of mass data entry, the spreadsheet-based system was more suitable. Being able to quickly type names into row after row with fewer mouse clicks was quicker, and the teachers were already familiar with the system. Even if I were able to make a well-designed website with a clean interface, the time, effort, and risk of teachers having trouble using the system wasn't worth it. I decided to stick with the existing system, but wrote a Python script to automate the processing of the spreadsheets. The script would read through all the teacher spreadsheets and feed the data into an in-memory SQLite database. Then, it would output the data as appropriate into the master spreadsheet and judge sheets. The script would take a few seconds to execute and made updating registration data a simple matter of running the script again. This system worked well for the 2020 contest, which also happened to be one of the last major in-person events I was able to attend.
A year passed, and contest season was approaching once again. The decision was made to hold the 2021 contest virtually, which forced major changes to how events would be judged. Synchronous events would take place through Zoom meetings, and the distanced format made it so that the traditional paper scorecard system wouldn't work. I adapted the script to output spreadsheet scorecards for each event. These scorecards would be shared with judges via Google Sheets. By the end of the contest, all of the score data would already be digitized and accessible in one place. There were a few end-of-contest processes I did not automate because they would only need to be done once. Additionally, tiny mistakes in scoring might be less noticeable than a mistake in registration, such as someone's entry in an event not appearing.
[TODO: Insert image of Google sheet]
One of the unexpected challenges I ran into was actually getting the program in a usable format for my teacher. Having him write down a few magic commands to execute a Python script directly from the terminal didn't seem like the best setup. After trying out a few different options, I packaged the script using PyInstaller into a Windows binary that he could easily download and install. I primarily used a Linux machine for development, so I had to switch over to my Windows computer for this step. There were many issues with the build process, mostly involving installing development libraries needed for certain dependencies to function. The most difficult dependency to get working was the one I used to interact with Word documents. On Windows, it would try to use a low-level XML parsing library that seemed to spit out a different error message every time I tried something new. Eventually, I managed to get everything sorted out and successfully built an executable. I have almost no memory of how I did it and fear having to go through the process again.
