We breathe cloud computing at Mailparser and are frequent users of all kinds of online applications. One of our most commonly asked questions is 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 an excellent fit for your use case. The same is true for those who wish to extract data locked inside their emails and create an Excel download on their device.
Today, I needed to send out a few personalized emails to a list of recipients 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.
Why send emails from Google Sheets in the first place?
Before we get into the nuts and bolts of Google sheets, let’s clarify why you might want to use Google Sheets to send emails in the first place.
To illustrate an example, let’s use our team of cats. Did you know that Mailparser uses cats behind our email parsing automation tool? But, to keep our cats productive and happy, we need to feed them in mice. At least 5-6 mice per day for a nutritious diet.
So, we pay our cats in mice – the amount depends on how complicated the task is and their overall workplace performance. Feeding cats is a tedious and challenging task because they’re constantly meowing and climbing all over us until we feed them. To ease the pain, we created email automation. We add our cats to the Google Sheet: listing the name, email address (so THAT’S why cats walk on keyboards), and the accumulated number of mice.
By the way, if you use an accounting app such as Xero to monitor payments to your cats, you can automate the data import using Zapier and Mailparser. Read more here.
Now, we can’t spend our days grappling with mice, or we can automate the process by setting up a rule: that each cat must catch at least 3 mice before they can eat them. If they aren’t hungry, they can wait longer until they find 10 or so, but why wouldn’t they want their payment right away?
Cats aren’t exactly savvy with spreadsheets, so we need to email them when the mouse count reaches 3 mice. We need to build a script to send emails from Google Sheets.
Instantly Send Your Emails from Google Sheets
Easily manage your emails with our automated email extraction tool.
Try it free. No credit card required.
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. However, if you dig deeper, 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 that let you do more advanced things like removing duplicates, splitting names, or merging cell values.
Our favorite Google Sheets emailing add-on is
Yet Another Mail Merge (YAMM)
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), add those to the Google Sheet as well then map them in your Gmail draft you compose. 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 significantly enhanced through using Mailparser, and piping data from incoming emails into your Google Sheet.
Our native integration allows you to parse emails to Google Sheets and extract tons of data points on each email so you can personalize the emails you send in YAMM. This is a superb option for an inexpensive and ridiculously personalized email campaign.
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. For example, one of the tasks you can perform is sending out an email, which is precisely 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. Unlocking the code editor is a no-brainer; click 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 the add-ons store mentioned above.
For the action we want to perform, we need to follow these steps:
- Read the email addresses from the first column
- Read the recipient name from the second column
- Assemble the body text, including the first name
- Send out a personalized version of the email to each recipient
To make things even easier for us, Google published an article about precisely 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. So, this is what our data looks like:
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 move the code into the Google Script Editor and adjust it to your needs, you should have something like this in front of you:
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 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 sending a bunch of emails to myself first.
Please note: The first time you click the Play button, Google asks you for permission to send out emails. This is perfectly normal as your script tries to access a protected function (MailApp.sendEmail).
Send Email from Google Sheets
Easily manage your emails with our automated email extraction tool.
Try it free. No credit card required.
If I can just use a script to automate emails, why should I use Mailparser?
With Mailparser, you can save time by fully automating your email parsing process. What Mailparser does is simple:
Forward your emails to our email parser specifically emails with data trapped in their body or attachments
We (mailparser.io) extract all specified data fields based on your custom parsing rule.
Then, we automatically send the data to your favorite applications. We offer many integrations with popular apps.
Our email parser grabs essential data from recurring emails based on the custom parsing rules you set. Once new emails are processed, we transfer your data to where it belongs–the applications that are already a part of your workflow. Send your data to Salesforce or other integrations. You can also customize your integrations based on webhooks or download parsed email data to Excel, CSV, JSON, and XML.
Watch our video on how easy it is to start our services:
What’s more?
We offer 1,500+ integrations with your favorite apps through Zapier.
We offer data parsing for file attachments. Process any text stored inside email attachments, then store it as usable data. (Not every parser provides this functionality!).
You can use webhooks to send important notifications to Slack automatically.
But, don’t just take our word. Read what our customers say:
Testimonials
I have to tell you mailparser.io is great. I found you while trying out another program to help us parse information out of our leads into ZohoCRM and let me tell you it works great. I receive leads from several lead sources and with mailparser.io it saves me at least 2 hours a day. I can log in and look at every email that it parsed and review it and if there was an error I can fix it and have it resend to my CRM. It was easy to set up and there is documentation that covers most issues. Your support staff is great. Thanks for the quick response time to my questions and for helping me out to make sure my transition went smooth. To sum up, it was fast, easy and affordable. I look forward to a continuing relationship with you.
John Ruff – discountforkliftbrokers.com/
We have been using Mailparser for about a year to extract email addresses that we receive from download forms on our website, gillsc.com. The emails are automatically sent over to Mailparser and categorised into the appropriate category through a unique email address supplied by Mailparser. The data is then extracted from Mailparser and then sent over via a webhook integration to Zapier, which then processes and sorts the data and delivers to Mailchimp. Mailchimp then sends out an automated email campaign.
Prior to using Mailparser we had to sort the emails manually and then add them to MailChimp; this would be very time-consuming. With the help of Mailparser, the process is now completely automated and takes no time at all.
Michelle – Gill Sensors & Controls
Our business focuses on helping doctors, hospitals, medical clinics and health providers in their collections and administrative tasks with patients and insurance companies. With mailparser.io we are able to parse emails, extract and take action using different webhooks that will allow our clients to receive their information filtered by what’s relevant or not. This allows our clients to focus on growing their business without the need of a robust administrative staff.
We are very happy using mailparser.io, it’s easy to use and very reliable.
Jerónimo – MEDIFIN
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 could do other valuable things with the Script Feature of Google Sheets?
Easily Send Emails from Google Sheets
Easily manage your emails with our automated email extraction tool.
Try it free. No credit card required.
34 Responses
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.
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
with this
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 🙂
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:
But, I get this: Hello Eric,
Your next two week assignment isundefinedThank you
Hi Eric! Try using row[2] instead of row[3]. Does that work?
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
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.
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.
Hi Aviophobic! According to the documentation it seems to be possible to add attachments: https://developers.google.com/apps-script/reference/mail/mail-app However, I have honestly no clue how to implement that. Maybe one of our readers knows the answer?
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.
Can you share some links?
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.
Properly adjusted – it should work
Awesome! Thanks for the code sample Aviophobic.
Hey Thanks this really works for me
Hi Vishal,
Thanks for the positive feedback, we really appreciate it! Please let us know if you need any help with the service.
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”.
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
Very nice, is it posible to send the messages in a specific date provided in a field?
🙂
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
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!
Hi Stan,
Thanks for reaching out! Could you clarify what you’re trying to do from within our app in an email to support@mailparser.io and I will be happy to assist?
Best regards,
Joshua Harris
Mailparser.io Support
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 .
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
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
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
}
});
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
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?
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,
Hi!
Is there a way I can send the same message for multiple email address?
Thanks!
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
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
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.
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.
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.