I mostly have liked the course management software I have used (Moodle and Canvas), but both are pretty terrible when it comes to keeping track of grades in an Standards-Based Grading system. I have mostly kept the my grades in a spreadsheet, which does all of the calculations that I want it to, but the students then do not have access to their grades. I tried using Canvas to report grades in Spring 2016 and Fall 2016, but Canvas will not do the calculations I need it to (I just posted the raw scores to Canvas, and I gave the students the logic to figure it out); I had to keep a separate spreadsheet to do everything I needed.
Neither of these made me happy, because I want my students to have access to their grades (if only to check for mistakes I have made), but I also want a single place to put my grades. My solution was inspired by Drew Lewis, who created a PERL script to send his students email updates of their grades directly off his spreadsheet. If I were more computer-savvy, this probably should have been an obvious solution, but I am very grateful that Drew pointed out what I could not recognize on my own.
I am more familiar with Python, so I wrote my own script (included below). Once I have the code written, I go to a command line (I use Linux), type “crontab -e” to edit my crontab, and type (without the quotation marks) “14 3 * * 2 /usr/bin/python 118-S17/Grades/118EmailGrades.py” to send an email at 3:14 am (the 14 3) every Tuesday morning (the “2” in “14 3 * * 2”). The “/usr/bin/python” says to run the program “python” and input the file “118-S17/Grades/118EmailGrades.py.”
Below is the code. It seems to work, but there is one issue that I am ironing out: I am only allowed to send five emails at a time when I tested it. I am pretty sure that this is a limitation on the server’s end, since I am sending the messages to only a couple of email addresses (all mine for the test runs). My (ugly) hack, which worked on Tuesday, is that I broke up my code so that each program only emails 5 students. I welcome troubleshooting ideas from those who know about this stuff, although I suspect that I could just try the single program and it would work, since I am not actually going to email to the same email address more than once for my class.
Here is the code. Note that indentation matters A LOT in Python, so be careful if you cut-and-paste.
import openpyxl import smtplib import email import time #This gets the spreadsheet the grades are in. wb=openpyxl.load_workbook('Grades118S17.xlsx',data_only=True) #Here I am getting each 'sheet' of the spreadsheet. rosterSheet=wb.get_sheet_by_name('Roster') summarySheet=wb.get_sheet_by_name('Summary') quizSummarySheet=wb.get_sheet_by_name('QuizSummary') quizLogicSheet=wb.get_sheet_by_name('QuizLogic') #I put my password in my spreadsheet, since that is supposed to be more secret than this code is. I put it in Cell AA1 of the "Roster" sheet, and this gets it out. pw=rosterSheet['AA1'].value #I am logging into my email server here. smtpObj=smtplib.SMTP('exchange.csbsju.edu',587) smtpObj.ehlo() smtpObj.starttls() smtpObj.login('firstname.lastname@example.org',pw) #I want to put the date in the email, so I am getting it here. todaysDate=str(time.strftime("%m/%d/%y")) #The From email address and Subject of the email will be the same for every student; I put today's date in the Subject for the students' convenience. fromVar="bbenesh@MYSCHOOL.edu" subject="Math 118: Grade Update for "+todaysDate #Put the last row you want to check prior to the +1 NUMBEROFROWS=54+1 #Put the rows you do not want to check (because they are blank or because the student dropped) in the list below. EXCEPTIONS= #I have to hardcode the range, and I skip rows 28 and 29 because they do not contain student data. #The commented out for loop is just to test so that I am not flooded with emails. for rowVar in range(2,NUMBEROFROWS): &nbsp#This just skips the blank rows that I hard-coded into the exceptions. if rowVar in EXCEPTIONS: continue #This gets the student's first name and email, and I print the first name so that I can see who received an email (I get an email update once this program runs). firstName=rosterSheet.cell(row=rowVar, column=3).value print firstName toEmail=rosterSheet.cell(row=rowVar, column=4).value todaysGrade=summarySheet.cell(row=rowVar,column=2).value #I am going to put together the body of the message in several steps, storing it in the 'text' variable each time. This is just the saluation of the email. text="Dear %s,\n\nBelow is your weekly grade update for %s. If the semester ended today, you would receive a grade of %s. Of course, I fully expect your grade to go up, since the semester is not yet over." % (firstName,todaysDate,todaysGrade) #Here I am getting the summaries of their grade components and putting it in the text. quizGrade=summarySheet.cell(row=rowVar,column=19).value gatewaysGrade=summarySheet.cell(row=rowVar,column=20).value teamProjectGrade=summarySheet.cell(row=rowVar,column=21).value individualProjectGrade=summarySheet.cell(row=rowVar,column=22).value SRLGrade=summarySheet.cell(row=rowVar,column=23).value text+="\n\nBelow are your current letter grades for each of the components of your semester grade. Your grade is determined by the lowest of these, so you should focus on the component with the lowest grade; see the syllabus for more details.\n\nQuiz Grade: %s \nGateways Grade: %s\nTeam Project Grade: %s\nIndividual Project Grade: %s\nSelf-Regulated Learning Reflections Grade: %s\n\n" % (quizGrade,gatewaysGrade,teamProjectGrade,individualProjectGrade,SRLGrade) #Here I am giving them the next two things they should be studying to improve their grade; the logic in the spreadsheet figures this out. firstMissingQuiz=quizLogicSheet.cell(row=rowVar,column=2).value secondMissingQuiz=quizLogicSheet.cell(row=rowVar,column=3).value text+="The two Learning Outcomes you should focus on next are %s and %s. At the bottom of this email is a list of the number of times you have demonstrated each of the Learning Outcomes. Please check this over to see that it is correct, and be sure to email me if you find a mistake.\n\nHave a great day!\nBret\n\n\n" % (firstMissingQuiz,secondMissingQuiz) #Next, I am just going to loop over the raw data for each Standard and print it out at the end of the email. This is so they can check to make sure that their records agree with mine. #Put the number of the column corresponding to your last learning outcome prior to the +1 NUMBEROFCOLUMNS=23+1 #Again, I am hardcoding the column range for my spreadsheet. for columnVar in range(2,NUMBEROFCOLUMNS): labelCode=quizSummarySheet.cell(row=1,column=columnVar).value numberOfMarks=quizSummarySheet.cell(row=rowVar,column=columnVar).value text+="%s: %s\n" % (labelCode,numberOfMarks) #Here I just format the final message, addding a subject header to my 'text' variable. Then I send the email. message='Subject: %s\n\n%s' % (subject,text) smtpObj.sendmail(fromVar,toEmail,message) #I log out of the email server. smtpObj.quit()