An Introduction to Google Apps Script Part 2 – Send Emails Using Spreadsheets, GMail and a Document Template

Welcome to the second part of our Introduction to Google Apps Script. This tutorial builds on what we learned in the first tutorial so if you missed that, give it a read or take five minutes and watch the supporting video. We’re going to move pretty fast on this one so without further ado, let’s go.

In the first part of the tutorial we created a spreadsheet that sent emails to recipients listed on one of the worksheets. The content of the email was hard-coded into the script and this is clearly not an ideal solution. In this tutorial we’ll learn how to use a template document for the email that we can easily customise. As usual you should open up the Tutorial Spreadsheet and Make A Copy for yourself to play with.

The first thing we’re going to do is create a new Document that we can use as the template for our email. Same deal as the before, head over to your docs home page and create one. Once you’ve created a document, give it a sensible sounding name and then we’re going to grab the id for the document so that we can refer to it from our script. Don’t worry too much about the content just yet, particularly the weird %…% stuff.

See the part of the URL that’s highlighted? That’s the Document ID. The format is the same all over Google Docs. Go ahead and copy the ID of your document then open up the Script Editor. Once the script editor is loaded you’re going to want to open up the “File->Project properties…” and add your Document ID to the “Project Properties” tab (I know, slightly confusing!).

Go ahead and change the “Value” to be the ID you’ve copied from your template document email.

With that done it’s time for the code, which should be familiar if you’ve gone through the first tutorial. Here’s the main body of code we’re interested in before we break it down in detail, just let it wash over you. Notice that there is a small change to the main send emails loop and a new function getHtmlEmail, which is going to be the main focus of this tutorial.

But first, let’s look at the new bit of the main loop. The GMailApp.sendEmail function actually takes a fourth parameter, which is an object (remember the curly {} braces thing) that can have a number of fields defined. Here we’ve replaced the hard-coded email content with an object definition to containing the HTML version of our email which we create by calling the function getHtmlEmail. Note that we use an empty string for the third parameter.

So far so good. Now lets take an overview of getHtmlEmail, its job is to;

  • open up the template email document,
  • make a copy of it,
  • replace keys (%…%) with the values we want to use (forename,surname,email,date),
  • retrieve the HTML for the document,
  • delete our working document – say ‘No!’ to a cluttered docs homepage

Phew! Here’s the code for all that:

This time more slowly. The first thing we do is use the global ScriptProperties object to call getProperty and get the one we set up earlier with the Document ID of our template document. If you used a different name for the property be sure you pass the correct one in.

The next three lines are quite dense. The first gets the template (DocsList.getFilebyId) copies it (makeCopy) and stores the ID in docId (getId). Then we open our working document (DocumentApp.openById). Finally we use doc.getActiveSection to get at the document manipulation functions.

Note that we’re going to re-use the template (the clue is in the name) so we have to make a copy or it will be filled with the data from the email we’re working on currently and will only work once.

Once we’ve got our copy of the template document we’re ready to replace the ‘keys’ with real values. What I’m calling keys are simply specially formed strings contained within the body of the document that can easily be extracted by the script. By enclosing an uppercase string in % symbols it’s easy for the script to extract the keys but it’s also easy for humans to see that these are keys. So here’s our example text with a couple of keys added for good measure.

Hi %RECIPIENT_FORENAME%,
This is a much better way to compose emails. Do you want to meet to discuss on %DATE%?
All the best,
Chris…

To extract and replace the document keys I’ve set up another object, where the field names are the document keys and the values are the values we want to replace the document keys with. This makes the code both clear and simple, even if the explanation sounds convoluted.

So we create a keys variable, which is an object with several fields (RECIPIENT_FORENAME, RECIPIENT_SURNAME, RECIPIENT_EMAIL and DATE). The values of all but DATE are passed into the getHtmlEmail function.

DATE is derived from the global Utilities.formatDate call, which in turn uses a new Date(), a timezone and a format string. Date is a global Javascript class, which when constructed with no parameters will contain the time and date at the moment it was created. Session.getTimezone is another does what it says on the tin function, it’s used to convert the date to the correct timezone for the person using the script. Handy.

Once we’ve got our keys object it’s pretty simple to replace them with the values we want. The takeaway from this is that if you set up a for loop this way on an object, it behaves like an array, which is very useful. The only difference being that rather being indexed by numbers (0,1,2,3…), you can use the field names. So the variable k contains the names of the fields and we can access the values using keys[k].

Now “all” that remains is to save the file, grab the HTML for the document before deleting it. doc.saveAndClose() does exactly what it says on the tin. I’m going to totally gloss over the contents of getDocAsHtml. Suffice it to say that it will return the html goodness that GMailApp.sendEmail is expecting. The final call uses the global DocsList object to File.setTrashed on the document. The html variable contains a string representing the HTML of our email. It’s simply returned to the parent function, in this case assigning the text to htmlBody: getHtmlEmail(…) from the main loop we went over earlier.

The following code lies in the realms of witchcraft and is a workaround we’re using until Google get around to implementing some more MIME types for the File.getAs function ie “text/xhtml”, etc… which they support elsewhere. I give you permission to just use the following code and treat it as a black box. Note to Google – get around to doing this!

Finally there’s an extra thing to consider when authorising this script. Normally Apps Script will detect the correct permissions automatically and prompt for authorisation accordingly. Unfortunately the drawback of the workaround we use means that Apps Script can’t detect a required permission from Docs (because it’s accessed through the URLFetch mechanism. This means that you need to run the code at least once through the debugger in order to properly authorise the script. Take a look at our ‘Whilstlestop tour of the Apps Script Debugger‘ screencast to see how that’s done, it’s quite straightforward and it’s a useful tool. You can also find the screencast on our YouTube Channel page.

Ok so that’s it for this tutorial. This has added a good new bit of functionality to our mailer, allowing us to use a Document as a template for our emails. You should experiment with formats, to see what kinds of things work – for instance it’s perfectly possible to create emails with images in them but tables don’t work too well. Feel free to contribute thoughts in the comments about where you’d like to go with this series. I think next time we’ll look at adding a simple user-interface to customise the contents of the email.

6 Responses to An Introduction to Google Apps Script Part 2 – Send Emails Using Spreadsheets, GMail and a Document Template

  1. Justin July 28, 2012 at 4:19 pm #

    Thanks for this tutorial, extremely helpful. Just a note to say that this script is now throwing an error for the “witchcraft” part of the script:

    Cannot capture continuation from JavaScript code not called directly by executeScriptWithContinuations or callFunctionWithContinuations

    In relation to this line:
    return UrlFetchApp.fetch(url+docId,auth).getContentText();

    Not sure if it’s something I did or if Google updated the tools to not need the witchcraft, but there ya go :)

    • chris July 28, 2012 at 4:35 pm #

      Hi Justin,

      Thanks for the head’s up. I’ll take a look at this and double check the code as there was a small mistake in the example relating to the scope in the OAuth library. It wasn’t big enough to break previously but that is most likely what has changed.

      The first piece of the ‘witchcraft’ code should in fact look like this:

      Note that in the tutorial the function conflates scope with url.

      Chris…

      • Justin July 28, 2012 at 5:11 pm #

        Thanks for the quick reply Chris, I had actually just stumbled on one of your Stackoverflow posts about this code while trying to figure it out myself.

        Unfortunately, it still doesn’t seem to work with the new witchcraft code – exactly the same error comes up. Also the doc is also set to “anyone with link” in case that was related to this same issue.

        Thanks again
        Justin

        • chris July 29, 2012 at 9:34 pm #

          Ok, I’ve just done a pass on the tutorial spreadsheet and integrated the fix as well as cleaning some test code I’d added. I’m finding that it works for me now. Bear in mind that it will fail the first time due to authentication problems and that you need to run it full speed through the debugger to make it work. I did a video showing how to do this.

          Hope that helps, let me know how you get on. It does work!

  2. Mauricio July 29, 2012 at 1:38 am #

    I also get the same error that Justin.

    • chris July 29, 2012 at 9:35 pm #

      Hopefully my fixes (from below) should improve the situation but let me know if you’re still having problems.