Using Google Apps Script to automate G Suite (Google Cloud Next ’17)

Using Google Apps Script to automate G Suite (Google Cloud Next ’17)


[MUSIC PLAYING] PAUL MCREYNOLDS: I’m Paul. I am a product
manager at Google. And I work on App Script, among
a couple of other products. I’m curious how many
of you in the audience have used Apps Script before
or are familiar with it, or whether this is
folks first time? Awesome. This talk is definitely
designed for somebody who hasn’t seen Apps Script before. And it’s an easy product
to demo in that way because it’s based
on JavaScript, which is something that many
or all of you already know. So just to tell you a
little bit about myself, my broader team is the
apps developer team. And we work on extensibility
and automation and integration for G suite. And I focus on Apps
Script in particular. I was previously a
startup founder and CTO. And in that role
and in this one, I know the challenge that
comes with prioritization. I would imagine that we all
have the experience of somebody approaching us
with whatever level of urgency, telling us about the
project that needs to get done, or the process that
needs to get implemented. And if the easy
things aren’t easy, then the hard things
don’t get done. And that is to say we have
to prioritize ruthlessly in our jobs. And an easy technology lets us
go from doing 5% of the things that we want to
do, to 10% or 15%. And that can be a
really big difference for those users who are affected
by that second 5% or 10%. I’m a big believer in
serverless development. You’ve probably already been
hearing a lot about serverless. Here, at Next, it’s something
that Google is thinking hard about across the platform. And Apps Script is
one of the places– one of the first places
that Google has championed serverless development. This is still my first
year here at Google, but I’m excited to be solving
some of these problems. I’m based in Boulder, Colorado,
but I grew up in California. So I’m glad to be back OK. So this makes some
assumptions about you. I know a little
bit more than I did when I wrote this sliders,
which is that about half of you have used Apps Script before. I’m also curious,
how many of you have formal engineering
or software training? OK. Yeah, it’s a slightly
smaller set and. It’s slightly disjoint
set, which is what we see. Apps Script is
something that gets used by people across the spectrum. And we like to see that. Apps Script is part
of the strategy for us that lets people solve
their own problems. And so you may or may not
have that formal training, but you have problems that
can get solved using some– hopefully a small
amount of code. And you use, or you want to
use Google for your business. And you want to be able
to automate and extend the products that you
get as part of G suite. So we’ll talk quickly about
what Apps Script, broadly what it’s good for, and the
actually several ways that you might use or have
already used Apps Script. And then this is a
technical session. And so then we’ll talk about
how to use Apps Script. And we’ll go through a
few different use cases around a common
theme that show you some of the different
aspects of the product. One thing we won’t
dive deep into that’s a super important facet
of the product is add-ons. The next session
in this room who is given by one of the engineers
on the Apps Script team, which is given by one of
the engineers on the team, will cover add-ons in depth. And so I don’t want
to steal his thunder. But please stick around
if you’re interested. And then we’ll talk a little
bit about the features that we don’t get to
cover in the hands-on part of the session. And then I’ll talk a little bit
about where we go from here. So Apps Script is a
serverless JavaScript runtime for G suite automation,
extension, and integration. The first key part
here is serverless. And serverless. And serverless has
as many definitions as it has people
who use the word. But the key point is not that
it doesn’t run on servers. Obviously, it runs on servers. It’s that you have no role
in provisioning or managing those servers. I think another
key piece of this– and this got talked
about yesterday– is that server lease
applications are event driven. You don’t have to proactively
call this application from other code
because then you’re still managing a
server somewhere that’s running some code. And so many of the use
cases for Apps Script are those where the
code that you write is triggered at the right
moment by a user behavior, by a change to a document,
without your having to write the code that triggers it. JavaScript. This is a key piece today, and
at least for the near term. Apps Script is
written in JavaScript. And then the last part I want to
highlight is G Suite affinity. That’s not to say
that you can’t use Apps Script to do other things. You can hit any RESTful
API with an URL fetch call. You can talk to JDBC. You can talk to a bunch of
Google services via our built in APIs, or directly to their
REST API via advanced services and Apps Script. But our bread and
butter is G Suite. And that means built in
APIs for talking to G Suite applications, for scripting
Gmail, Docs, Sheets, Calendar, but also
event-driven behavior based on those applications,
and extensions and add-ons to those applications. So G Suite is really
where Apps Script starts. A few use cases. You know, JavaScript is a Turing
complete programming language. You can do just about
whatever you want to. But in the enterprise, this is
really what we see happening. One way that I might summarize
some of these, at least, is that these are
applications that used to, or often still do get done in
Excel or in Google Sheets where you have a spreadsheet
that maybe has some macros or some
scripts attached to it that is doing more than a
spreadsheet ought to do. And frankly, that’s
often the way that an Apps Script
application itself evolves. You open the script
editor in Sheets. You start typing some code. And before you know
it, your spreadsheet is a mission
critical application for your team or
the whole company, and you have built your first
Apps Script application. We’ll talk a little bit about
where you might go from there, add a UI on top of it and make
it a full fledged application. And I’ll also touch
briefly on App Maker, which is a tool that was
presented yesterday that’s great for building UI
on top of Apps Script business logic. But key use case is enterprise
workflows, especially approval type workflows on
anything document-related. If you’re on G Suite, you’ll
find yourself automating an Apps Script often. Resort management. We internally use a tool
that’s built in Apps Script to do product management on the
docs and drive team at Google. And automation. This is anything from writing
a little one off script to setting up a time trigger
to having a spreadsheet that generates a report
on a bi-weekly basis. And reporting is right
there on the list too. And then with 3P
integrations too, if you want Sheets or Docs to
talk to a third party service, then Apps Script is a great
way to accomplish that. And then, lastly, domain admin. We have some APIs that let
you talk to the Admin API. If you were in the App
Maker session yesterday, they mentioned a
cool new team’s tool that’s being deployed as an
App Maker template that’s built on top of the
Admin SDK, which is accessible in Apps Script. OK. So there are four
different ways that folks come to use App
Script whether or not you’re aware that
you’re using App Script. And by far the
biggest touchpoint is the Doc, Sheets and
Forms script editor. So this is the script
editor that you access by going to the Tools
Script Editor menu in Docs and Sheets, or the vertical
ellipsis Script Editor menu in Forms. That drops you into the script
editor in the specific context of that document. So that lets you write some code
that operates on that document. You’ll use the
script editor when you have a script that
has a special relationship to a document. And so this is that use case
where the sheet is basically the front end to
the application, or where you are adding some
functionality to a document to build your table of
contents, for example. And the way in which
that special relationship is captured is with
these three methods. And so, depending on which
application you’re scripting, you’ll call these methods to
get an object representing the current document
that you can use to act on that document. You also have access
to simple triggers, which are super easy to code. You just define an on open
or an on added function. That code runs every time that
event happens to your document. And so on edit in
particular, is good for doing complex work that depends on
content across say, a sheet. You might use custom functions
for something piecemeal. And so you can also
define a custom function that you can call in
your sheet, and it will operate on whichever
cells you reference where you call it in the sheet. If you want to do something
that is more broad based that sweeps in the whole
contents of the sheet, you might look to
the on edit trigger. The entry point number two is
directly at script.google.com You’ll notice if you go to
Tool Script Editor you’re editing a script at
script.google.com exactly the same product, just
a different context. And by and large, you can do
all of the exact same things in one of the standalone
scripts that you can do in a bound script. Again, the decision tree
is, is this script– does it have a
special relationship with a particular document? If so, Tool Script Editor. If not, or even if in
the future it might not have a special relationship
to that one document, consider going straight
to script.google.com and using other means to
get access to the documents that you need to talk to. You can use the drive picker
to get a drive file ID. And you can use the
document app API to get a reference
to whatever document you want to talk to
in Docs, for example. And so that’s kind
of the first decision that you’ll make when
you’re approaching a script is, is this script
always going to be attached to this one document. Here are some of
the things that you would do in a standalone
Apps Script project. You can just write a
quick utility script. And so if you need to blast
50 emails or 100 emails to people in your
org based on rows in a spreadsheet, or some
external data source, say, write it up in Apps Script. Hit the Run button. Test it out first. And send those emails off. You can also build web
apps in Apps Script. And this is something that
we see leveraged particularly heavily at Google. A ton of the tools
that we use internally are written in Apps Script. When the user hits
your web app, which is published at
a well-known URL, it runs your Apps Script code. And your Apps
Script code returns HTML back to the client. We have a templating
system that lets you easily code HDML
templates and populate them. And we’ll actually
do a deep dive on this later in the
session, how you build a web app using Apps Script. And you can also build
writes to be web services. It’s not something that
will go deep into today, but our execution
API lets you invoke Apps Script via a REST API
so you can build the back end service. Lastly, add-ons. As mentioned, that
is going to get covered in CB304, which is
the next session in this room. Stick around if you
like what you hear, or maybe even if you don’t,
because he’s cooler than I am. OK. One place you may have
seen Apps Script before without knowing that it was
Apps Script his AdWords scripts. AdWords scripts run in
the Apps Script runtime, and can leverage a bunch
of the Apps Script APIs. And then, lastly, AppMaker,
which I’ve mentioned, and which is being demoed across
the street in Moscone West. Incredible tool for
building web applications. Apps Script sits
behind AppMaker. Deployed AppMaker application is
an Apps Script web application. And as such, it has access
to all of the same APIs and facilities that
Apps Script does. When you write a server
side script in AppMaker, that is Apps Script code
that you’re writing. If you want to learn more,
I think the website is GSuite.google.com/appmaker,
all one word. I definitely suggest
checking it out. This is the team we work
really closely with. OK. So we’re going to do
some hands on coding. I hope it’s not too slow. Stop me if you’re
bored to tears. We’re going to
organize a speaker event, a little bit contrived. But it shows off a
bunch of the stuff you can do at Apps Script. By the way, we’re
not going to be following along our
quick starts directly. But we are going to touch on
these three types of scripts that you see featured
in our documentation. And also up there
is the web address. Apps Script documentation
is the best place to start, and certainly the
place you’ll be coming back to as you go
deeper in Apps Script. OK. So we’re going to proceed
through three versions of the same application
that introduce new types and levels of functionality. The first one is super simple. We’re going to build drive
notifications for ourself. And so we’ve got a folder. We’ve asked everybody who’s
presenting at the conference to put their decks in that
folder for review and approval. And we want to get notified
every time something new comes into that folder. Super simple use case, but
a good jumping off point. All right. So first thing that
we’re going to do is we’re going to grab the
ID of this folder in Drive. This is our special folder. And then we’ll create
an Apps Script project. Sorry, I should go back. You can go directly to a
script.google.com or to create an Apps Script project in a
particular place in Drive, it’s really handy to
just go to the new menu, click More, and then
Google Apps Script. All right. So obviously, you
memorized the folder ID, and then you recognize it here. So we’ve stashed
that in a global. And we’re going to do some
processing on that folder. You’ll see what we’re going to
do on one run of this script as I step through the
code, and then I’ll show you how we set it up
to run on a time trigger so you’ll get those
notifications regularly. So the first thing
that we’re going to do is we’re going to grab a folder
object using the Drive App built-in API that represents
that folder that I just showed you in Drive. This lets you script that
folder in a lot of ways. And the way that
we’re going to do it is to look at what’s
inside that folder. And you’ll see when we go
to install our trigger that runs this regularly,
you as the developer are going to get asked to
authorize access to Drive. And so everything that Apps
Script does, whether it’s running as you the developer,
or as we’ll see later on, if it’s running as
the end user, everything is authorized by OAuth. And so you or the end user
know what data Apps Script is working with. All right. So get an iterator
over the files. We’re also going to grab
our ScriptProperties. ScriptProperties is a store key
value pairs for this script. You also have user properties. And in the context of a
container bound script– a script attached
to a document– Document Properties. And so this is your very
lightweight persistence layer for Apps Script. When we get to the roadmap,
I’ll talk a little bit about our vision for
storage in Apps Script. But this is where
you start today. And it works great
for this use case. All right. So this is the while loop. I don’t want a
single anybody out. Does everybody in
here know JavaScript like the back of your hand? OK. So right. This is a while loop. That iterates over a set of
code as long as the condition in the parentheses is true. And so file iterator
has this method that tells us whether it has
more files that we can look at. And we’re just going to look
at each file in this folder until we run out. Our strategy here is
we want to know what new files are in this folder. That inherently is
a persistent problem because we have to know what
files we’ve seen before. And that’s we’re going to
use our property store for. And so we’re forming a key with
the file ID, the file slash and the key name. That is– that’s arbitrary. That’s up to you
as a developer how you want to define your key. This is with the
anticipation that you might put some other stuff
in that property store. And so you’re just
name spacing it. And then you’re going
to see if you’ve already put that key in before. If you have, that’s because
the script ran before, and you already saw that
file, no action required. But if not, then you’re
going to set the property– so you know you’ve
seen the file– and then you’re going to
just push it on to an array. And so that push method
is just adding your file to your new files array. And then, lastly, if
you found any new files, we’re going to log that. And I’ll show you
what that looks like in the logger feature. And we’ll sent off an email. So this is
demonstrating the script running as a standalone on Apps
Script I’ll show you how to set up a trigger in a minute. But for now we’re just going
to select the process method that we just defined
and hit play. It’s going to run. Now go to the logs. And we see indeed new slides
were found in that folder. We’ve got two decks
in that folder. We go over to our Gmail. And we also see those two
presentations listed there. OK. So the last step to have
true folder notifications is that you don’t want to have
to go and hit the Play button every five minutes. And so in the
resources menu, you’ll find triggers for the project. Configure a trigger here. This is the permissions step. And so because you’re the
person installing the trigger, the script is going
to run as you. You the person with access to
the folder that it operates on. So that’s by design. And you’ll need
authorized access. And so this is the part
where what kind of data this script is accessing. So it knows who I am, but most
importantly are these first two where we see that it’s
going to be a look at Drive, and it’s going to and it’s
going to manage my email. In the roadmap
portion of the talk, I will talk about
what we’re doing to let you tailor the
scopes more narrowly. And I want to call out that
this first scope is broad. This is full Gmail scope
where this script all it does is send an email. And so I’ll talk
about what we’re going to do to
make sure that you don’t need to request
this scope when you just want to send email. Save that trigger
and off you go. So you now have folder
notifications at Drive. It’s a small use case, but it’s
also a small amount of code. And that’s what we like. We want those easy
things to be easy. AUDIENCE: What was
the trigger again? Is it every time I
view that folder? PAUL MCREYNOLDS: This
was a time trigger. And so, in this case, we
selected the time driven type, and ran it every five minutes. There are a few other
kinds of triggers, some of which we’ll talk
about, some of which we won’t. But it’s a very similar
mechanism to the one that you would use, for example,
to write a script that looks up a document, whether one
is given to you by user, or selected using the Drive– the Drive picker, et
cetera, and install a trigger on that document
to get notifications when it changes. But in this case, we’re just
talking about a simple time driven trigger. I should say I feel free to
jump in and ask questions as you did, anytime. Yeah. AUDIENCE: Let’s say you
have a domain, 15,000 user. I don’t know, 100. I know there’s quotas
for the use of apps. I found really–
well, I don’t know, I’m not a developer– but
difficult to calculate the triggers. If there’s a lot of action
going on on that folder, how do I calculate the amount
of in and out transactions or whatever? PAUL MCREYNOLDS:
That’s a great point. So I– Linked in
our documentation, are the quotas for
each of our APIs. When you write a
script in this way that runs as the developer,
that is something you have to think about because
it will be the developers quota that will determine
how many of those calls you can make. That becomes an
architecture question. Now, it’s very
unlikely, for example, that you as a developer
would write a script that was processing a folder that
was being accessed by everybody in the organization. On the other hand, you might
be writing an application that lets somebody configure
a folder or create a folder for which they wanted
to receive notifications. At that point, you’d be
writing a script probably that ran as that user. And it would be that user’s
quota who is getting absorbed. These quotas are designed to be
permissive enough that you can get done what you need to do. And typically, your
architectural decision about who the script
should run as will dictate that it run as the
user in those cases where there are going to be a
lot of people impacted by it. But if you did find yourself
in a situation– say, you were building a web
app that used your OAuth token to access some
company wide data and present it to the user. If that became a mission
critical application, you might have to
think about how you would have that
run as the end user instead, and use their quota. When we get to the
roadmap portion, I’ll also talk about
some of the work that we’re doing on quotas
to make them more flexible. OK. So in the second
step, we’re going to contain our bound scripts. That’s a script that’s attached
to a particular document. We are still working
on the same context. We’re planning a conference. We’re still operating
on a folder in Drive, but we’ve also got
a sheet that has some metadata about the
decks, about the sessions that are reflected in that folder. And so for every
session we’re going to have the title of the
session, which will also be the title of the document,
a link to the slides, a user name of the owner,
and then comma separated list of approvers and
people who have approved. And so if this is
used correctly, which I have coded no checks to
ensure that it is, but wanted to keep it simple,
the approvals column will be a subset of
the approvers column. That it is to say if John, and
Tim, and Lisa need to approve, and John and Lisa have
approved, then you’ll see John, comma Tim,
comma Elisa in approvers, and John comma Elisa
in in approvals. All right. So you’ve set up your sheet. And you want to
write a script that has a special
relationship with a sheet. To do that you go to the Tools
menu and click Script Editor. All right. So here’s our script. Firstly, we’ve got the
familiar folder ID. Global. We’re still working
with that folder. And then we’ve defined some
globals for column indexes. Please speak up if you’ve
asked Apps Script before and you have other ways
of doing these things. There are many ways
to skin these cats. And we’d love to hear from other
folks how they do these things. The other thing that
we’re doing here– and this is one
of the cool things about working with JavaScript. Gmail app is just a global
in the JavaScript namespace. I hear we’re going
to stub it out. We’re going to replace it
with our own implementation. And that lets us
write a script that works with a sensitive
API that sends email to people with the confidence
that the first time we run it if there’s something
wrong, if there’s a typo, we’re going to be the
ones who see it instead of the recipient of the email. And so this is going to
leverage logger.log which is just a simple logging tool. I’ll talk a little bit later
about more sophisticated logging. And then there will be
a session at 4:00 today that goes all the way down the
rabbit hole on our Stackdriver logging integration, and
that will be over in Moscone. But here is simply
stubbing out send email so that our script doesn’t
actually send e-mails until we’re ready for it. All right. So first thing that
we’re going to do, we’re going to call a
spreadsheetapp.getactivesheet. I call this out earlier. This is the way that you
get that document with which you have that
special relationship. I’ve abstracted
out find new files. It’s the same code
we saw previously. It’s going to leverage
property service to keep track of where we
are, and to identify new files in the folder identified above. The new code here
is that we’re going to iterate over those
new files and we’re going to add a row to the sheet
for every file that we see. And so the design
here is that you might update the sheet directly. But if not, you’ll
get a row added as people drop their
slides into the folder. And then we’re going to iterate
over all rows of the sheet. And we’re going to do– we’re going to use an
email based workflow. And so process row
is the relevant call. And I’ll show you what it does
for each row of the sheet. Just jumping back
briefly for some minutia, and you’ll notice that
rows are zero index, but we have a header row so
we’re starting with row 1. I should say there are other– so we’re calling
getDataRange().g etDisplayValues() That’s going
to return two dimensional array of the values of the
cells in a sheet. There are other methods that
allow you to grab a range. Those are not zero indexed. Like sheets, those
are one index. But when you’re operating on a
JavaScript array, zero indexed. All right. So here we’re doing
some quick housekeeping. We’re making sure
this is in good order. And so we’re going to check that
there’s an owner for the row. And so when we discover a new
set of slides in this folder, we’re adding an
entry in our sheet that has the title and
a link to the slides. That obviously doesn’t
have any of the metadata. That’s only stored in Sheets. And so we need to let
somebody know that those rows need to be populated. And so we will
look for an owner. If there’s no owner, then
we’ll contact the owner of the script, which is you. Once we know the
owner, then we’re going to look and see if we
have approvers for the slides. Did I do that wrong? OK. So edit. Approvers column index on
line 73 should be on line 64. And approvals column index
should be on line 73. Apologies. And then we’re going
to check our approvals. And so this is the
actual business logic. This is the workflow here. So quick use of
JavaScript split function to split up our approvers
and our approvals values. So we have arrays of people who
need to approve this and people who have approved this. And then we’re going to
iterate over our approvers. And we’re going to check
whether they’ve approved. Any approver who
hasn’t approved is going to get a
notification reminding them to hey, go check
out these slides, we need your sign
off for this session. OK. So now we’ve got some
data in the sheet. It may have been populated
automatically by our script. Or it may have been
populated manually. And we’re going to add a
new session to our folder. Again, just showing you how
you would run this manually. We’ll run the process function. Here, we get much more
substantial logging. These are log messages generated
by our send email stub. By our send stub. And so what you can see here is
that this is a lot of activity that would have happened in the
wild that would have generated real email messages if we
hadn’t stubbed out this method. It’s a great idea for
testing to put it in the logs first and make sure
that these messages say what you want them to. And we see that our
sheet has been updated. We’ve added a new row
with a link to slides. Also in those logs, you
would have seen messages going out to you
requesting you to populate the owner for this new row. All right. So in the last step
here, we’re going to build a front end
for our workflow engine. And so we’re going
to build some UI that is no longer running
as the developer, but that runs as the end user. Who’s going to be somebody
who’s involved in the conference that we’re planning, and who has
approval responsibility for one or more of the presentations. We’ll show them what
they need to approve, and we’ll let them
approve in the UI instead of going
directly to the sheet. OK. So first thing that
we’re going to do, we’re going to add
a new script file to isolate the logic
related to our web app. This is going to
be a script file– we’ll just call it web app– that defines a do get method. And sorry, it’s covered up
by the orange box there. But that’s the magic
name of a method and a web app that responds
to an HTTP get request. You can also define
a do post for posts. Again, we’re grabbing
the active sheet. This do get method
does two things. It renders the list of sessions
that a person needs to approve. It also processes
the request that’s generated when somebody
clicks the approved button. And so here, we’ll
see some logic that’s checking if an
approval is being granted. And that will be in the
form of a URL parameter with the document URL. And so we’re going to
split up the document URL, and we’re going to extract
the ID of the document that’s being approved. And so the request coming
in to this would be your script’s base
URL, question mark, and then your doc
parameter– that’s just doc as we’ve defined it– which will have a URL in it. Which will be the
URL of the slide deck that you are approving. We know the URL of
a slide deck has an ID for the document in it
before the trailing slash. So we’ll split it up and
we’ll take the second to last portion of that URL. And we know that
that’s our document ID. So this is just how
we grab the document ID that’s being approved. Then we’re going to call
session dot get active user. And this is interesting. This is the part
where we identify the person at the keyboard. This is not you, the developer. But this is the person
accessing your web app. Because that person has a Google
login in and is signed in, their identity is already known. They will see an OAuth
prompt, because we’re going to be looking at
some data on their behalf. But the log in flow is already
part of their Google login. And so we’re going to
grab that identity, and we’re going to
get their user name as part of their email address. All right. So now we’re generating
our response. And the loop that we’re about
to walk through does two things. If this is the document
that’s being approved, we’re going to
update the approvers. We’re also going to add
every document to an array of approvable documents. And we’re going to use
that to then render an HTML response that
shows the list of documents that this user needs to approve. All right. So this is an
example of using the get range method on a sheet. Those methods are one index. Not zero index. Or just an important
detail to remember. And we’re going to grab
the approvers value for the current
row of the sheet. That’s who needs to
approve this session. We’ve abstracted out
parsing the approvers. A little bit of a mouthful. But we’re going to
split on commas. And for the empty
string, we just have to handle the case where
we wind up with an array, with one empty string in it. In that case, there
are no approvers. And so just splitting
up on commas. Here we’re asking,
is the current user an approver of this document. So remember, we’re iterating
over all rows of the sheet. We’re only going to do
anything for those rows where this user needs
to approve our slides. And we’re going to
grab the approvals. And then we’re going to– why are we checking again here? Right. OK. So we are iterating
over our approvals. And we’re seeing
if this approver has approved this document yet. If not, then we’re
going to include it in the approvals list. Excuse me. We’re going to add it to the
approvals list regardless. But we’re also going to
stash a value that tells us whether it’s been approved. All right. Lastly, we’re going
to build an object. And so here, just for
every row in the sheet that represents a document that
needs somebody’s approval, we’re going to have an entry
and an array that tells us the title, URL, whether
it’s been approved, and then the URL that you would
have to get to approve it. And that’s the URL
with the doc parameter that we know how to
process, so that we can approve an entry in the sheet. OK. Cool. We’ll look next at
how we do templating. So here, we are grabbing
our HTML template. And we’re shoving the data in
approvables into that template, and rendering it. This is just a
classic separation of controller and view, of
business logic from the view. And so here we know what
our approvables are. And we know we’re going
to write a template that knows how to render
those out to the client. And so we write this out. It’s boilerplate. We call it template.evaluate. And that’s going to
return our template HTML with values populated. So now I’ll show you what
that template looks like. All right. So you’ll create
a new HTML file. We’ll call it page. This part gets
written out for you. Basic HTML. The interesting part here
is the code that we write. And so we’re going to
render a table that has a list of the documents
that need to be approved. It’s going to have
an action button that lets you as the approver
approve a document. So here what you’re saying
is JavaScript code embedded in HTML, angle
bracket, question mark is the escape
sequence to do that. And so approvables
here, if you’ll recall, we’ve passed in to our template. And so properties
of the template are accessible from within
your HTML template code as JavaScript variables
using that escape sequence. And so template.approvables
sets that approvables array on the template. And it’s this for loop here
that iterates over those approvables. Now once we close the escape
sequence, we’re back in HTML. And so we’re just
writing out a tr for every document in our list. And then we’re going
to render the Control button to approve the document. Or we’ll render out a checkbox
if it’s already been approved. And so you’ll see here in the
second block of the statement, if this document
hasn’t been approved, then we’re going to
render an empty check box. And that’s going to link to that
approval URL that identifies this document as the
one that you– again, you, the end user of the
application– want to approve. And then we’re
going to render out the title with a
link to the document so that you can review it. Close our curly braces. OK. So in the Publish menu,
you’ll find Deploy as Web App. This is how you tell Apps Script
that you want this code to run in response to HTTP requests. And to give you a
well-defined URL that users can go to
use your application. This is all it takes. You write some HTML, you
write some business logic, and then you deploy
it as a web app, and we’ll tell you where users
go to access that application. So a couple of key things here. Execute the app as the
user accessing the web app. This is designed to be
a user centric view. And this gets to your
question as well. This is a case where
your quota is not going to be absorbed as a developer. If this were not conference
planning but vacation planning, and everybody in the
company were using it, then you would only be
absorbing end users’ quota as they hit it. And then who has
access to the app? We’re going to allow
everybody in the domain to access the application. And then we get our URL. And so this is the
application URL. You may choose to map this
with a URL shortener, or even a subdomain. That’s one way that we
see folks kind of putting the stamp of approval
on an application that’s built using Apps Script. So that folks know that it’s
sanctioned by IT, by the folks who control the DNS record. All right. So now we’re experiencing this
web application as a user. The first step is going to be to
review the permissions that it requires. Again, I’ll talk about
how we’re working on this. This is a long list
of permissions. The big ones,
though, that you see are Gmail, Drive, and View
and Mange Spreadsheets. And so here’s what I see. These are the presentations
that need my approval, where I’m in that list of approvers. And neither of
these is approved. And so I have a checkbox
next to each of them. After reviewing
this presentation, I click the checkbox. Page loads. We render again. And this time we
render a checkmark instead of an open box because
I’ve approved this document. And here we see I’ve been
added to the approvals column of the document. So the spreadsheet
is up to date. What we’ve really done here is
we’ve replaced the spreadsheet as an interface to our
application with a web application that’s
tailored to the use case. And it also prevents
the user from doing stuff they ought not
to do with this sheet. Great. So that was a walk-through
of three key deployment types in the context of one use case. And just wanted to quickly
touch on stuff that didn’t get swept into it. Because there really is a lot. This product has been
around since 2009. And it’s grown significantly. So built-in APIs, we
saw a few of these. We saw Drive app, we
saw Spreadsheet app. There about a dozen of
these built-in APIs. They are designed to be
ergonomic and accessible to people who don’t
write a ton of code. In most cases, those APIs
mirror and are basically wrappers around a
public rest HTTP API that you can use to
talk to these services. We’ve packaged them up in
an ergonomic, easy way. If you’re familiar
with J2EE Beans then the method naming pattern
will be super familiar. It’s a great way to
quickly automate G Suite. We also give you access to
other Google and GCP APIs as advanced services. Those are APIs where you
do effectively hit them in the existing RESTful form. We just put some niceties
around them in Apps Script to make it easier
to make those calls. We define google.script.run. This is in the
context of add-ons. Specifically, again, if you’re
around for the next session, you’ll see how this can
get used to generate a call from the client side that
runs code on the server side. This is also what you
would use in App Maker if you wanted to run a server
side script in response to a client event. Super easy to call any
function that you’ve defined in Apps Script from the client. The 04:00 session over in
Moscone, we’ll dive deep. Our Stackdriver
Logging integration is currently in EAP. Folks who are in
the App Maker EAP will have access
to this feature. And it lets you log messages
using the familiar console.log method. And those messages go
to Stack driver logging. And there, they’re available
for analysis and for monitoring. It’s a super powerful tool for
monitoring your application. And I would recommend
it highly for anybody who’s doing something
sophisticated or that has lots of users. Logger.log becomes
cumbersome when you’re trying to work with
more than one end user. Add-ons for docs,
sheets and forms. That’s the next session. Custom menu items,
sidebars, and dialogs for doc sheets and forms. And so you can define
these in an add-on if you want everybody
to be able install them. Or if you just want
to build some UI around a particular
document, you can– you still have access
to the same methods. You can add menu items. You can add a side bar
that has your custom UI. You can trick your sheet
out to your heart’s content with these custom
UI elements in Sheets. You can find custom
functions for sheets. We see that’s used a lot in
vertical-specific use cases, especially around finance,
but others as well, to extend sheets just with
custom functions that do stuff that the built-ins don’t. Both event and
time-based triggering. We talked a little bit
about both of those. And then you can use
our execution API with an OAuth token that
you obtain or generate for the user that runs in
the context of that OAuth token, the outstrip code
that you’ve defined. So this is a way that you would
build a RESTful web service with Apps Script
that does something more than existing G
Suite APIs do for you. That interleaves
functionality of G Suite APIs, or possibly brings data in from
an on-prem or a third party source. Here’s the full list of,
on the left hand side, built-in services. Some additional script services
for programming primitives and JDBC. And advanced services. These are RESTful APIs that
are scriptable in Apps Script without using [INAUDIBLE] fetch
to do explicit HTTP requests. All right. The fun part. The lawyers literally
made me put this up here. This is the usual. This is where we
think we’re going, and the timeline that
we think we’re doing it. No formal commitment. But the stuff we’re
doing is important. So I hope we do it on
the days you see here. So the first thing
that we’re working on is a step toward improving
developer and end user visibility into the
behavior of Apps Script. Often, your scripts run in
the background in response to triggers that
don’t have a UI. And whether to bug them
or just to make sure that everything’s
going well, you want to see basically
your top, you want to see your process list. And so this is something
that’s currently in TT. You can go and see all
of the scripts that are running as you– various filters available
with their result. This It’s great for debugging. But it’s also great for as
an end user, understanding what’s going on
with the application that you use in your day to day. We’re working on a new
management and deployment API. This is geared toward
more sophisticated users of Apps Script and more
sophisticated workflows. This is the way that you would
use your own developer tools and in particular
your own source code management
with Apps Script. This API will let you push
scripts in to Apps Script, and deploy your Apps Script
project programmatically. You can use this also to
do continuous integration and automated testing
with Apps Script. Write your code, commit
to version control, push it up to your dev server,
deploy your dev server, make sure it works,
push that code up to your production server,
and deploy production server with the new code. Explicit OAuth. So this is something I
mentioned a couple of times. Today, Apps Script
infers the OAuth scopes that are required to do the
things that the script does. And so if you use
Drive app, you’re going to request Drive scope. If you use Gmail app, today,
we request full Gmail scope. This is a way that you can
be more narrowly tailored. And it’s just a first
step toward being better about requesting kind
of a minimum of access that an application needs. Which is something that
everybody cares about. This first step, you’ll be
able to list out the scopes that you want as
an override to our automatically determining them. So you’ll be able to narrowly
tailor those scopes to exactly what your script is doing. Just announced this morning,
Gmail add-ons is in TT. Like docs, sheets,
and forms add-ons, Gmail add-ons will be
written in Apps Script. Unlike docs, sheets,
and forms, Gmail add-ons use a domain specific
markup to render cards. And the cool thing about that
is that Gmail has written the code that renders the card. And that code runs on
the web and in mobile. And so you as a developer
of a Gmail add-on don’t have to think about
what’s your UI going to look like in all
these different contexts. You just render a card
using their markup. And it displays images
or links or lists. Lets you add a bunch of
richness around a message that matches your criteria
for running the add-on. We’re really excited about this. SlidesApp and Slides add-ons. So we’re going to have a built
in API for scripting slides. It’s long overdue. We’re really excited
to have it landing. And also the ability
to build add-ons for Slides in the
same way that you do for docs, sheets, and forms. There is a short link here if
you want to sign up for the TT. If you use slides heavily,
please give it a look. Batch and parallel jobs. This is partly in response
to quota concerns. One of the things
that developers run into when they try to
do big jobs in Apps Script is the 30 minute or
six minute execution time limit, depending on what
context you’re running in. We are building out a service
that lets us still maintain some of those important
assumptions about the longest that a script
could possibly run, but while letting you
kick off a big job and not have to worry
about how it gets split up between Apps
Script processes. And so you’ll pass
in an array of tasks that are just units of work. You’ll tell us what
function you want to run on each of those units of work. And then you’ll tell
us what function you want to run with the results
of each of those units of work. And we’ll split that up
among Apps Script tasks. We’ll parallelize them to
the extent that we can. This is intended to serve the
use case of large batch jobs that would normally take a long
time or consume a lot of quota. And then lastly, and a
little bit further out, we’re thinking about more
developer and admin visibility. And that’s kind of a high
level view of script health, without having to
dive into logs, or just look at the
most recent execution. To point you into
the execution history you see in the
process dashboard, but to let you easily navigate
from lots of different scripts you work with. See adoption of
your scripts, get notified of
deprecations, et cetera. We are in the earlier
phases of that project. But we think it’s super
important to let developers– and the downline admins
have a lot of visibility into what’s happening in their
domain, whether their scripts are working, whether they’re
calling deprecated APIs and so forth. All right. Just a few quick
links to resources. Depending what level
you’re working at, a great intro here first on the
list, thanks to our own Wesley, is at 1sXeuD. I’ll leave that up for a second
so folks can snap a photo or write it down if you want
to check any of these out. [MUSIC PLAYING]

Author:

14 thoughts on “Using Google Apps Script to automate G Suite (Google Cloud Next ’17)”

Leave a Reply

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