A few days ago, a friend asked me if it was possible to do mail merge with GMail. No out of the box solution came to mind, but a bit of Googling revealed that it would be possible with a bit of scripting - in Google Spreadsheets! The scripting I speak of is Google Apps Script which allows you to write JavaScript to interact with various Google apps.

The concept is very similar to VBA and Excel, however I think it is even more powerful because it has several convenient methods that allows you to easily leverage other Google apps. For example, to send an email as the currently logged in user all you need to do is: MailApp.sendEmail(email, emailSubject, emailBody);

As you can probably imagine, to get a mail merge working would require a fair bit of learning the spreadsheets API. However, luckily for me, Google has an excellent Simple Mail Merge tutorial :)

The tutorial takes you through the basics and explains the code quite clearly. However, my friend had a few extra requirements, so I needed to make few changes. I will go through a couple of the more interesting changes.

Firstly, sending a test email before sending out all the emails was high on the 'nice to have' list. This led me to discover Browser.inputBox which is like the JavaScript window.prompt.

So a new method came to light:

function sendTestEmail() {
    var testEmail = Browser.inputBox("Send test email",
                                     "Enter email address to send test email to",
                                     Browser.Buttons.OK_CANCEL);

    if (testEmail != 'cancel') { //inputBox returns 'cancel' if Cancel was clicked
        //...
        // Code that grabs the relevant information from spreadsheet
        //...
        MailApp.sendEmail(testEmail, emailSubject, emailText, extraOptions);
    }
}

If you've had a look at the tutorial, you'll notice they had a hard-coded email subject. To make things flexible I also made that template-able - essentially an extra cell in the spreadsheet that was dedicated for the subject (same pattern as the email body).

The more interesting addition would probably be extraOptions. This was added for two reasons: sender name and attachments.

1) Sender name

Apparently MailApp.sendEmail() is supposed to use the user's name by default, but in my testing, it defaulted to my email address. Could've been that my account wasn't set up properly, but regardless, added the functionality to allow for a custom sender name.

2) Attachments

This was surprisingly easy. From the docs for MailApp, you can source the attachment from Google Drive, or an external URL. I opted for the latter. My friend only needed one or two attachments, so I took the lazy path and did not accommodate for more. However it should be just a bit more work to make it take an arbitrary number of attachments.

The code for the extraOptions object is as follows:

var extraOptions = {};
var senderName = templateSheet.getRange("D1").getValue();
extraOptions.name = senderName;

var resp;
var attachmentUrl1 = templateSheet.getRange("D3").getValue();
var attachmentUrl2 = templateSheet.getRange("D4").getValue();

if (attachmentUrl1 || attachmentUrl2) {
    extraOptions.attachments = [];

    if (attachmentUrl1) {
        resp = UrlFetchApp.fetch(attachmentUrl1);
        if (resp.getResponseCode() == 200){
            extraOptions.attachments.push(resp);
        }
    }

    if (attachmentUrl2) {
        resp = UrlFetchApp.fetch(attachmentUrl2);
        if (resp.getResponseCode() == 200){
            extraOptions.attachments.push(resp);
        }
    }
}

Finally, although it was relatively simple to run the desired script via "Tools -> Script Manager" I felt it would be nice to have dedicated buttons or menu to make it even simpler. This was made almost trivial with the ability to add a custom menu to the spreadsheet ui.

A few lines of code...

function onOpen() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var menuEntries = [ {name: "Send test email", functionName: "sendTestEmail"},
                        {name: "Send email to ALL", functionName: "sendEmails"}];
    ss.addMenu("Send Mails", menuEntries);
}

...resulted in a relatively user-friendly menu:

custom-menu

Overall, the experience was quite positive. From not even knowing about Google App Scripts, to having a working mail merge working in a few hours was very satisfying. However, as always there are a few not so nice parts.

The main one would be speed. From the user side, the scripts seemed rather sluggish. It wasn't as responsive as I had hoped. From the development side, the built-in script editor was a bit slow and not that nice to use, especially the debugger. During development, the editor/debugger even crashed my Chrome.

The authorisation process (it asks for permission to send emails as you), though required was a bit confusing and didn't feel very smooth. When I first shared the document with my friend, the custom menu didn't appear. It was only after a refresh did the authorisation prompts appear and then the menu showed up after several seconds.

Although I can't think of any other application for these scripts at the moment, hopefully you have found it useful. I know that next time I need to automate something related to Google's apps, I will definitely have a look into these scripts to see what can be done.

One final comment. In the sendEmails() function, the tutorial makes use of dataSheet.getMaxRows(), however I don't see a reason why it shouldn't use dataSheet.getLastRow() instead - anyone have any ideas?