Introduction to Google Apps Script Triggers

IKAI LAIN: Hi everybody,
my name is Ikai Lain. KALYAN REDDY: And my name
is Kalyan Reddy. IKAI LAIN: And today, we’re
going to be talking about Apps Script Triggers. So what are we going
to be doing today? We’re going to be doing a
full-on, I want to call it a show about Apps Script
Triggers. Where we’re going to start
talking about first, what they are, where you’d want to use
them, ways to run them, and we’re going to move into
a little bit of code. I’m not going to type too much
code out, but I’ve been preparing some samples
for you guys. And towards the end, we’re going
to talk about patterns and things you can and can’t
do using triggers. So that’s what we’ve got
today for you guys. Oh, I have this thing in
presentation mode. So let’s go ahead
and get started. KALYAN REDDY: Sounds good. IKAI LAIN: And Kalyan’s going
to be the guy who’s going to be asking questions,
who’s making sure I don’t go too fast. He’s going to pace me. He’s going to help me out. So first off, what
are triggers? Triggers are a way for us to run
code either in response to an event or non-interactively. When I say interactive, what I
mean is you’ve got the script editor, where you
can hit play. You can also run a script as a
result of something going on in a script. You can have it attach
a menu item. Or you can invoke some code
via a web application. So that’s what I mean when
I see interactive, as in, there’s a user that goes in
and clicks some action or takes some action that causes
some code to run. And this is one of the most
common uses of Apps Script, because you want these to be
doing something before the Apps Script fires. Now, you want to use triggers
because there are lots of cases where you want to run
code and not have the user necessarily do something. So a really good example would
be a clock-based trigger. So let’s say that you want
it to run some code. You want it to run a scheduled
job every day at midnight or 1:00 AM or at every
day at noon. Let’s say every day at noon, I
wanted to email everyone and say, hey, go to lunch. That’s something you can
do with a trigger. Or if you want to set something
up to run at a certain date or time, that’s
something you can do via what we call clock triggers. The second type of triggers
are something that we call spreadsheet triggers. So we have these, they’re called
onInstall, onOpen, onEdit, and on form submit. And I will talk about
these later. But what these allow you to do
is to wire up code so that when something happens
inside a spreadsheet the code gets executed. So this is a very powerful
technique and you can build some really interesting
applications using some off-the-shelf components, like
spreadsheets for UI or forms for collecting user data. And you can widely scale using
triggers so that you can build something that’s a full-fledged
application. So a really good example of
something that we’ve done like this would be something like
a help desk application. Or for those of you who have
been to our Apps Script events before, you’ve seen our
signup sheets, that’s just a Google form. But you’re able to get
a confirmation email. We also do some logic in the
back end to send out reminder emails as well as tell you if
you’re on the waitlist or not. That’s all done via
Apps Script, spreadsheets, and forms. KALYAN REDDY: So if you’re
familiar with the Unix world, then the cloud based triggers
are like a cron job, where you can have a script executed
at a certain time. IKAI LAIN: That’s right. And do you have a Unix analogy
for spreadsheets? KALYAN REDDY: Not so much. IKAI LAIN: Just like bringing
you into my trap here. So why don’t we go ahead and
show you guys how to set up some of these triggers. Let’s go ahead and go to Script
Editor right now. So I’ve written some
code here. This is Kalyan. His email is [email protected] Everybody knows this. He’s very popular around here. And what I want to do is I want
to really bother him. I want to tell this guy that,
hey, you’re such a cool guy. I want to tell this
guy every day. KALYAN REDDY: Everybody
wants to tell me that. IKAI LAIN: I want to tell
him every five minutes. So I’ve written this function,
and as you can see, I’m just using standard Mail App. So in Mail App you have a
recipient, a subject, and a body of the email. So I want to email you every
five minutes, because I want you to have high self esteem. So let’s go ahead and figure
out how we can do that. Well, when you’re in the Script
Editor– and this is– you click on Resources, Current
Script Triggers. And you’ll see that right now,
I have no triggers set up. And that’s because I don’t
have any code yet. I haven’t set this up yet. I do have code, excuse me. I’ll click on No
Trigger Setup. And which one of these
do I want to select? I want to select email gkal. Now one thing you might notice
is that I’ve got a few other functions in here. That’s because I wanted to go
a little bit off topic. We’re going to talk about public
and private functions. This is something that’s
really useful to know. You’ll see that I have a
function here called Public and Private function. Private functions are functions
that end with an underscore. When you have a function that
ends with an underscore, you’re telling Apps Script
that this is just for me. I don’t ever want to be
able to run this code. So when you click the drop down
for running, you’ll see that private function is not
there and public function is. And the difference is an
underscore between the function name and
the parentheses. So that’s one of the
requirements of writing code that you want to execute
a trigger, is that it must be public. You can’t have an underscore
at the end of the function name. So again, let’s go back to
current project triggers and set one up. Now, I’m going to pick
the function I want. And again, that’s email gkal. And I’ve got a couple
of options. But right now, since this
script is not bound to a spreadsheet, that is, I didn’t
go to the spreadsheets editor and click Tools,
Script Editor. I just went to
and started furiously hacking away. The only option I have
is time-driven. And the next option I want
to pick is what kind of time-driven trigger is this? First off, I can pick a specific
date and time. So you’ll see that I want
to enter a year, month, date, and a time. So what’s a good day
to remind you that you’re such a cool guy? KALYAN REDDY: Right now. IKAI LAIN: No, I don’t think
you deserve it yet. How about on the 25th
of December? I mean, you’re getting
presents. You’re having a day off,
drinking some eggnog. So let’s remind you
at 3:00 AM. And you’ll see that the field
turns white, because now this is a valid time. And I can save this thing. And that’s when it
will email him. It’s going to ask me
for authorization. So I’m going to go ahead
and authorize. You guys are all familiar
with this screen. Close. And now, I’m going to
save the trigger. Remember, you must pass
authorization and then save it again. It’s like the same way when
you’re running a script. You have to authorize
and rerun the thing. So now, if I go to current
script triggers, I’ll see that, oh, there’s an email
set up to go out to you. And it’s going to execute this
code on December 25. KALYAN REDDY: So it’s just going
to execute it the one time on that date,
and that’s it. IKAI LAIN: Right. That’s right. And you sounded disappointed
there. So why don’t we increase
the frequency? When you create triggers, you
can create many, many triggers to fire on a single function. So I’m going to add a new
trigger, email gkal. And I’m going to make
it time-driven. And I want this thing
to run every hour. I don’t think you
deserve that. Let’s set it to every
two hours. So I’m going to set it
to every two hours. I’m going to hit Save. And that’s going to start
emailing you every two hours. It’s going to execute
this code. So in this type of trigger, you
can put almost any kind of code in here that Apps
Script supports. I’m using a very simple
example to email you. But I could also edit
a spreadsheet. So I can keep a log of all the
times I’ve emailed you. I can also keep a Google
Drive document. I can do a lot of different
things. I can insert a row into
a database via Cloud SQL and JBC. Anything you can do inside an
Apps Script, you can basically do inside a trigger. I mean, obviously, you
can’t render a UI. But that would be silly,
wouldn’t it? KALYAN REDDY: Because there’s
no particular use– IKAI LAIN: Who’s going
to look at? Who’s going to look
at it, right? If a tree falls in an empty
forest, I don’t know the rest of how that goes. Not very philosophical. So that’s how you manually
set up a trigger. But sometimes you want to be
able to set up a trigger and you want to be able to do it so
that you can program, and you can program a trigger in. For instance, let me show you
guys how this should be done. So let me go ahead and– excuse me. Let me go ahead and
do this in code. So I’m going to create
a new function. I’m going to call it
scheduleTrigger. Now what I’m going to do is
I’m going to use a trigger builder to build something
that will also schedule something that’ll email
you every minute. How does that sound? KALYAN REDDY: That sounds
much better. IKAI LAIN: Let’s go ahead
and do this then. So let’s say var builder equals
ScriptApp.newtrigger. And the first parameter of new
trigger is the function that you want this trigger to call. So the function I want this
to call is email gkal. This must match the case. Everything in Apps Script is
case sensitive when you’re writing code. So remember that if something
is uppercase, you have to match [INAUDIBLE]. If something’s lowercase,
you have to match it. So we’re going to chain this. And we’re going to say,
let’s make this a time-based trigger. So I’m going to make
it time-based. Let’s set this. Again, I’m hitting period
every time. And Apps Script Editor is smart
enough to know, hey, this is a clock trigger
builder time. Try saying that 10 times fast. And it’ll pop up the functions
I need to build this trigger. So when I say, dot, I want
this to be every. And you can see that as I type,
it filters down the function that match
every minute. So let’s make this
every one minute. And let’s go ahead. And that’s it. So that’s the trigger so far. And when you have a builder,
you go ahead and do builder.creates. And this returns a
trigger instance. But you don’t have
to necessarily do anything with a trigger. You don’t have to return it. You can just say
builder.create. In fact, a lot of people, what
they do is at the end of this call, they’ll simply say
dot create and it will return a builder. So a very common thing I’ve seen
people do, is to do dot create, and that’s
the end of it. But I want to show you guys that
you can use a builder, because builders are very,
very powerful. Builders are a pattern
that come from Java. They come from Java land and
they are best described in this one book called,
“Effective Java,” by Joshua Bloch. The nice thing about builders
is that they allow you to programmatically slowly
construct a complex object. And this was intentionally
built. This pattern exists to solve the
problem of Java not having named parameters. So in some programming
languages, like Python, you can call a function. And then you call a
name parameter. You could say name is equal to
ABC, value is equal to 123. But you can’t do that in Java. They created this builder
pattern where what each of these functions returns
is the builder object. So the trigger hasn’t
been created yet. You return an instance of a
builder and when you’re done with the builder you call
builder.create to essentially, finalize the object. So why is this nice? Well, let’s say that we want to
make it time-based, but we want to make it every few
minutes but we didn’t know how many minutes we want the
interval to be, well, we could have a conditional that says,
if some conditional builder dot every one minute. And if there’s a different
conditional I could say, builder dot every
five minutes. KALYAN REDDY: So you could hook
this up as a response to external conditions, as well. You can pull data from an
external source and change you trigger timers based on that. IKAI LAIN: Right, absolutely. So you can do that
with triggers. Is very, very cool. And that’s typically, what’s
used to build a pattern for because a lot of times you’ll
have a lot of different conditionals, and instead of
writing this out every time, you’ll simply modify the builder
in the way that you need it to change based on the
current condition you’re evaluating. I know it seems confusing. So just take a look at what I’ve
done with the code here, and you’ll see what
I’m doing here. Now, obviously, this is always
going to set to every minute. And that’s because I’m
doing an if true. So let’s go ahead and
remove that, because that’s kind of silly. So let’s go ahead
and select this. I’m going to save this. And now you’ll see that, once
the code finishes saving, I’m going to do a drop down. And I’m going to pick
schedule trigger. I’m going to run
this function. Again, I’m not running the
email gkal function. I’m running the schedule
trigger function. So I’m going to go ahead
and click Run. It’s going to ask me
for permission. I’m going to authorize it. Close it. Now again, I haven’t run this. I haven’t actually scheduled
the trigger yet. So this is going to create
a trigger that runs every one minute. So when I go to Resources,
Current Script Trigger, I’ll see that, OK, I have the
triggers I created before. Let’s go ahead and delete
these for clarification. So you see that there are no
triggers set up, right? KALYAN REDDY: So all you’ve done
so far is authorize it? IKAI LAIN: That’s right. Thank you for the
clarification. So I’m going to click Run. It’s going to run schedule
trigger. And when I look in Resources,
Current Script Triggers, boom, you’ll see that I’ve programmatically created a trigger. And this is a very nice feature,
because sometimes you might want to create a trigger
based on what someone is doing in a web app, what’s happening
in a spreadsheet. So you want lots of options. And being able to
programmatically schedule triggers is really
nice because, yeah, you have options. And you can be very flexible
in scheduling when you want code to run. So let’s talk about how we
can use this in practice. So here, I’ve set up
a demo spreadsheet. And we call this fundraising. So this is just a basic
Google spreadsheet. I’ve got two tabs, one for fundraising, one for donations. And we have a name and
a funds raised. Again, this is a very, very
simple spreadsheet. I’m dynamically generating
a chart from this data. So right now, I have
Arun as a slacker. He’s only raised $20. He’s a busy guy, right? So let’s go ahead, and let’s say
that he’s raised $20,000. Well, he’s totally blown
up the chart now. So let’s go ahead and lower
that scale a little bit. KALYAN REDDY: It’s a good
month for Arun. IKAI LAIN: He’s back
down to $40. That’s a 100% increase in the
funds that he’s raised. So charts a really cool because
you can just make changes, and the chart will
automatically update. So let’s say that with this
chart we want to send out a nightly notification with the
latest chart that says, hey, here’s the latest status
on fundraising. Well, how do we do that? We do that with triggers. So inside the spreadsheet, I’m
going to go ahead and click Tools, like so. I’m going to go to
Script Editor. And this is going to
open up the editor. Oh, the reason it’s doing
this is because I had the tab open already. So it’s just refreshing
the tab. So this is the script that
I have attached to the spreadsheet. So I’ve written a function that
emails that email as a report to myself. This is my test account. So [email protected] Please don’t ever email
me at that email. I will never see it. So let’s walk through this
code real quick. First off, if you’ve ever
written code to attach a spreadsheet before, you
recognize this. You want to get the active
spreadsheet. And we’re storing in
a variable, SS. We’re going to get the
sheet by name. The sheet’s name
is fundraising. Because if you remember, it’s
down here in the lower left-hand corner. You see a name on the tabs. And the tab here
is fundraising. So we’re going to get all the
charts for your sheet. And we’ll say, sheet.getCharts. We really only want the first
one, because there’s only one. But when you say, get
charts, you can only return all the charts. Right now, there is no
way to just return one particular chart. So we’re going to go ahead and
create the email template. I’m taking advantage of HTML
template to render an HTML email template. A lot of times I see people,
they’re doing this in code. They’re writing the template
out in the script editor. Or they’re using a spreadsheet
cell as the email template. There are advantages
and disadvantages. I personally like to use a
template, because it lets me separate the view
from the code. So right here, you’ll see I’m
using htmlService.crea tetemplatefromfi
le(“reportTemplate”). And if you’ve ever written any
kind of HTML service apps before, this should
look familiar. Because once I go to report
template, you’ll see, hey, it’s just an HTML page. Big H1 title, Daily Fundraising
Report. This is an HTML service
standard where I can inject a variable. And the variable I’m injecting
here is the date variable. I’m using the question mark
equals notation to say, whatever the string value of
data is, inject it here. I’m doing something here, it’s
a little bit outside the script apps we’ve talked
about this. But I feel like it’s worth
talking about. Inside an email you can
have inline images. And there are a couple of
different ways of doing it. One of the ways is using an
image source tag, and just using an image that’s hosted
on whatever CDN or whatever asset server that you have. And when you do this, the
benefits are that you kind of get to count how often people
get to view the images. Now, the disadvantage of doing
things this way are that when you send an email to someone
in Gmail or in some email clients, they don’t display
these images by default, and for good reason. Because when you request an
image from an image asset server, your IP address
is logged. It’s good for them, because you
get analytics about who’s seeing the email. But not everyone displays
images. So there are benefits of
using an inline image. Because once you use an inline
image, even though you don’t get the analytics, people will
always see the image. So here, the format
is cid colon and the name of the image. And we’ll get to what it means
to have a name of an image. So back in, the next
line, I’m going to set the date variable. Again, I’m just using
utilities.formatDate are to show I only care about
month, day, and year. I’m going to use
MailApp.sendemail. This is very familiar, because
this is just something we’ve taken out of the tutorial. I am sending it to myself. I’m sending the title to Daily
Fundraising Report. I’m not sending a text body. You can and should, because
not all email clients will render HTML. So we’re going to use the
last parameter to pass an HTML body. And you’ll notice that I’m
using template.evaluate. But I’m doing one more thing. I’m doing .getContent. You don’t typically need to do
.getContent when you’re doing an HTML service. But we’re forcing it to render
the template as a string, because HTML body
takes a string. So we take
template.evaluate.getContent, turn it into a string, pass it
into HTML body parameter. There’s another parameter called
inline images, which takes in a JavaScript map. And this is where we
had the word chart. Oops, excuse me. And chart here is we’re using
this again in report template. So if I want to name this Chart
Fundraising, for this to work correctly, I would need
to save that, come back to code, and name this the
chart_fundraising. And for this, I’m
using charts. I’m using 0, because that’s
the first chart. There’s only one chart. And I’m doing .getblog. Inline images takes a blog. KALYAN REDDY: And
that’s because earlier, you got all charts. And now you’re referencing
the first chart. IKAI LAIN: Earlier, up here, I’m
seeing var charts equals sheet.getCharts, So I could just
as easily say, var chart equals sheet.getCharts, and
then just return the first index, like so. But it was an arbitrary
decision on my part. So I’m going to go ahead and
run this function now. Let me go ahead and run
this email report. I could run this. But I’m actually not going to. Excuse me. I’m going to go ahead and
schedule a trigger. And I have this set right
now so it emails every night at 3:00 AM. And again, I did this by
clicking Add a new trigger, selecting Email report,
Time-driven. And you’ll see that because
I’m actually attached to a spreadsheet now,
I have the From spreadsheet option as well. You’re seeing up here, I have
an On form submit handler. We’ll get to that real
soon, but not yet. I want this run every day,
3:00 AM to 4:00 AM. So let’s save this bad boy. And what does this look like? Well, we can wait for it. Or I can just show you guys. So I’ll just show you guys. This is my test email account. When I click on Daily
Fundraising Report– this is from yesterday– you’ll see,
hey, Arun’s raised $20. Eric’s raised a lot of money. That guy’s a go-getter. And that’s how you schedule a
nightly report using Apps Script and spreadsheets. KALYAN REDDY: Oh, very cool. And then every time the data
modifies, the next time the trigger runs, it’ll
be a new chart. IKAI LAIN: That’s right. That’s a very good point, which
is that, what’s nice about this report is that it’s
taking the latest data from the spreadsheet. So when I do update the
spreadsheet, when I do run this, this chart is going to
look totally different, because everyone’s going to
presume we raised more money. I mean, it’s a problem
if anyone’s funds raises is dropping. It means you’re losing
commitments, not closing. KALYAN REDDY: This way you’re
staying on top of it. And then you can blame
that person. IKAI LAIN: You stay on
top, then– not blame them, you help them. We’re a team now. So that’s how to use triggers to
generate a nightly report. Now, I mentioned that there’s
also form-based triggers and other types of triggers
that you can run. And there are a few types
of triggers here. Let me go ahead and
demonstrate. So in this spreadsheet, I’m
going to use a clear one. So there are four types
of triggers that are spreadsheet based. One is on install, on
open, and on edit. So let’s say that you want some
code to be executed every time you open a spreadsheet. Well, let’s do this,
function onOpen. And you would define
something in here. And when you take a look to see
current script triggers, you’ll see that by naming it on
open, you’ve automatically defined that function as the
function you want to run every time the script is opened. So it’s the same thing
with onEdit. When you define a function
called onEdit in a spreadsheet attached script, you
look in triggers. You’ll see that it’s
automatically attached to the script’s triggers. Now, if I want to rename this
onEditSuper, it’s no longer named onEdit. So what happens to a trigger? Well, let’s take a look. Let’s go to Resources, Current
Script Trigger. It’s gone. It’s no longer an automatic
script that will run. So I’m going to have to click
Add new trigger, click on EditSuper, From spreadsheet,
and click onEdit. KALYAN REDDY: And for those
of you out there who are thinking, where am I going
to find all these special function names, you can go to
the Apps Script documentation. And you can look up what are all
the special functions that automatically become these
trigger functions. IKAI LAIN: Fortunately, there
are only three onInstall, onOpen, and onEdit. Oninstall is what happens when
someone installs your scripts from the script gallery. There are limitations here. You can only do certain things
in onOpen and onEdit. For those of you that define
custom spreadsheet menus, you’re already familiar with
onOpen, because you’ll see something like var ss getactivespreadsheet. And you’ll see
spreadsheet.addmenu. And that’s typically what
you want to do it in an onOpen hook. Sometimes, you want to be
annoying, and you want to see something like
Browser.msgbox(“don’t mess with this script”). And Browser.msgbox pops up a
little message box on top of your spreadsheet. So I’m just going to go
ahead and save this. I’m going to double check
under Resources, Current Script Triggers. And sure enough, it’s there. So let’s move onEditSuper,
because the function no longer exists. Now, I’m going to go back to
spreadsheet, I’m going to refresh this, so I’m hitting
Command-R right now. And it’s loading, loading,
loading, loading, loading. Firing up the spreadsheet,
fire up scripts. And there’s my pop-up box,
“Don’t mess with this script.” So you can do a lot of
things in onOpen. Oop, closed the window. So Tools, Script Editor, that’s
how you get to the Script Editor from
a spreadsheet. And we’re opening this
up right now. Let’s just discard the draft. We don’t care about that. So you’ve got a lot of
limitations when you’re using onOpen and onEdit. And there’s good reasons
for this. First off, you can’t ask a user,
you can’t say, who’s the current user? You can’t send an email. You can’t open a user’s drive. In fact, the only things you
can do it onOpen and onEdit are edit the spreadsheet
itself. You cannot edit a different
spreadsheet. And the reason for this is that
you don’t prompt the user for the permissions. So the only things that you
should be able to do are edit the spreadsheets. So the downside here is that you
can’t have an onEdit that says, every time this
is edited, email me. I’m sorry– yeah, you can’t email
the user. That’s not something
you can do. However, you can say every
time you edit, add a time stamp to the currently edited
row, or every time you open a spreadsheet, add a menu. And typically, what you would
do instead is in onOpen, you would create a menu that on
clicking the menu, then the user authorizes your scripts
so you can access user’s drive, access other
spreadsheets, basically, just work on behalf of the user. But you cannot work on behalf
of the user in onOpen or onEdit, as in you can’t access
email, you can’t access drive, you can’t access docs, you can’t
access anything else. So that’s how you do that. So let’s talk about the
very special one, which is On Form Submit. And this is the one that we very
commonly use any time we work with forms. So again, let’s go back to our
fundraising spreadsheet. We’ve got two tabs. One is for fundraising. And the second one
is for donations. So donations, I’ve created
a Google form under Form, Edit Form. It’s because I’ve already
created this. That’s why you’re seeing
this right now. So you’re seeing that when
I hit Edit Form, well, basically, this collects name,
email, and a domain email and that’s all I really want. So I’m going to close
this window. When we go to Live Form, you’ll
see that this is what my form looks like. It’s a form that tells people,
hey, give me your name and give me your email, and what
your donation amount is. So let’s go ahead and try
doing this right now. So I have this wired up to an
Apps Script to send an email to whoever fills out the form,
if you give me a correctly formed email address. So let’s go ahead back
to on form submit. On form submit is not one of
these magic function names. You have to go into Resources,
click Current Script Triggers, select the function, From
Spreadsheet, and click on form submit. So you have to do that. There’s no magical name
that you can do to make this thing work. Once you hit Save, whenever
someone submits the form, it’ll execute the code in
whatever function you’ve defined to execute. So, in this case, there are
better ways of doing this. You can do E dot values
to return to form values as an array. You can also use named
parameters. There are lots of things
that you could do here. So I suggest that you check out
the triggers documentation for an exhaustive list of how
to get at the parameters. But for now, we’re just going
to use an array for clarity. So each one of these values
corresponds to a spreadsheet column. And spreadsheet columns are
what we call one index. So the very first column
here is 1, 2, 3, 4. However, this is a
JavaScript array. And sometimes this mismatch can
be a little bit confusing. A JavaScript array, it’s
like a European floor. It starts at 0. So we’re going to start
at 0, 1, 2, 3. So we go back here. And timestamp is now column 0. Name is column 1. Email is column 2. And donation is column 3. So we’re going to create
variables for this, just for code clarity. You don’t have to do this. I do this because when someone’s
reading my code, they know what’s going on. Again, I’m going to use the
HTML service trick. We’ll create template
from file. Now I’m going to set two
variables, name and amount. So let’s take a look at
notificationTemplate.html. And you can always create
HTML files by going to File, New HTML File. So here, I’ve created a basic
HTML file that says, “thank you for your donation.” You
know, dear name, which is going to be the variable
name inject. “Thank you for your
generous donation of” some dollar amount. So we’re going to go ahead and
inject that dollar amount. And I’m going to sign this,
because I’m very grateful that you donated money. So this is all wired to
when a form submits. And when this is done, I’m
going to send an email. I’m going to thank you
for your donation. And I’m going to say
htmlBody dot template.evaluate.getContent. So let’s go ahead
and do this now. And how much do we donate? We’re Michael Moneybags. KALYAN REDDY: Well, I know
this very, very rich guy. He’s a good friend of mine. So let’s donate a
billion dollars. IKAI LAIN: $1 million. Why would you donate
$1 billion when you can donate $1 million? KALYAN REDDY: Sure. IKAI LAIN: A little silly
joke for those of us who are kind of– nevermind, Austin Powers
generation. So I’m going to hit Submit. And it’s just a standard form
output that says, hey, your response has been recorded. So when we go back to the
spreadsheet, we see that Michael Moneybags has committed
to donating $1 million to our cause. And you notice that the
commas went in. This is a nice little
spreadsheet feature. You can format a row. So I’ve selected this row. And I’ve gone to– this is always format, number,
and just selected currency. So if I select currency with
cents, any new thing that comes in will be formatted
currency with commas, dollar signs, and cents. So let’s go ahead and check
my email inbox. Oh, I’ve got a new email. What could this be? I’m Michael Moneybags. “Dear Michael Moneybags, thank
you for your generous donation of $1 million.” One thing to notice is that this
is not formatted the same way it’s formatted on
the spreadsheet. That’s because internally,
it’s still saved as the value 100000000. Spreadsheet formats are
purely view only. So if I want to format this so
it looks nicer, I’m going to have to go ahead and manually
format this thing before I send it out to the template. So keep that in mind that the
number you get is not going to look exactly the same as the
number you get inside the spreadsheet. So right here, I’m just
saying the amount. And the raw value I entered,
if you remember, it’s just this number. So that’s why it looks like
that in email and it looks different from the
spreadsheet. OK, so that’s kind of– oops. That’s the next part. Don’t look. It’s a secret. So there are a couple of things
to know about working we’re triggers. Triggers do have CPU limits. So you can’t just write a
trigger that runs for days. So a trigger will time out
around a five minute mark. It’ll probably go a little
bit longer than that. And also, if you run 10,000 jobs
a day, you’re probably going to run out of quota. So you can’t just use triggers
as a way of doing infinite computation. You need to think about what
you’re doing and kind of balance things out. With triggers, when you want to
return the user as running as, you want to use
section.geteffectiveuser. Because it’s not an active user,
it’s an effective user. It’s who am I running as, as
opposed to who’s actually on the computer typing away,
pushing buttons and whatnot. Well, two things that I haven’t
talked about yet that I will demonstrate right now are
All My Triggers and email notifications. So when you’re in the Script
Editor, sometimes, like me, you might be working on five
or six scripts at once. And you don’t know what
triggers are running. And that’s why there’s an All
My Triggers resource. So if you go to Resources,
All Your Triggers. And I’m in a totally different
app right now. You’ll see that I’ve returned
all of my triggers across all of my different applications. KALYAN REDDY: We’ll see the ones
we created with the other script as well. IKAI LAIN: That’s right. I have the email gkal
trigger right here. And let’s go ahead
and cancel it. So you’re no longer going to
get congratulatory emails. So we’re going to save this. That’s really nice. The other nice thing is that
when you go to either All My Triggers or Triggers, I’ve kind
of been glazing over this notifications thing. What’s nice about this is that
you can set up notification so that when there are errors in
your script, you get an email. And there are no errors
in anything that I write, because I’m me. Kidding. But sometimes, you might get
invalid input or there might be a bug in your code. It’s very important that you
want to get notified. So when you have a notification,
you might want to be notified that
there’s an error. And under the notification,
there are various options. You might want to be notified
every time, immediately when there’s an error. And if you have a low volume
usage script, you sometimes want this. Because if someone uses your
script, it doesn’t work, you don’t want them to call you. You want to be on it. You want to be on top
of that thing. So you go ahead and
click Immediately. And whenever there’s a failure,
you’ll get the failure right away. Maybe a common failure is– let me show you guys
something. KALYAN REDDY: And it’s still
good practice to run your trigger function before just
to test it, make sure everything works. But this is also a good catch
if there is some kind of extraneous error, then you’ll
get notified of it. IKAI LAIN: Right. When you get an error, it looks
something like this. This is a summary email. But it’ll tell you
all the errors. It’ll tell you what the
error message was. And it’ll say what caused
it and what time it was. So this isn’t a lot
of information. But one of the patterns that
you can use is if you know there’s some place that you’re
trying to debug, put a try catch block around it. And when it does blow up,
then go ahead and email a stack trace. Because sometimes the stack
trace is more useful. But we would completely blow up
your inbox if we were to do that every single time. You want to record relevant
values, as in, what are the values that might have
caused this error? So you’re going to want to
go ahead and build that. So again, the other values
here, all your triggers, notification. Besides immediately, you have
hourly, daily, weekly. So most of the time, I just want
notifications daily, how many errors there were. And occasionally there’s
one or two. And such is life. That’s just the user putting in
wrong input or something. And that’s why I might want it
daily instead of immediately. But then again, it just depends
on a volume or script and what kind of debugging
you want, what kind of notifications you want. So that’s how you can add error notifications to your triggers. Because when you’re running
a script, what I call interactively, you get
the error right away. You can dig into
it right away. But when script is being run as
a trigger, there’s no other way of figuring out when
it’s bombing out or when there are errors. So that’s why you want to use
a notification functionality to debug your script when
there’s problems. Everything good so far? KALYAN REDDY: Yeah,
it makes sense. IKAI LAIN: Good. Good. Let’s talk about things
you can’t do. I touched on this earlier. But again, you cannot use
onInstall, onOpen, and onEdit to do anything beyond accessing
the spreadsheet they’re contained by. You can’t send emails. You can’t access user’s drive. You go only edit the
spreadsheet. On form submit and on clock
triggers, these run as a user that installed the trigger. So if your app programmatically
creates a trigger for a different user,
it runs as a client user. So let’s say, I create
a web application. And Kalyan comes, and he
clicks some button. Well, the trigger now runs under
him and not under me, and I’ll show you guys
what I mean by this. So here’s a web app
I’ve written. It is in one of these windows. Cancel. Cancel. So I called the schedule
trigger from a web app. And I created this just
at It’s got a do get handler. And it creates a single button
that’s says schedule trigger. That creates a trigger that
emails me every one minute. When it calls email me, again
I call session dot get effective user. Who is the user I’m
running as? And I’m sending an email that
says, you’re getting this because of a button. Hi, there. So if I go ahead and
deploy this thing, it looks like this. Publish the app. Do not publish at the Gallery. People commonly mix this
up with [INAUDIBLE] web app. I’ve done it a couple
times by accident. So here’s the current
web app URL. So let’s open a new
window, like so. And it’s going to load a single
button that says, schedule something for me,
schedule a trigger. All right, that’s fine. But right now, as you
can see, I’m logged in as my test account. So let’s log in as a different
test account. So this is a different
test account. This is dinodinosaur.test. I just copy, pasted the URL. And the first thing I’m going
to see is an authorization dialogue, because this
is a web app. So I’m going to say, oh, this
script wants access to send email and also wants permission
to subscribe to external events, as in, schedule
more triggers. So I’m going to authorize
this. It’s going to take me to
the web app and it looks exactly the same. And I’m going to schedule
a trigger. That’s all it does. I should have set some feedback
or something. But this is a demo
application. So I’ve gone ahead and I’ve
scheduled a trigger. And what you’ll see is that
you’ll get an email that says, oh, hey, you just installed
a web application. Here’s how you uninstall it. So in about a minute, I’ll
start getting emails. And there you go. There’s my first email. You are getting this because
of a button. A trigger has been scheduled
for me that’s emailing dinodinosaur.test. And if we go to test script,
this is nothing. I just save this so
I can look at the current scripts trigger. There are no script triggers for
a current script, because I didn’t set any up. This is a blank script. But if I go to Resources, All
Your Triggers, oh, well, I have an email me trigger. It’s not attached to a script. So I want to cancel this. So there are two things
I can do. I can either go back to this
notification email, more information, and click
on uninstall. Or I can just go here and delete
the email me trigger. And that’ll kill the
trigger off. So again, let’s go through
that again. If I click Schedule Trigger,
it’s already– whew, wow. I zoomed in a lot there. Resources, All Your Triggers. I pushed a button twice. And now I can cancel it like
so, and click Save. And that’ll get rid
of the triggers. KALYAN REDDY: So this running as
the user executing it, will this also have implications for
a quota of certain things? IKAI LAIN: Yes. So when it runs, it will use
up your user’s quota. So because it runs as you,
it uses your quota. Unless, for instance, you saw
it when I published the application, I said run
it as the end user. Let me see if I can show
you what I mean. If I execute it as me,
[email protected], it’s going to use my quota. KALYAN REDDY: So there’s
uses for both. One is if you don’t want to run
over the amount of emails you can send every day, you can
have it run as the user, and then use their
quota instead. IKAI LAIN: Exactly. And the thing about that is
that when I go here and I click sent email, you see
that, oh, well, this is running as ikai.lain.test. Whereas when it runs as
you, Mail App sends this to you, too. So this is coming from
dinodinosaur.test. When I go to send mail, it’s
running as the second user. So it’s not just for
quota reasons. The main reason should
not be quota related. The main reason should be you
want to run it as you. You want someone to sign up and
get update notifications on, I don’t know, the latest
documents coming into your drive, or whatever. So let’s go and talk about
some advanced patterns. So there are three patterns I
want to talk about, and of where you would use triggers
outside the exams I’ve showed you. One is when you want to chain
long running jobs. Like right now, as you know,
there’s a limit to how long a trigger can run. So let’s say we want to run
a job that going to take, say, an hour. Well, how would you do that? Well, you really can’t
right now. But you can with triggers. And what you would do is you
would create a trigger that runs every five minutes or
so, and just goes ahead. And when it’s done executing,
it executes the next block. So you might want to save a
point or two where you are in your job, and just
keep moving. And you save that either in
a property or in ScriptDb. And that’s how you would
do a long running job. And when you’re done with the
job, you can program and create triggers. You can also unschedule
triggers. So let’s talk about
how to do that KALYAN REDDY: So just to be
clear, you would want to do this because there is an
established limit on how long each execution can run. IKAI LAIN: Exactly. You have a total CPU
time that you can use up for your triggers. And sometimes you just don’t
want a runaway trigger. If you want a user to be able to
cancel all their triggers, for instance, you can
write a trigger that says, kill all triggers. And you don’t have
to name it this. What you would do
is you would do ScriptApp.getScriptTriggers. This returns an array
of all the triggers. You can iterate through this. There’s a syntax error here. And this would cancel
all the triggers. So triggers, when I pick the
trigger it’s currently working on, I would just say– opps. You would delete the trigger
via script apps. So you would say,
ScriptApp.deleteTrigger, and pass it the current trigger. And this deletes all
the triggers. This is like a big
red panic button. But really, sometimes
you only want to delete a specific trigger. So that’s when you would put
in a conditional like this. Triggers, the current one. And you would say, well, what’s
the handler function? If the handler function is email
me, go ahead and delete the trigger. And there’s one more way to do
this, which is that triggers have something called a unique
ID, get unique ID. And this is unique globally. This is unique across all
scripts for all users. So at the time of trigger
creation, what you might want to do is say
Trigger.getUniqueID, save this somewhere, like in ScriptDb,
or in properties, and that way, if you’ve got a bunch of
different triggers running for a bunch of different people, you
can just go ahead and get unique ID matches the one in
ScriptDb, delete that trigger. So that’s typically how you
want to programmatically unschedule triggers. So there’s one more pattern. I said before that you cannot
set up triggers in onOpen. When you copy a spreadsheet,
it does not copy over to triggers. So if I go here to the
spreadsheet that I created, demo fundraising, and I
made a copy of this– make a copy. Copy demo fundraising. And right now, this is a total
copy of the spreadsheet for the most part, except when I
go to Tools, Script Editor. And scripts take a few
seconds to copy. So this might not
have copied yet. But fortunately, I was
slow, so it did it. You’ll see that it’s copied
all the code. But when I go to Resources,
Current Script Triggers, only the onOpen and onEdit
triggers are set up. We don’t have the trigger that
says, run this every hour, run this every minute, run
this at midnight. That’s not copied over. So this is problematic if your
workflow is to create a spreadsheet and you have people
in your organization make a copy of it. Because you often want
them to copy the time-based triggers, too. So what do we do about this? Well, a common pattern
that I do is I use onOpen to create a menu. In the menu, I say, set
up a spreadsheet. And I store it inside script
properties, or user properties, or wherever that
says, you have created the triggers or you have not
created the triggers. You can’t access those
inside onOpen. But what you can do is you
create a menu item that says, schedule set up the script. So you go ahead, and you
click the menu item. You say, set up script. It’s going to ask the user
for authorization. And at that point, it’s going
to go ahead and schedule the triggers for you. Because you have functions like
get script triggers, you can look to see if the trigger
has already been set up. So you can run through it. You can say, does this
trigger exist? Yes, no. If it does, set it up. If not, don’t set it up. Because if I run schedule
trigger again and again, if I run schedule trigger three or
four times, I will create three or four triggers. Because again, you can
attach multiple triggers to the same function. KALYAN REDDY: So basically, it
would just run the function that many times. IKAI LAIN: Exactly. So a trigger is kind of like me
creating a memo that says, do this job. And you create five copies
of the memo and I give it to Eric. And if Eric takes things very
literally, he will do the job five times. Fortunately, Eric will just come
back to me and say, what are you doing, man? What is this? But this is a computer. So with a computer you have to
be very, very specific about what you want it to do. And that’s why triggers have
this get unique ID function. So that if I give him five
memos, each memo has an ID that says, this is ID 1, this
is ID 2, this is ID 3. And he knows, oh, these
are different jobs. And you can do the same thing
when you’re managing triggers, that once you know the ID, you
know that they’re different, unique triggers. And that’s what I have
for patterns so far. There are definitely
many more patterns. These three are the ones that
tend to be the most common. So I just like to talk
about these a lot. And that’s what I have
for you guys today. If you guys have any more ideas
for shows you’d want to see, do let us know. We have more shows happening
this week and next week as well. So again, my email address
is [email protected] Or Kalyan’s is [email protected] You can email him. It’s appeared in this
demo multiple times. So do you have any
questions so far? KALYAN REDDY: No, I think
triggers are a great way, for example, to get around some
things like making a long running job. IKAI LAIN: Great, and
now you’re going to use them every day. KALYAN REDDY: All the time. IKAI LAIN: All the time. KALYAN REDDY: I’m going to set
up a trigger to email myself and remind myself
how cool I am. IKAI LAIN: All right. So when we started this show, no
one was asking questions on Google Moderator. So let’s go ahead. Apparently, the status
of this has changed. So we’re going to go a little
bit over the time limit for the show. Originally, we were set up
to go for 45 minutes. But let’s see what questions
people have. I’m always nervous about
live questions. Mostly because people ask
something, I don’t know how to answer it. I’m not going to click play,
because that’s going to cause some crazy feedback. “I’d like a trigger
for onClose. Given the three triggers
available, is there a way to trick one into being
in onClose?” Maybe. I’m not sure about
this, actually. KALYAN REDDY: I don’t think it
would work in the same way, because the containing
spreadsheet is no longer open. IKAI LAIN: Right. I was recently thinking about
something with onEdit. When edit stopped for more than
a certain amount of time, then maybe someone has closed
your spreadsheet. But that’s not really good. KALYAN REDDY: You could
do something like– yeah. IKAI LAIN: I don’t know. I don’t think there’s a good
way of doing this. But definitely put in
a feature request. And we’ll figure out why
there is or isn’t a way of doing this now. I remember hearing something
about why there wasn’t an onUninstall. But do open a feature request. And we’ll check this one out. KALYAN REDDY: And a close
approximation has set up a time based trigger that will go
back and clean up all the tasks, like whatever
you want to do. Maybe you want to clean up some
of the fields, or format something differently. You can do it on
a time trigger. Just go back and do
that same thing. IKAI LAIN: Right, right. The next question is “will the
emails created with these triggers gets stored in the Sent
folder in Gmail?” If I’m running a trigger as myself and
I use Mail App, yes, it will be in my Sent folder. If it’s coming from Kalyan,
and it’s executing as him, they’ll be in his Sent folder. And the third question is, “I
just want to create social site.” Well, that’s
good for you. And I wish you luck
in your endeavors. All right. So I think that’s it for now. If you have any more questions,
we’re going to wrap this show up. But we’re doing office
hours this Thursday. If you’re watching the show
live this Thursday– and we do them every Thursday
or most Thursdays– just go to and look for Apps Script
Office hours. We do them almost every week. So check us out and join us. KALYAN REDDY: And you can
always go to our Stack Overflow posted against
our tag. We are pretty active there. And also the issue tracker if
you come up with any issues. IKAI LAIN: That’s right. All right, well, I think
that’s it for today. Thank you guys very much. And we’ll see you
guys next time. KALYAN REDDY: Thank you.

37 thoughts on “Introduction to Google Apps Script Triggers

  1. If you are looking for a book on GAS specifically for spreadsheet programming, check out this one:
    There is a substantial free sample that contains an appendix showing how to perform common spreadsheet tasks in both Excel VBA and GAS.

  2. Emailing the embedded chart through MailApp seems to stop working now. I get a blank white chart. It used to work 2 days ago. Please advise if Google is doing some updates on the server side. Thanks!

  3. As of now, I am not able to see the automatically generated trigger when I created onOpen(e), onEdit(e) and onFormSubmit(e). Also, value for e was "undefined" while debugging. So, how to make it work?

  4. I wanted to trigger a brief Google Slide onOpen from my drive. It looks like triggers do not work with Google Slides. Is this accurate? I would welcome any advise on how to trigger a brief animation onOpen in Google Forms?

  5. But what can you do with it,,,Just copy & paste message,,,It don't make me money,,,Just social messages,thx bruvas,fundraisers cool,G/Luck & Well done.

  6. Thank you very much ! This made it possible for me to run background jobs to support several flows !

  7. So if I do a onEdit function there is no way to send an email if a specific cell is edited or a specific value is added to that particular cell which would trigger an email ??

  8. Use this link to check the triggers in your project that are currently up and running:<INSERT ID HERE>

  9. What exactly is the code at 41:58 doing?

    function killAllTriggers() {
    var triggers = ScriptApp.getScriptTriggers();
    for(var i in triggers) {

    Is triggers indexable using i?
    Isn't i the trigger to be deleted?

  10. I'd like to send reminder emails from date of entry or status. Send email 30, 90, and 120 days after entry. Or, send an email every time a status is marked. Any ideas how to do this?

  11. Google app script is rejecting any kind of quotation marks on my computer when I try to code something. How do I solve that?

  12. i want to create a trigger which should runs every day at 9 pm. Is there a way using app script….please help

  13. Is there a trigger as the SelectionChange() event in precious MS Excel? It runs every time when you change selection, not when you edit or change it, but when you just select new cell or range.


  14. sir,the way approach was simply good,i have a doubt that .
    how can i run a url whenever there is an update in the data.
    like if the data in row like A3 changes there should be a run of the url:-like************/############/devansh/on

  15. very nice ..i joined you guys today. i lover your content and humor. by the way i run excel vba access educational channel and it has 560 videos on automation. request you to have a look or all readers and share your feedback with me.

  16. Hi, please help to write the script for gmail PDF attachment? if we click the PDF attachment in Gmail, pop-up will come like preview, so there top open with is there, and there I need to display the icon? like that how to write app script? Please help me.

  17. sir i need help
    I want to hide my code of script from user , because i use mastersheet link in my code , i want to hide all code from sharing sheet users .

  18. Can we also call a trigger manually, like from inside a custom function ? I am working on a sheet where i need to hide certain columns, if a certain value is chosen in a cell. Ive already created a script for it, but dont know how can i call it via a trigger via a custom function. Thanks in advance.

Leave a Reply

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