General Musing

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 WordPress.cam 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.

Image source: Search Engine People