Embedded Files
This project began, as so many do, with stacks of paper and a highlighter. The principal of one of Trumbull's middle schools emailed me because he had heard about a Python project I had done at the high school that automated the task of emailing attendance letters to parents. He was interested in bringing that efficiency and increased parental communication to his school in the hopes of alleviating chronic absenteeism in his building.
My initial sit down with the middle school attendance secretary showed her workflow. First, she would run a report on our Student Information System, Infinite Campus, which would generate a PDF of students and give number of absences for students above a given threshold. She would then compare her newly printed list with the previous printed list to identify students who were newly crossing the threshold in order to contact parents.
To start, I created a Student class to hold relevant student data and an AttendanceReportParser module to parse a given PDF and return a list of students.
In previous Python projects, I had used CSV files to hold lookup information, like parent contact information, and log information, like a sent message log. I decided that, for this application, it would be advantageous to move to a Google Sheets based data storage and retrieval. Since the individual who would be running the script was going to be in a separate building, it would be difficult to update the parent contacts or access logs if needed. I created a GoogleAuth module to handle access token generation from given credentials and the google-api-python-client module to access needed Google services.
For the end product, the script loads up student data and parent contact information from the Google Sheet, then parses the PDF and updates values in the list of students. The script then identifies students for whom letters would need to be sent, and then identifies which letter to send based on the number of absences to date. Emails are generated using HTML templates and sent to parents using the contact information retrieved from the Google Sheet. Email contacts are logged in this Google Sheet, including sent date, recipients, and an email ID that can be retrieved through the Google Admin Console if needed. School secretaries were trained in how to run the needed report through Infinite Campus and a batch file was created to automate running the script.
As of 10 January 2024, this script has sent 665 emails to parents regarding students' absences. As a result of this script and other initiatives by the school, chronic absenteeism in the school has decreased in excess of 6%, year over year (data current to December 2023). At this time, the project is housed in a private GitHub repository. If you have any interest in the project, please email christopher.eide@gmail.com and I can provide needed code snippets or discuss strategies for implementation.
Page updated
Google Sites
Report abuse