[MUSIC PLAYING]
MIKE PROCOPIO: Well, hello, everyone.
And welcome to Google I/O 2022.
My name is Mike Procopio.
I'm a senior staff software engineer here at Google.
And today, it's my pleasure to be speaking with you
about extending Google Workspace with AppSheet's
no-code platform and Apps Script.
Let's have a quick look about where we're
going today with today's talk.
We have five key pieces that we'll touch on.
First is AppSheet, that's Google's no-code platform.
Next is Google Workspace, so properties
like Gmail, Calendar, Drive, Docs, and Sheets.
We have Google Workspace APIs, the Application Programmer
Interfaces that power, each one of these
and enable third-party developers
to programmatically interact with Workspace.
We have Apps Script, which is Google's low-code platform that
wraps those Google Workspace APIs.
And then finally, we have what we are previewing here today,
which is AppSheet's Apps Script Connector,
an integration that connects all of these together.
Let's talk about Google's AppSheet.
I like to say that the era of no-code is here.
And this comes from the perspective
of watching programming languages evolve over
many years.
The earliest programming languages, as you may know,
were very rudimentary, very low level,
and close to the processor.
Over time, these languages got higher and higher level--
two languages, for example, like Java and C++--
and then even higher level than that, for example scripting
languages.
And if you take this to the limit,
you end up in a place that basically
looks like specifying things using definition and semantics,
and not actual code.
How can we do this?
Well, it turns out that many business applications
have similar structure, right?
Apps are built on a data source.
Users interact with them.
And so it's possible to build a great variety of apps using
this general definition of what the app should do,
as opposed to actually writing code.
So AppSheet is Google's no-code platform.
It's a fast way to build apps and to automate work.
It lets an app creator build an app
without actually writing code, using
the definition of what the app should do as opposed
to actual code.
Here's what that looks like inside an actual editor.
So this is the experience of the app creator.
And we are creating the app using the app definition.
So instead of actually coding, we're
basically saying what the app should do.
So here in the middle, we're building an automation,
and this is something that will preview here shortly,
which says, when something happens, do this other thing.
In this case, the other thing is we're
going to call an Apps Script function.
So that's one way we can edit an app.
Another way is we can build on top of a data source
and map the columns inside the data source
to fields that the user sees inside the app, and so on.
On the right-hand side, we see a live preview
of what the app actually looks like when
it's running, in this case, the mobile version.
So one key thing that a no-code platform needs to do well,
and that AppSheet does well, is to automate workflows.
We sometimes refer to this as bots.
What this basically means is creating-- using a definition,
not code--
a set of expectations that when an event happens,
we can listen for it and then trigger some other action.
So for example, concretely, when a user
creates a data row inside the app,
then we can do this other action-- for example,
sending an SMS text message or an email for approval.
Another key thing that a no-code platform needs to do
is to integrate with a wide variety of services.
So for AppSheet, this means inside Google, of course.
But it also means a great variety
of third-party services.
Now, you can build an AppSheet app
on top of a variety of data sources,
including Office 365, Dropbox, and Salesforce.
And then using Apps Script, which we'll see here shortly,
you can then call into an equally wide range of services,
including Google and other services.
So Google Workspace is Google's productivity suite
that has more than 3 billion users,
helping teams of all sizes connect, create,
and collaborate.
So these are the apps that we know fairly well--
Gmail, Docs, Sheets, Slides, Calendar, and so on.
Behind each Google Workspace app is also a mature API.
If we look back, we can see many of the Google Workspace
applications, like Docs, Sheets, and Slides,
date back over 15 years.
But what may be less clear is that each one of those
has an API.
This lets an application developer automatically
integrate with the Google Workspace property.
So for example on Drive, programmatically,
using the API, you can upload and download files.
Or in Google Docs, programmatically,
using the API, application developer
can automatically add text to a document
or edit text in a document.
Same goes with Sheets and Slides.
For Calendar, you can create a calendar appointment
automatically, and send it out, and manage its responses,
and so on.
So we've seen the Google Workspace APIs.
We also have Apps Script at Google,
which is Google's low-code development environment.
This lets an application developer, among other things,
easily access the Google Workspace
APIs from a high-level perspective-- more
of a scripting language.
As we will see, this is really key for the no-code space
because if you can connect the no-code app with Apps Script,
you can greatly extend the possibilities of what
the no-code app can do.
So a quick recap here.
Let's see, we have four key pieces--
AppSheet, we've seen, is Google's no-code platform;
Google Workspace-- that's Gmail, Calendar, Drive, Docs,
Sheets, and Slide; the APIs behind each of those Google
Workspace properties; and then Apps Script, which
is Google's low-code platform that wraps those Workspace
APIs, in addition to other third-party services.
That brings us to our core idea here today.
The AppSheet Apps Script Connector.
And basically, what this means is,
from the context of AppSheet, a no-code environment,
an app creator now has access to all of the functionality
that Apps Script provides.
But it's key to note that the person building the AppSheet
app, the no-code environment, isn't necessarily the person
who wrote the script.
So there has to be a sensible way
to expose the Apps Script script information
inside the no-code environment.
Here's what that looks like.
On the right, you can see that the user has chosen
a script available to them-- in this case, the application
author.
We list out the parameters that are required to pass
into that Apps Script function.
And then we simply bind them to the data source on which
the AppSheet app is built.
So here are just a few use cases for how this might be useful.
The use case that we'll focus on today, including in the demo,
is to be able to create an audit log by appending
a summary of changes that happen inside the AppSheet app
to a separate data source logging it
in a separate isolated way-- in this case,
for demonstration purposes, simply a Google Doc.
There are other use cases, though.
For example, inside Google Workspace
you can automate Slides, for example,
adding a new slide every time a personnel directory is updated,
creating and editing calendar appointments, managing files
inside Google Drive, or, equivalently,
calling an external service to do something similar
with a different provider.
OK, so in this screenshot, we see an actual picture
of the Apps Script Editor creating a function
that ultimately, we will call from a no-code application.
As noted before, the person who works
in this environment, this low-code environment
inside Apps Script, may not be the same person
who's creating the no-code app.
In many cases, these will be different.
And the skill sets will also be different.
So let's take a look at the actual code
that we'll see here in the demonstration shortly.
This is an Apps Script function.
It's a working piece of code that
shows what it looks like to create a reference to a Google
Docs document, and then take the parameters that
have been passed into the function
and append them to the document.
So here we're using the Google Docs API to automatically
script the content in the document using
Apps Script and Apps Script function, which
has data and is called by an AppSheet Automation.
Let's take a look at what that looks like from the perspective
of AppSheet.
As we noted before, the person who wrote the Apps Script code
function doesn't necessarily have
to be, in many cases who actually won't be,
the person creating the no-code app.
From the perspective of the no-code app,
it's as simple as choosing the Apps Script function
and then mapping in or binding the values from the data
source on which the AppSheet app is built
to the parameters in the function
that we're calling within Apps Script.
And this is what that looks like, here on the right,
simply going down through the list of the parameters
and mapping them one by one to the columns and the data source
inside the no-code app.
OK, it's demo time.
We've seen AppSheet, Apps Script, Google Workspace,
and the Google Workspace APIs.
Here's a demo of a use case that we saw before,
where we create an audit log as a result of some trigger
inside the AppSheet app.
We do this by calling an Apps Script function.
Hello, and welcome to the demo for the Apps Script
Connector for AppSheet.
In today's demo, we'll see step-by-step
how to call an Apps Script function from AppSheet.
We'll see how to set this up using an AppSheet Automation
and how to pass in parameters to the Apps Script function using
the Expression Assistant.
For this demo, we'll be using the Travel Request
tool, an AppSheet Demo app.
This app is used for managing employee travel requests
inside an organization.
Using the app, an employee will create a travel request,
which will then be sent to a manager for approval.
So the travel request contains fields
such as flight information, cost, business purpose,
and other details.
In any no-code app, there may be times
when an app needs additional capabilities beyond what
a no-code platform can provide.
The Apps Script Connector that we're demoing here today
greatly extends the abilities of AppSheet apps
by letting them access the power of Apps Script and all the APIs
and services that it integrates with.
In this demo, we'll use the App Script Connector to call
a function in Apps Script.
This function calls the Google Docs API
to append travel request details to a Google Doc, which
creates a simple audit log.
This function will be called from an AppSheet Automation
that triggers whenever a new travel
request is created in the app.
In this demo, we have three key players.
First is the AppSheet Editor, shown here on the left.
This shows the app definition for the Travel Request tool
and also a live preview of the app on the right-hand side.
Next we have the Apps Script Development Environment,
shown in the upper right, which is
where we'll be editing the function that we're calling.
And finally in the bottom right, we see the Google Doc itself,
the audit log, which we will update
in real time via Apps Script whenever we create a new travel
request in AppSheet.
Let's get started.
The Travel Request tool is already built and shown here
in the AppSheet Editor.
The first step in calling Apps Script
is to create an automation in the AppSheet app.
To do this, we select the Automation tab, and then
click New Bot.
The bot will listen for an event,
in this case, whenever a new requests record is
created using the app.
When that happens, the automation will call a process,
and in that process will create a special Call a Script task.
Now let's expand that task where we can configure the details
for calling the script.
To configure the script, we'll first
choose the Apps Script Project.
We have this created already.
It's the script shown in the upper right in the Apps Script
Editor.
This script has been saved to Google Drive,
and so we can select that here.
Once selected, a list of available functions in the Apps
Script Project will appear.
Choose the function to call.
In this case, there's only one function,
called Log Travel Request Update.
We'll choose that one.
Once the function is selected, a list
of parameters needed to call it will be populated.
So in this case, that includes details
like request date, requester, travel origin, and destination,
and so on.
You can see this matches the expected function
signature shown in Apps Script.
Next we'll use AppSheet's built in Expression Assistant
to choose which values in the underlying data
source to pass as the function parameters.
Here, we'll simply map each parameter
to a single column in the data source.
So that includes ID, date, requester, origin, destination,
and purpose.
And that's it.
Click Save, and now let's test our work.
To do that, we'll add a new record
using the live preview of the app
here on the right side of the AppSheet editor.
We'll just create a sample travel request, in this case,
flying from Denver to San Jose.
We'll add in the details, such as departure and arrival
dates and business purpose.
Note that not all of these will be part of the auto log.
Now, let's save this record and then sync the app.
This will trigger the automation we created earlier,
calling the Apps Script function using information
from the travel request that we just added.
The function uses the Google Docs API
to directly edit the Google Docs file, which
is shown in the bottom right.
This typically takes a few seconds to execute,
which happens in the background on AppSheet's servers.
And there it is.
The details of the travel request
are added to the Google Doc in real time.
From here, we could add in additional parameters
to the Apps Script function or even
extend it to use other APIs or services available in Apps
Script, like YouTube, BigQuery, Google Analytics,
or other external service.
Well that concludes our demo.
Thank you for watching our demo for the Apps Script
Connector for AppSheet.
We can't wait to see what you build.
OK, so we've just seen the demo of creating an audit
log using an Apps Script function called
from an AppSheet no-code app as a result of an automation.
Let's take a look at next steps.
Great first step for beginning to work with AppSheet
and no-code in general is to join the AppSheet Creator
Community.
And then once you've done that and you've
created your first AppSheet no-code app,
you can try out the Apps Script integration.
And linked here is the Help Center
and the docs and tutorials that will help you get started.
And then finally, we have an excellent how-to video
that gives a different take and a different use
case for how to call an Apps Script
function from the context of an AppSheet no-code app.
Well, thank you, everyone.
On the right-hand side, you'll see some links
to some additional resources.
I'm Mike Procopio.
It's been my pleasure to speak with you today
about AppSheet and Apps Script integration.
And I hope you have a great rest of your Google I/O 2022.
[MUSIC PLAYING]