Send email from Google Sheets

As you probably know, we breathe cloud computing at Mailparser and are frequent users of all kind 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 need to parse data from your email to excel, then you’ve come to the right place. The same is true for extracting data from email to Google Sheets. Where you can then send email from google sheets.

Today, I needed to send out a couple of personalized emails to a list of recipients which 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 several easy (and free) ways to send out multiple emails directly from within a Google Sheet! Interesting, let’s try it out!

Send Email from Google Sheets

You probably already know Google Sheets. It gives you all the basic spreadsheet operations and feels a lot more easy 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 cells 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 have a columns 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. But rather than hash this out in this post, I will provide you the link to the Add-on itself, where there is all the information you need, and a video to introduce, (and it is a free Add-On). Check out YAMM here.

All said, creating your Google Sheet full of email user information can be greatly enhanced though 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 Mailparser account

Send Email from Google Sheets Script

Today, I want 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. 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 actually be divided in 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

Easy! To make things even more easy for us, Google published actually an article on exactly this use-case on their 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 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 which looks like an insect. This little unimposing play button is the one you need to press to get the script executed.

But before you do this, please consider testing all this on a small data set. It is in my opinion very important to proof read an email at least once inside an actual email client. What I did was that I replaced the values of the first column with my emails 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?

Excellent! 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 for 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?

Tags: ,