IKAI LAN: Hi, everybody. Welcome to a very special
edition of Google Developers Live. My name's Ikai Lan. NICK MIHAILOVSKI: And I'm
Nick Mihailovski. IKAI LAN: You almost
botched your own last name right there. It's a hard last name
sometimes, you know? It takes a lot of practice
to get it right. IKAI LAN: I'm glad you had
to do it instead of me. That could've been
me right there. So for this show, it's a very
special edition, because we'll be talking about Google
Apps Script. And we were arguing before the
show about whether this was my show or his show. Nick is here from the Google
Analytics team, and we're going to be talking about ways
that you can speed up how you use or optimize-- whatever verb we want to use-- you can enhance your Google
Analytics usage using Google Apps Script. NICK MIHAILOVSKI: Absolutely. IKAI LAN: Before we get started,
so there's gonna be lots of people joining us on
this show, half of which will be coming from Google Analytics
side, and half of us will be coming from the
Apps Script side. For those of you that don't
know, my name is Ikai Lan. I work on the Apps Script
Developer Relations Team. We have a regular show that runs
every week where we do office hours. And what we're trying to do is
we're trying to do more shows like this when we talk about
how to use Apps Script with other Google properties. About two weeks ago, we did
a show about using apps [INAUDIBLE] big query, and today
we'll be doing something with one of our most popular
APIs, which is a Google Analytics API. Now, Apps Script, for the
analytics user, it's a way to operate and integrate all your
different Google properties. So for instance, you might want
to write a spreadsheet app that sends out emails or
creates Google documents, and that's what you'd use
Apps Script for. We have an editor. We have an ID. It's all cloud based. All you need is an internet
connection and a browser, and you can start writing code to
automate your work flows. And Nick is here because
he works on the Google Analytics team. And he's going to talk to us
about Google Analytics, the analytics API, and some of the
cool stuff that his team has been working on. I've heard the word magic thrown
around a couple of times, so he'll be telling
us about that. NICK MIHAILOVSKI: Definitely
a lot of magic in the show today, yeah. Definitely. IKAI LAN: So Nick, why don't you
tell us a little bit about Google Analytics. I mean, hopefully you've
all heard of this. This is one of the most awesome properties on the internet. NICK MIHAILOVSKI: Absolutely. Analytics is fantastic, and
absolutely amazing. What Analytics allows you to
do is it allows business owners to measure how users
interact with your business. So if you own a website, you
can now measure how many visitors come to your site, from
where they come, and what do they do. You can also measure this kind
of content interaction within mobile applications, websites,
and we released something new called Universal Analytics that
now allows you to bring Google Analytics to pretty
much any environment, any platform, to measure
user interaction. So the Google Analytics platform
is quite robust. There's ways to automate configuration, to collect data. One part that we're really
excited about is our reporting APIs. And with the reporting
APIs, it allows you to get the data-- the actual reporting data-- out of Google Analytics and
put it into different environments. And so what we've done with Apps
Script is we've made it a lot easier to use Apps Script
to access your Google Analytics data. IKAI LAN: Very cool. And you work on the API. So what is it that people use
the Analytics API for? What are some the most
common use cases? NICK MIHAILOVSKI: For
the reporting API, it's a great question. I mean, when you think about how
you're doing analysis, you typically are coming
into the reports. You have a business question
that you want to answer, and so the user interface provides
this really rich experience to go in, configure reports, pivot
on different data, and really answer the question. But what we found is, once you
kind of got your initial question answered, what most
people do is they'll return back to Google Analytics to
view how that data is trending over time. Is it improving? Is it declining? And so this revisiting of
data is an opportunity-- it's a complex process. It's an opportunity
to automate. So what lot of people are doing
is saving a lot of time automating all the reporting
using our APIs. IKAI LAN: Let's use a little
bit more concrete examples. Let's say I've got a fantasy
football website where I give out tips, because
I'm a champion. So I want to figure out what's
the type of data I can track, and what would I use
the API for? NICK MIHAILOVSKI: So if you have
a fantasy football site, the first question you might
answer is how many people are coming to my site? IKAI LAN: I'd say on the
order of three or four. NICK MIHAILOVSKI: Right. So ideally, over time, you'd
want to know as the season progressed, as you do different
things, is that increasing or decreasing? So your first experience with
Google Analytics is figuring out where does the data live? What is that three
or four number? But over time, you don't want
to have to log in, click around, get that number again. You just want that number
to be presented to you. That's where the automation of
the reporting API comes in, because now you can bring that
data and put it in a dashboard that you can just view
automatically without having to do all these logins. IKAI LAN: And what's some of
the data I can get beyond a number of viewers? NICK MIHAILOVSKI: It's a
huge amount of data. We have over 200 data
points that are API. IKAI LAN: 200? NICK MIHAILOVSKI: Yeah,
it's pretty crazy. You can get data-- geographical
data, so from where the world are visitors
coming to your site? What was the different
traffic source? What was the different ads? What content were
they viewing? If you're on a mobile device,
you might run a website. How many people are coming
to your site from a mobile device? It's a simple question to answer
using Google Analytics. IKAI LAN: OK. So I understand that there
was some stuff you were going to show me. NICK MIHAILOVSKI: Absolutely. So one of the things that we've
done is we've worked with Apps Script. So if you're familiar with Apps
Script, it's a really easy way to automate
processes. And so the integration we've
done is we've created a couple APIs for Google Analytics using
Apps Script allowing you to get access to your data. IKAI LAN: So a little
bit of background on Apps Script again. Apps Script-- we started out as
functions for spreadsheets. So things such as I want
a custom function. And we started branching out
doing more and more things. One of the most common
use cases of Apps Script is mail merge. What you would do is you would
set values, you would set email addresses in a
Google spreadsheet. The Apps Script will run on a
periodic schedule, maybe once a day, maybe once a week. It'll go all the way down and
it'll email each person individually. And that's what we mean when
we say automating and scheduling workflows. Apps Script allows you to do
this so that you don't have to manually do these repetitive
tasks over and over again. And that's why I think Apps
Script is such a perfect marriage for Google Analytics
and the API, because you have this repetitive task of
accessing data that you just want to automate over
and over again. So what we've done is we've
released these APIs, and along with the APIs is we actually
released an application. It's called the Google Analytics
reporting automation tool, or as what we
call Magic Script. IKAI LAN: Magic Script! NICK MIHAILOVSKI: So Magic
Script is awesome. What we pretty much did is we
looked at all the different use cases people had for
creating simple dashboards, and we said, instead of having
to write all the code, we'll just write the code for them,
include the best practices, and release a script that
integrates with Google Spreadsheets to allow you to get
all your Google Analytics tabular data directly into
Google Spreadsheet. IKAI LAN: Very cool. And you're going to show
us this right now. NICK MIHAILOVSKI: Absolutely. So here I have a Google
spreadsheet on my page. I've loaded the script before,
and we'll link to some similar descriptions on how you can
actually set the script up. I've gone ahead and set
the script up already. IKAI LAN: And what we'll do is
this show will be posted to our standard channel
on YouTube. It'll be
YouTube.com/GoogleDevelopers. It's where all of
our shows are. We will be posting links
to all the things we're doing in this show. So if you're following along
live, or if you're watching the show, you can click
those links. You don't need to remember what
we're saying, just wait for the show to get posted. NICK MIHAILOVSKI: Absolutely. So the Magic Script
is installed. We're ready to use the magic. So the first thing you'll notice
here is there's a new menu item we've used. It's called Google Analytics. IKAI LAN: And for the people
who are new to Apps Script, one of the things you can do
using Apps Script is you can add custom menus to
spreadsheets. And this is a very good thing to
do, because a lot of times, the people using your scripts
are going to be people that didn't program and don't want
to open up a script editor. In fact, they don't
even want to know there is a script editor. They just want to know,
where is my menu? I just want to push a button
and do this thing. I want to run the mail merge. I want to verify
these columns. That's why we allow people
to build custom menus. And you guys have taken
advantage of this to build a special custom menu for
Google Analytics. NICK MIHAILOVSKI: Absolutely. If you don't code, you're still
going to get value from the Magic Script. IKAI LAN: Totally. NICK MIHAILOVSKI: So
here's the custom menu for Google Analytics. There's a couple different
options here that you have that I'll talk through
as well. What we've also done is when the
script runs, by default, it creates this new tab at the
bottom called GA Config, and we'll show you how to use it. So the first question, the first
task you want to do is get some data out
of Analytics. So what you do is you
go to the menu, and you Create Core Report. And that'll go ahead and run a
script that will update the GA Config tab. And there you go. And now when you access data
from Analytics, you generate a query, and the query specifies
the data you want to get out of the product. In this case, there's a couple
required fields. The first one is understanding,
in Analytics, what is the report profile, what
is the set of data that you want the access to? And that's defining the query
by what we have here is the IDs parameter. Now, most people don't know what
their IDs parameter, and we know that and
understand it. So what we've done is we've
created a little tool under the Google Analytics tab here,
Menu, and when you click Find Profile IDs, we'll go out using
the API, query all the accounts for the authorized
user, and present a dialogue that shows you all the IDs
you have access to. IKAI LAN: Now, this dialogue may
look nonstandard to people using spreadsheets, and the
reason is that it is nonstandard. So using Apps Script, you can
create custom UIs for your spreadsheets. You can build them using
HTML and JavaScript via the HTML service. Alternatively, you can use UI
service, which comes with a lot of built in widgets like
charts and drop down boxes, and I think that that's what
you guys are doing here. NICK MIHAILOVSKI: Absolutely. And what's really cool is this
drop down box here is dynamically populated. So we're actually, for each of
these drop down boxes, we're issuing a query to our
API to populate them. And as you change various
selections, they'll change as well. So here we have a
little pause. At Google, we all have to
actually update our Macs, and I've been so busy preparing this
demo that I haven't had a chance to run an update
to Mountain Lion. IKAI LAN: I love Murphy's Law. Don't you? NICK MIHAILOVSKI: We knew
this would run. There's no way for
us to change it. Fantastic. So after a brief interruption to
streamline my data process, let's go back to
the demo here. So as I was mentioning, what's
cool about these drop downs is as you select various drop
downs, we'll actually go ahead, query different APIs,
and update that. So right there, there was three
queries we issued and updated the list and
have different IDs. IKAI LAN: What's this
website right here? NICK MIHAILOVSKI: So this is
another blogger that we have. He's our digital evangelist
at Google, [INAUDIBLE]. And I've access to
his blog's data. So if we wanted to access his
data through the script, we can use this ID. IKAI LAN: How did that happen? NICK MIHAILOVSKI: Well, we're
good friends and we've done a lot of shows together. IKAI LAN: Yeah? No [INAUDIBLE] hacking, right? NICK MIHAILOVSKI: So let's go
back to our demo site, the Google store. It'll take a second. So I have a profile here,
and we have one here. I'm going to go use the one that
I'm familiar with, which is my test one, which is 1174. I already know it, because
I've done this demo a couple times. So the first thing is you figure
out what profile you want to get data for. The second one is you need to
figure out the date range, and so in analytics, you can put
the start and end date. We've made a little function
here to really simplify it. You can just get the
last n days. This is really useful if you
want to automate the script. You don't know what today's
data is, because it's automatically running. So for this, we'll say, let's
get data for the last 28 days. Now, you also need to
specify the data you get out of Analytics. So here we have two values--
metrics and dimensions. Metrics represents the
numerical data. So we want to get the total
amount of visits coming to our site. And dimensions represents
the actual rows, or the string values. So we want to get the source
of traffic coming to the site as well. So we'll say j source is the
name of them, and we want to sort descending by j visits. There's a couple other
parameters here that are all documented, but the last
important one is defining where does the data go
when it's processed? IKAI LAN: And before you go on,
I mean, these values are totally unfamiliar to me. Where did you get these from? Is there a list of
these somewhere? NICK MIHAILOVSKI: Yeah, that's
actually a great question. So we actually have an amazing
set of writers who are doing our developer documentations. And if you go to
developers.googl e.com/analytics, you get
this beautiful site. And if you go under the core
reporting API in the reference section, we actually give you
a list of all the 200 data points that we return. So if you click Expand All,
you'll see a huge plethora of values for dimensions
and metrics. Visits is right here. If you want to know what is a
visit, you can click right into that, and boom. It counts the total number
of sessions. IKAI LAN: Very, very cool. NICK MIHAILOVSKI: So what we
want to know is the total number of sessions for the last
28 days, and show all the different traffic sources
that sent that. So I'll say my report
will be this sheet. IKAI LAN: And what is this
name that you're entering right here? NICK MIHAILOVSKI: So what we
want to do is put all the configurations in the GA
Config tab, right? So if we want to run multiple
reports, they're all defined in one location. But where the data gets
outputted will be to a separate tab. And so this will be the
name of the tab. So what we'll see here is when
this runs, all the data will be in the My Report tab. IKAI LAN: So one thing that the
Analytics team is doing here is they're using
spreadsheets as a UI, and this is one of the very powerful
things you can do with Apps Script. With Apps Script, you can create
your own UIs using UI service or HTML service. But a lot of people have opted
to use Spreadsheets, because Spreadsheets itself
is very powerful. You've got sorting, you've got
formulas, you already have a lot of things built in that I
would say you get for free already by using the
spreadsheets UI. And you guys have decided to
go that route, which is-- so another you guys could have
gone would have been to build a pop up box that allows
him to input data. But you've got a lot
of options when you're building UIs. So anyway, part of
my interruption. NICK MIHAILOVSKI: Absolutely,
yeah. And then I'll show you
a little bit why we chose this route. I mean, there's definitely
considerations on various designs, and this actually
helps us out in some other ways. Let's go with the standard
approach here. Once we've got the configuration
report set up, go ahead under analytics menu
and click Get Data. So again, we haven't
written any code. We've just configured
this script to run. The script is going out, using
this configuration, and getting some data. And so what we've done is now
created a little dialog to describe the status
of this report. Here we've seen everything is
successful, and we've gone ahead and we've written
the results and the script is done. Fantastic. IKAI LAN: The status box could
be a little prettier. I mean, I think it's lacking
in the ponies department. NICK MIHAILOVSKI: Yeah, if we
had more images, and some animated gifs, and some blink
tags, this would definitely work better. IKAI LAN: Marquee. Don't forget marquee. NICK MIHAILOVSKI: Marquee
would be perfect. So what we've done here,
boom, just like magic. We just now have a report of
all our analytics data in Google Spreadsheets. Here we see all the different
traffic sources. Google, right now, for this
site, is returning the most traffic, but we see some of the
other search engines, some referral traffic from YouTube
and so forth as well. So it's a really quick overview
of how easy it is to get data using Apps Script
and Spreadsheets. IKAI LAN: So, just out of
curiosity, I know what these numbers are because
you told me. But what would this look like
on the Analytics website? NICK MIHAILOVSKI: That's
a good question. So if you go on Analytics, you
can see all the data here. Here we see for this date
range, we see there's a certain amount of visits. If we go to Google Analytics
and sign in, and we get our report here, we'd be able to
now go into the traffic sources overview section, take
a look at the sources-- remember, we're looking at
sources of traffic-- and then here's the
source tab. And so here, this is the
same view of the data. The date range is slightly
different, but here you get the top 10, and of course you
can select this down to be the top 100 results as well. So this is how it would
look in the Google Analytics interface. IKAI LAN: This is a pretty
robust interface. I mean, I guess you've walked
into my trap, which is why would you want to export
to a spreadsheet? Why use App Script at all? Why not just use this website? NICK MIHAILOVSKI: Absolutely. For people not familiar with
Analytics, you can actually export all these different
things. For example, to CSV,
to TSV for Excel. Now, that totally works in many
cases, but what we really wanted to solve for
is two use cases. One of them is that the
permissions you have for Analytics might be different
than the permissions of data you have for people within
your organization. And so by using Apps Script,
you actually have to authenticate to get access. The script has to authenticate,
get access to your data, to get the data
into a spreadsheet. But once the data is in the
spreadsheet, you now have all the Google Docs and Spreadsheets
permissions to share that data within
your organization. So for some people, they might
not want to have the CEO and all the marketing team have
access to their Google Analytics data. Instead, they have one person
with access to their GA data, and then automating
the data into a spreadsheet, and share that. The other thing is automation. So, over time, it takes a lot of
clicks to go into all your different reports and
export this to CSV. With the Apps Script, you can
completely automate that process, saving yourself
a lot of time. So really, really easy
ways to automate it. IKAI LAN: What are some of the
things that you could do after-- and I know the answer
to this question, but I'm curious to see if you
know the answer. What are some things you can
do after the data's in the spreadsheet? NICK MIHAILOVSKI: Yeah, so the
first thing you might want to do is you might want to
run this every day. You don't want to have
to go and recreate this all the time. So there's some nice features
built into Spreadsheets along with Apps Script that you
can use, and one of them is called Triggers. So if you go under
Tools and you go into the script editor-- IKAI LAN: So this gets a little
bit more advanced. So if you are one of these
people that are not a full time programmer, you'll still be
able to use this tool, but some of the stuff may be a
little bit more advanced. This is just a quick warning. So this is the script editor. This is where you write all of
your code for Apps Script. And you'll be able to open this
up and see that all of the code for this tool that the
Analytics team has built, it's all just standard
Apps Script code. NICK MIHAILOVSKI: Absolutely. And what's great about this is
because it's just a script, all the source is available
for developers. So if you want to learn some
best practices of what to do, how to set up-- take the same look at what
we're doing here. All this code is completely
available, open source. We even have some really nice
documentation in the code here, just so that you
know what's going on. So aside from the code, if we
wanted to automate running this script, under the resources
menu, we have something called all
your triggers. So what triggers allows you
to do is to run any of the functions that have been
defined automatically. IKAI LAN: Right. And triggers are a
little bit more-- triggers give you a
lot of options. For instance, you can run
them on a set schedule. That's one of the primary
use cases. But in addition to doing that,
you can also do things like set triggers on spreadsheets. You can set a spreadsheet so
when the spreadsheet's edited, it automatically
runs some code. It can run the code when the
spreadsheet's open, and one of the ones I like the most is that
it allows you to run code whenever someone
submits a form. So we use this all the time
whenever we have events. Whenever we have events, we like
to do on form submit, and we send people an email that
says, hey, thank you for registering. We have space or we don't
have space for you. So triggers are one of the many
ways you can get to your Apps Script. And I like them because you kind
of say, run this script automatically when this
event happens. Hence the trigger. NICK MIHAILOVSKI: Boom. And that's it. These triggers, the way we've
use them is to actually automate actually getting
data from your account. So when you create a new
trigger, here's all the functions that we've offered. They're the same ones that
are in the menu. You can click on the Get Data,
which runs everything, all the configurations. You can do this time driven. You can have it run every day,
and you can have it run midnight to 1:00 AM. So that way, every night, all
the data is going to be retrieved for the
last 28 days. If there's any errors with it,
you can actually set it up so that it will email if there's an
error, so that way you can go back and get that stuff. IKAI LAN: And with triggers,
you can manage these programmatically. So what Nick has shown us here
is how we can set up a trigger through the menu. But you can also set it up so
that you can dynamically create triggers and dynamically
delete triggers, and this is very,
very powerful. NICK MIHAILOVSKI: Absolutely. And so once you've set up the
trigger, you hit Save. And now, every night, I'll get
the last 28 days worth of data and put them directly into
the spreadsheet. So that's a little quick
overview of Magic Script, and kind of how we've used Apps
Scripts to build an app that makes it super easy for
nontechnical people to actually access the Google
Analytics data. IKAI LAN: So a little bit more
drilling down on that topic. Apps Script allows you go ahead
and do a lot of really cool things, like, for instance,
you can email the results people on
a nightly basis. You can update a Google Doc. You can send them out via what's
called URL fetch to an external API. I was just talking to someone
today who was telling me how he uses a cloud print API this
way, and he prints stuff. And it was a really funny lunch
conversation, because we were talking about how you could
denial of service attack his printer. But we won't be teaching you
guys how to do this today. So one question I have for you
is, you were showing me something with a Google
site earlier. What was that all about? NICK MIHAILOVSKI: Absolutely. So Apps Script's completely
automated and integrated with all the different Google
properties. What's nice about here with
Spreadsheets now that the data is in a spreadsheet,
there's some nice tools that are included. If we wanted to visualize
this, you have all the visualization tools here. So if we wanted to get a chart
of the top 10 sources of traffic, you just click
this little button, click Insert, boom. You have a nice little chart
that shows this data. Very easy way to
have your data. IKAI LAN: And this is nice,
because then if you use the Google Analytics web API,
you're kind of bound to whichever chart's
already defined. But if you have some custom need
for charts, if you need to integrate with different
spreadsheets, or different data sources, this is where Apps
Script really is kind of a killer tool for taking all
these different data sources and building a single
dashboard for you. NICK MIHAILOVSKI: Absolutely. And so what's nice about this
is Spreadsheets is also integrated with Google Sites
and other Google products. And so what I can do is create
a dashboard in Google Sites. So if I go to sites.google.com
here, this is the free product that you can use. I can go ahead and create a new
Google site, and creating this is probably the hardest
part, because there's a lot of stuff you have to input
in here, like-- IKAI LAN: I'll bet you
that's taken away. I'll bet you that's gone. We'll see. Now what does this say? NICK MIHAILOVSKI: That's
a good question. IKAI LAN: Oh, maybe this
means you're a robot. NICK MIHAILOVSKI: All right. Will it work? Oh, it's not available. IKAI LAN: Just bang
some keys, dude. NICK MIHAILOVSKI: All right. There it goes. IKAI LAN: And it looks like
the site is being created right now. NICK MIHAILOVSKI: That's it. Here's a brand new
Google site. If I want to, I can just click
Edit Page, go to Insert, and go ahead and click
under Chart. We'll see here's the spreadsheet
that I was working with earlier. Go ahead and select that, and
you'll see this chart that I created from that data
is right there. So I can go ahead and
click this chart, go ahead and save it. It's embedded. And what happens now when I save
this page is I'll have access to see this chart
directly in Spreadsheets. So this is like a beautiful way
that you can actually have multiple charts running all on
the same dashboard, and you could present his dashboard
to different users who are curious about how many people
are going to your fantasy football team, and how
many people are going to those pages. IKAI LAN: Very, very cool. So we've got a few
minutes left. And there have been some
questions in Google Moderator. So let's take some time to
answer a few of these. NICK MIHAILOVSKI: For sure. MALE SPEAKER: So, Nick and
Ikai, while you're going through that, Cesar had a
question on the YouTube livestream that was asking
about testing. He says he was testing during
the week, and he wants to know if there's a way to have the
catalog of metrics and dimensions automatic instead
of consulting the guide. NICK MIHAILOVSKI: That's
a great question. So this has been a big feature
request-- it's is there an API for the dimensions
and metrics? There currently isn't. We have a pretty comprehensive
guide. It's something we're
looking at. So currently, no, but we're
looking into that. But great question. IKAI LAN: It's something we
could totally do at some point using a drop down via the
spreadsheets popup again. But nothing to announce,
right? NICK MIHAILOVSKI: Right. Right now, we don't
have an API. We have a lot of tools, a lot
of people asking for it, but definitely great feature
request. IKAI LAN: All right. Let's go to the Moderator
page. NICK MIHAILOVSKI: So the first
question here is from A Taylor in McKinney, Texas. He asked, how can I pull a
Google Analytics report into a temporary Google document that
will expire after 30 days? IKAI LAN: Self destructing. I like it. I like this. So this is something that you
can do via triggers, and we were just talking about setting
up time triggers. And one nice thing is that you
can programmatically create triggers using code. I don't remember
the exact code. It's new trigger, new clock base
trigger, and you set a date that you want something
to be deleted. And you could totally create
that for a document that says, for this document, delete this
bad boy after 30 days if it's still around. And what it does that, the
document will be gone. So it's not built into Docs,
but it's something that you can do with Apps Script. NICK MIHAILOVSKI: I think that's
what's beautiful about Apps Script, is that there's a
feature like this that's not within what was provided, you
can definitely write that functionality in to
do exactly that. That's a great answer. So here's the second question. This is from Jimmy in
Birmingham, Alabama. How can I modify the script to
pull data from multiple GA accounts into one report? That's a fantastic question. I'll actually show you
how easy it is. The way we set this up here is
within the actual screen is if you go to the GA Config tab,
you can actually create multiple reports. And so if I create another core
report here, you'll see another two columns being added
into the spreadsheet here, and there you go. And so as long as you specify
the IDS parameter here, now you can say whatever the other
IDS parameters you have, this could be in a different
account. When you call get data,
each of the different tabs that you define-- so, for example, this would
be like report 2-- the data will be actually
executed sequentially for each of these reports. So I was talking to one person,
he had 13 reports configured in one spreadsheet. So every night, 13 queries were
made to update 18 tabs. So definitely possible. IKAI LAN: And that's really
nice, because then you don't have to go 13 different
tabs in your browser. You get them all
in one report. NICK MIHAILOVSKI: Exactly. Super easy. MALE SPEAKER: Speaking of
reports, [INAUDIBLE] from YouTube asks, can you embed
those reports on a website from Apps Script? IKAI LAN: You can, yes. There's lots of different
ways. And one way is, for instance,
with Apps Script you can publish a webpage via the HTML
service, and when you do this, you can either iframe that data,
and there's one more way, which is Apps Script has
something called a content service, where you can publish
some kind of JSON result. And you can create using
the [? gviz ?] library, you can consume
a JSON result by setting a data source. And that's one thing that
there's an APIs for get data source URL and the charts API. So using the charts API, you
can spit out a data source API, and you can consume that on
a different website, or you can use the iframe method. You've got a lot of different
ways of doing this. NICK MIHAILOVSKI: Absolutely. I think one of the easiest ways,
as Ikai was mentioning, is if you make the chart public,
you can actually query that data source from the
visualization charts API, and then you get the data directly
on the page as well. It's really interesting, because
we actually have a lot of quotas for the analytic
service, so in this case, when you put the data into Apps
Script and you query Apps Scripts, it actually acts as a
caching layer, so that way you can adhere to our quota to get
the data from our service once, which is the expensive
part, and then just serving the read only data then to all
your users is cheap, and then you can get around
it [INAUDIBLE]. IKAI LAN: And when say
expensive, cheap, you mean like computationally,
time wise? NICK MIHAILOVSKI: Right. So you might have billions of
pages, billions of data points that analytics is processing
for any of these given queries, right? And we've abstracted the
complexity here. So for certain queries, they can
be very complex, and so we do have different quotas for
our analytics API than Spreadsheets has for querying
a data source. IKAI LAN: So I think
we've got time for just one more question. NICK MIHAILOVSKI: Great. So the other question here is
from Anna N in New York City, and she asks, please tell me how
to compare time periods. This is a great question,
Anna. It might not be actually
apparent, but the solution really is here is to actually
create two queries, where each of the different time periods
is slightly different. So what I've shown you here is
how you can create data for the last n days. And so what you can also do
here is you can actually create start days
and end days. And so what's nice about this
is Spreadsheets has a lot of functions built in, and
one of the ones I like is the now function. And so if I say equals now, it
will give us today's date. There it is. And the way we wrote the script
is that whether it's today's date in this
representation or the other representation that we have in
Analytics, the script will still run fine. So if I do now equals
now minus 7, I'll get seven days ago. And so for the first query, I'll
have the last seven days, where now is automatically
updating. And then for the second query,
I'll say the end it will be e equals now minus 8, one day
before there, and I'll say equals now minus 14. And now, as long as I can then
copy and paste the different dimensions, when I run these two
reports and execute them, you'll see the different date
ranges for that data. So, a really simple way to get
two time ranges that are always changing day after day
into two different reports. IKAI LAN: And with that, I think
we're out of time, which is unfortunate, because I feel
like we've got so much to talk about here. NICK MIHAILOVSKI: Yeah. IKAI LAN: Was there
a followup show? A sequel? NICK MIHAILOVSKI: Potentially. Potentially. IKAI LAN: We'll see. Send this video out to your
friends, and get the views up there, and we'll do a sequel. Thank you guys so very much
for joining us today, if you're on the live show. If you're not, thank you for
watching this video. My name's Ikai Lan. NICK MIHAILOVSKI: And my name's
Nick Mihailovski. IKAI LAN: And we'll be catching
you on the next show. NICK MIHAILOVSKI: Yep. Take care. IKAI LAN: Take it easy, guys.
