Putting the magic in the machine since 1980.

Tuesday, June 9, 2009

How to Email Rows from a Google Spreadsheet

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.

#!/usr/bin/python
# email-rows
#by
#jmvidal
#
#Emails every row from a google spreadsheet to the email address in that row.
#The spreadsheet must have a first row with a cell with 'email' in it.

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) #so gmail won't ban me as a spammer

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() #populate table.fields

    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 #if testing is true then we don't send the emails, just print them.
sender = "username@gmail.com" #your gmail address

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.

1 comment:

Chris Maguire said...

Hi Jose,

This is exactly what I'm looking for as well. Are there any instructional videos, or have you found another app that does this?

I have used conditional formatting to mark a maths assessment task. Used Power Tools add on to count the total of green coloured cells (based on conditional formating) now I'm looking to share the results with students based on email column.

Here's my sheet here.

Any help would be greatly apreciated.

Chris

https://docs.google.com/spreadsheets/d/1x6QIFfICQrhg-ZRlRe0uJaqLRCHLqtXVmtiU_HPuJjk/edit#gid=1213521358