Sending emails from a Spreadsheet – Apps Script Tutorial

Hey, everyone. Welcome to Abstract
Video Tutorials. Today, we’re going to be
looking at how you can automatically send emails
from a spreadsheet. All right. Now, the tutorial actually
resides in our dev site at developers.googl
e.com/appsscript. If you go to this intermediate
tutorial section here and go to Send Emails With a Spreadsheet, here’s a tutorial. Now, as it says here,
it takes 10 minutes. But it’s actually much
quicker than that. So we’ll run through that
really quickly. I’m going to get rid of
these extra tabs. Now, this is a container-bound
script. So it lives within
a spreadsheet. So first, we need to go to
Drive and create a new spreadsheet. We’ll give it an appropriate
name. All right. In order to paste this code
snippet in, we need to access the script editor, which
you get to from Tools, Script Editor. Let’s grab this code here. And once the script editor
opens, this will paste the code here from the tutorial and
give this a name as well– if I can spell correctly,
but it doesn’t matter. Save it. Now, the tutorial says that the
set up for the script is you need some fake data in your
spreadsheet so that you can test it out. So let’s say, for this example,
we’re planning a party and we get together in a
room and in a spreadsheet, we decide who does what. In this email, we have an
Email Address column. And then we also have
a Message column. Now, because this is a test,
we’re going to be sending all the emails to a test account. But as you can imagine,
this could go to various different people. So let’s just send
two emails here. And the message will be “Pick
up the balloons.” And then this one will be “Invite
guests.” So if this works properly, this person will
get an email saying, as a reminder, to pick up the
balloons, and this person will get an invite guests reminder. We’ll go here into our
script editor and run the send emails function. Now, you need to authorize it to
use Gmail to send messages on your behalf. So I’ll go ahead and
do that here. I’ll go ahead and
run it again. Now, it ran. When we go to our Gmail, we’ll
see the two messages from the current time. And the first message is “Invite
guests.” And the next one is “Pick up the balloons,”
as we expected. And now, an extension to the
script that’s noted in the tutorial– let’s go ahead
and delete the old data. Actually, we didn’t
need to do that. Grab this other code right here
and just copy paste over the existing code. Now, this will give you a
Send Emails To method. When you run it, you’ll see that
in the spreadsheet, it marks a third column
with EMAIL_SENT. This is so that if you wanted
to resume running the script at a later date, it’s not
going to send these same emails to these same
people again. It’ll mark it as done. And if you go back to your
inbox, you’ll see two more emails arrive. As just a proof, if you click
it again, because it already has that EMAIL_SENT column,
you’re not going to see any more emails come through. That, in a nutshell, is it. Now, let me just briefly walk
you through the code here. All it does is it gets the
active spreadsheet. It says that the start
row is 2 because you have a header row. And it gets the data from that
sheet as a 2D array. And within this, it goes through
that array and assigns each of the columns to a
variable here– email address variable and the message
variable. And this is the advanced
example. So it also has the EMAIL_SENT
variable to mark that third column. And basically, all it does is
MailApp.sendEmail, specifying the address, the subject of
the message, and then the actual message itself, and
then sends that through. And here, what you see is it’s
marking the third column with the value in emailSent which,
if you scroll up, is EMAIL_SENT. And that’s it– as
simple as that. I hope you enjoyed it. Tune back for some of
our other tutorials. My name, again, is
Kalyan Reddy. Goodbye.

Tags: , , , , , , , ,


  • yassine belbadaoui says:

    that's so Wonderful 😀 , thanks for this helpfull infoo 

  • Lemuel Cariñote says:

    how about recurring send?

  • modelsofidentity says:

    How do I custom change the subject line of each email?

  • Jacquelyn Whiting says:

    I keep getting a message: Failed to send email, no recipient (line 14, File "Code")…

  • yashaleo N says:

    What values need to change, if I don't want this feature in column A, B, & C. What if I wanted this in column J, K, & L?

  • Brandon Kim says:

    If i able it, it says: Cannot call method "getRange" of null

  • Amy Lane says:

    I did this & tested it several times & it worked GREAT! Now, when I ran it on the real spreadsheet, it only sent emails to 2 of my contacts & then stopped. What could have happened?

  • Alister Gray says:

    Hi there and thanks for this. How could this script be modified to send an email automatically if the sheet was modified with new data from a linked Google form? I like that existing recipients will not be emailed (email_Sent)

  • Andy says:

    I have 1740 rows and it only sent 92 emails. A daily limit error showed up. How do you increase the daily limit? Thanks!

  • NSLAS Shore says:

    thanks for sharing..

  • WhiteHatBox Service says:

    you can use software for this email sender buck https://plus.google.com/113616256529424387892/posts

  • Geek Tom says:

    thanks for sharing, i hop to add the person name plz how can i do that

  • rahithya k says:

    Can we send email alert at particular time using jscript

  • Guillermo Costamagna says:

    Muchas gracias por el tutorial.

  • Jeanette Jones says:

    It took me hours to find something like this to send grades to students. Thanks for the quick tutorial.

  • Care For You says:

    Can you help me with similar email notification.
    I have pivot table which keeps updating daily basis.
    I would like to send this pivot table to 7-8 users every day at 10 PM.
    How can achieve it with this code.?

  • Nguyen Toan says:

    Why can not I send out 10 emails?


    Code EmailSent:

    // This constant is written in column C for rows for which an email
    // has been sent successfully.
    var EMAIL_SENT = "Đã Gởi";

    function sendEmails() {
    var sheet = SpreadsheetApp.getActiveSheet();
    var startRow = 2; // First row of data to process
    var numRows = 2; // Number of rows to process
    // Fetch the range of cells A2:B3
    var dataRange = sheet.getRange(startRow, 1, numRows, 3)
    // Fetch values for each row in the Range.
    var data = dataRange.getValues();
    for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var emailAddress = row[0]; // First column
    var message = row[1]; // Second column
    var emailSent = row[2]; // Third column
    if (emailSent != "Đã Gởi") { // Prevents sending duplicates
    var subject = "Thông Báo xxx";
    MailApp.sendEmail(emailAddress, subject, message);
    sheet.getRange(startRow + i, 3).setValue("Đã Gởi");
    // Make sure the cell is updated right away in case the script is interrupted

  • Sandor Szabo says:

    He says "automatically send emails from a spreadsheet", but, in fact "MANUALLY send".
    Otherwise, this tutorial is good.

  • Marvin K. says:

    How to get rid of 'Failed to send email: no recipient" even though it sends the email?

  • Will Junior says:

    Why keep sending emails again for those that have EMAIL_SENT filled?

  • Syndrella Montañez says:

    Thank You. It works.

  • Enas EL-lithy says:

    Hi I Try using Google sheet
    first i making google sheet
    Sheet Contain
    3 column
    and There Have
    3 rows
    i put this code put there are error
    function sendEmails() {
    var sheet = SpreadsheetApp.getActiveSheet();
    var startRow = 3; // First row of data to process
    var numRows =3; // Number of rows to process
    // Fetch the range of cells A2:B3
    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
    var message = row[1]; // Second column
    var subject = "Sending emails from a Spreadsheet";
    MailApp.sendEmail(emailAddress, subject, message);

  • gokhan cacan says:

    Can i add my file(Word document) from computer add this mail message and send to people?,thanks

  • jeff cirson says:

    Please advise how the send email app can be triggered by a change in sell value eg Sell A1 goes from blank to "Alarm"
    Also it would be handy to have a button in the spreadsheet to reset the macro eg remove "email sent" message"

    Please advise?

  • Ali Mansour says:

    what if i wanna attach a file ?

  • Friday Quran Arabic class says:

    i edited the code to the columns & rows numbers in my sheet but this message, appear every time Invalid email: undefined (line 14, file "Code" ( i didnt change anything in line 14 )

  • FiEisALz says:

    Hi, how do you make text bold, italics, and underlined, or change color, for example to blue? Thank you.

  • Team Inteligencia financiera says:

    how many emails can i send?

  • Hessam Adin says:

    What if I want to CC others in the email? How would I need alter the code to do that?

  • Dushilino Dushilino says:

    Hi Google Dev, I would like to send emails to diff people with this but also want to add an attachment how does that work

  • HvacAdmin says:

    is there a way for the mailApp.sendEmail to email the body in "color" ?

  • Jaffa Soft says:

    My script editor page will not open the editor. Also how can just use to send one email if a cell reaches a certain number percentage?

  • MRS PRIME ✔ says:

    Thanks a lot..

  • Cirilo Jr Ogang says:

    It's not working if I skipped a row between email address. If i'm not ready to send msg at row5, ofcource I will leave it blank then I want to send my msg at ROW6, what will I do? In this script, you will also need to fill–up the email add at ROW5. Thank you.

  • Syed Hussaini says:

    I am collecting the data from forms under the sheet. Each time the form is submitted, the data is emailed to a specified email address, however, it records my email as Sender. Can we do something to place the submitter's address in the from field? maybe pick a value from one of the fields of the form and set it as a value for from field while mailing the form data.

    Can you please advise.

    Best Regards,
    Syed H | https://dailypuzzleanswers.com

  • Kiran Nandgawle says:

    can i use this for a live intraday chart where cells is blank and when buy or sell signal come it should mail to my email id

  • Salvatore coco says:

    how to add pdf, if i want attach document?

  • Tony Rickard says:


    is there a script that will email and or print the active sheet, i have tried a few different and none work. I want to be able to click a button and either print to pdf or email, on that sheet there is an email and message
    Any help would be greatly appreciated…..thanks for the tutorial, very well presented….

  • Okto Sihombing says:

    i already test this methode but send with spreadsheet cant use delay sending if we send bulk email..
    can u solve this issue?

  • 朱俊逸 says:

    it can be automaticly, but I still getting 2 times notifications of each new row

  • David Hestrin says:

    THanks Kalyan!

  • Till Sommer says:

    OAuth Client Verification Process Feedback P R O B L E M ??? pls help

  • Brenden says:

    How can I send a range of data/cells with in the email instead of just the 1 cell?

  • Christian Sutter says:

    This is amazing! I have a few questions that I'm curious to know about these features. I have a multiple of lists from blogs. The emails associated are sometimes not actual emails and return mail to sender notifying. My goal is to sort over 20,000 subscribers. I would like to know if source code is available for entry into the schematic here that would report whether or not the mail was returned. I know the limitation on email appears to be 500 a day. I was thinking subdividing into 300 to prevent any flagging that might occur. Is there a way to partition the inbox in google to thread all emails sent via excel? And, how would one create the scripts that might offer cron job per 24 hour style automation to assist in the effort. is there a google application that offers a "scrubbed email" list which has already been processed? Is there a simple method for such reference that can result in ability to refine lists when bulk emailing references to merchandise on the internet? Great tutorial thanks!

  • Alex Villalongue says:

    any way i can send from a different email service, zoho mail instead of gmail?

  • José María Díaz Gámez says:

    How to introduce one bold word?

  • 2000luckyscharms says:

    link no longer exists at https://developers.google.com/apps-script/quickstart/forms

  • Nikolay Kalinin says:

    copied.. doesn't work!

  • i Gag says:

    I am geting: Ogiltig e-postadress: NO-001 (rad: 23, fil: Kod) ???

  • Ahmed A.M says:

    hi this code doesn't work with official email which is liked to a domain all emails are bounced back

  • Robert VIVANCO says:

    HI everyone, thanks a lot for this vidéo Google ! Is it possible to put people in CC or CCI through this method ? Thanks again 🙂

  • AjayKumar Yadav says:

    Can you guys provide code to send customized emails using existing template?

Leave a Reply

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