An Introduction to Google Apps Script – Send Emails Using Spreadsheets and GMail

Most people who have used a spreadsheet (usually Excel) are familiar with the idea of Macros. These are simple scripts that can automate basic tasks. And most people who use Google Spreadsheets haven’t gotten round to figuring out the Google Apps equivalent of Macros, Apps Script. That’s a shame because Apps Script is unbelievably powerful when compared to the Excel equivalent. Apps Script is based on Javascript, which means that almost anyone who’s done anything with the internet for the past five years has some idea about it. This means there’s lots of help around. While I’m not going to go into too much detail about Javascript itself, I’ll touch on some key principles that will help you adapt the code to do more useful things.

What we’re going to do with this very simple tutorial is to walk through some basic data manipulation and finally send a standard email to the people listed in the spreadsheet above. If you don’t feel like working this through, the complete version is contained in this spreadsheet and accompanying code.

Now let’s crack on. First up you’ll want to create a spreadsheet. Head on over to your docs homepage and create one.

Click the big red Create button

Once you have created and named your Spreadsheet, load it up and open the Script Editor.

The Script Editor is in the Tools menu

This is the glorious Script Editor

Once into the Script Editor we can start to build something. With every Script you need a way for the user to interact with it, this is called the User Interface. For this example we’re going to add a “Tutorial” Menu to the Spreadsheet and a Menu Item called “Send Email” that will run our Script. To do this we need to write our first piece of Apps Script, a Function called onOpen.

There are a couple of Functions in Apps Script that need a specific name and onOpen is one of them; it is executed every time the Spreadsheet is opened. So if you change the name of our function from onOpento something else, your menu won’t appear in the Spreadsheet. You can change what’s in quotation marks to change the name of the Menu (Line 9), Menu Item (Line 5) and the name of the Function executed (Line 6). If you want to add more Menu Items that’s easy:

In Javascript square brackets ‘[]‘ are used to define Arrays (collections of ‘things’) and curly brackets ‘{}’ to denote Objects (‘things’ that can do ‘stuff’). The menu is therefore an Array of Objects and each Object has two Fields, name and functionName. Fields and Objects are separated by commas. Go ahead and Save your file by clicking the disc icon, then refresh your Spreadsheet. You should see your new menu added to the right of the standard menu. Be patient because it can take a few seconds for the menu to appear.

Clicking it will have no effect because we haven’t added the uiSendEmail function yet. This one is a bit more complicated!

Let’s break it down to understand how it works.

The first few lines grab the Active Worksheet and a Range Object. A Spreadsheet usually contains several Worksheets but for this example we’re only using the active one. A Worksheet is the bit of the Spreadsheet with the grid of Rows and Columns, it’s where you put data.

The Range Object specifies areas of a Worksheet and lets you read/write the data contained within it. In our case we’re using a special Function in the Worksheet Object called sheet.getDataRangethat returns the Range Object encompassing only the area of the Spreadsheet that contains data.

This line simply adjusts the Range object down one Row so that we skip the field headers. The offset Function here is being provided with three parameters – range.offset(1, 0, range.getNumRows()-1). The first (1) moves (offsets) the top of the Range down one Row. The second (0) leaves the left of the Range as it was. The third subtracts 1 from the number of rows in the Range. The effect is to move the Range down one Row and reduce the number of Rows it encompasses by 1.

Ok so that bit of code was dense, don’t be put off but it does a lot. It grabs the contents of the Worksheet Range using range.getValues, then for each Row (.forEach, get it?) it sends an email (GMailApp.sendEmail) using that data (contained inside recipient). Like I said it does a lot so let’s go through that again more slowly.

The data from our spreadsheet.



range.getValues will return what’s called a two-dimensional Array, or to put it another way an Array of Rows and Columns. The contents of Worksheet Row 2 (remember, our Range starts at Row 2 to avoid the Field Names), Column A (Forename) are stored in recipient[0][0]. You might think that it’s weird that it’s [0][0], but you just always need to subtract one from the row and column numbers when working with Arrays, so Row 1, Column A will always be [0][0]. .forEach acts on the data returned by range.getValues and executes a Function for eachof the items contained within the Array. The upshot is that these two lines of code are called for each Row in the Range:

recipient is an array with three elements (Columns); Forename (recipient[0], Column A), Surname (recipient[1], Column B) and Email (recipient[2], Column C). All that’s required now is to assemble our email (body) using the recipient data and send the email. Note that the “\n” characters each represent a carriage return or you pressing ENTER – don’t worry about this too much because I’m going to show you a much better way of doing this in the next tutorial. Then we call the GMailApp API and ask it to send the email for us. GMailApp.sendEmail takes three parameters, email address, subject and email body. Parameters are just things you send to a function to tell it how to behave. For the email address we’re using recipient[0] (Forename, Column A) and recipient[1] (Surname, Column B) as well recipient[2] (Email, Column C) for readability. You’ll notice that “+” is used in an intuitive way here, it just adds the strings together to produce one bigger string. When finished, the email address will look like this: Test One <test@one.com>, which is nicer than just the naked email address. The subject is just a simple string, nothing fancy there. The Email Body has been built already and stored in body before being used for sendEmail.

Once you’ve added this code, save it and go ahead and click the Send Email menu item. The first time you do this you’ll be asked to grant permissions by a dialog box like the one below. It will ask for read/write permissions to both Spreadsheets and GMail, which in this case is fine so you should grant those permissions. Every time you change the code, you’ll need to re-grant these permissions, it’s annoying when developing but once you’ve released your script to users they should only have to do it infrequently. You should always make sure you and your users understand these dialogs before granting the permissions.

An example of the permissions dialog.

Ok so we covered a lot of ground there but you now know how to create a Spreadsheet, Open the Script Editor, Add a Menu, Access Worksheet Data and call the GMailApp API to send a bunch of emails. This tutorial has shown you the most basic way of doing things and there are many things we can improve. Check back next week to find out how to use a Google Document as a template for your email.

11 Responses to An Introduction to Google Apps Script – Send Emails Using Spreadsheets and GMail

  1. marge June 6, 2012 at 7:01 pm #

    Nice script. Now is it possible to add an entry to the spreadsheet to show that the email was sent?

    • chris June 7, 2012 at 9:55 am #

      Glad you like it. It certainly is possible to add a log entry to another sheet in the spreadsheet to record emails sent; it’s probably one of the first things you’d want to do once you’d got the basic system working.

      I’ll work this up into a tutorial when I get a spare minute but to get you started, the following is the kind of thing you need to do:

      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getActiveSheet();
      // note: change this to specify a particular sheet for logging

      var range = sheet.getDataRange();
      var newRow = range.getNumRows();

      sheet.insertRowAfter(newRow);
      // note: inserts after newRow

      var data = [new Date, "Some Log Text", "email address"];
      sheet.getRange(newRow+1, 1, 1, 3).setValues([data]);
      // note: newRow+1 is the newly inserted row
      // note: [data] – 2d array, an array of arrays must be specified.

      Hope that helps, feel free to ask questions if you’d like more info!

      • Alec September 13, 2012 at 3:54 am #

        I’m trying to use this script with multiple address and using cellphone numbers instead of regular email addresses. I tried to add 6 recipients and it’s not working. Here is the script below. Can you tell me what I’m doing wrong? I need to add probably 20-30 in the end. Please help.

        function onOpen() {
        var ss = SpreadsheetApp.getActiveSpreadsheet();

        var menu = [{
        name: "Send Email",
        functionName: "uiSendEmail"
        }];

        ss.addMenu(“Email”, menu);
        }
        function uiSendEmail() {
        var ss = SpreadsheetApp.getActiveSpreadsheet();
        var sheet = ss.getActiveSheet();
        var range = sheet.getDataRange();
        range = range.offset(1, 0, range.getNumRows()-1);

        range.getValues().forEach( function( recipient, index, data ){
        var body = “Hi ” + recipient[0] + “,\n\nThis is a test to see if my scripts work for setting up text. Let me know if it works or not.\n\nAlec”;
        GmailApp.sendEmail(recipient[0] + ” ” + recipient[1] + “” + recipient[3] + “>” + recipient[4] + “>” + recipient[5] + “>”, “”, body);
        });
        }

        • chris September 13, 2012 at 9:18 am #

          Obviously you’ve changed the structure of your spreadsheet so I’ll work on the assumption that while you’ve added street addresses and cell numbers, the original structure is still intact; ie Column A: Forename, Column B: Surname, Column C: Email Address.

          A quick looks suggests that your problems lies here:
          GmailApp.sendEmail(recipient[0] + ” ” + recipient[1] + “” + recipient[3] + “>” + recipient[4] + “>” + recipient[5] + “>”, “”, body);

          This code won’t result in an email being sent because the first parameter of sendEmail expects “Some Body <email@yourdomain.com>”. Yours is constructing something from Column A, B, D, E & F that would look something like this:
          “Forename SurnameColumnD>ColumnE>ColumnF>”.

          My conclusion is that you’re confusing rows with columns. The recipient array contains columns, not rows.

          So your line to send an email needs to look like this:
          GmailApp.sendEmail(recipient[0] + ” ” + recipient[1] + “ < ” + recipient[2] + “>”, subject, body);

          The only reason to mess with this is if Column A no longer has the Forename or Column B no longer has the Surname or Column C no longer has the email address in it.

          • Alec September 13, 2012 at 5:15 pm #

            Chris,
            Thanks for looking. I just used the original script you had and tried adding more than 3 contacts and it worked.

  2. Cenk July 6, 2012 at 2:15 pm #

    Dear Chris,
    Thank you so much for this great tutorial!
    I would like to ask you how I can send email to the users in a particular column when a new line added to the spreadsheet. We use Google Docs for project management and I would like to confirm the client contact everytime I enter a project (each line is a new project entry).
    Thanks!

    • chris July 12, 2012 at 11:22 am #

      Hi Cenk,

      That’s a slightly more complicated problem to solve but it is doable by setting up a Trigger for the onEdit event. You would need to track some state (which is easy with ScriptProperties) to know which was the last row added. Once you know that, you simply look up new rows after the last one added and send an appropriate email.

      Hope that helps!

  3. alan July 20, 2012 at 2:51 am #

    thanks for the tutorial / I have a google spreadsheet where the email address is in column M, not C

    when I test your tutorial out with a sample spreadsheet and move the email from column C to D, the script doesn’t work for me

    Therefore, the big question – How do I get the script to pull the email address from a different column?

    • chris July 20, 2012 at 9:18 am #

      Hi Alan,

      Thanks for your question, it’s a good one as it highlights an important shortcoming of the approach taken in the tutorial; moving the data around in the spreadsheet will break it.

      A much better way to access spreadsheet data is to create a header row that defines the contents of columns and use that to reference the data. Unfortunately that’s a fair bit more complex to implement and not something for a beginners’ tutorial.

      Fortunately, Google have an example that shows how to read data from a spreadsheet in this manner and in fact Cloud Touch’s libraries are based on this approach. You can find that tutorial here: https://developers.google.com/apps-script/storing_data_spreadsheets#reading.

      The quick fix for your problem lies in this part of the code:

      var body = “Hi ” + recipient[0] + “,\n\nHow do you like that?\n\nChris…”;
      GmailApp.sendEmail(recipient[0] + ” ” + recipient[1] + “< " + recipient[2] + ">“, “My Spreadsheet Can Send Email!”, body);

      The ‘recipient[0]‘, ‘recipient[1]‘ & ‘recipient[2]‘ elements are affected by moving columns around. ‘recipient[0]‘ refers to column A, ‘recipient[1]‘ to column B and so on. If you move the email address from column C to column D simply change references to ‘recipient[2]‘ to ‘recipient[3]‘.

      Hope that’s helpful.

      Chris…

Trackbacks/Pingbacks

  1. Cloud Touch Google Apps Script Tutorial | Evans & Finch - February 22, 2012

    [...] If you’re a Google Apps user, the chances are that you haven’t played much with Apps Script yet – there are so many awesome features to play with already, it’s easy to forget about scripting. But over at Cloud Touch they’ve written a handy primer. It’s a wee bit techy so you may need to get in touch with your inner nerd, but by the end of it you’ll know how to use a Google Spreadsheet to send Emails to people. [...]

  2. An Introduction to Google Apps Script Part 2 – Send Emails Using Spreadsheets and GMail with an Email Document Template | Cloud Touch - March 4, 2012

    [...] Tutorial Part 1 [...]