Google I/O 2011: Enterprise Workflow with Apps Script

Google I/O 2011: Enterprise Workflow with Apps Script

  29 Sep 2019   , , , ,

Zhang: So
before we get started, can I just get a show of hands
for how many people here work with workflows
on a daily basis? So most of you, and that’s
probably why you’re here. So you know,
by our definition, the workflow
is just the management of a sequence of steps
in a business process, and that can be anything
from a vacation request approval to a document approval,
to an expenses report. And so workflows really are,
you know, relevant to everybody. And again, by a show of hands,
how many people here are familiar
with Google Sites? So most of you, as well. And how many of you were at
the Apps Script talk yesterday? Okay, so about half of you.
So great. Just for those
that aren’t familiar, Google Sites
is just an easy way to create and share
web pages and websites. You can embed documents. You can share them
with your co-workers, and you can even publish them
on the web. Apps Script is our cloud
scripting service. It’s a service side
JavaScript language, and you can use it to extend
and automate Google Apps. So here’s the agenda
for today. I’m going to go over the basics
of Scripts and Sites, then I’ll talk about
a simple workflow, which is posting comments
to a site owner. Then I’m going to go over
a slightly more complex document approval workflow,
and then Evin’s going to talk to you about
advanced document approval. So let’s go ahead and start
with Script insights. So if you’re familiar
with Apps Scripts, you know that they are available
in spreadsheets, and you can use them to interact
with all sorts of Google Apps. But ultimately, I think
Sites really serve as the hub for workflows. If you think of your HR site,
or your team site, or your expenses application,
these are all sites in which these workflows live,
and so we’re happy to say that since the beginning
of this year, Google Apps Script
is available in Google Sites. With it, you can
now write scripts that interact with a site
directly, and you can create
these Script Gadgets that let you render dynamic
content on a site’s page. If you use Sites,
it’s actually really easy. You can use Scripts
to create pages, to share the site
to other people, to apply themes and templates,
to search. Pretty much anything you can do
through the site’s UI, you can do
with Apps Scripts. So now, there’s this top-level
Sites App class with it. You can grab a site object
by domain name and site name. Once you have a site object,
you can interact with the four basic
page types, which are WebPage,
ListPage, AnnouncementPage,
and FileCabinetPage. Themes can be applied
to pages or templates, and pages are mostly
standard HTML, so they’re really easy to create
and edit with Apps Scripts. You can also share
at the site level very easily. You can add and get each
of the roles, the user roles. So there’s Viewer,
Collaborator, and Owner. When you add somebody,
you add them by email address. And when you get
the users for a role, you get an array of email
addresses, so really simple. How do you use it? In the Insert menu,
you can see here there’s a new Apps Script Gadget
menu item, and that’s available
in the page Edit mode. That lets you insert
the Script Gadgets that dynamically
render content. They work just like
any other Sites Gadget. A lot of the properties
are the same, and there’s
some additional things that let you change
the behavior of Script Gadgets. In addition,
in the Manage Site pages, there’s a new Apps Script tab
that lets you see the scripts that you have in the site,
and you can launch the editor, which you’re probably
already familiar with, if you want to write
new scripts. So let’s go ahead and talk
about a simple workflow. So one thing that’s been
requested in Sites that still isn’t available
is the ability to email a site owner without
revealing their email address, and it turns out this is
actually really easy to do with App Scripts. There’s two parts
to this problem. The first part is
the rendering of this form. You can see what that might
look like on this screenshot. And the second part is
to actually grab the owners and send them an email. This is the code to do
that second part, which I would say
is the most important part, and it’s really
only two lines long. This is the
“sendEmail” function. That underscore means
it’s a hidden function. We’re not going to go
into that here. It’s not really relevant. But in the first line
of the function, we use SitesApp.getSite. We grab our site
by domain and name. We call getOwners
on that site object, which gives us an array,
and then in the second line, we join that array
into a comma-separated string. We pass it to
MailApp.sendEmail. We’ll set the subject line
to “hello” and the body of the email
to, and I’ll show you where
that comes from later on. But that’s pretty much it. That will send an email
to the site owners. So let’s talk about where
we actually get that email from, and this is the code
to render that form. We use the built-in doGet
function in Apps Script, and that means that we’re
going to be publishing this script as a service, which lets us render content
in the Script Gadget. In the first few lines,
we will use UiApp, and I hope, you know,
at least some of you guys are familiar with this. UiApp.createApplication
will create a UI application, obviously, that will let you
render content. We’ll set its title
to “Contact owners,” we’ll create a vertical panel
that lets you arrange things vertically on the screen,
set its ID to mainPanel, which lets us refer to it
later on, set its spacing. And obviously, you can make it
arbitrarily beautiful, but we’re going to keep things
simple here. Then we’re going to add it
to the app, so pretty basic. Then we’re going to call
Session.getActiveUser. That gets the user that’s
currently viewing the form. We get their email address,
and we will add a label to show who the email
is coming from, as the first element
in the mainPanel. Then we will create a text area,
set its height and width, and set its name to Email, and this is where that came from. Finally, we’re going to
create a submitButton. We’ll create
a server clickHandler that points to that sendEmail
function I just showed you. We will add the mainPanel
as a CallbackElement, and that lets us get
the content of the mainPanel in our handler. We’ll add the handler
to the submitButton, add the submitButton
to the mainPanel, and we’ll return the app
to render it, and that’s pretty much it. And for those of you that were
at yesterday’s talk, you’ll know that we know have
this Apps Script GUI builder, and that’s also usable here, but we wanted to
keep the code simple and make sure people knew
where things were coming from, so we went with code here. So let’s see that in action.
This is our Acme Corp. website. We’re going to create
a new Contact Us page, so that people can go there
to send us email, and this is just
standard Sites UI that you’re probably
familiar with. So this is our
Contact Us page. It’ll drop us
in the Edit view. We don’t have our Gadget yet,
so we’ll just save it. Now, if we go to More actions,
Manage site, which is at the bottom, we can see
that in our Admin pages, there’s now this
Apps Script item. Going there shows us the scripts
that are already there. We’ll launch the editor
and paste in the code I just showed you. And I think
that all looks right, so we’re going to save it, and we will call it
Contact Owners. So okay, so that’s it. So let’s go back
to the site. And on this Admin page,
if we refresh it, you can see that the Contact
Owners is now available. So going back to our
Contact Us page, we can now edit that page and add that Gadget
we just created. From the Insert menu,
we’ll go to Apps Script Gadget, and you see the Contact Owners
is now available. We’ll select that, and we get
a properties dialog. Now, this is actually
important. Because we want to allow
anybody who views the site to be able to send email, we’re going to allow anyone
to invoke this service. And you could have just
the members of your domain, or you even just yourself. At the bottom,
those are the standard Sites Gadget properties. We’ll set it to Contact Us,
and save it. Once we save it, you’ll see
that our form now renders. So it looks pretty good. The email is coming
from [email protected] Let’s go ahead
and type a short message to see if this works. So we’re typing. So it looks like– I don’t know if you guys
are paying attention, but the front page
was actually all in Latin. So we don’t know
how to read Latin. We’re going to
ask for some help. So let’s go ahead
and click Submit. And we click it
a few times. Nothing seems to really happen;
however, if we go to the Mail, we see that we actually
did get email, and we got it
multiple times. So it’s sort of working.
So what happened there? The problem is that we didn’t
give the user any feedback. And obviously
we need to tell them that we actually sent the email,
so that they know not to constantly
click that button. Here’s that sendEmail function
I just showed you. The first two lines
are the same, but now we have this extra code
that will update the UI. We call
UiApp.getActiveApplication. We get the mainPanel by ID.
That’s why we set it earlier. We’ll set it to notVisible, so that the user can no longer
click on that button, and then we’ll add
another label that says “Thanks for your feedback.” Once we add that to the app,
we can return it to render it, and let’s give it a shot. So we’ll go back
to our Contact Us page. We type the same message, because we still
can’t read Latin, and the site
is still in Latin. So this time
when we click Submit, we say, “Thank you
for your feedback.” All right,
so that was pretty easy, things fairly basic
to the people who are familiar
with Apps Script. It should be
pretty familiar. So let’s talk about
complex document approvals. Now, document approvals can get
really, really complicated, and we’re going to start
with a simple example and move on from there. So in our simple document
approval workflow, there is essentially
three steps. In the first step, the requestor will submit
a doc for publication. In the second step,
the approver will review and hopefully
approve the document. And once the document
is approved, we can then publish that. And this is actually
really easy to build on top of Contact Owners. This is the form
that we just created. We’re going to change the title
to write an article, change the button to
Submit for Publication, and the text area will just be
for the document itself. So here’s what
that’s going to look like. We’ve made the text area
a little bit bigger, so you can actually see
what you’re typing; and when you click
Submit for Publication, that’s just going to send
an email to the approver. But what does this mean? Well, it means that documents
now live in emails, and as we all know,
emails get deleted. They get overlooked.
They get forgotten. It’s just not a very good
situation to be in. So what we really need is to
push this data to a Datastore. For simplicity, we’re
going to use a spreadsheet in this example. Apps Script also supports
a JDBC connection, so you can push this data to
a real database, if you like. We’re not going
to get into this here. The documentation
for that is all online, so you can review it,
if you’re interested in that. So here’s the approval flow
that I’ve shown you so far. In the first step,
the user goes to the site, either types or pastes
their document into the form
that we created. When they submit
that document, it will send an email
to the approver, and it will also send that
document to our spreadsheet. From the spreadsheet, we can
then publish that document either back to the same site
or to a new site. So here’s a spreadsheet
that we think has the columns that are going to be relevant. You can see
that we have a column for who the document’s from,
when it was submitted, when it should be published,
the title, the approver, the status, and the content
of the document itself. So how do we update that? So again, this is
the sendEmail function. The first two lines
are still the same, but this time we’re also
going to post that data to a spreadsheet. In the first line, we will say
SpreadsheetApp.openById, and that ID is that funky
string of characters that you see
in the spreadsheet URL. We’ll get the active sheet, because there’s only one sheet
in the workbook. We will call
sheet.getRange, and we want to get the first
empty row in the sheet. So to do that,
we call sheet.getLastRow, which gets the last filled row,
and we add one to it. Once we have the range,
we can set the values that we’re interested in,
which is the user’s email, the date, the fact
that it’s not been approved yet, and the body of the email, which is the content
of the document. So let’s see that
in action. We’ve added this
Get Published page, where we’ve already added
our Gadget. We’re going to start
typing our document. And in the interest of
keeping people awake, we’re going to actually paste
that document in. From there, we can actually
just go down to the bottom and submit it
for publication. So this time,
we actually get feedback, so that’s good. And if we go to
our approvals spreadsheet, we see that our document
is now here. Its status is Unapproved, and the content
is in that column. So great.
That looks good. Now, from that spreadsheet, how do we publish that
back out to a site? This is our publishRow function.
So it takes one parameter. The (r) variable is going
to be the index of the row that you want published. So in the first line, again,
we get the sheet by ID. We will grab the range, and that’s the range
that we’re interested in. We call getValues to get
the values of that range as a two-dimensional array,
and then we get the site that we want to publish to: SitesApp.getSite domain
and name. Once we have the site object,
we can call createWebPage with the title and the body
values from the array, and that will actually go ahead
and create a web page directly. If we wanted, we could use
an announcements page or an announcements post here. We’re going to keep things
simple and create just a web page. From there,
we’re actually done. We’ve already
published the document, but we want to do
a few cleanup tasks, so we will send an email
to the author saying, “The post is live.
Here’s the URL.” And we can update the Sheets
Status column to be published. Let’s try that out. We’re going to
set a title here, because we didn’t
do that earlier, and this time
we’ll go the site. And we don’t have
a Gadget this time, so we’re just going to
test that function from the editor directly. We’re going to go
to Apps Script, and you see that we have
our Publishing workflow script. This is the publishRow function
I just showed you, and there’s this test function
here that calls publishRow just on row 2
of this spreadsheet. When we run that, it looks like
there were no errors. Something red would’ve popped up
if there were. And if we go back to our site,
you see that there’s now a Lorem Ipsum page
in the site. So that has all the content
that we’re interested in. If we go to our email, we see that we got
the Article published email. Clicking on the link
will obviously bring us back to that page. Okay, so that was
pretty cool. So the user
went to the site, submitted their document
for publication. We sent an email
to the approver. We saved it in the spreadsheet,
and from the spreadsheet, we’ve published it
to the site. Now, for those of you
that are paying attention, you might’ve noticed
there we’re missing a kind of critical part
of this workflow, right? In all the approval flows
that I’ve worked with, there’s usually
an actual approval Step. The problem is that there’s a whole lot
of ways to do approvals, and so I’m going to
hand it over now to Evin to talk to you about
all the different ways you can complete this workflow. Levey:
Thanks, Eric. So as Eric said, we’re
going to focus a little bit on the approval Step,
and there’s quite a few different ways
that we can do this. The document is now safely
into our spreadsheet, so we’re not too worried
about the email. The approver knows
about the email and can head back
to the control panel. So let’s look
at using that spreadsheet as a Control Panel for
this whole publishing process. We can write a simple
publish function that will simply get the active
row of the spreadsheet, the row that the publisher
is looking at, and then invoke Eric’s
publishRow function. What we really want, though, is not to have to run that
from the script editor, but perhaps to be able
to run it from a spreadsheet menu
or something convenient. So we can add
an unopened function; and this, like doGet, is another one of those
special functions inside App Script
that has a special meaning. And any time a spreadsheet
document is opened, we automatically run
this function. So in here, we’re just going
to add a little bit of code that adds an approval menu
onto our control sheet. So let’s see that
in action. We’ve got a second article
in here about gravity probe B. We can go to our script editor
inside the spreadsheet this time and paste in all the code
we’ve shown you, including Eric’s
publishRow function. We’ll save that, and now
what we’re going to do is reload the spreadsheet
to simulate an unopened event happening, and you’ll see
that a new menu pops up, a Publish menu. So we’ll select a row,
and we’ll click Approve. You see the script is running
in the background, and the status updated
to Published. And now when we go back to
our Sites page and refresh that, you see now
we have a new article listed in the left hand panel.
Okay. And, obviously, it’s
the same publishRow function that Eric showed us, so that the author will get
an email telling them that their article went out. So it’s all well and good. But going to a spreadsheet every time you need
to approve something is a little bit cumbersome, so let’s talk about
approving from email, which is maybe
a little bit nicer, a little bit slicker. It’s far more efficient
for the approver. And what we’re going to do
is send a message with an embedded HTML form. The process just becomes
a one-click. So we’re going to have
a little digression here and talk a bit
about HTML forms. How many folks
are really comfortable and familiar
with HTML forms? Okay, so maybe only 50%, so this is probably
a good digression. Any time you log
into a website, chances are you’re using
an HTML form. You put in your user name
and your password, and you hit a Submit button. And here’s the actual HTML code
that you’d use to do that. You see the first line
defines it as a form with
a particular action. I’ve truncated the URL there,
and the details from the form are going to be posted back,
so it’s a post method. And we ask
for the user name, and then we have
this input type field, and the first one is a text
field called user name and then, the second one
is a password field, which means that we don’t
display the content, and it’s called password. And then the third input type
is actually a submit, and that’s the action. And in this case,
it’s called submit too. So we can mimic this
inside Script, and we’re going to write
a little test function called sendEmail, and we’re going to set
a variable called HTML to be a form, and then we’re going to send
that variable as a payload in an email
and basically see what happens. Okay.
So that’s my little form. It asks a question. “What is the name
of the Lone Ranger’s horse?” It gives me an input box
to fill in the answer, and it takes a Submit button
to do an action. So next, we want to figure out
how we capture that action. So if we look back at
the original example of the login page, you’ll see that first line
is the critical line, and it’s going to tell
the email client, in fact, what to do with the content
that’s entered in the form, once this Submit action
is chosen. And so we’re going to have
to publish something at a URL to receive that feedback. So we’ve talked about unopened,
and we’ve talked about doGet. Now, we’re going to talk
about doPost. It’s another
magic function. And when we have a post
back to Apps Script, it’s going to ask this function
to handle the payload, which is (e), the event. So you can see
in my example here, I’m going to create
a little UI application. I’m going to add a panel,
and into that panel, I’m going to put a label
for every parameter that was passed to me.
Okay? So I don’t know what’s
going to come in the post. Anybody can post anything
to this handler. So let’s see where
we go with this. Eric showed you earlier
how to publish into a site. This is very similar.
You can publish as a service, and then you get back
a URL to use. So let’s watch
that in action. I’ve added my doPost code
into my script. You can see it all there. I’m going to go to Share
and Publish as Service. And I get the same three
familiar options: allow only me, only members
of my domain or organization, or anybody at all
to invoke the service. And that’s the critical URL.
That’s now live on the web. And what I want to do is
go back to my email payload and use that action,
and in the payload, so that the email client
knows where to send the click. It’s a little bit convoluted, but I hope everybody
is following along. Now, when I send it, my email
comes down to the email client. I get the question. I fill in the answer. And this time,
when I hit Submit, we actually are going to post
all the content of the form back to our script. Our script is going to
handle the payload and print it out
on the screen with a little UI app,
very, very basic. It’s going to give us
the service that’s been invoked, which is actually
the ID of the URL, and it gives me
back the answer, and that’s the really
important part. So that field
that I named answer, that was a text box,
is now coming back to my server, back to my script. So to approve from an email, we’re going to use
that HTML form capability. We’re going to embed the article
and the approval button into the email. That’s actually
not sufficient. We also need a hidden field
that tells us which row of the spreadsheet
we’re going to approve. And then lastly,
we want to ensure that our doPost is checking
which user is posting. We don’t want to let just any
user run the approval function. We might find
we’re under attack. So our approval flow
that Eric showed us then becomes an author,
goes to a site, submits an article
for publication. It gets written to our
spreadsheet Datastore, and it gets emailed
out to the approver, and then the approver
in the email can just click a button
to approve, and it’ll get posted back
to the site for publication. So let’s look at the first
part of that, the request. This is almost identical
to before. We send the body of the email
out in the message, but we also include
a little HTML form, and it’s exactly
as I mentioned before, a form action that’s going to
point back to our script server, a hidden value called row
that’s going to reference the spreadsheet row,
and then a submit button that we’re going to call
Approve, in this case, and then we send it.
So let’s see that happening. We’re going to write yet
another article to send, maybe Google I/O,
an appropriate topic. And when we submit that, you’ll see when we go
to the email, it goes into
the spreadsheet, and then when we go
to the email, you’ll see that we get
the full article, along with the Approve button. So now we just need to wire up
the Approve button. We have a simple doPost,
and now this time, instead of throwing up
the content that was posted onto the screen
for the approver, we’re going to call Eric’s
very, very simple publishRow, and invoke
that part of the workflow, and we’re also going to
let the user know, the approving user,
know that it worked. So we click Approve– and we have a very simple
acknowledgment page that just says Published. You could obviously make this
as fancy as you like, but then when we go back
to the site, we now have an article
about Google I/O. All right. So this is all getting
a little bit better. One thing, though,
that I didn’t talk about yet is the security of this. How do we know
who clicked the Approve button? And this is
obviously critical. What happens if the approver
accidentally forwarded the email to somebody else? Could they just click this
button, and would it work? Well, we showed you
that the service itself is restricted to the domain
or organization, but we really need
a user level check. So inside our doPost function,
let’s– this is a good example
of what we could do. We could set the message
to be “Access Denied!” and then only if the active user
is the approving user would we actually call
the publish function and update the message
to be published; otherwise, we’d just
throw up whatever message we have on the screen
for the approver, be it “Access Denied!”
or published. Now, obviously, we’re not
advocating hard coding approvers into your script,
but for clarity, this is the easiest way
to show it. We’ll talk a little bit
later on about roles and how we can handle
that more gracefully. So to get a little deeper
into the security model– and there’s two user concepts
inside Apps Script, when it’s used as a service. There’s the Effective User,
and that’s the account running the script
and the publisher, in this case. And there’s an Active User,
and that’s the user whose fingers are
at the keyboard. Script can only figure out
who the Active User is in special cases. Obviously, we don’t want these
public on the Internet, and publishers then scraping
identities of all kinds of users who happen to visit a page. So it’s only when both users
are part of the same domain or organization we let you
identify the user, and the domain must not be
a special domain. We don’t allow this
in partner or edu domains, so it’s mostly
Google Apps Premier, Google Apps Standard. So that was
document approval. That was the entire workflow,
end-to-end. This next section of the talk
is a little bit different. We’re going to go into
pretty advanced topics. I know this is
a 101 level course. We’re not going to go through
detailed work examples. We’re going to talk more about
theoretical ways that you can manage the
complexity and add a few more– I won’t say bells and whistles,
because that kind of implies they’re unnecessary– but a few more
little advanced tweaks to your workflow
to make them more usable. The first thing,
multiple approvers. The real value of a workflow
is to codify a complex process. Our process is
ridiculously simple. It’s just one
marketing person approving a post
to a sites page. We generally need
more than marketing. We probably need legal, and PR,
and managerial approvals. And big companies, probably
an awful lot of people involved. So how do we handle
this explosion of complexity? And that’s really what this
section of the talk is about. So what we want to do
is introduce a better representation
for this state of the approval at any point in time. I’m going to use a simple
diagram to illustrate this. The original workflow
was a submit by anybody that would go to
our marketing person, who could then approve an email
or in the spreadsheet. But, realistically, it would
then go, once approved, to the PR person,
and then maybe to legal, and then to the manager, and then get published,
if we’re lucky. And more often than not,
something’s going to go wrong, and you’re going to get rejected
and go back to step one. To get even more realistic, it would get submitted to PR
and marketing at the same time. And then only when
it’s approved by both, a kind of
a synchronization point, then it might go to legal,
and then to the manager, and then to publish. And these are still
probably very basic examples, compared to what most of you
deal with day-to-day, but even at this level, we’re
not going to include rejection, because it’s just getting
too difficult to draw. So let’s model this
inside our process. This process
is a sequence of steps. There’s always
one approver per step, and in the first step, there are
actually multiple approvers. The approval state is going to
be maintained inside the Step, and I’m using Step
with a capital “S” here, because it’s an object type that
we’ll talk about in a moment. We’re going to use
a JavaScript object to represent that Step, and then our process
is simply an array of Steps. Now, I’ve given you
a sequential process. There’s definitely
more advanced things we can do, maybe in a 201-level course
some other time. So just think
about it that way. It’s sequential,
and so it’s an array. So we’re going
to create a Step. It’s going to take a list
of approvers for the Step. We’re going to create
a JavaScript object in the first row. We’re going to set the number
of approvers in the Step to be the number of elements
passed in, in our array. We’re going to copy
that array. Just a little quirk
of JavaScript, that we don’t want to reference
to an array. We want an actual copy, so we’ll use
the shorthand approver’s slice. And then we’re
going to set the status for the overall Step
to be pending. Okay? Then we’re going to iterate
through all the approvers that we’ve been given
and put a field into this object for each approver, indicating
that approver’s specific status. So on the right hand side,
if you’re familiar with JSON, you’ll see that if I create Step
with Evin and Bob, then I get the object beneath. Status is set to pending. Two approvers. The approvers are Evin or Bob–
Evin and Bob–excuse me. And then both Evin and Bob have
their status set to pending. All right,
so pretty straightforward. Then we’re going
to create the process, and we’ve got the little diagram
of the example process in the top right, and it’s going to be an array,
as we mentioned, so that the first Step in
the array is marketing and PR. So we just simply push back
that newly-created Step onto the array. The second Step is legal. The third Step is the manager, and then we return
the process object. So it’s just a slightly
more complex JSONObject. So now we’ve got
a state representation for each Step
and for the entire process, and we want to be able
to load and save that at will. So saving the process,
I’m going to show you saving that to a spreadsheet,
because it’s very easy to see. But as Eric said, you could use that ideally
in some kind of SQL database. So I’m going to have a function
that can save my process to a particular row
inside a sheet. So I pulled back a status cell
to put this into, and then I set the value
of that cell to the JSON stringify
of the process. Now, if you’re not really
familiar with JavaScript, this isn’t that
much magic at all. It just serializes
that JavaScript process into something that’s
actually human readable. We’ll see that
in a moment. To load, I just want
to do the exact reverse. Given a sheet and a row,
I want to pull back that cell and then turn it back into
a real live JavaScript array and sub objects,
and all I do is call eval on the text I get back. So let’s see that working. It’s far easier when you see it
than when I try and explain it. So we’re going to
run our example, and you’ll see that it’s very,
very straightforward. We now have an array of Steps that’s been saved
to the status row. And as we update that, we can load it
and save it trivially. Okay, so now we’ve got a handle
on how to manage the state. The algorithm breaks down
to be something very, very straightforward. When we get something submitted
into our workflow, we create an appropriate
approval process. Okay? We write it back
to our Datastore, and then we start
the first step, and we do that by, in our case,
emailing the approvers. You can just
grab the first step, iterate through the approvers, and send each of them
an email. Then when we get
an approval event, because somebody has clicked
the approval button or approved in some other manner,
our script is going to wake up. It’s going to go back
and get the active Step from our Datastore
and update the status. Somebody just approved.
I need to update the status. If the Step now
is fully approved, I’m going to start
the next Step, naturally send out
more emails. And if all the Steps
are approved, I’m going to go
to the final state, and in our case,
that’s publish. And this is
pretty generic. You can use this for almost
any level of complexity for a sequential workflow. So let’s talk about
dynamic approvals. Our process structure
is very flexible. Steps can be added
and removed trivially. We’re just manipulating
a JavaScript array, and I think everybody knows
how to do that. So some of the things
we could do is we could automatically
add a legal Step, perhaps, if a post contained profanity
or restricted keywords, or any kind of odd content
that we’re able to detect inside our script. Another thing we could do is,
along with an approval button, we could also have
a bunch of reference buttons or refer buttons. So if one of the approvers
is concerned about the content, they could add a Step
by clicking, not the Approve button,
but the Refer to Legal button, for example. So it really is
quite a flexible system. So from here,
let’s look at another kind of advanced feature
and reminders. As Eric pointed out,
when we were writing things to the Datastore,
we can’t just rely on emails. Emails often get overlooked
and ignored. So the delays that that cause
are annoying and potentially costly. We’re going to show a way to issue a daily reminder
of pending approvals, and this is basically
a nag email. So the first thing
we’re going to do is figure out
who needs to be reminded. So we’re going to get
the pending approvers from our process. The pending approvers
are going to be the approvers on the active Step. So we’re not really concerned
about spamming everybody who’s in the process; just those people
who are pending. The first thing we do
is we get the active Step, and I’ll show you
that code in a moment, and then we’re going to set up
an array of pending approvers. So we don’t want to
spam the people who have already approved. We just want those in the Step
who are set to pending. So for every user
in the list of approvers, in the active Step, we’re
going to check their status. So active Step
for user X is pending. Then we push that user back
onto the pending approvers. Let’s have a look at
the getActiveStep function. We’re going to just iterate
through the array of Steps in the process,
and we’re going to find the first Step that hasn’t
been fully approved. So the first Step
that’s pending. So relatively straightforward,
relatively simple. Then to send the reminders, our function is going to
grab open our Datastore, our spreadsheet. It’s going to pull all the data
from the spreadsheet, and then it’s going to iterate
through every single row and get the pending approvers
on each row and send them a reminder email.
Okay? So in one step,
we’re going to iterate through the entire Datastore
for every article that’s pending and send reminders
to everybody. And then we need a way
to run this automatically. This is pretty important. Inside the spreadsheet editor,
inside the script editor, you can set a function–
in this case, send reminders–
to run automatically every day at a particular time,
and so we’re going to do it every morning
between 8:00 and 9:00 a.m. So now, when folks
get in to work, they’ll have a bright
shiny email nagging them about approving
whatever is on their plate. Okay. So that’s a very basic
reminder strategy. I think, before you know it,
you’ll be getting nasty emails telling you to back off, that it’s only been 24 hours,
et cetera, et cetera but it’s very easy to extend
and customize. You could, for example, store the last date of nagging
in the Step and then check how long
it’s been since you sent a nag, so that you’re only sending
a nag every few days. It really is a very solid
foundation for Escalations. If you’re going to escalate
something, though, you really need to know about
the organizational hierarchy, and that’s what we’re
going to talk about next. This is one of the most
important pieces of workflow, and I think it’s something
that traditionally, we haven’t had
a great solution for. You typically do need
to know a user’s manager. You need to know
who fills the PR role, who fills the marketing role,
who fills the legal role. We need to be able to find
the users responsible for the approval, and we don’t want to hard code
these things into script that has to be updated every
time there’s an org change. We’d go crazy. So it is very important
that a script be able to get access to an
organization hierarchy. So at Google, we’ve got these
things called Google Profiles, and I guess–has everybody here
seen their own Google Profile? No, blank stares. Okay, one or two people. It really isn’t all that useful
in an enterprise setting. You go there, and you see
some security settings, you see your dashboard,
you see your own email address, all things that you already
know about, and generally
not all that helpful. But behind that, there is
an incredible Datastore that has a huge amount
of information, and that’s all thanks
to a thing called Google Apps Directory Sync. Directory Sync allows you
to take an LDAP server’s content and synchronize it out
to Google Profiles or maybe even an active
directory server. This Apps Directory Sync
runs inside your organization, periodically scraping
all that data and pushing it out
into the cloud, so then anything
that’s running inside the– running outside your firewall
and in the cloud still has access
to all that information that’s centrally managed
inside your organization by LDAP, or Active Directory,
or whatever system you’re using. And you can see
in this screenshot that there’s all kinds of
attributes like manager, Assistant, Division,
so it’s a wealth of information, and it’s exactly
what we need for workflow. Along with the Directory Sync
and the Profiles, there’s a directory service
that’s going to be announced. I guess I’m
announcing it now. It’s going to be released
in the next couple of weeks. So we can use this
to look up managers. So how do we call
these APIs from App Script? So App Script has a beautiful
range of very easy-to-use APIs, but obviously there’s a lot more
APIs that are available that aren’t yet available
in Apps Script. So we’re going to walk
through an example of how to call these APIs
from script. So I’ve thrown a big monster
chunk of code onscreen here, and I think you’ll all
be relieved to know that most of it
is boilerplate. And also, I should highlight
that in this case, we’re going to call
the Profiles API, and we’re going to access
the Datastore directly. The Profiles API
has a weird characteristic in that it’s admin access only,
and the API that we’re releasing in a couple of weeks
is called a Directory Service, and that’s available
to everybody, and the main difference is that
the Profiles API is read/write, and the Directory Service API
is read-only. Our example is read-only,
and so you’ll be able to use this code
pretty much exactly to call the Directory Service when it’s released
in a few weeks, and you won’t need to be
a domain administrator. All right, so the first
part of the code here, what it does is–
as I said, boilerplate– you tell it which service
you’re accessing, and then the documentation
for that service gives you the access token URL,
the request token URL, and the authorization URL. You then also set a consumer key
and a consumer secret. And for every Google service,
they’re anonymous. Okay? It confuses everybody. Why would your key and secret
be anonymous? But they are.
And then we can set options. In this case, we’re just going
to give it a name–Directory. We’re going to say
to use this OAuthToken. That’s actually
what we’re creating in the boilerplate code. We’re creating
an OAuthToken, and then we’re going to
set the headers to tell it which version
of GData to use. And really,
the only important code here is at the very bottom
of the screen, where we construct a URL
to hit this service. And again, it’s a URL
from the documentation. And then, on the very
second last line, we’re going to add
the user name that we’re looking up
and the options, and then we’re going to return
the content text. Okay? It’s a mouthful,
but, trust me, it works. Actually, and this is just
a larger version of what I was talking about–
we’re going to fetch that URL, and the user name
we took in as a parameter is just going to be
appended at the end, and then we’re going to
return the result. So when I call that function
on myself inside Google, this is actually
what I get back, and I know it’s very small
for you to read, and that’s intentional. There’s nothing
all that interesting about what my entry is, but there’s all kinds of
stuff in there like my telephone numbers,
my email address, my manager’s email address,
my manager’s name, the projects that I work on,
the OUs that I’m a part of. Okay? So this is kind of
a gold mine, when it comes to process
and workflow. So we now have
a single source of truth for our org structure, and it’s available today
in the Profiles API, if you’re a domain
administrator. and if you’re not,
it’ll be available to you in the Directory API
in the next few weeks. So that’s pretty much
what we have for you today, and I’m going to do a quick
recap of what both Eric and I have talked about, and then
we’ll take some questions. We started with Script
in Sites, and Sites is the hub
for workflow, we believe. Actions on a Site, embedding Scripts
into a Site’s page. Then Eric told you
all about simple workflow, private comments
to a site owner, very straightforward, and then we went into
document approval and the simple publishing
workflow with a single approver, and then we talked about
approval mechanisms, how we can actually capture
that approval action and translate it
into motion. We talked about approving
from a spreadsheet and approving from an email,
using HTML forms. Then we talked about
multi-step approvals, and this is really where
workflow gets realistic, and saving the state of
the approval is the key thing. It helps you
manage the complexity, if you have a good
representation of the state. And I can’t emphasize
that enough. It may be a dry topic,
but it’ll save you weeks and weeks of frustration
in the future, if you do it nicely. We talked about dynamically
altering the process, which is often essential,
and then we talked about the additional features
that are going to be essential: reminders, escalation,
and figuring out how to navigate your org’s
hierarchy from Script and from the cloud. All right, so that’s what
we have today. Maybe we’ll hold it there
for questions. [applause] man: I have a question.
Levey: Yes. man: It’s more something
I thought about when you were talking about
the reminders, and you can set those scripts
to run as a service. Levey: Mm-hm.
man: So you know, there’s turnaround
in companies, and maybe the person who wrote
that service is gone, and somebody is getting
this constant reminder, and we don’t know
where it’s coming from. Is there a way to see
what services are running, or, better still,
running out there in the cloud? Levey:
Yes, absolutely. So I think the question is,
with all these different services
that you may be running, and all these different triggers
that you may have set up, is there any central place
to see what you’ve got? man:
Right. Levey: For triggers,
the answer is yes. Inside the script editor, when you go to
the triggers menu, you can look at the triggers
for the current script, or you can look at
all your triggers. man: Okay. Levey: And so you can get
a central view of everything that you’ve set up to run
and respond to an event. man: So at an administrative
level, we could see it across
maybe all the scripts that other people have created
within the organization? Levey:
No, it’s on a per user basis. man:
Per user basis? Levey: And so it’s intended
to be user granularity, absolutely.
man: Okay, great. Thank you. man: Hi. So you mentioned
saving a state to a database. Levey:
Mm-hm. man: And so I know that hosted
SQLs and Trusted Tester– are you going to be able
to write from Apps Script to hosted SQL?
Levey: Yes, absolutely. That’s one of the most
exciting things that we’ll be able to do
shortly. And for any folks who are on
the hosted SQL Trusted Tester, feel free to come by,
and I think I can hook you up with something that’ll
do that for you. Okay, I guess
that’s all the questions. Thank you very much, everybody. [applause]

10 thoughts on “Google I/O 2011: Enterprise Workflow with Apps Script

  1. great video with very useful information!

    but who controlled the camera?? missing some important information there..

  2. @paucarre there are hundreds if not thousands of nobodies like Google would have to compete with as far as the BPM goes…

  3. Seriously. Google-IO guys. If the person on stage is showing slides and code examples, I DON'T CARE ABOUT HIS FACE. Stop showing the person talking about a code example and show the code example. The interesting thing about the person talking is what he is saying and I can hear that even if you show the slide. I can however not hear him if I have to pause the video because you think half a second is long enough to show the slide/code.

  4. yeah seriously I am trying to get thru this video but it keeps going back to the speaker during an explanation of the code … very frustrating

  5. Does anyone know how to grab the first step and iterate through approvers to send an email? I would also like know how to do update the approval status of each person in the array as each person approves the form. Someone help please!

  6. Does anyone have a link to the actual code for this workflow.  I am wanting to change it and use it for Purchase order workflow.  I like the aspect of being able to approve in an email would also need to add rejection in the email.

Leave a Reply

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