General Musing

blaze your trail

Proof of Concept: Google Docs Mail Merge Form #wordpress #updated

leave a comment »

I needed a way to be able to shamelessly plug the posts I recently bundled into the booklet “Write Something“. I want to build a list, and offering something which adds value for the subscriber is a good way to do this. There is a host of good material which you can use to help, so I won’t elaborate on that in this post.

I have a hosted blog, which means that I can’t run a local script to collect the mail addresses and mail them, so I turned to Google Docs’ Form functionality for the entry form, naturally I give them the option to download the booklet there, and I wanted to send the subscriber a message to thank them. In the Google tutorial: Simple Mail Merge they explain how to do a mail merge using the Script Editor. I wanted to go a little further and have it send a mail with thank you note and a link to each subscriber as soon as they filled in the form.

Using the tutorial and Script Editor, which uses javascript, I had a starting point. I wanted three changes:

  1. send only the last mail address mail
  2. customizable subject line
  3. trigger mail

The first two changes where easily done by changing the example script. Rather than cycling through the spreadsheet with a for-loop I requested the last rowData. And I would be using A1 for the subject and A2 for the mail body, instead of using A1 for the body and having a hard code subject line.

function sendEmails() {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var dataSheet = ss.getSheets()[0];
   var dataRange = dataSheet.getRange(2, 1, dataSheet.getMaxRows() - 1, 4);

   var templateSheet = ss.getSheets()[1];
   var emailTemplate = templateSheet.getRange("A2").getValue();

   objects = getRowsData(dataSheet, dataRange);

   var rowData = objects[objects.length-1];

   var emailSubject = templateSheet.getRange("A1").getValue();

   MailApp.sendEmail(rowData.mailAddress, emailSubject, emailTemplate);

For the last requirement I needed to use the Triggers functionality, which can be found in the menu. Select Current project’s triggers… and add a new trigger which should be run On form submit.

Read next article in series…

Image source: Search Engine People

Written by Daniël W. Crompton (webhat)

January 23, 2012 at 9:59 pm

Please Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: