Productivity Programming

How to Send Email in a Google Sheet With Google Scripts

Ryan Dube 07-07-2017

Google Scripts is a powerful tool you can use to automate Google productivity tools How To Create An Automated Google Spreadsheet Report From Analytics Data Read More like Sheets and Docs. The ability to automatically send emails makes it an extra powerful tool.


Maybe you’re an employee hoping to send a monthly email out to your boss with automatically-calculated formulas in Google Sheets Visualize Your Data & Make Your Spreadsheets User Friendly With An Excel Dashboard Sometimes, a simple spreadsheet format isn't engaging enough to make your data accessible. A dashboard allows you to present your most important data in an easy-to-digest format. Read More . Or maybe you’re a manager who spends far too much time emailing individual members of your team with things like performance data or status updates. The Google Scripts function you’ll learn in this article will help you accomplish these tasks and much more.

With just a little effort one time, you’ll never have to manually send out data again. Let Google Scripts act as your own personal assistant, doing all of the work for you.

Setting Up Your Sheet to Send Email

The first step in getting your Google Script to send mail via Google Sheets is properly setting up a sheet that has all of the names, email addresses and messages to all of the people you want the script to email.

In my example, I’m starting with a spreadsheet that I’ve set up to automatically import all of the published articles for the month from Google Analytics Create A Killer Website Analytics Dashboard With Google Core Reporting API Would you like to just publish a site and run it blindly, or would you like a full-featured, functional dashboard that shows you what's going on with the site at all times? Read More . I’ve created an “All Authors” sheet that tallies up the published articles for each author, and now I want to send each individual author a email with their tally.

To get started, create your special email sheet by adding a new sheet in your current spreadsheet and call it something like “Send-Emails”.


send email google scripts

In this new Send-Emails sheet, you’ll want to create a header. Each row in this spreadsheet will represent an individual email that will get sent out. So in this example I’ve created a sheet for the recipient name, their email address, and pieces of the message that I’m going to piece together inside of the script.

send email google scripts

You can build an email of any size and structure by using columns to piece together both static and dynamic information.


send email google scripts

The static information is just text that I’ve typed into the cell. This won’t change from month to month. It’s just a part of the email message that always stays the same. However, for the data that you want to change every month, you can insert functions that will import the relevant data from any other sheet in your spreadsheet.

send email google scripts

The idea here is that whenever you open the sheet to review your automated report, you will have a menu item you can click to run the send email Google Script and distribute the results to everyone. You’ll see how to customize this new menu item at the bottom of this article.


Once you’re finished creating your sheet, it’ll look something like this:

send email google scripts

Now that your sheet for all of the individual emails is ready, it’s finally time to write the script!

Writing the Automated Email Script

To write your script, you need to use the script editor. You’ll find the script editor under the Tools menu by selecting Script editor.


send email google scripts

The script editor will look something like below. You’ll need to crate a new function at the top of the pane on the right.

send email google scripts

Just copy and paste the script below into the script area.

function sendArticleCountEmails() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var dataRange = sheet.getRange("A2:F4");
  var data = dataRange.getValues();
  for (i in data) {
    var rowData = data[i];
    var emailAddress = rowData[1];
    var recipient = rowData[0];
    var message1 = rowData[2];
    var message2 = rowData[3];
    var parameter2 = rowData[4];
    var message3 = rowData[5];
    var message = 'Dear ' + recipient + ',\n\n' + message1 + ' ' + message2 + ' ' + parameter2 + ' ' + message3;
    var subject = 'Your article count for this month';
    MailApp.sendEmail(emailAddress, subject, message);

This script may look somewhat complicated, but it actually isn’t at all. Let’s break it down so you know exactly what each line is doing.

Breaking Down the Code

In order for this function to work right, you need to ensure that the sheet where all of your email information is stored is actually the active sheet. Otherwise everything that comes after won’t work. That’s what these two lines are for:

var ss = SpreadsheetApp.getActiveSpreadsheet();

Next, we need to extract all of the data from that sheet. The sheet.GetRange() method will extract information from whatever range of cells you provide within quotes. Next, the dataRange.getValues() method actually extracts the values and stores them into a two-dimensional array called data.

var sheet = SpreadsheetApp.getActiveSheet();
var dataRange = sheet.getRange("A2:F4");
var data = dataRange.getValues();

Now that we have all of the data stored in an array, we can loop through the array using a for loop. Each element of the array is a row that contains a one dimensional array of column elements. You can reference each column using a number.

var rowData = data[i];
var emailAddress = rowData[1];
var recipient = rowData[0];
var message1 = rowData[2];
var message2 = rowData[3];
var parameter2 = rowData[4];
var message3 = rowData[5];

As you can see above, I pulled the column elements into a one-dimensional array called rowData. Then I reference the second column (to obtain the email address) by referencing rowData[1], the second element of the array (the first element of an array is always zero).

The next step in this function is to piece together all of the segments of the messages that make up the body of the email. The nice thing here is that you can actually give the email content a pretty good format by using the \n character, which is a carriage return. Here is what the concatenation of the email body looks like:

var message = 'Dear ' + recipient + ',\n\n' + message1 + ' ' + message2 + ' ' + parameter2 + ' ' + message3;

The + character is the concatenation command 15 Excel Formulas That Will Help You Solve Real Life Problems Excel isn't only for business. Here are several Microsoft Excel formulas that will help you solve complex daily problems. Read More . You put actual static text inside of single quotes. So this message is pieced together by putting the word “Dear ” in front of the recipient variable (notice “Dear ” has a space).

Next, you add two carriage returns onto the end. This will start the actual message inside the body two lines down. Next you add the first message segment, a space, the second message segment, a space, the variable that will get inserted into the message each month, and finally the last part of the message.

You can have as many pieces of the message as you want, you simply have to use more columns to create the entire message in pieces.

The last couple lines of the code just set the subject line for the email (this could also include data from the spreadsheet if you wanted), and finally the sendEmail() method.

How to Trigger Your Script

Back in the Script Editor window, just search for the “onOpen” function. If the function is there, you’ll likely see a bunch of code inside of the brackets. Go to the bottom of that code and insert the following lines:

var spreadsheet = SpreadsheetApp.getActive();
var menuItems = [
  {name: 'Send Emails', functionName: 'sendArticleCountEmails'}
spreadsheet.addMenu('Send Emails', menuItems);

Just make sure to type the exact name of your function in single quotes for the functionName parameter. This will load a custom menu item into your sheet every time your sheet opens.

send email google scripts

Now, whenever you open up your spreadsheet on a monthly basis to review all of your data, all you have to do is click on the menu item to send out the emails. This will run your new function and send all of those emails for you. A job that used to take you several hours will now only require a single click of the mouse!

Google Scripts Can Help You Save Time

This is just one example of the many ways you can use Google Scripts to automate all sorts of things 4 Google Scripts That Make Google Sheets More Powerful Google Sheets is great out of the box, but can be even more powerful if you use Google Sheets scripts to enhance its capabilities. Read More that save you time. For example, you can use Google Scripts to automatically generate a Google Analytics report. The difficult part is only that it does take a bit of work up front, but every minute you spend setting up automation like this using scripting is many hours saved. Just think of all of the other wonderful things you could do with those hours!

Have you ever used clever Google Scripts to automate your productivity? Share your own examples in the comments section below!

Related topics: Google Apps, Spreadsheet.

Affiliate Disclosure: By buying the products we recommend, you help keep the site alive. Read more.

Whatsapp Pinterest

Leave a Reply

Your email address will not be published. Required fields are marked *

  1. Matt
    August 15, 2019 at 12:43 pm

    This is a great article on how to code this yourself - but why not just use a sheets addon which does exactly what you want much easier and faster? For sending regular reports I use this addon:

    You can even create a custom trigger (with a workaround), export your files, send multiple emails to different people and so on - I like it 😁

  2. Ansh
    June 12, 2019 at 7:06 am

    I can't find the OnOpen function in my sheets. is there any other way to involve the above-mentioned trigger?

  3. Moshe
    November 28, 2018 at 7:51 am

    Hey Ryan,

    The best script I found yet.
    Is there a way to make it align to right for RTL languages?
    In addition is there a way to bold part of the text?

  4. Kettler Thomas
    April 17, 2018 at 7:02 pm

    Hey Ryan,

    Great Script!

    I was curious how I could stop duplicates from sending? I.E add an Email sent column?

    If you have any ideas this would be greatly appreciated.

  5. Rasmus Jensen
    January 5, 2018 at 9:51 pm

    Nice Tutorial..

    Is it possible to send an email when a cell is filled..

    eg, I have a google forms, and the answers in a google sheets, I want to send an email to the person who answered the Form just after I received it.. :-)


  6. Sheila
    October 28, 2017 at 11:37 pm

    This is great! How can I send a cell that contains a hyperlink? I have my script working, but I want to send links added as part of the message. Thank you!

    • Fred
      February 12, 2020 at 10:37 am

      Hello Sheila,
      I am as well interest in adding a hyperlink (or image9 on the email.
      Did you figure how to do so?
      @Rayan, would you able to help on this?

  7. Scott
    October 25, 2017 at 11:28 pm

    Hi, I have a report in which certain cells that are populated by a field manager will transfer to different sheets on the same file, allowing for each sheet to be emailed to different department heads. Is there a way to accomplish this?

  8. David Berman
    July 8, 2017 at 11:46 am

    Is there a way to have the script send an email triggered by an event, such as a cell containing a certain value, instead of having to choose from a menu? For example, I would like to automatically notify Jill if there is an error in a certain cell.

    • Ryan Dube
      July 8, 2017 at 1:30 pm

      Hey David,

      Yes, definitely. The way I would handle automation is to go into the Script Editor, Under the Edit menu, go to "Current Project's Triggers".

      Click on the "Add a new trigger" link. Under the "Run" column, select the function you've just created that you want to trigger, select "From spreadsheet", and choose "on change" or "on edit" (I think either would work fine).

      So long as in the start of your script, you have an if statement that references that cell and only sends an email if it contains an error (and does nothing else otherwise), it should work fine and shouldn't be too resource intensive. If you do find that it slows down the sheet at all though, you may want to experiment with changing "From spreadsheet" to "Time-Driven" and just having the script run every hour or so, checking whether that cell has an error.

      Hope that helps, and good luck!

      • Joao Monteiro
        January 7, 2018 at 4:26 pm

        For me, programming is very frustrating.
        I spend a lot of time and often not reaching the goal.
        I've been trying to copy and run a script that sends an email every time a new entry is submitted on a google form.
        I want to identify the last row in the sheet, using the sheet.getLastRow () and systematically I´m getting a row that is not the last row filled but rather the last row of the sheet.
        I also want automatically include a EMAIL SEND comment on the last column to avoid send duplications.

        Could you give me some clues?


      • Stas
        February 18, 2019 at 7:58 pm

        Hello, Ryan!
        I have a database of 1192 people sporadically sending me some info I have to enter into my spreadsheet I've been running for 6+ years, and every time I receive their submission I have to reply to them in order to confirm their submission was received. Your script is VERY close to what I want to do, and I think I need to modify your script to make it meet my requirements as following:
        1. I need to set it to run at a certain time, say, 11:45 pm on the days there were submissions made, if any;
        2. I need it to run selectively and notify only those who made a submission today.
        Please help me to adjust your script to my task.