Tip:
Highlight text to annotate it
X
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 *** 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.