Project Update
April 10, 2023
Airtable for EC50
Building a custom course management system to support a 520-student course in 72 hours.
Project Dates: January 2023 - May 2023
Built with: Airtable, CanvasAPI, Python, Selenium, JS
The class management system includes an attendance excusal and extension request form which sped up the processing time of student requests.
As a Teaching Assistant for Harvard's Economics 50 course, I implemented a completely custom-made course management system with enrollment and attendance tracking, sectioning, and student requests management on Airtable. The class has an enrollment of 520 students, and the system supports automated email notifications and integrates with the LMS CanvasAPI to push attendance and assignment grades.
The class management challenge in EC50
The main components of EC50 are lectures and labs, which are small group sessions to practice data science skills in the context of economics problems. As such, labs have an average size of 15-20 students, and based on usual enrollment, the class offers over 30 labs at different times throughout the week in two languages: R and Stata.
Up until this year, the course staff handled enrollment and lab assignments in a semi-manual way. First the students filled out a lab time preferences form, and staff used submissions to make initial lab assignments. Throughout the semester, especially in the first 6 weeks, as students enrolled in the class or had shifting schedules, staff would have to manually process lab time change requests. In total, around 400 requests are processed each year during this period.
I was onboarded on the EC50 team in mid January, and classes were set to begin a week later. Besides leading a section and grading, the instructor of the class asked if I had any ideas to handle the issue of enrollment and lab changes. The system needed to be up and running in a few days.
Harvard uses Canvas as their Learning Management System, and there is no easy way to handle lab assignments, so everything had to be custom-built. Most of the staff was familiar with Excel and Google Sheets, so I knew we had to create an interface with similar characteristics. We also needed the new system to be flexible enough to make API calls to Canvas and Gradescope, our assignment grading platform.
Building fast
While working on a previous project, I had was exposed to Airtable and briefly used it, but never. I knew it had a spreadsheet-like interface, so I signed up for an educational team account and started building. In 24 hours, I built an Airtable base (think spreadsheet) that supported storage of student, lab section and teaching staff data, along with the ability to automatically process lab change requests with automated JavaScript scripts and email notifications to students. You can read a detailed rundown of the original prototype here.
The lab assignments were integrated into the Canvas LMS course site for easier access by students.
Iterating and reimplementing
As we were uploading student data and assigning them to lab times and TFs, we thought of informing all students of their assignments, something that was previously impossible given the semi-manual way of handling lab assignments. Through an Airtable automation, custom JavaScript code and a custom-built Airtable interface for monitoring, I sent out individualized emails to every student with lab times, TF contact information and resources to switch labs.
Students received a personalized email detailing their lab and TF assignments.
After the initial registration week, where we handled over 600 lab and TF assignments and lab time switches, we turned our attention to late enrollments, permanent and one-time lab change requests. In previous iterations of the course, students had to be manually added to the internal course lists by comparing snapshots of the course roster provided by the Registrar. Moreover, there are always some students have had issues enrolling in the class, but want to participate in course activities while their enrollment is finalized.
I wrote Python scripts to automate the data import process. I used the CanvasAPI to pull the Registrar's course roster throughout the day, compare it to our internal rosters, and add any new students to our Airtable base. Students received an automated email asking them to register for a lab section, so that we didn't have to manually reach out to new enrollees. Later on, we also wanted to handle students dropping the class, so I wrote another Python script to automate the process. I set up Deepnote Python notebooks to schedule the script running every hour.
Some students noted that they would like to have an option to be placed on a waitlist for full lab sections, so I created an Airtable form that allows waitlist registration based on lab section enrollment numbers that are updated in real-time. Airtable automation scripts check for any pending waitlist requests when lab seats become available, and students are added off the waitlist. Airtable automation scripts process permanent lab change requests to sections with open seats automatically. Students receive automated emails once their permanent lab change request is processed.
Students are able to enter a waitlist for a permanent lab change to a lab section that is currently full. When other students drop the class or switch to another lab section, students are automatically added from the waitlist.
I also created a one-time lab change request that allows students to request a lab change for a particular week. I created a calendar view for students to browse lab sections with open seats for a particular week, which is updated in real time. Requests are handled automatically and students receive confirmation emails, while TFs of the original and one-time change lab sections receive notification emails informing them of the change.
All in all, we went through five iterative cycles of prototyping-testing-feedback and redesign, until we reached a fully-fledged product in just under a week.
Left: students can request a one-time lab change to another section. Right: a calendar view allows students to browse sections with open seats that they can switch to for a particular week.
Expanding functionality
Once the main functions of the course management system, I added additional functionality to automate and speed up administrative tasks in the class:
Lab attendance tracking: I created individual pages (views) on Airtable for each lab section, where lab leaders enter attendance each week. They included students who requested a one-time lab change for that week. Attendance is then pushed to Canvas through the Canvas and Airtable API at the end of the week in a matter of minutes.
Attendance excusal and extensions: Throughout the semester, many situations arise where students have to miss a lab section or cannot complete the assignments on time. I set up an attendance and extensions form to allow students to document an absence, correct their attendance record, or request an extension. When students are excused from attending a course component for a week, an Airtable automation marks them as excused in the attendance sheet and the student and TF are notified. When students receive an extension, students are notified of the new submission deadline. We have processed over 800 attendance and extension requests to date.
Gradescope grade publishing: EC50 uses Gradescope for assignment submission and grading. There is no automatic process to sync grades on Gradescope with assignments on Canvas, rather a "Post Grades to Canvas" button on each assignment's page. Because of regrade requests and other adjustments, grades need to be frequently synced, so in previous years, the course opted to keep grades only on Gradescope. I created a Python script using Selenium to automate logging in and clicking the Canvas grade posting button on every assignment page. I set up Deepnote Python notebooks to schedule the script running multiple times per day.
Staff issue tracker: We wanted to have a central way to document issues students brought up to the teaching staff, so I created a dedicated staff issue submission form with automations to notify head staff and the submitting TF of our response.
Gradescope grading assignments: Because EC50 supports and teaches in two different programming languages for lab assignments (Stata, R), the course's 520 students need to be split into grading sections each week so TFs can grade assignments effectively. We wanted TFs to grade random sections of students, so I created a Python script using the Airtable API to split students into grading sections based on the language they are using to complete the assignment.
Guest lecture attendance: EC50 takes attendance in guest lectures and simultaneously enrolled students complete an alternative assignment to receive guest lecture attendance points. To accurately record attendance, we needed a way to ensure students were present in class when completing the attendance form. Because Airtable forms do not have log-in or other protection mechanisms, I wrapped the form in a Qualtrics empty survey that authenticated students through Harvard's SSO HarvardKey and redirected them to the Airtable attendance form. Airtable does not support hidden identifiers to be captured, so I created a hidden "referer" form field that captured the HarvardKey login with which the Qualtrics redirect link was generated. This was possible because the Qualtrics redirect passed the HarvardKey login identifier as a URL parameter. Attendance is pushed to Canvas through the Canvas and Airtable API right after lecture in a matter of minutes.
Left: attendance sheets allow teaching staff to quickly enter attendance every week, including for students who are attending a lab through a one-time lab change. Right: an Airtable automation randomly splits the students to grading sections based on the language they use.
Improving the design and user experience
The additional features were tested and iterated upon to improve the experience of students and course staff. In particular, one bottleneck we noticed was the time it took to enter extensions for assignments. EC50 has a two-part caption assignment, the Empirical Project, which is due in late March and mid April. We received over 100 extension requests for the Part I, which took a very long time to enter on Gradescope. Because we expected a much greater number of requests for Part II, I wrote a Python script using Selenium that adds an extension for a student by navigating the Gradescope website programmatically. The script is triggered when an extension request is approved, and adds extensions for both assignments of each part of the Empirical Project (code and write-up). I also generalized the script it so that it works for all course assignments. The extension script saved us more than four hours of work per week, as we averaged 100 extension requests per week over 3 weeks.
The attendance and extensions table was redesigned to include views that presented unprocessed requests. Automations add the approved extensions on Gradescope through Selenium.
Maintaining and documenting
As the semester is wrapping up, development of the project is focused on maintaining existing features and providing small tweaks based on the course's changing needs. For example, I implemented a link-sharing check for our Guest Lecture attendance forms, which tracks whether students are sharing their attendance form link with other students not present in lecture and invalidates those submissions. The system also sends an automated email notification to affected students, telling them to grab one of the staff members in lecture to record their attendance, as we noticed that a portion of the link-sharing was happening between students who came in late or had some technical issue with their devices.
Students received an email informing them that their attendance was not recorded if they submitted their attendance using a form link that was previously used by another student (link sharing).
At the same time, I'm making sure that the project can be re-implemented for next year's iteration of EC50, and potentially other courses in the Economics department, by providing succinct and clear internal documentation.
Notes
This project was developed with the support of EC50's Instructor Dr. Gregory Bruich, who provided helpful feedback and ideas for new directions throughout the process.