Google spreadsheet mail merge... makes rollover a joy!

 Some rights reserved by limbte

My work life is mostly occupied by the rollover (moleover) at the moment and so it's the little things that make the process easier, more efficient and generally more interesting that please me greatly... and one of these things is the humble mail merge!


In MOLE (Sheffield's VLE) we have things which I've always called Custom courses... I'm no longer allowed to call them that as users don't know what that means so aside from calling them non-standard or non-CIS courses I'll have to fall back on a fuller definition...

Courses which are not created automatically as part of student information system integration (modules) but are requested on a case by case basis by academics. Before we had Organisations these courses were used as community spaces and departmental virtual offices amongst other things but now they tend to be courses for a whole programme of study or used for cross modular projects.

Anyway, long winded explanation over, we have these courses which are not part of the automated rollover each year and so we have to rely of the course owners to get in touch with us and let us know what they require for the following year. Previously we have sent out general emails requesting that people fill in forms and let us know what they require. Invariably people didn't know the email actually required them to do something and so a number of things happened...

1. The course would disappear from the student's list before expected
2. The course wouldn't get the next year's cohort of students added

So this year I've attempted to streamline the whole thing a little....I thought that if I could gather the requisite bits of data about the non-standard courses I could create a mail merge to send the emails to the individuals directly involved in the courses which contain the specifics of the current course and even pre-fill some of the form fields for them on the Course Request form for the coming year.

Mail Merging

• So first gather your data - in my case it's about all these non-standard courses which includes useful things like the Course ID and Name, the Instructors on the courses and their email addresses (very important!) and also any module/programme codes which have been used for student enrolment.
• Then put all of this into a Google spreadsheet and here comes the interesting bit...
• Go to the Script Gallery (under tools) and install a script called 'Yet another mail merge' (accepting the permissions along the way).
• This mail merge script integrates with Google mail so you can format your draft email and insert the spreadsheet fields as variables written like this - $%fieldname%. You can even put a variable in the email subject (which is dead useful!)
• So once you have your email written and your data in the spreadsheet (including the email addresses) you click the mail merge option in the menu and off it goes... it asks you which email draft you would like to use and also for the column containing the email address (if it's not obvious from the title). It then populates a new column in your spreadsheet telling you an email has been sent.  

Prefilling google form fields 

In my case I wanted to make it as simple as possible for the Instructors to fill in the Course Request form and make sure that we received accurate data back and the best way of doing this was to try and pre-fill some of the fields on the form. You can do this by finding the ID element from the HTML code for the form field you wish to pre-fill something like entry_05 and then append it on to the URL for the form in the usual way using &entry_03=$%fieldname%.


https://googleURL.com/spreadsheet/viewform?formkey=dFhWaFRUTjRMQ&entry_15=$%CourseID%


What I did find was that, as this method of pre-filling the form fields is a little bit of a hack (I would be glad to hear of a better way), it only works for fields which are on the first page of a Google form. Any subsequent values disappear as they are in the URL (GET) and the form is submitted using a 'POST'.


I have yet to send out the full mail merge but I have tested it successfully and I am looking forward to all the responses coming into another lovely spreadsheet rather than having to collate all the individual form responses of previous years!


Jo