I have been keeping the grades from the various classes I teach in a google spreadsheet for almost two years now. It works great because I can share them with the TA or grader (back when we had TAs, before the budget cuts) while google docs maintains all the past revisions so I can always revert back if any one of us makes a mistake (always me, it turns out).
The only problem arises when I have to give the students their grades. Since the University has banned us from posting the grades outside our door, as my professors used to do, I have to email each row of the spreadsheet individually to each student. Luckily, google provides a spreadsheet API so I could write a short script that does just that.
The script below will email each row to the person whose email appears on the column named 'email', as shown in this sample spreadsheet. You will need python as well as the python gdata libraries installed for this to work. Installing them is just a matter of downloading and unzipping them in the same directory as the email-rows
program below. Before you use it you will also need to change the sender and spreadSheetName to be your gmail username and the name of the spreadsheet you are using, respectively.
import gdata.spreadsheet.text_db
import getpass
import atom
import smtplib
import time
def smtpLogin(password):
"""Login to the SMTP server. I'm assuming the smtp server is gmail"""
global smtpServer
smtpServer = smtplib.SMTP("smtp.gmail.com",587)
smtpServer.ehlo()
smtpServer.starttls()
smtpServer.ehlo()
smtpServer.login(sender, password)
def sendMessage(to,subject,body):
headers = "From: %s\r\nTo: %s\r\nSubject: %s\r\n\r\n" % (sender, to, subject)
message = headers + body
print message + "\n===================================\n"
if not testing:
smtpServer.sendmail(sender, to, message)
time.sleep(1)
def emailRows(spreadSheetName, workSheetName):
password = getpass.getpass()
smtpLogin(password)
client = gdata.spreadsheet.text_db.DatabaseClient(sender,password)
db = client.GetDatabases(name=spreadSheetName)
table = db[0].GetTables(name=workSheetName)[0]
rows = table.GetRecords(1,1000)
subject = spreadSheetName + ' grades'
table.LookupFields()
for s in rows:
if s.content['email'] and s.content['email'] != '':
body = 'Your grades are:\n\n'
for key in table.fields:
if key == 'email' or key == 'name' or not s.content[key]:
continue
body += key + '\t' + s.content[key] + '\n'
body += '\nJose\n'
sendMessage(s.content['email'],subject,body)
testing = False sender = "username@gmail.com"
emailRows(spreadSheetName='Gdata 101', workSheetName='Sheet1')
If you like the idea but don't feel like running your own code then wait around here for a bit. I am working on turning this script into a web application so anyone can run it from the web.