Sending emails from a Spreadsheet – Apps Script Tutorial

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.

52 comments

  1. 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?

  2. 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?

  3. 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)

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

  5. Hi
    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.?

  6. Why can not I send out 10 emails?

    Thanks.

    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
    SpreadsheetApp.flush();
    }
    }
    }

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

  8. Hi I Try using Google sheet
    first i making google sheet
    Sheet Contain
    3 column
    Email
    Message
    Cost
    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);
    }
    }

  9. 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?

  10. 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 )

  11. 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

  12. 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?

  13. 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.

  14. 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

  15. 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

  16. Kalyan

    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….

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

  18. 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!

  19. 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 🙂

Leave a Reply

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