Apps Script monitoring and analytics with Stackdriver Logging and BigQuery (Google Cloud Next ’17)

PAUL MCREYNOLDS: Hey, everybody. Thanks for joining us for
one of the late sessionsand sticking it out
till the end of the day. I will call out that
there’s one more sessionafter this on Gmail
add-ons, also an Apps Scriptrelated product. You guys should check
it out if you reallywant to go big tonight on Next. But we’re going to talk
now about Apps Scriptand a new monitoring
capability that’sbeen made possible
with our integrationwith Stackdriver Logging. That’s currently in an
early access program,and is planned for launch to
G Suite business customers,and possibly more
broadly down the line. And with me here is
Romain, from Revevol,and he’s going to be covering
most of the presentation. He is truly the expert on this,
one of our earliest adopters,and has figured out how to hook
it up to several other cloudtechnologies to extract the
maximum value from StackdriverLogging. So I’ll just give
a quick overviewof what we’re
going to talk aboutand what the technology
is, and then I’llhand it over to Romain. So first we’re
just going to talkabout Stackdriver
Logging in Apps Script,and how the integration works. Romain will give an example
based on Yet Another MailMerge, which is a product
that he has built and manages. And we’re going
to talk about howyou get your logs from
Stackdriver into BigQueryfor analysis, and do a lot
more powerful stuff with them. And then another example–a Firebase example– and
then finally Data Studio. So Romain has really
done our work for usin identifying all
of the ways that youcan leverage this data once
you get it in to Stackdriver. So the basics are that we now
define console dot star methods–the familiar logging methods
from the JavaScript clientin the browser–and messages– and I should
also say objects and valuesof other types that
you log to console, getsent to Stackdriver Logging. You can also configure Apps
Script to log exceptionsto Stackdriver Logging. This is particularly
powerful if you’rebuilding an add-on or something
that has a large user base. You want to keep
track of whether it’sworking for people
other than yourself,in environments
other than your own. And the existing
logging solutionis frankly not up to snuff. It captures the most
recent set of logs. And so when you’re working
with a large user base,you really need
something richer. And so here in
project propertiesyou can check the
exceptions box,and you’re off and running. Here in the view menu
there’s a new menu itemfor people participating
in the EAP,or when the feature rolls out,
to view your console logs. And here you see logs in the
Stackdriver Logging interface–a couple of hints
at other cool stuffyou can do, creating
metrics and exports. And then you can also filter
by either the metadatathat we send along with the
message, or the message itselfif you’re sending adjacent
object as your log message. We also, automatically,
in additionto some other metadata,
we automaticallytag every log entry
with a user key. And this is done
for two purposes. Firstly, it just makes it
easy for you to see what’shappening to a particular user. The other reason that
we do this is as kindof a serving suggestion. These keys are–
don’t contain PII,they’re based on
the user’s identity,and they rotate once a month. And so this is kind of extending
a Google practice to youto use if you like, to track
users for short periods of timeand make sure the application
is working for them,but to protect their privacy by
not tracking them over a reallylong period of time. You can log other keys–
you can log the user’s emailaddress, for example. Your script has access to it. And so this is
something that youwould use for support
and triaging issues,and it’s a great way to
do that in a way thatprotects your users’ privacy. And I’ll hand it off to Romain
for the rest of the show. ROMAIN VIALARD: Thank you, Paul. OK, so– I’m going to
present how I will–how I have started
to use StackdriverLogging with my add-on,
Yet Another Mail Merge. So Yet Another Mail
Merge is an add-onfor Google sheets, a
self sufficient add-on,meaning that it doesn’t
connect to any other service. It really lives
only as an add-on. It’s a mail merge
add-on, so it letsyou send email campaigns using
Gmail and Google Spreadsheets. It’s one of the top add-ons
for Google Spreadsheets. So this means that we are
now at 700,000 installations. So 700,000 people have Yet
Another Mail Merge installedon their Gmail Google account. But we do have a great number
of monthly active users, morethan 80,000 monthly users,
sending email campaignwith Yet Another Mail
Merge every month,for a total of more than 40
million emails sent monthly. And all of that
means that we do haveto provide support to a lot
of people using this tool,even if it’s quite
simple to use. Many people still need help
or still have issues with it. This logging tool, like
Stackdriver Logging,is really helpful
to investigate–better understand–all of the steps a user went
through before he had an issue. So I will demonstrate
how we’re usingthat now to help in our
support and in other partof the development
of the add-on. So first, just to present
a bit, Yet Another MailMerge– so it’s
quite simple to use. You simply start–
so it’s a toolto create a mail merge
campaign, email campaign, soto send the same email to a
bunch of different people. You start by creating
a draft in Gmail,where you will be able to
place some place holders thatwill be replaced by data
from your spreadsheet. So, for example, here
we have a place holderfor first name here
and in the spreadsheet,you can see that in the
first column, column A,there’s a column
named First Name. So for each row of
the spreadsheet, YetAnother Mail Merge
will send an emailand will reuse our
template, the email draft,and replace in
these draft markersby the data from
the spreadsheet. So to install Yet Another
Mail Merge in a spreadsheet,you just have to go
to the add-on store. So if you had the menu
add-ons, get add-ons,and you will see Yet Another
Mail Merge pretty easily. It’s, as I said, one
of the top add-ons,usually it’s even with
the top six add-onsthat you can find directly
when launching the gallery. After that, so, add-ons are
a specific menuin each spreadsheets, and
for Yet Another Mail Merge,you have several menus to–to trigger the add-on functions. So you can either directly start
the merge if you already havea spreadsheet containing
contacts– recipients–or, you can use one of our
integration to import contacts,either from Google Contacts or
from cell phones, and so on. So check with the–
configure from submission –while different–
other features. So you– you indicate
everything you want–mostly you select the
draft you want to reuseto send your email campaigns. So we list all drafts
from your Gmail account. And then you can,
for example, setif you want to track emails
opened, clicked, and so on. If you want to set
up a specific areas,add some personalized
attachments,and many other options that
we added over the yearsfor the add-on. And once you’ve sent your
campaign into spreadsheetsdirectly, you are able to see
the results of your campaignin real time. So the add-on displays
information in a little sidebarand also updates
that you scored on, letting youknow–so how many people have
either opened, or clicked,on your email. OK, so, that’s the–that was just an introduction
to explain how the–what Yet Another
Mail Merge does. As we have a lot of
users, we thoughtit was interesting to
track usage and learn howpeople are using our add-on. And for that, since
more than a year now,we are heavily using
Google Analytics because itlets us see the exact number
of users using the add-onevery month, day, and so
on, and to track the usageof different features. So before going to the
Stackdriver Logging part,I want to show what we were
using before Stackdriver,and when we decided
to switch and–what decided us to switch some
parts from Google Analyticsto Stackdriver. So in Google Analytics
you are able to easily seeyou’re active users, the
number of daily active users,seven day active users,
or monthly active users. You are able to compare over
time those users, so youare able to see if
the number of usersis increasing from
month to month. So if you are on a gross–or if there are some issues
because less and less peopleare using your add-on. We are also able to
track several events. So, for example, as
Yet Another Mail Mergeis a mail merge tool to
send email campaigns,we are sending to
analytics informationabout those e-mail campaigns. So we are able to see how
many email campaigns were sentover a specific period of time,
how many e-mails were sentper campaign, and as a total. And we can also send a lot more
information to Google Analyticsto get information about which
features people are using,and so on. So, for example, I
presented earlierthat, in your Gmail draft, you
can put placeholder makers sothat the email
won’t look exactlythe same for every user, but
will be a bit personalized. But not everybody is using it. In fact, yes, more
than 36% of all usersare sending the exact
same message to everybody. And I don’t have
that on the slides,but that’s something we
have actually improved. So there were a
lot more people notusing any kind of
personalization before,and because we track
this kind of information,we were able to better
indicate to people howto personalize their e-mails. And once they knew
that that was possible,they used that feature–so the ratio has
increased in favorof more people are using
placeholders as time goes by. It’s the same for our
email tracking feature. Now pretty much
everyone is using it,and before it was not the case. Thanks to all the data
we are collecting,we were able to see where we
needed to make improvementsto be sure that people know
that the feature existed,and that it was useful for them. And we’re tracking
several data like that,mostly thanks to the ability
to create custom dimensionin Google Analytics that lets
you send specific data relatedto your app– your add-on–to analytics. So if people have activated
one feature in your app,also to categorize people. So, is it mostly used
by gmail. com users?By G Suite users?How many people are using, and
also paying, for the product?It’s a premium
product so some peopleare using it and sending emails
for free, and some are paying. What is the
proportion, and so on. So, all that were very
interesting metric–interesting metrics for us. And we improve our usage of
Google Analytics over time. And so we were also using Google
Analytics to track errors. So that was good because
we were able to see, thanksto all the chats and default
reportings available in GoogleAnalytics, directly the number
of times a specific errorhappened, the number
of users impacted. And so we were able
to know on which errorwe needed to be proactive
and find a fix quickly. But, apart from that,
sending errors to analyticsis a bit restrictive,
because you can onlysend a simple string
of errors, and youdon’t have all the
stack of the error,and it’s not really great to
investigate and understandwhy this error is happening. So that’s why it would be better
to activate cloud logging,and that’s what we did. We activated in Yet Another
Mail Merge the connectionwith Stackdriver. And now we are able to see,
in real time, all the errorshappening on the add-on. So every time someone
is using the add-onand is generating an error, we
can see the log in real time,and we can click
on a specific logto have all the
information about the log. So for which user is
impacted, on which function,at which specific line
of code in the script,and so on and so on. All those information are really
helpful to investigate and finda solution. So the question was, was
it interesting for usto switch to
Stackdriver Logging,knowing that we already
have a lot of stuffin place with Google Analytics?Was it– in fact, does it
make sense to totally forgetabout Google Analytics
and go all Stackdriver,or should we use both of them?And we’re trying to
answer that question. So all the advantages
of Stackdriveris that it’s, well, it’s easier
to see all errors leadingto a specific user. In Google Analytics, you must
aggregated data,but it’s difficult to
see precisely what ishappening for a specific user. With Stackdriver we can
enter a specific user key,link to a user, and
we can see everythingthat’s happening for
the specific user. Plus, we do have
all the stack tracesto have all the details
about the error. And it’s really easy to use. In fact, we don’t
have– we only haveto check a box in the
app script editor,and all the errors start
flowing into Stackdriver. And also, the logs are
available in real time. So that has been very
helpful in the recent weekswhen we deployed a new
version of the add-onbecause it’s not always possible
to launch perfect updates. And if you see new
errors, you areable to quickly react and
updates and push a new version. The limitation that we saw
with Stackdriver Loggingis mostly that there’s no
real metric capability. So this means that it’s
not easy, or not possible,to directly, from
Stackdriver Logging UI,see the number of people
impacted by a specific error,or see the top triggering
errors on your script and so on. The good news is that it’s
possible to export logsto BigQuery. And when you do that, it
solves the previous limitationbecause we can aggregate the
data available in StackdriverLogging in BigQuery
in any way we want. And that’s what I
will show you now. So it’s very easy
to set up exportto BigQuery in
the cloud console. You only have to click on a
few buttons to set that up. And once that is
set up, the logsare sent to BigQuery in real
time using streaming inserts. A new table is
created every day. You don’t have to create the
table schema, or anything. And it’s good because
you can decideif you want to keep
all the logs forever,or you can set up expiration
dates on the tablesto be sure that you
won’t be invoicedtoo much at the end of the
month because BigQuery hasa free tier, but
if you are storingtoo much data in BigQuery,
it’s going to cost yousomething at some point. So usually those
kinds of logs areuseful for a few days or
months, or something like that. And it’s possible
to say to BigQuery,”OK, I only want to keep
this data for a month,and that’s all. “Also, directly in
Stackdriver, if youare using the free
version of Stackdriver,logs are only kept
for seven days. So if you want to increase that,
using the BigQuery integrationdoes help, because you can save
all logs forever if you want. So once the data
are in BigQuery,you can make
inquiries, as I said. So the logs are split
in daily tables,but it’s easy to
perform inquiriesover multiple daily table
within a day trench. So it means that you don’t have
to look at the errors per day,but you can look at the
errors over a period of days. So, for example, among
the useful queries,I will explain a bit right
after how we write our queries. But among the useful queries
we have you can, for example,see the number of
errors happeningevery day and the number
of impacted users. So that’s just basic statistics,
but it doesn’t tell youwhere it’s happening exactly. So you can also list the most
recurring errors on the script,and also look at the
number of impacted usersfor those errors. So you will know which–which error to fix, while
some of– most of those errorsare errors happening
on Google Sites. And it’s just a matter of adding
more retrial when it fails. It lets you quickly see
if there’s somethingyou can fix to improve the app. You can see, also, the number
of errors per function name. So to see, also, if a specific
function contains more issuesthan others, and to
which part of your codeyou should take care most,
based on all those data. And we are also able to
see the number of errorsgenerated by each user. As Paul explained
previously, whenApps Script is automatically
sending data to Stackdriver,each time it’s sending
a new error log,it provides the user
key for specific users. So we’re able to see if some
users are encountering waymore errors than other people,
and then investigate onthat because sometimes people
do have a lot of errors,but aren’t reporting
an issue to us. So it’s useful to understand
what is happening. And maybe that’s
something we canfix because we are simply not
thinking about the specific usecase. And so this will help
us investigate and seethat people are using the
add-on in a specific waythat we haven’t
thought of, and we canapply a fix to make it work. OK, so, if we take a look
at the query syntax–so first, when you want to
make a query in BigQuery,you have to indicate the
source data to be queried. So usually it begins with the
ID of the project on the cloudconsole. Every Apps Script project
is linked to a cloud consoleproject where you can
find the Stackdriver logs. So for– in the case of
Yet Another Mail Merge,we have the YAMM. app_scripts
console logs that areautomatically created
when we have activatedthe synchronization between
Stackdriver and BigQuery. Here we can indicate
with BigQuerythat we want to query
multiple daily tableover a specific date range. So after that, I
will simply tellthat I want to query the tables
from now to the last seven daysto have all the
most fresh errors. I can do my Select, and indicate
if I want to display issuesby date, according to
the number of errors,and indicate also
the users impacted. So we can retrieve
the user key, as wewere saying before,
that is alwayspassed by Apps Scripts for
each log sent to the driver. Also, we can indicate that
we want only to investigateon errors, knowing
that Stackdriverlets you log errors, but
also other information. And we will see that very soon. We can also do a group by to–group by to get a date
and get some aggregates. OK, so, exports to BigQuery
are a very nice addition. It’s not available
in Google Analytics. So Google Analytics only
provides sample data,and sometimes it’s not enough. If you want an integration
between Google Analyticsand BigQuery, you
have to go to–you have to pay for Google
Analytics Premium, whichis very, very expensive. And so, the ability
to use Stackdriver,with it’s free integration,
with BigQuery is quite useful. So we decided that, as Google
Analytics do provide chartsand a lot of metrics
right out of the box,it makes sense to continue
to use Analytics to trackour number of monthly
users, the usage of valuesfeature in the add-on,
but it made total senseto stop plugging errors
in Google Analyticsand start logging
them in Stackdriver. OK, and now we
will see how we cansend more logs to Stackdriver,
in addition to errors,to help you when you want to
push updates, or understand howpeople are using the add-on. So first I will
do a little demo,just because we
haven’t showed yethow easy it is to integrate
Apps Script and Stackdriver. So, as we said
before, for now it’sonly available as
part of EAP slashtrusted desktop program,
so you don’t have it yet. But it’s rather easy to add
a new line to log informationin Apps Script. Here, I will just send a little
information to cloud logging. So if I run this function,
it logs automaticallythis information in
Stackdriver Logging,and I can then click on
the view console logs. And, as it’s the first time– as
it’s a new Apps Script project,I haven’t actually
enabled logging before. So Apps Script is
asking me if I wantto enable exception logging
for all future executionof the script. And that’s very
useful because thismeans that without adding
any more lines of code,every time the script
produces an error,it will be automatically sent to
cloud– to Stackdriver Logging. So here it lets me open
the console project linkedto my Apps Script protect–opens directly in
this console project–my
project Stackdriver Logging,and I can see the
information I just sent. Where is the timestamp, my
user key for this project,so if multiple users
are using the script,they will of their own user key. I can see that the–it has– the log has been
sent by the function namemy function, that the
type iseditor, and you–it’s also makes a difference
if logs are coming from–time drive and trigger–from a UI of your add-on in the
spreadsheet– in the sidebar–and so on. So it’s quite useful
to exactly seehow the error happened exactly. And so here it’s not an
error, it’s a simple log. If I want to generate an
error, it’s very simple. So here, for example,
a mistake in the code. I don’t– I haven’t put that
console. log but console. org. And so If I start
streaming the log,I should be able
to find the errors. You can see that here I haven’t
entered any new line of code,not to try
or anything, to sendthe error back to Stackdriver. It’s just that among
the project properties,log exceptions is checked
now, automatically. It means that every time the
script will show an error,I will be able to find
the error in Stackdriver. That’s very useful
because you don’thave to add any
complexity to your code,and you can benefit from
the feature right away. That’s all for the demo. If I can go back to
the presentation,thank you very much. So in addition to
basic informationlike the
I just entered,it’s also possible to
send additional infoas JSON objects. So, for example, here we
are collecting informationabout the number of e-mails
sent to better understandif people are able to send
all their emails in one batch,or if they have to do it in
multiple batches, and so on. So you can put a lot of
different information–and you will retrieve all this
information in StackdriverLogging as JSON output. And you can then reuse all that
information to filter the logs. So if you see at the
top of the screen shots,there’s a little
query bar where youcan indicate that you want
to filter the data basedon the JSON payload and the
data in the JSON payload. So that’s really
useful, especiallywhen you are unloading
the support for the appbecause if you need to see
everything that happenedto a specific user who
is asking for help, well,we can easily add
information using–or consult that log at
every important pointduring the use of the add-on. And so, for example,
I can automaticallycollect the remaining
daily quota,and doing that I don’t
send the email myself. I’m calling the Gmail
API to send emailsthat each user has a specific
quota to send emails every day. So I can check the
quota beforehand,and see how many emails he
will– he was able to send,and the time needed to send
all those emails, the remainingquota after that,
and so on and so on. And with all those
information, whensomeone asks for help
on our support platform,we’re able to check back
the logs for these users,and this is really
helpful to understandwhat he has done because
every user is notable to exactly describe what
he has done before havinga specific issue, and we are
able to see all the historyand better help him
understand what went wrong. Something– we started using
Stackdriver Logging in partbecause–due to a change
in the Gmail EPI–a lot of users reported
that it took timeto send email campaigns. And so we wanted to know
exactly how many peoplewere impacted by slow sending
time for our email campaign. So we started to
tracking that exactly–to track that exactly. We added different
information to understandwhat could cause this issue. Could it be equally
different of the draft size?So, for example, if you change
a lot of images and attachmentsto your draft, so you end
up with a 20 megabyte draftthat you will send
to 1,000 people,it means that you will, in
fact, send 20 gigabytes of datawith your campaign. And so that might explain why
some people find it a bit slow. So in fact, it was
not the reason,and we were able to see that
it really simply dependson the number of emails
you want to send. Gmail– the Gmail API–has specific
threshold to make surethat you are not sending
too many emails in a veryshort time. And based on all the data we
aggregated with Stackdriver,we’re able to update
all documentationto indicate to people the time
needed to spend a campaign–to send a campaign based
on the recipients you add. And we were– we were
able also to updateadd-on itself to warn users
before they send a campaign. So, for example, if they’re
done with spreadsheets and wantto start a campaign sending
e-mails to more than 100 and–1,200 users, we know that
it will take thirty minutes. So we display a
warning, it will takesome time we have to leave your
computers open for some time,so do you want to
do it now or doyou want to reschedule
your campaign, and so on. OK. We’ve displayed previously the–always the user
keys, so Apps Scriptgenerates a specific
user key for each userand then sends it to
Stackdriver Logging. But in fact, if a user sends
you a request,you have to match this
user and his email addresswith a specific user key. And so in Yet
Another Mail Merge weare using Firebase as
our database, wherewe are storing user profiles. In each user profile we
are storing the user key,so that when someone
asks for helpwe are able to open his user
profile, get that user key,and use that to find all logs
related to the specific user. So to do that there’s simply–in Apps Script, you can
call thatget temporary active user
key, and send that informationto your own database. So in our case, we are
sending that to Firebase. And so that’s especially
useful to investigate whenpeople are asking for help. Also, as it’s yet
another tool addedto add metrics and do
some analytics on usageof the add-on, you might
want to aggregate everythingin a single dashboard. And while the good news is that
our studio is here to help,there have been some other
session about that studio,so I won’t speak more
about it– just that itis possible to build a single
dashboard where you willaggregate information
from Google Analytics,and information
from Stackdriver,so you have everything
in one single place. And it’s not pretty,
but you can actuallydo a very pretty dashboard
with that studio. OK, so, some resources. So if you want to have access
to Stackdriver Logging,for now you have
to join the EAP. So you have
for that. There’s also a
documentation explaininghow to activate Stackdriver
Logging on your scriptand how to use it, especially
the console. log part,and everything outside of
the basic error logging. There are documentation
also on how to exportStackdriver logs to BigQuery–so how to set the
synchronization up,and also the query
reference, to knowbetter how to query multiple
table over a and other things like that. At the beginning of
the presentation,I also spoke about
Google Analytics. If you also want to start
using Google Analyticsin your add-ons, know that
there are some specificities,and so there is a blog post
available on the GoogleDeveloper blog about that,
about how to best use the userIDs and a lot of other
features in Google Analyticsfor indicates of
Apps Script add-ons. And if you’re interested
in using Firebaseas your database for your add-on
or any Apps Script project,there’s a code library and
some examples that are alsoavailable at this address. PAUL MCREYNOLDS: Awesome. Thank you, Romain,
that was great. Thanks everybody for
coming late in the day. It was great to see you.