Learn How To Automate Your Business

Actionable tips on how to save time and money with automation.

Send email from Google Sheets

As you may know, we breathe cloud computing at Mailparser and are frequent users of all kinds of online apps. We are going to walk you through one of our most often asked user questions: how to send email from Google Sheets. If you still need to extract the data from your emails and send it to Google Sheets, our app is a great fit for your use-case. The same is true for those who wish to extract data locked inside of their emails and create an Excel download on their device.
Today, I needed to send out a couple of personalized emails to a list of recipients that were stored in a CSV file. The usual way of doing this would probably be to pay for a newsletter software like Mailchimp and import the spreadsheet containing the recipients there. Not this time, though! I stumbled upon several easy (and free) ways to send out multiple emails directly from within a Google Sheet! This article is a brief walkthrough of my findings.

How To Send Email from Google Sheets

You probably already know Google Sheets. It gives you all the basic spreadsheet operations and feels a lot easier to use than the traditional Microsoft Excel. If you dig deeper, however, you’ll soon realize that Google Sheets is much more than a nice looking tool for making lists and adding numbers. You can install one of the many add-ons which let you do more advanced things like removing duplicates, split names, or merge cell values.
Google Sheets Add-On Install

Our favorite Google Sheets emailing add-on is
Yet Another Mail Merge

Using YAMM is really easy – All you need to do is compose a draft email in Gmail, and create a couple of header columns in your Sheet with the corresponding data you wish to send. Typically your Google Sheet consists of an email address, a name, and should you want other fields drawn into the email dynamically (City, State, Company Name, etc.), you simply add those to the Google Sheet as well then map them in your Gmail draft you are composing. Rather than fleshing this out here, I will provide you the link to the (free) add-on itself, where there is all the information you need, and a video to introduce it. Check out YAMM here!
All said, creating your Google Sheet full of email user information can be greatly enhanced through using Mailparser, and piping data from incoming emails into your Google Sheet.

Our native integration will allow you to parse emails to Google Sheets and extract tons of data points on each email that will allow you to really personalize the emails you send in YAMM. This is a really superb option to have an inexpensive and ridiculously personalized email campaign.
Create a free Mailparser account

Send Email from Google Sheets Script

Today, I wanted to try out the scripting feature of Google Sheets. The scripting tool lets you perform tasks and automate your work in Google Sheets. One of the tasks you can perform is sending out an email, which is exactly what we need!
To get started, we need to know how to open the script editor of Google Sheets and think about what our function needs to accomplish. Opening the code editor is a no-brainer, just go to “Tools” in the top navigation and click on “Script Editor …”. As you can see, next to “Script Editor …” is also a “Script Gallery” which is actually the add-ons store mentioned above.
Google Sheets Open Script Editor
The action we want to perform can be divided into the following steps:

  1. Read the emails addresses from the first column
  2. Read the recipient name from the second column
  3. Assemble the body text including the first name
  4. Send out a personalized version of the email to each recipient

To make things even easier for us, Google published an article about exactly this use-case on its developer blog. We took their sample as the basis of our script.

Script to send Emails From Google Spreadsheet

As written above, we want to read the content of the first column and use it as the recipient address. Then we want to take the value of the second column and use it as the first name in the text body of the emails. This is how our data looks like:
Google Sheets Email Data
So how does the function look like? Have a look at the code snippet below. If you read carefully the comments inside the source code, things should become understandable even if you don’t have much coding experience.

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2; // Start at second row because the first row contains the data labels
  var numRows = 3; // Put in here the number of rows you want to process
  // Fetch the range of cells A2:B4
  // Column A = Email Address, Column B = First Name
  var dataRange = sheet.getRange(startRow, 1, numRows, 2)
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (i in data) {
   var row = data[i];
   var emailAddress = row[0]; // First column of selected data
   var message = "Hey " + row[1] + ",\n how are you doing today?"; // Assemble the body text
   var subject = "Sending emails from a Spreadsheet";
   MailApp.sendEmail(emailAddress, subject, message);
  }
}

Testing and executing your Script

Once you have moved the code into the Google Script Editor and adjusted it to your needs, you should have something like this in front of you:
Google Sheets Code Editor
As you can see, there is a little “Play” button in the top bar next to an icon that looks like an insect. This little unimposing play button is the one you need to press to get the script executed.
Before you do this, however, please consider testing all of this on a small data set. It is very important (in my opinion) to proofread an email at least once inside an actual email client. What worked for me was replacing the values of the first column with my email addresses and sent a bunch of emails to myself first.
Please note: The first time you click the “Play” button, Google will ask you for permission to send out emails. This is perfectly normal as your script is trying to access a protected function (MailApp.sendEmail).

So how did that work out for me?

Splendidly! The code block above is what I used today for sending out a couple of emails to a list of recipients in Google Sheets. As mentioned above, the code is based on a tutorial written by Google for this use-case. It took me only a couple of minutes to adjust the code shown above to my needs. On top of that, it saved me some bucks as I didn’t need to subscribe to a paid newsletter software for this simple one-time emailing job.
Please let me know if this worked out well for you in the comment section! Also, maybe you were able to do other useful things with the Script Feature of Google Sheets?

34 Responses

  1. You could improve this article changing a little the script creating a column for the salutation, one for the name and a third one for the message, e.g. var message = row[1] + row[2] + row[3]
    In this way you could assemble the message more dynamically and create a custom text for each email address in your list.
    As it is now this tutorial is quite disappointing, the message has to be written inside the script making the feature almost useless, anyway thanks for the idea of use name as a variable.

    1. Hey Tobia!
      Good catch! As you already pointed out your suggestion would be pretty easy to implement. It’s totally possible to have the entire message stored inside the Spreadsheet in Column C. In this case, one simply needs to replace

      var message = “Hey ” + row[1] + “,\n how are you doing today?”

      with this

      var message = row[2];

  2. Actually I just found out an easier way to use variable names.
    It is enough to use the JOIN function into the message cell in order to assemble the message, this will make possible to use the original Google script with no changes.
    Hope this can help someone else 🙂

  3. Hello. Thank you for this! I want to add a section telling a student where they’ve been assigned. I would like this to be done in the body of the message. Here is what I have:

    function sendEmails() {
    var sheet = SpreadsheetApp.getActiveSheet();
    var startRow = 2; // Start at second row because the first row contains the data labels
    var numRows = 3; // Put in here the number of rows you want to process
    // Fetch the range of cells A2:B4
    // Column A = Email Address, Column B = First Name
    var dataRange = sheet.getRange(startRow, 1, numRows, 2)
    // Fetch values for each row in the Range.
    var data = dataRange.getValues();
    for (i in data) {
    var row = data[i];
    var emailAddress = row[0]; // First column of selected data
    var message = “Hello ” + row[1] + “,\n Your next two week assignment is” + row[3] + “Thank you”; // Assemble the body text
    var subject = “Your next two week assignment”;
    MailApp.sendEmail(emailAddress, subject, message);
    }
    }

    But, I get this: Hello Eric,
    Your next two week assignment isundefinedThank you

  4. Really good article. I was just wondering if there is a way to send an email automatically when a new email address is added to the sheet, when it is not open

    1. Hi David! Good point. To my knowledge, the script can only be run if the document is open. If you are looking for a fully automated solution I would recommend to play around with Zapier. I think they have a trigger ‘New Row in Spreadsheet’ and they offer an action ‘Send Email’. Hope that works for you.

  5. Thank you very much for this! It helped me a lot. Is there a method to attach files from google drive to the e-mail? I need to attach *pdf and two pictures to each e-mail.

  6. Thank you for the suggestion. I found some clues in other place and I was able to attach one file to the e-mail. Now I am trying to add several files.

  7. Its here: http://webapps.stackexchange.com/questions/60445/send-an-email-with-attachment-using-google-apps-script
    I am posting the code I prepared using your code and the one provided above. I use 5 columns and two rows table, because I need more variables in my e-mail. The file (“file.pdf”) is on gogle docs. It can be placed in a folder. I was not able to attach more than one file though.

    function sendEmails() {
    var sheet = SpreadsheetApp.getActiveSheet();
    var startRow = 2; // Start at second row because the first row contains the data labels
    var numRows = 2; // Put in here the number of rows you want to process
    // Fetch the range of cells A3:E3
    // Column A = Name, Column B = Email, Column C = Message, Column D = Message1, Column E = Message2
    var dataRange = sheet.getRange(startRow, 1, numRows, 5)
    // Fetch values for each row in the Range.
    var data = dataRange.getValues();
    for (i in data) {
    var row = data[i];
    var emailAddress = row[1]; // First column of selected data
    var message = “Dear” + row[0] + “,\n” + row [2] + row [3] + row [4]; // Assemble the body text
    var subject = “Subject”;
    var file = DriveApp.getFilesByName(‘file.pdf’);
    if (file.hasNext())
    MailApp.sendEmail(emailAddress, subject, message, {
    attachments: [file.next().getAs(MimeType.PDF)],
    name: ‘Sample mail’});
    }
    }

    Properly adjusted – it should work

    1. Hi Vishal,
      Thanks for the positive feedback, we really appreciate it! Please let us know if you need any help with the service.

  8. Hey,
    I have similar, but different need. I have a spreadsheet with tasks and their status and people assigned to those tasks. I need to send an email to the tasks owners when status changes. I’ve managed to write a script which sends an email to one hard-coded user, but I cannot handle conditions to check different column and send an email only when for example cell in column B is “done” and cell in column D is ” Jon Smith”.

    1. Hi Jon,
      Thanks for reaching out! Unfortunately this is not something our app can do as we do not offer any if/else or robust logic-based functionality, sorry!
      If you have any questions please let me know at support@mailparser.io
      Best regards,
      Joshua Harris
      Mailparser.io Support

    1. Hi Marcos,
      Thanks for reaching out!
      Our app could return data after a certain date if you used a specific date as an anchor keyword – could you send in an email to support@mailparser.io with more information about your use-case and I will try to assist?
      Best regards,
      Joshua Harris
      Mailparser.io Support

  9. Hi, is there a way to update the update the data range automatically when a new email is added underneath?
    function sendEmails() {
    var sheet = SpreadsheetApp.getActiveSheet();
    var startRow = 2; // Start at second row because the first row contains the data labels
    var numRows = 3 (this one to be +1 every time a new email is added?); // Put in here the number of rows you want to process
    Thanks!

  10. Hello ,
    I am looking into sending customized emails to a group of people with maybe 5 pieces of information found on different rows .
    Example
    Row 1= Email I want message sent to
    Row 2 = Name
    Row 3: Event booked
    Row 4 : Date
    Row 5 : Time
    So I want the final product to be something like :
    Hello (Name) ,
    This is a reminder you booked ( Event booked ) , on (Date) at (Time) .
    How can I do that ? What should I change in the script .

    1. Hi Monica,
      Thanks for reaching out! Unfortunately our app is not really built for your use-case as we intake emails with data that needs extracted, parsed that data on our app, then send that data elsewhere – we do not offer direct SMTP email sending per your query, sorry 🙁
      If you have any questions please feel free to send them to support@mailparser.io.
      Best regards,
      Joshua Harris
      Mailparser.io Support

  11. Hello Sir,
    I have a column called “Assigned To” which is consist of drop down list with 4 names like, (A,B,C,D). If I want to send an automatic mail with some text when I will select a name (Let’s say “B”) from the drop down list and the mail should go to this person (“B”) particularly which have been selected from the list. Is it possible? One more thing that if I will select a name from any where of this column randomly instead of serially, then this mail will send or not?
    Please have a look:-
    https://drive.google.com/file/d/0Bzr8nF5BSuWTeFlqbW9oVEpiMlU/view
    Please help me. This is very urgent for me.
    Looking forward to your inputs.
    Cheers,
    Arup

  12. HI. I ‘d like to send 2 pdf files in the same email, so is it possible ?
    the pdf file from the spreadsheet : pdf sheet 1 and pdf sheet 3 for instance.
    I try these in the script :
    …..
    (part of error here..)
    var response2 = UrlFetchApp.fetch(url + url_ext + GrilPlan.getSheetId(), {
    headers: {
    ‘Authorization’: ‘Bearer ‘ + token
    }
    });
    var response1 = UrlFetchApp.fetch(url + url_ext + GPJan.getSheetId(), {
    headers: {
    ‘Authorization’: ‘Bearer ‘ + token
    }
    });
    blobs[2] = response1.getBlob().setName( titrepdf2 + moischoix + ‘ général.pdf’);
    blobs[1] = response2.getBlob().setName( GPmois + ‘ P-‘ + clepabr + ’18 ‘ + ‘.pdf’);
    var pdfListe1=blobs[2]; var pdfListe2=blobs[1]
    }
    MailApp.sendEmail(emailAddress, subject, body, {attachments:[pdfListe1,pdfListe2]});
    But after the third email: I have “a error excepton….””.
    so how can we to put together pdf files with a unique UrlFetchApp.fetch(url + url_ext … ?
    –> not good t put 2
    var response = UrlFetchApp.fetch(url + url_ext + GrilPlan.getSheetId(), {
    headers: {
    ‘Authorization’: ‘Bearer ‘ + token
    }
    });

    1. Hello,
      Thanks for reaching out! The script in the article is designed to send one file per email only, sorry – if you have any questions please let us know at support@mailparser.io
      Best regards,
      Joshua Harris
      Mailparser Support

  13. Hi,
    Is it possible to send HTML format email? Like formatting the email message like,
    Bold characters,
    Font formatting,
    Links
    Alignment
    Color formatting
    Signature
    Image
    etc
    Can this be done with the Google Apps Script?

    1. Hello,
      Thanks for your interest in Mailparser and apologies for the late reply here!
      Our app was built to extract data locked inside of emails and is not built to forward emails via SMTP – if you have any questions please let me know at support@mailparser.io 🙂
      Best regards,

    1. Hi Eyel,
      Thanks for reaching out! I’m having a bit of trouble understanding your query.
      Could you send a ticket in to support@mailparser.io along with what you’re trying to accomplish and I will confirm if our app can suit your use-case?
      Best regards,
      Joshua Harris
      Mailparser.io

  14. Hi.
    What a great idea.
    One interesting limitation I found was that lines of text over about 70 characters is split into two lines in the email.
    eg.
    const CalMessage = ‘We would like to provide the same friendly and personal service you are used to.’;
    Becomes in the email:
    We would like to provide the same friendly and personal service you are
    used to.
    I have tried a few ideas, but cannot get a long line.
    Can you make a suggestion when convenient?
    Thank you for your consideration.
    Cheers,
    Phil

    1. Hi Phil,
      Thanks for reaching out! I can confirm we have controls to merge broken lines with our table filters, I would recommend reaching out to support@mailparser.io with your account email and inbox you’re working on and we’ll be happy to have a look and assist.

  15. Hello everyone,
    I am trying to add a link in the middle of the message in a third column, but the result appear as a text, the text is just: Follow the “link” to claim the item you like.

    1. Hi Teak,
      Thanks for reaching out!
      Our app was built for extracting data inside of emails you send us, we don’t have a way to append new data to emails, sorry.
      If you have any other questions please feel free to reach out to us at support@mailparser.io.

Leave a Reply

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

Hi there, I’m Joshua.
I oversee product development at Mailparser. This includes working with customers and overseeing new feature implementation.

Try Mailparser for free!
Save hours of time!

No credit card required.