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.