Automating internal processes using Apps Script and APIs for Docs editors (Google Cloud Next ’17)

[MUSIC PLAYING] SAM BERLIN: Hi. I’m Sam, and this is Maurice. We’re both leads
on the Sheets team. I lead the creation
of the Sheets B4 API, and Maurice led the Slides API. And we’re here to
talk to you today about using Google Apps
Script and the G Suite editors to make your life easier,
automate internal processes, and really just make things
simpler and better for you. So we’re going to walk
through some demos today. We’re going to give an
appetizer demo and then a longer run-through
of a process that an HR person might
use to hire people. And I’ll let Maurice talk about
his demo when it comes to him. And our goal today
is really to show you how you can use App Script
and spend less money getting the stuff you need to do done. Companies spend billions
of dollars a year automating their workflows. And that’s not even
mentioning the time they spend figuring
out how to do it. They have to contact
people to build it and work through everything in
order to get it all done. With Apps Script, we can
take a lot of the pain away. These are some stories
of some customers that have used Apps Script
to make their life better. My most favorite one, by
far, is the very last one, where a company simplified
a horrible revision merge nightmare of taking all
these different files, merging them together, and then
creating some kind of output. And they simplified
their lives by using G Suite to automate it,
generating a PDF at the end. And what previously took
about 40 hours a month now takes under three
and saves them, they estimated, over
$100,000 a year. And a customer literally said–
this is a natural quote– finally I can take a vacation. So our goal here
is for you to be able to take a vacation
from automating all of these workflows. So here’s a simple example. Everyone likes parties, right? So imagine that you’re
throwing an office party, and you want to send out RSVPs. You want to analyze
who responds to it, what parts of the
company they’re from. Are they from sales, are
they from engineering? Book a venue based
on the results. Do you have enough people
that can fit into one? Do you need to split it
up into multiple venues? What kinds of places do
people like to go to? Traditionally, this would
be a lot of manual labor. You’d have to pore
through all the results, figure out how many
people there are, where they belong
in the company, maybe what office they’re from,
where you can host things. Or instead of doing that, you’d
purchase some turnkey solution, which would cost a lot of money. But realistically,
office parties aren’t all that important. So you’d probably just
have some poor person doing the most
minimal thing possible and throwing a
rather generic party. With App Scripts
and Docs editors, things are pretty easy. You can create a Google
Form, collect user names when sending out the form,
get all your results back, and associate the
form with the sheet. In Forms itself, you can
analyze all the responses. You can see 66% of people
said yes, they’re in. 33% said, no, they’re not. And in Sheets, it’ll sync
right back up into Sheets, and you can look and
do analysis in Sheets if you want to do it in
a slightly manual way, maybe using Sheets Explore
or asking questions of it. But you can also
create an Apps Script associated with the sheet in
order to do things for you. So here’s a 20-line
example of how you could create a very
straightforward app script that looks at all of your data
in the sheet and figures out who’s in sales,
who’s in engineering. So I’ll go through each line and
explain what’s happening here. So at the top, we
use the Groups app. And we get the [email protected]
and the [email protected] groups. And then we get the
active spreadsheet, because the script is
associated with the spreadsheet. And we get the data
from that spreadsheet up until the last row of data. And we start the engineering
and sales counts at 0 and then loop through
all of our data. And we say is this
user in engineering– by asking is the engGroup– does
this engGroup have this user’s email address. If not, are they
in the salesGroup? Does it have that
user’s email address? And we just increment the sales
and the engineering variables by one. And then at the very end, after
we’ve collected all of that, we go back to the spreadsheet,
and we get the sheet name with results. And we get a range where
we want to put the data in. We say this is how
many people were eng, this is how many
people were in sales. And so this is a very
straightforward example of how you can use Apps Script
just analyzing your data sheets automatically,
associating it with groups, and putting your data
back in the sheet. So all of this
code is JavaScript. It’s all stored in the
cloud with revision history. So if you make a mistake, you
can go back in time and fix it. And it’s hosted and
run in the cloud, too. So you don’t have
to spin up any jobs, have maintenance deal with
having all of your servers running, seeing if anything
goes wrong with them. And it’s all secure, and
it comes with G Suite. But automating parties
isn’t what brought you here. Businesses have a lot
more critical tasks, like hiring people. There are a lot of
steps to hiring someone. You have to send offer letters. You have to collect
employee information. You have to have them sign NDAs
and verify their employment history, and so on and so forth. So I’m going to walk through a
demo of how you could automate the process of hiring someone
if you can switch to the demo machine. So here’s a spreadsheet. And I filled it in with some
basic examples here of employee name, salary, manager
name, the team, and whether or not an offer letter was
sent, the offer letter URL, if they signed an NDA,
and so on and so forth. There’s an extra column in
here for their email address, which I’ve kept hidden. Because I’m going to,
right now, actually make this an interactive demo. And if you’d like to, you can
see that there is a URL here–, capital
PDW, capital EQT. And if you want, you can go
to this place and fill it out. Just remember, it’s
all being recorded, and you can fill out a
fake name if you want to onto the employee tracking bit. So the idea here is that– let’s
say there is an HR manager. And they have this
spreadsheet with their data and a form for new hires. And they want to send this
out to hiring managers and say is there anyone
that you want to hire. If so, fill out their
email address, their name, their salary,
their manager name. You can look what the form
looks like from the manager’s perspective. And it’s just here. You see a little form where
they can type the stuff in and submit the results. Others– let’s go
to Google Forms. And the form is synced
up with a spreadsheet. So the results come
into the spreadsheet. At this point, you can click
on this Workflows, which is this custom menu item that
you added with Apps Script, and click Send
New Offer Letters. What that’s going
to do is iterate through the spreadsheet, similar
to how we did the party example and create a
customized Google Doc for the employee of
their offer letter, and then email it to
them with the link to their customized Google Doc. Afterwards, it’ll mail
that to the employee. And the employee will basically
see, in their email, the link to their doc and be
able to click on it. And so I’ll show you
what that looks like. So here’s one user. It says you’re hired. Dear John Doe,
congratulations, you’re hired. Please view your offer letter,
which includes the next steps, at this URL. You open the URL, and you
see this customized Google Doc for John Doe. And it has their
salary, their name. The title is specific
for John Doe. And it says to
continue the process, please fill out this NDA. So we click on this link. This NDA is hosted
by App Script. App Script is acting as a
web app in this scenario. And here’s where it goes. It says here’s an NDA. Click here to sign it. When you sign it, it
comes back and says thanks for signing the NDA. And that was also
hosted by App Script. And you can see that
the Apps Script then filled in that yes, the
NDA was signed by John Doe. And none of the others
yet had filled it out. And if, for example, I
see some new people filled out the workflow or the form– so if you click Send
New Offer Letters again, it will figure out who hasn’t
had an offer letter sent and send new offer letters
to each of these people, and update with the yes
and the customized Google Doc for each of them. So I’m going to walk through the
code that makes this possible. So there is a script editor
associated with every sheet. And when you open up
the script editor, you see the code
which runs it all. And there is a bunch
of functions in here– add menu items, send
new offer letters. And I’ll walk through
each one in turn. So add menu items
is the first one. And this is how the
workflows menu item got added into your spreadsheet. Every Google Doc– or I’m
sorry, every app script has this Current
Project Triggers. You can edit triggers
with your Apps Script. And here I’m saying when I
want to run the Add Menu items function, when there’s an event
from a spreadsheet on open– so whenever anyone
opens the spreadsheet, it will run this arbitrary
function that I made and run this code. And what this code
is saying is give me the UI, this spreadsheet
app UI, and create a menu in the UI, the Workflows menu. And in that Workflows
menu, I want to add an item whose text
is send new offer letters. And when someone clicks
send new offer letters, it will run this function,
send new offer letters. And then I want to
add it to the UI. So send new offer letters–
what does that do? In send new offer letters, we
get the active spreadsheet. Because this sheet is run from– the script is run from a
spreadsheet when they clicked the button in the UI, there’s
an associated spreadsheet, and you can get it, and then
pop up in that spreadsheet a toast saying
sending offer letters. Then we get the active sheet,
which is the employee tracking sheet, and the last row,
which is the last row of data in the spreadsheet. We get the range of all of
the data that we care about– the start row from the
start row and column to the number of rows, which
is the last row minus 1, and the number of columns
with the data we care about. Then we get the
display values of each of these things,
which is how they’re presented in the UI itself. Then we iterate through
each of the bits of data and get the data for each row– the row number, the name, the
email, salary, manager, team, whether or not the
offer was sent. And if the offer
sent is not yes, which is how I was able to
click it again and only send new offer letters– if they haven’t said yes
that an offer was sent, then we create an offer letter. And I’ll go through that
function in a moment. So after we create
the offer letter, it gives us the ID
of what document was created for
the offer letter, and we create this URL–,
document offer ID, and then a message that
we’re going to email– dear so-and-so, congratulations. You’re hired. Please view your
offer letter, which includes the next steps
at your offer URL. Sincerely, HR. It’s a very customized message. So then we use Mail App, which
is one of the other apps that’s built into Apps Scripts. There’s many that
exist, and Maurice is going to go through a few more. But Mail App is one
that you can send mail. So you say mail app, send
email with the message. The email is the user’s email
address, the subject of You’re Hired, the message,
and just a little token saying we don’t want
people to reply to this. And then we get the range in
the spreadsheet saying yes, we did send an offer, and here
is the URL of your customized offer letter. We increment the
amount of letters we sent, flush all of the
changes out to the spreadsheet so it puts it in
the spreadsheet, and then pop up a
toast saying either you sent this many letters or
you didn’t send any at all, because there was no
new letters to send. So how did we create
the offer letter? So we have a
customized Google Doc, which is the template
of the offer letter. And in this template,
we basically say welcome to the
next demo company. Hello, Name. We’re thrilled to
offer you a job. Your starting salary
will be salary. Just basically a spreadsheet–
or I’m sorry, a doc with tokens that we’re going to
replace with customization. So in the script, we– using our Drive app this time,
we get the file of our ID with the ID of our template,
and then make a copy of it. On the copy, we then
change the sharing, and we say this copy
can be visible to anyone who has the link, but all
they can do is view it. And then we change
the name of that file to say offer letter
for so-and-so. Then we use Document App,
which is the hook into Google Docs within Apps Script. And we get the doc
by Document App, and get the body
of the document, and then just do a
bunch of replacements. We say for your name,
put the person’s name, for their salary, put the
person’s salary, manager, team. For the NDA, it’s a
little more complex, because we want to
do text and URL. So we get the starting
point of where we want to replace
text and generate a URL and then say on this
portion within the document, set the URL to
this NDA URL using this other function that
I’ll go through in a moment, generate NDA URL. And then return
the offer file’s ID so that the email that we
generated can link to it. So how do we
generate the NDA URL? Because if you remember,
that was served, I mentioned, by
Apps Script hosting as a web app in this scenario. So generate NDA URL uses
this script app, yet another thing that’s
associated with Apps Script, which is the script
itself as a service. And you can get the URL of
the script app as a web app and return this,
saying this is the URL, and then append some parameters
to it saying here’s your name, here’s your email address,
and here’s your row. And you notice I use this encode
URI component, which is just part of JavaScript, because this
is a JavaScript environment. So you can just call JavaScript
functions and make them work. And there’s various other
bits in generate NDA URL, such as whether or not they’re
actually signing it or just viewing the NDA. So how do you get Apps
Script to act as a web app? It’s pretty simple. You publish it by clicking
this Publish button, deploy as web app. And then here you see do you
want to deploy it as a web app. And it gives you the
URL and gives you some information of the version,
who it’s going to execute as, who can access the app. And in order to
serve it, you just create a function called Do Get. And Apps Script
will automatically run this function
when someone hits the HTTP request at that URL. And we can get parameters from
it, such as the URL parameters. We get the name, the email
parameter, the row parameter, and the signed parameter. And we just do some
basic validation here, saying whether or not– if the request didn’t have any
of these fields, we say sorry, we couldn’t verify
your employment. But if they did have
those fields, then we want to hook into the
spreadsheet a bit more. So if you remember back
at the top of the example, we used spreadsheet app,
get active spreadsheet, because the spreadsheet was
associated with the script since you were running the
script from the spreadsheet. In this scenario, you’re
not running the script from the spreadsheet,
so you can’t say get active spreadsheet. But you can just load
an arbitrary spreadsheet with that spreadsheet ID. And then we say,
give me the sheet by name of the employee
tracking sheet. And we get the row
that the employee said that they were at from the URL. So in this scenario here,
we’re not using great security, because we just put an arbitrary
name, an arbitrary row. And someone could theoretically
change their row number, and just troll through, and
try to figure out who you’re hiring, what row they’re on. In a better example,
you could imagine that you could generate
a random number, put that random number in your
spreadsheet, put it in the URL, and all of those things
have to match up. But for demo purposes, this is
a little more straightforward and easier to walk through. So here we get the values
of the data in that range and verify that the name
they gave us and the URL matches the name in the
spreadsheet at that row. And if it doesn’t, we say sorry,
we couldn’t verify things. If they’ve already
signed the NDA, then we’d just say
you’ve already signed it. No more to do. But if they haven’t signed it– and this is the request
to do the signing– you said click here to sign
it, and they clicked it to sign it– then we update the spreadsheet. We get the row, the column
that is the signing, and say yes, you signed it. And you can see back
in the employment that yes, they’re signed,
and some other people have signed it. And we spit back some
HTML saying thanks for signing the NDA. If this is not the signing–
if this is serving of the NDA– then we generate a URL
for actually signing it and create some HTML output
saying here’s an NDA. Click here to verify it. And that’s it. It’s less than 200 lines. It’s about 167 lines
and a full workflow of how you can automate
with forms, sheets, docs, create templates,
customize things, serve Apps Script as a web app, and
do all of these things that make life a lot simpler and
easier using Apps Script. And if you can go back to
the presentation, please. And that’s it for me. I’m going to– Maurice is going to continue
for the rest of it for his demo. Thank you. MAURICE CODIK: Thanks, Sam. Another common process we
hear a lot about is reporting. So this is a sales
rep named Jane. And the end of the
quarter is approaching, and it’s time for her to prep
her quarterly business review. So she needs to load up the
data on all her accounts. She wants to get it into Sheets. She needs to do some analysis,
and then finally, package it together into a nice
presentation she can give to her team
and to her leadership. Now, without any
kind of automation, this would be really tedious. She’d have to dig up the data. She’d have to find
the presentation she used last time. She would have to copy paste
the data from her new data onto the old slides. And it’s really tedious. You can make mistakes. You can copy paste
the wrong numbers. And at the end of the day,
it can look really bad if you make a mistake. These mistakes are
not easy to catch. So using App Script and
the G Suite REST APIs, we can make this
process a lot easier. We have a Google
Sheets REST API that was designed to make it
really easy to import data from external data sources
or third-party systems into Google Sheets. Last fall, we also
launched our first-ever API for Google Slides so you
can take the data in Sheets or another third-party system
and make a nice-looking slide presentation with
some simple code. So let’s walk through how we
can use these REST APIs together to make Jane’s job a lot easier. So the first step is to get the
raw data into Google Sheets. Now, once it’s there, Jane can
use the great analysis features of Sheets, such as Explore
or all the functions that come built in, to
understand the data, figure out what trends
she wants to highlight, and visualize it in
a good-looking chart, for example. So how do we get the data there? Several large enterprise
partners have already integrated with the Sheets API. And if you use some
of these tools, you can take advantage of that
and get the data into Sheets very easily. So earlier in the
conference, for example, we announced that Salesforce
launched a new Sheets integration where you can
import your opportunities from Salesforce straight into
Sheets and update into Sheets, and it will sync back and forth
between Salesforce and Sheets. So if you’re a
Salesforce user, you can use that and get your
data into Sheets very easily. So let’s say that’s
not the case. Let’s say you have some
data in internal service, an on-premises
database, something where you are on your own. Thankfully, these APIs are
pretty easy to work with. You can write a quick
program using the Sheets API, and take that data from
your on-prem database, and put it directly into sheets. So let’s take a look
at how this code works. Can we switch to
the demo, please? So here we are. This is a Python script I wrote. I won’t go through
all of it in detail. The bulk of it I copied from the
Google Sheets API Python Quick Start. And it has all of
this boilerplate code in the beginning. That takes care of initializing
the client libraries, going through authentication,
and making sure that the user gives
consent for this app to edit their spreadsheets. So this is boilerplate you can
get from our developer website. I won’t talk about
the bulk of that. So here is the
core of our script. So the first thing
we do is we have to get the data that we
want to put into Sheets. And here I have a
simple SQLite database that’s running on my laptop. I have two tables,
customers, and orders, and I’m using some SQL
to join in together and get the data
out that I need. So SQLite works
great for a demo, but this could be anything. This could be a
big data warehouse. This could be an
on-prem Oracle database. This could be an
internal ordering service that you need to call over HTTP. This could really be anything. Just plug it in with whatever
you need to get the data from. So now let’s go to the
sheet-specific parts of the script. So in this mean method, I
call this load data function that gets the data
from the database. I add a header
row to the data we get so that it’s easier for a
human to work with afterwards. And then I start
calling the Sheets API. So the first thing I do is I
call the spreadsheets create method, which creates an empty
spreadsheet in Google Sheets. Here I’m giving it the title
Imported From DB On Date so that it’s easy to pick
out from your drive later. And then after that,
I take the data that we got from the database
and turn it into the request that we eventually need
to send to Sheets to get the data there. So I do that with the
spreadsheets value Update Function. And for that, I give
it the spreadsheet ID where in the spreadsheet I
want this data to end up, so the range A1, just
the top left beginning of the spreadsheet. I tell Sheets that
I want it to treat this data as user-entered. So it’ll automatically–
it will behave as if you typed this data by hand. So it will understand that the
things that look like dates are dates, the things that
look like numbers are numbers. And again, we’ll make this sheet
easier to work with at the end. I also tell Sheets that my
data is formatted in rows. Just because we read
this from a SQL database, it’s naturally in rows. It’s easier to import. But if your data happens
to be shaped in columns, you can say columns here, and
Sheets will do the right thing. So in a pretty quick
script, I think I have 30 lines of
actual Sheets code. We can get data from
any data source, practically, that the script
can talk to on Google Sheets. So let’s give that a quick run. So it’s just a Python script. I hit Enter. And there we are. So it put in the URL
of the spreadsheet. I can load here. And I can open it up, and here’s
the data from the database. So I have the customers,
the name of each customer. And each row is an order
that that customer placed– so how many items
in their order, the total dollar
amounts, and what date the order was placed on. So based on this,
Jane can get started. She can do some analysis,
use some functions, and get her work done. Back to the slides. So the data is in
Sheets, and Jane can get to work doing the analysis. So the next step is to make
this into a good-looking presentation she
can give her team. So for this, we’ll use
the Google Slides API, and we’ll call it
from App Script so she can work with
the spreadsheet she’s working on and with the
slides at the same time. So using those things
together, in a few clicks, we can get a good-looking
presentation. So back to the demo, please. All right. Great. So let’s say Jane
did her analysis, and this is what she came up. She has a pivot table with the
top 15 customers she works with and their sales per quarter
for the past year sorted by their key force sales. She also has a chart that’s a
little squished on the display, but demonstrates how
the sales per customer has changed throughout the year,
and some other configuration data that we need to get the
slides ready– so what year we care about, what
quarter, her name, things that aren’t
necessarily in the data but end up in the
final presentation. So there are a couple
of ways you can trigger App Scripts from Google Sheets. Earlier, Sam showed you that
we can add a custom menu item where you could go
to the menu, click click, and it will run and App
Script function for you. Here, I created a
button, because I think big buttons are fun to push. So if we click Generate Slides,
now this is running my script and taking the data
from the spreadsheet, combining it with a
template that we created. And in a few moments, we’ll have
a nice, finished slide deck. So here’s the link to the
presentation that we created. And this is what we end up with. So this is a simple quarterly
business review presentation. It has the title slide with
the quarter we’re reporting on, the year. It has Jane’s name. We have an Executive
Summary slide that has the chart we came up with. It has the total revenue
for the quarter, how much we grew compared to our goal. We have a table describing
the top five customers and their quarter for sales. We have a slide with
the big, ambitious goal to grow 12% next quarter. And finally, we
have another slide that has a link to
this spreadsheet so that if you send this
presentation around, the people looking at it
can dive into the same data that you worked on and
either learn more about it or double check your work. So let’s walk through the code
that powers that big button. So there’s a lot that goes into
creating a slide presentation. So for example, here we had to– we chose a theme. We had to make sure the
colors are on brand. Sometimes, you want to
use some custom logos. So instead of writing code
to define all these things, it tends to be a
lot easier to work with a template presentation. So this is the template that
we used for this example. It has the five slides we’ll
end up with at the end. And we’re using some
variables in curly braces that we’ll use some
replacements that get the data from the sheet
and into the presentation. So here we have a quarter
label, the year, the name. On the executive summary,
we have the little variables for the total sales,
the direction, and we have a
rectangle that we’ll use to replace the
chart into that spot. We have the table that the end
has the top five customers. We have the slide
for a goal, and we have a slide that we’ll use
to link to the source data. So if you notice, the variables
already have styling on them. So instead of having to specify
all the styles by hand in code, you can make your slides
pretty close to what they’ll end up looking like at the end. You put the styling
on the variables, and the Slides API
will keep that styling when you do the replacements. So let’s look at the code
that creates the slides. So the bulk of the work
happens in this Export to Slides function. This is the function
that I’m calling when you click the big
button on the spreadsheet. And the first thing we
do is tell the user, hey, we’re working on it. We set some text on the ranges,
and we flush the changes out to the spreadsheet
so that users know that we’re making progress. The next thing we do is we grab
the data out of the spreadsheet that we need. Sam went through how this works. You basically use Spreadsheet
App to pick out ranges, and you can get the displayable
values out of those ranges. So I’m not going to
cover that in detail since we’ve already seen it. The next step is that
we use the Drive app. That’s one of the
built-in libraries inside of Apps Script. And that represents
your Google Drive. So we have the ID
of this template. This is the ID from the URL. And we have it coded
into our script. So we tell Drive app get me
this file, make me a copy, please give it this title. So this– we made a
copy of the template, and then we’ll use the Slides
API to modify that template to produce the
final presentation. So how do we talk to
Slides from Apps Script? So here we’re going to use
what’s called the Slides Advanced Service. So an advance service
is a thin wrapped around a Google REST API that’s
available through Apps Script. There’s a pretty large list
of advanced Google services. You can go to Resources,
Google Advanced Services. And here’s the big list of
the ones that are available. You see I have the Slides
API, and it’s turned on. And a lot of these things are– it’s a lower-level API. You get the full
power of the REST API. So it ends up looking a little
different than the built-ins, like Spreadsheet App, for
example, that was simpler. But here you get
a lot more power. I should mention that Slides
Advanced service launched on Tuesday. I actually turned it on while
I was on the flight over here from New York. So let’s get to the
Slides-specific parts of this. So the Slides REST API works
using a batch update model. You basically assemble
a list of requests, and you tell Slides please do
this, please do that, please do that. You send the big batch
over the Slides API, and all those
requests get executed in order and in atomic fashion. So the rest of the
script basically goes through all
the things that we want to do to the presentation. We assemble this big list
in Slides Requests, which is just a JavaScript array. And then we call the
presentations batch update method to actually
execute those changes. So I’ll go through what the
steps are in a little detail. So the first thing we do is that
we call over this Replace All Text method. So earlier, we took the
data out of the spreadsheet, and we added it to
the data object. And it’s just a key
value of the things we want to replace
in the template with the data we want
to replace it with. So the request for that
looks pretty simple. We look at all the
keys in the data map. We create a little object that’s
the Replace All Text request. We say Slides, please take all
of the text that matches text, and replace it with
this replace text. So this will go through
all of your slides and put the data in
for all those variables we had in the template. The next thing we do is
that we call this Replace Shape With Chart API. So if you remember,
in the template, we had a big rectangle
on the second slide that had the text YTD Chart. And we’ll use that
as the location of where the chart will end up. So instead of having
to mess with– I want the slide to be here, and
you have to nudge it a little– sorry. Instead of messing
with the chart and saying I want
this chart to be here, and fidgeting with
where to position it, you can put a shape
in your template, and then you can position
it where you want. You can see it in context. And you use Replace Shape
With Chart to actually put the chart in that place. So the request for
that is pretty simple. You say I want to
replace the shape that contains this text, which is
just a YTD chart in our case. We tell it the spreadsheet
to grab the chart from, which is just the
active spreadsheet this script is being run from. We give it the ID of the
chart, and we tell it we want this chart to be linked. So what I mean by
linked is that slides will remember that this chart
came from this spreadsheet so that if the underlying
data in Spreadsheets changes, there will be an update button
in Slides that you can click, and it will refresh the
chart automatically. So that way, your data
is always up to date. There’s also an API
on the Slides API to call refresh on
a chart for you. So if you’re working with both
the sheets API and the Slides API, you can change
some data in Sheets and then tell slides,
hey, there’s a new chart. Please pick it up. So the final thing we do
is that we add a link. From the last slide, we add
a link to the source data so people can take a look
at the underlying data that went into the presentation. So the first APIs
that we worked with, they worked in a find
and replace manner. So we just gave them
the text in a shape, and the API found it
and did the right thing. Some of our other APIs– are Object ID based. So I have to give slides
the ID of the object that contains the text that
we want to add a link to. So to do that, we call
the presentations Get API right here, and we fetch the
presentation from slides. We pick out the final
slide, and then we iterate through all the
elements on that slide. So we know that slide. We created it. It’s a title and body slide,
so it has two shapes on it– a box for the title
and a box for the body. And I iterate through
the two shapes on there, and I find the one that
is the body placeholder. So we know that’s the one
that has the text that we want to linkify, so we pass the
object ID of that shape into this update link function. So Update Link is
a function that updates the textile of that
box to add a link style to it. So we do links and
slides or textiles. If you wanted to do
other textile changes, like you want to make
the font different, you want to make
it bold or italics, you would do it in
a much similar way, using Update Textile. So that’s all the
requests that we needed to make the slides
look the way we want. So we replaced all our
variables, we added the chart, and then we added the
link to the source data. So finally, we
call batch update, and then we tell the user
we generated your slides. Here’s when we did it. And we add the URL to the
presentation back to sheet. So in all, it’s probably
174 lines of code, including the Sheets
stuff I didn’t cover. But with that, we created
a good-looking presentation using the data in the
spreadsheet using Apps Script. Back to the slides, please. So using these APIs
in G Suite, Jane saved several hours of her time. She avoided having to do
all this manual copy paste, and now she’s ready to give
this presentation to leadership. So that saved time. She can get to work meeting
her next quarter goals, or she can go outside and enjoy
the sunshine a little bit. So we broke up the steps
here to demonstrate what your options are. But really, you could
automate this a lot further. So for example, you
could have the import to sheet script run
automatically every quarter. You can have it– you can say– once
the data is there, you can send an email to Jane
saying hey, your data is ready. Please go take a
look and get started. You could– if
you wanted to, you could skip Sheets altogether
and have your program that queries the database do
the analysis for you, and then go straight to
Slides, because Slides is also REST API that you can call
from a third-party tool. So in summary, with
some creativity and just looking
around, I think you can find a lot of workflows
in your enterprise that can be automated with App
Script and the G Suite APIs, similar to the ones
we’ve seen today. And these are small automations. These are things that you
can write in an afternoon. You can write it in
a couple of days. But even though they’re
small, you can– the savings add up. If you do the same
thing for every hiring manager, for every
party organizer, for every sales rep
in your company, the dollar savings just
multiply and overall can make a big impact. So the demos we did show you,
they do require some coding. It’s pretty simple. Most of it is just JavaScript. We showed some Python
code today that was also pretty simple to write,
and those libraries are available in pretty
much any major language. So it does require some coding. It’s simple. It’s within the reach of power
users and IT professionals. But it’s not for everybody yet. So if you’re looking
for a low code way to do these kind of
automations, there’s a G Suite product
called App Maker that’s currently in early access. And that provides
a drag-and-drop UI that lets you create apps
without really doing much code. So if you’re interested in that,
you can request access to App Maker at And they also have a
showcase base at Mosconi through the end
of the conference. If you find them, you
can learn a lot more about how that works. So another good
resource for learning about these automations is
the transformation gallery. It’s a gallery that lists
several real world use cases of the G Suite APIs– and not even just the APIs, but
the applications themselves– and how you can
use them together to transform your business. So a lot of them come
with customer stories so you can see what
the real business impact was of deploying these
integrations in real life. You can check it out
at transformationga So wrapping up– App Script is a lightweight
tool that lets power users and IT professionals
write simple JavaScript to automate actions on top
of many Google services. So today, we showed you how to
script with Docs, with Slides, with Sheets, with
Gmail, with Drive. But App Scripts supports
many other Google services that we didn’t cover today. So for example, there’s
a library for Calendar, for Translate, for
YouTube, for Maps. And there’s also that
list of advanced services that give you more
powerful interfaces that are easy to use to
call Google REST APIs. So in general,
it’s easy to learn. It runs in the cloud,
and it’s available to you as part of your G
Suite subscription. It is part of the
glue that helps make G Suite into a true suite
of apps that work together. So there are also REST
APIs for Sheets and Slides that make integration with
third-party data sources easy. So they provide simple ways
to get your data into G Suite, where you can
collaborate on it, you can script it using Apps Script,
you can present it to others. So using App Script
and our Rest APIs, you can automate
many of the tedious process in your business,
save time and money, and let folks focus on creating
value instead of busywork. So to learn more about the
things we covered today, you can check out our
developer websites for Apps Script,
Sheets, and Slides, and the URLs are on
the screen right now. There’s a lot of
documentation available, and there are plenty of samples
to help you get started. [MUSIC PLAYING] as

9 thoughts on “Automating internal processes using Apps Script and APIs for Docs editors (Google Cloud Next ’17)

  1. Are the code examples for the automation examples shared in this video posted anywhere? Can't find on github or any reference on Google Cloud Next '17 site.

Leave a Reply

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