Here’s yet another example as to why python is a very useful tool. I used this script to send the email with quiz grades:
import pandas as pd
roster = pd.read_csv("raw/roster.csv")
netid_to_email = dict(zip(roster["Net ID"], roster["Email"]))
grades = pd.read_csv("raw/grades.csv")
# remove rows with NaN SIS Login ID
grades = grades.dropna(subset=["SIS Login ID"])
allowed_sections = [
"Section M", "Section N"
]
# Filter sections by allowed_sections
grades = grades[grades["Section"].isin(allowed_sections)]
# Filter sections by columns that contain grades (defined manually)
grades = grades[columns_with_grades]
# let's create a dictionary mapping SIS Login ID to a dictionary of grades, with keys being the column names and values being the grades
grades_dict = {}
for index, row in grades.iterrows():
grades_dict[row["SIS Login ID"]] = {
column: row[column] for column in columns_with_grades
}
import os
from dotenv import load_dotenv
load_dotenv() # you can store sensitive data in .env file so that only you can see it
email = os.environ.get("EMAIL")
password = os.environ.get("PASSWORD")
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
# Email server
server = smtplib.SMTP_SSL("smtp.gmail.com", 465, timeout=1800)
server.login(email, password)
# Let's create a custom email body for every student and send it to them
for netid, grades in grades_dict.items():
student_email = netid_to_email[netid]
msg = MIMEMultipart()
msg["From"] = "Anton Morgunov"
msg["To"] = student_email
msg["Subject"] = "[Chem156] Your Quiz Grades"
name = roster[roster["Net ID"] == netid]["First Name"].values[0]
body = f"Hello, {name}!\n\nI learned today that, for some reason, you don't see the quiz grades on Canvas. I've asked to make them available, but in the meantime, here are the quiz grades:\n"
for grade_name, grade in grades.items():
if grade_name == "SIS Login ID":
continue
if pd.isna(grade):
grade = "missing"
body += f"{grade_name.split(' (')[0]}: {grade}\n"
body += "\nThis email has been generated automatically using a Python script.\n\nBest,\nAnton"
msg.attach(MIMEText(body, "plain"))
server.sendmail(email, student_email, msg.as_string())
print(f"Sent email to {student_email}")