KALYAN REDDY: Hey, everyone.
Welcome.
Today we have a special
edition of an Apps
Script talk for you.
We're going to be talking about
the Charts Service.
But before we get to that, I'd
like to introduce a new member
of our Developer Relations
team for Apps Script.
This is Dan Lazin.
And I'll let him introduce
himself.
You've been working here
for how long, Dan?
DAN LAZIN: Oh, all of three,
maybe four weeks now.
KALYAN REDDY: All right.
DAN LAZIN: I'm pretty
much an expert now.
I'm a new tech writer on the
Developer Relations team for
Apps Script.
I'm going to be helping
to improve some of the
documentation and make sure
that you guys can find the
information you need to know.
KALYAN REDDY: All right.
It's great to have someone
newer than I am.
Finally understanding how
to get things done.
All right.
Let's start.
So today, I'd like to talk
about how you can use the
Charts Service in Apps Script
in order to do really quick
dashboards.
You can integrate it very easily
using the capabilities
of Apps Script that you might
have already gleaned from
other tutorials or
other videos.
And one example of such a thing
is easy interaction to
spreadsheets.
Any data that you have in
spreadsheets, you can just
pull in really quickly,
throw up a dashboard.
And everyone can have controls
and filters on it, interact
with it, drill down, visualize
the data any way they want.
It's also easy to get the data
from external sources.
And I'll cover that.
So I'd like to start by going
through a couple of slides.
Let's see.
Everyone can see that?
Great.
So basically, what is it?
We're going to be creating
a dashboard, which is a
combination of several things.
We are using a data source to
get some data into our Apps
Script application.
We're going to add charts and
controls to it in order to let
users change the data
that's visualized.
And then we're going to build
a dashboard in a UI app and
return that so that people can
visualize it on a web page or
embed it in a spreadsheet or
Google Site or what have you.
The second part of this, I'll
be doing a demo showing this
interaction between a
spreadsheet attached to a form
and how those populated
values can
propagate onto a live dashboard.
And then also, I'll be showing
you something that I built
recently that I actually use
in my day-to-day work.
It's like a Stack Overflow
monitoring system.
And it's a way to query an
external API very quickly
using URL Fetch in Apps Script,
and then just pull
that together into
a dashboard.
So let's get started.
DAN LAZIN: Let's.
KALYAN REDDY: All right.
So what is the Charts Service?
It's a part of Apps Script.
It's a service that gives you
access to several different
types of charts.
Examples are area charts, line
charts, scatter, pie.
There's many of them, combined
with controls that I'll get to
later, served from a UI app.
And I think I already
mentioned this.
So an example of how to invoke
it is from a script file.
You would just do Var Chart
equals Charts.newBarChart, dot
setDataTable, Data.
So the newBarChart will give
you a new chart builder, in
this example a bar chart.
And then also, you can
add some data to it.
So that's a very quick outline
of how to do it.
And I'll go into more detail.
One of the major things that's
important about the Charts
Service is the Builder
pattern.
It's used everywhere.
The goal of the Builder pattern
is to abstract the
steps of object creation so
that you can do it in
different orders.
OK, so maybe it's best
demonstrated by an example.
Here I'm using
Charts.newLineChart.
And that returns a line
chart builder object.
And on that object, I can call
these various methods, like
setDimensions, setTitle,
useLogScale.
I can call them in various
orders, as well.
And there are many
more options.
You choose which options you
want to set and change from
the defaults.
And you create your
chart that way.
And the key to everything built
this way is the build
call at the end.
After you're satisfied with the
way you've configured your
object, you call builder.build,
and it returns
you the chart object.
So on the one side, it's
a line chart builder.
You call build.
It returns a line chart.
And then you can embed this
as a chart in anything.
DAN LAZIN: Nice and simple.
KALYAN REDDY: Yes.
So one of the key things about
charts is a data source.
Obviously, if you're going to be
showing data, you need the
data to come in from
somewhere.
There's a couple ways
to do this.
A simple way if you just want
to play around and start
experimenting is to manually
add the data.
So you can just create a
new DataTable builder.
And then you can add a column
to it for each category that
you want in your chart.
And then you can add a
row for each entry.
And then you can do Build,
create the DataTable.
And then you can add
that to a chart.
Another way is a little
bit more automated.
If you have a spreadsheet with
the data you're interested in,
you can access the range of
that spreadsheet, just by
opening the spreadsheet with
an ID, as you'll see later.
This is the first demo.
DAN LAZIN: Using the spreadsheet
service.
KALYAN REDDY: Using the
spreadsheet service, you pull
the spreadsheet really
quickly.
And then you get the range
of the data from
the spreadsheet service.
Then you can just pass that
directly into the DataTable.
And it automatically populates
the columns with the correct
headers, and the rows.
DAN LAZIN: That makes it really
powerful, because then
you can share that spreadsheet
with other people in your
organization.
Everybody can be collaborating
on the same data set.
And you see it reflected in
your live web dashboard.
KALYAN REDDY: Exactly.
And also that's a good point
that you made, that it is a
live dashboard.
Every time someone new goes to
the page, every time you
refresh the page, it'll
dynamically pull the new
values from the spreadsheet
and then update the chart.
DAN LAZIN: Cool.
KALYAN REDDY: And the third
way is from a URL.
You can actually provide a data
source URL and access the
data that way.
I'm not going to be showing
you an example of this.
But I will be using URL Fetch
app to get some data from an
external API and packaging it up
into a DataTable object and
using that in a chart.
We'll get to that.
It's cool.
So the second major part of the
Charts Service are controls.
Controls are basically user
interface widgets that can
interact with a person.
A person can use the controls
to filter the data that the
DataTable shows.
So for example, one thing would
be a category picker, or
maybe a range slider.
Or if your chart has many
different people's names, you
can have a text box that's
a string filter.
You type in a person's name.
And it filters the chart and
shows only the names that
match that filter.
And those are the three main
types of controls-- category
controls, number range
controls, and
string filter controls.
And an example of initializing
those would be using the same
Builder pattern that I talked
about before where you just do
Charts.newNumberRangeFilter to
get a number range filter
builder, for example.
And then you do
setFilterColumnLabel.
So you'll notice that this
method is different from the
method for the other line chart
builder that we saw earlier.
So that's an example of
the Builder pattern.
So you can filter this on
a column labeled Age.
And then when you call Build,
you get a new builder.
You can combine that with
the chart, as we'll
see in just a second.
Now, dashboards--
dashboards are a way to bring
all these things together.
You have your data source.
You have your charts.
You have your controls.
How do you present it
all to the user?
A dashboard allows you to
combine several charts into
one organized layout and have
the controls connect to the
charts so that as you adjust
the controls, as the user
adjusts the controls, it
dynamically queries the data
source, pushes that data
source to the charts to
display only the
relevant data.
DAN LAZIN: That's a really nice
way to visualize data.
It allows you to sort
of on the fly out--
allows different users to filter
things the way they
would in a spreadsheet, but
doing it live right on the
visualized data.
KALYAN REDDY: Exactly.
So that's the key thing
about this.
A dashboard is not static.
You don't just make a view
that's like one size fits all
for everyone and then publish
that and no one has any option
on how to change that.
It's a very dynamic thing.
And people can consume
the data any way that
makes sense to them.
Let's continue to the
live demos now.
The first demo we're going
to go through is--
in the interest of saving time,
I've created a simple
Google Form.
So it's very easy to
create the form.
All you do is you go to Drive.
And you say New Form.
And then you can set it up.
Here I have four questions.
What's your name, your age?
How do you get to work?
And how long is the commute?
It's a simple form to ask
people what their daily
schedule is like, and then to
collate that data and present
it in an interesting
way, maybe.
It's an example.
Anyway, so I've already been
filling in some data here in
order to make it
not too boring.
But here, let's go ahead
and fill in some more.
I'm going to add myself.
I walk to work.
How long is my commute?
Maybe 12 minutes.
Go ahead and submit that.
And as you know, Google Forms
and Spreadsheets have a very
tight integration where
the entry that I
just added gets appended.
And we can add you too, Dan.
Dan.
DAN LAZIN: I'm 33.
Uh-oh.
I think I'm 33.
I have no idea.
KALYAN REDDY: You
think you're 33.
That works.
DAN LAZIN: Google would know
this, here to organize the
world's data.
KALYAN REDDY: How do
you get to work?
DAN LAZIN: I bicycle.
I think you're going
to need a new--
you're going to have to put
me down as Other, man.
KALYAN REDDY: All right.
Fine.
DAN LAZIN: I mean, it's
New York City.
It's about 20 minutes, if
I'm going fast, 30 if
I'm taking my time.
KALYAN REDDY: You bike
every day, huh?
DAN LAZIN: Almost every day.
KALYAN REDDY: Whew.
All right.
Let's go ahead and submit you.
And let's just add another one.
Let's see.
Marty McFly.
DAN LAZIN: Marty McFly.
KALYAN REDDY: 18, I guess
he was in the movie.
DAN LAZIN: Always.
Perpetually.
KALYAN REDDY: Of course,
he drives
himself in the DeLorean.
And I think he can get there
really quickly, because he can
set the time, right?
DAN LAZIN: I mean, that's
a negative value.
KALYAN REDDY: So let's say--
no.
I don't know if we want to get
into paradoxes of him getting
there before he actually left.
DAN LAZIN: All right.
All right.
KALYAN REDDY: So let's
just say one minute.
Keep it simple.
That's good enough.
So we have all these values
automatically populated in a
spreadsheet.
And this can work for any form
and any spreadsheet that you
might have in your own
organization or among a group
of friends.
Now let's create a new script
that will call into the
spreadsheet to get the data
and present it in a nice
layout for people to use.
So to do that, I'm going to
create a stand-alone script,
going to the script.google.com.
Let's give it a name.
Let's say Form Visualization.
DAN LAZIN: That is an
excellent name.
KALYAN REDDY: It's
very unique.
I thought a lot about it.
OK.
The first thing you want
to do is you want to
change this to a doGet.
Because this is a visual
representation of the data,
we're going to be returning it
as a UI app web service so
that people can see it
in their web browser.
All right.
Now let's get started adding
stuff to this.
The first thing that we need
is we need to reference a
spreadsheet.
So Var SS equals
SpreadsheetApp.getSheetById--
oh, openById.
Autocomplete is perfect
for that.
And then to get the ID of a
spreadsheet, it is this value
right here.
So Key Equals before you get
to the numbered GID stuff.
Just paste that in here.
OK.
Now when the script
runs, you'll have
access to the sheet.
Var Data is ss.getDataRange.
And bam, in two steps.
In two steps, what you were
able to do is open a
spreadsheet, integrate it very
tightly, and then get all the
data from it.
DAN LAZIN: Sorry for those who
are just joining us now.
We had some technical
difficulties with
getting the feed up.
But we are now live at Google
Developers Live.
You'll be able to catch the
first few minutes of the talk
that you missed.
You'll be able to catch them
again earlier if you go to the
saved feed of the GDL, which
we'll send out a link to with
our Google Developers Twitter
feed later on.
KALYAN REDDY: And if you
missed the exciting
introduction earlier,
this is Dan.
He's a new tech writer
for the DevRel team.
DAN LAZIN: Hi, guys.
KALYAN REDDY: And you can go
read about his exciting
biography by watching
this video again.
DAN LAZIN: That's going
to be a great
afternoon for everybody.
KALYAN REDDY: All right.
So let's see here.
What do we want to do?
So if we go back to the building
charts, you can step
through the workflow
in this way.
First, you make the controls.
Then you make the chart.
And then you add them
to the dashboard.
And then you present
it externally.
In the interest of not messing
up too much on Live, I do have
another version of
this script.
And I'll be copying it line by
line, but still explaining to
you every step of the way.
So this is what we've
done so far.
And the next is we want to
set up are the filters.
So here we go.
Now, what I've done is I've
set up three filters.
If you look at our data in the
spreadsheet, the columns are
Name, Age, Where do you work,
and How long is your commute.
So this age filter allows me
to restrict the values that
appear in the chart
by the age column.
And how I do that is I create
a new number range filter
which gives me the builder.
And on it, I set it to filter
column index 4.
And if you look at
our spreadsheet,
that's 0, 1, 2, 3, 4.
Maybe that's wrong.
We'll get to that in
just a second.
But there's a great debugging
tool for this purpose as well.
Go ahead and build
that age filter.
Let me zoom in there so
people can see it.
Now, transport filter--
this is for, obviously,
if you want to
drill down by the transport.
But the key thing to notice the
difference between these
two is that the age filter
is a number range filter.
But the transport filter
is a category filter.
That's because when we set up
the form, I set up the fields
such that the age filter was a
text box and you just entered
your age, whereas the transport
filter had options.
So that's why you had to choose
Other for a bicycle.
That's why I chose Walking.
Anyway, there's a set
number of options.
And you'll see that propagate
to the UI later.
Now, the last filter is
a number range filter.
Say you have tons of data in
your charts, and you want to
just look at the people whose
names start with K. So you can
create a new string filter, and
then set it to filter on
any text string.
And once you build that, you'll
see the control that
allows you to do just that.
All right.
Let's see.
OK.
The next thing that we want to
do is add in our charts.
Now, for this example, I think
it would be pretty cool if we
can display in a table chart
what our data is.
And table charts are great
because they're quick.
You can just say, here's
my data source.
Put it in a table.
And then it gives you the
categories for all the headers.
And you can say, arrange those
ascending, descending.
It's a really quick way
to visualize the data.
And a pie chart, because it's
just a cool visualization.
DAN LAZIN: Of course.
Classic.
KALYAN REDDY: Now notice
how I'm building
this table chart here.
Charts.newTableChart--
that gives me the table
chart builder.
Then I set the DataView
definition.
Now, when I was talking about
the DataTables earlier,
DataTables are the data that
you provide to the charts.
DataViews are different, in that
they're a specific subset
of the DataTable.
So say you only want to have two
columns of the DataTable,
the first and the
third column.
You can have the DataView
represent that subset of the
DataTable by setting up a
new DataView by using
Charts.newDataViewDefinition
and setting
the columns on that.
Here what I'm doing is I'm
setting the DataView to have
columns 1, 2, 3, and 4.
Now, if you go to the
spreadsheet, you'll see that
corresponds to 1, 2, 3, and
4, all the form data.
And I excluded timestamp.
DAN LAZIN: Right.
Indexed from 0, so we're looking
at everything except
the first column.
KALYAN REDDY: Exactly, because
no one wants an ugly timestamp
in their charts.
Now, to set up the pie chart,
you do something similar.
Except instead of newTableChart
here, you call
newPieChart.
And when you set up the data
view, I set it up using
columns 1 and 4.
So what will that give us?
That'll be their name against
how long their commute is.
So you can visualize who's
spending most of the time
getting to work and
not working.
DAN LAZIN: Nice and easy.
KALYAN REDDY: OK.
Now, the next part is setting
up the dashboard.
Now, like I mentioned earlier,
a dashboard is a way to tie
these filters and these
charts together.
It's a way to present everything
in one logical
chunk so that changing these
controls will change what's
displayed on all
of the charts.
DAN LAZIN: Right.
So instead of drilling down
into each of the different
charts, you can give your
settings once and allow it to
be reflected in multiple
charts.
KALYAN REDDY: Exactly.
So here I created a new
dashboard panel, which, as you
notice, is under the
Charts Service.
It's not in the UI service.
But it is a panel that you can
add to the UI service, as
you'll see in just a second.
When you call setDataTable on
it, you can add data here,
which up here, as you'll see,
is just the data range.
It's that easy to connect
a data range to a chart.
You just pass in the range.
DAN LAZIN: Now, people who are
used to using Spreadsheet
Service might have expected that
they would actually be
getting values for
that data range.
But here, you're actually
getting the range.
You're not getting the
values, correct?
KALYAN REDDY: Right.
And that's kind of a cool
thing to point out, just
because it shows how tightly
integrated Apps Script is with
the rest of the Google
Apps ecosystem.
You just get this range object
from the spreadsheet.
And bam, you can use it as like
a native object here.
Now the next step--
notice that we're still
in the builder.
This is another example of
the Builder pattern,
newDashboardPanel builder.
Set the data table
on the builder.
Then we bind that builder.
So what Bind allows us to do is
take an array of controls
and bind it to an
array of charts.
So that's what I have here.
I have two arrays.
One array-- these are all the
controls, age filter,
transport filter, name filter,
that we set up up here.
And down here are the two
charts, table chart and pie
chart, which we also
set up up here.
Now, when you call build, it'll
give you the actual
dashboard panel that you want
to add to your UI app.
So next, all we have to do is
create the simple UI app.
And we will be done.
Here we go.
OK.
Let me go through
this real quick.
Now, as you may be familiar
with this-- if you're not,
there's excellent tutorials on
how to use UI app with the
various services.
You need to create
an application.
And that application in turn
will give you access to UI
elements, like panels that you
can add, widgets that you can
add to those panels.
And ultimately, you add the
panel to the UI app.
And then you return the app.
And that's what gets rendered
in the user's browser.
DAN LAZIN: Perfect.
And just a reminder for those
who missed the very beginning
here, this UI app is looking
for the doGet function that
we've sort of got our entire
Charts Script here wrapped to.
KALYAN REDDY: Exactly.
And I'll scroll up to that.
So here is the doGet function.
Because this presents a UI, you
need the user to see it.
For example, here in a web
browser, they'll be able to go
to the URL that we generate.
And then that performs an HTTP
get, which gets this UI,
displays it to them.
There's also other ways.
You can embed it in
a spreadsheet.
We won't go into that here.
But suffice it to know, this
is how you create a very
simple UI app.
DAN LAZIN: Yeah.
Just a few lines of code.
KALYAN REDDY: And what I do is
I create a filter panel to
contain all my filters, which
is a simple vertical panel,
and then a chart panel to
contain my charts, which is a
horizontal panel.
And to the filter panel, I add
the age filter, transport
filter, and name filter,
set spacing just to
make it look nicer.
And to the charts panel, I add
table chart, pie chart, and
set spacing.
So here's another kind of
extension of that pattern we
saw earlier.
When you call add on this, it
returns that panel object.
So then you can continue
changing it and calling add
again and again to add all the
things that you want to.
And then once you've created
your panels containing the
filters and charts, you add it
to your dashboard, which we
created up here.
Right here.
So I create another vertical
panel just to
put my panels in.
It's just an organizational
thing.
You can do it however
you want.
There's many different
ways to do this.
But this is how I do it.
So you add these two panels
to the dashboard.
And then you add the dashboard
to the UI app, and then return
the application.
DAN LAZIN: It's turtles
all the way down.
KALYAN REDDY: Yup.
So here is everything
you need.
You open the spreadsheet, get
the data range, create the
filters, create the charts,
add them to a dashboard,
return the dashboard.
Now, like other publish as a web
service applications that
Apps Script has, the
flow is the same.
You go to Manage Filters,
save a new version--
the version that you want to
show to people, obviously.
And then you go to Publish,
Deploy as a Web App, the
Project Version 1, which
you just saved.
And now I'll just give myself
access to see it, because this
is only a demo.
But you would want it to execute
as [? you ?] yourself
as the user accessing the app.
And then Anyone, to let other
people just get the link and
then go to it.
Once you deploy it, you
get a URL that you can
access this app in.
DAN LAZIN: And just a note.
If you're using Google Apps
for Domains, you have the
ability on a per-domain basis
to set what the sharing
options for published
scripts are.
So if you want to create a web
app that is visualizing
proprietary data for your
company, you'll have the
option to publish that only to
users who are signed in within
your domain.
So it's live on the internet,
but only if you're signed in
as a member of your company.
KALYAN REDDY: Yes.
All right.
And there we go.
There is our dashboard.
DAN LAZIN: That is remarkably
professional for just a few
lines of code.
KALYAN REDDY: Yeah.
And you know that there's
still alignment
issues here and there.
This thing can be aligned
to these corners.
If I were to spend more
time on this, I would
definitely do it.
And Apps Script gives you all
the options that you need to
arrange this exactly
how you want it.
You can arrange the padding,
the spacing, all of these
things, put panels in panels,
do whatever you want.
And look at our documentation
for that.
It very clearly shows exactly
what you can do with all of
these things.
DAN LAZIN: Let's show some of
the filtering options here and
show people how it allows us to
drill down into the data.
KALYAN REDDY: Sure.
So here are the three filters
that we added.
This is for the length of
commute, which is a number
range filter.
As you see, it goes between
two number ranges.
And you can drag the sliders.
This is the category filter.
So these are the categories
from the form.
They automatically get
translated to categories in
this dropdown box.
And this the name filter, where
you can type in strings--
excuse me.
This was a string filter.
So let's start out with
the string filter.
On the left is our
table chart.
On the right is our pie chart.
Let's say we want to
look at just me.
So I'm going to type
in my name.
DAN LAZIN: You spend
100% of your time.
KALYAN REDDY: I don't google
myself all the time.
What are you talking about?
Anyway, so yeah,
so this is me.
Instantly, all the charts
are updated.
And the pie chart has just me.
DAN LAZIN: That's a pretty
useful pie chart.
KALYAN REDDY: So that's my
12% commuting to work.
And since 12% is all the minutes
ever in this data
range, I'm all of it.
OK.
So let's make it more
interesting.
Let's look at Dan.
So now, obviously, you see
filters work just like you
would expect.
One key thing before even
jumping into filters is that
just throwing your data in a
table chart, for example, is a
great way just to get a
quick handle of what
your data looks like.
So here, you can arrange it
alphabetically by clicking
this header.
You can arrange it by decreasing
age by clicking
this, arrange it by
commute times by
doing that, et cetera.
Now, we can also filter in
other interesting ways.
Let's say we only want
to look at the people
who are using a carpool.
Click that and bam.
Carla, Frank, and Willis are the
only ones using carpool.
And the pie chart also reflects
to see their total
minutes commuting.
And it looks like Frank has
a very bad carpool.
Maybe he's the first
person picked up.
Maybe he's the driver, so it
takes him the longest.
Another interesting thing about
this filter is what if
you want to look at walking
people, also.
Now you're looking at
carpool and walking.
So it updates the chart here and
adds the members in here
accordingly.
DAN LAZIN: The nice thing is if
you're using this to do a
presentation in front of a group
as well, if you click on
one of the segments of the pie
chart, it will actually keep
it highlighted with that nice
bar there, which allows you to
sort of use it almost like a
laser pointer when you're
giving a presentation.
KALYAN REDDY: Yeah.
Just give focused areas
of the chart
that you want to highlight.
OK.
Let's see.
And then number range filter is
like a dynamic filter where
you can do a number range,
like you would expect.
And as you can see,
the pie chart is
getting updated as well.
It's all very cool.
But let's move on to an example
that may be more
applicable to the real world.
Who really cares how long it
takes for people to commute to
work, right?
DAN LAZIN: You do.
KALYAN REDDY: I do.
Only me.
So let's move on
to an example.
Lots of people use
Stack Overflow.
As Developer Relations for Apps
Script, we're very active
on Stack Overflow.
We have a tag that you can write
your questions against.
And we try our best to answer
as quickly as possible.
And we also have a great
community of top contributors
that also patrol these forums.
I'm always amazed to see that
I go to answer the question
and then there's already like
three answers by people that
don't work here.
It's great.
It's great that we have such a
user base that's devoted to
keeping this project going.
DAN LAZIN: And very well
informed, too.
KALYAN REDDY: Yeah.
DAN LAZIN: Those guys
know their stuff.
KALYAN REDDY: Definitely.
And so something that I just
built simply is that Stack
Overflow provides an API to get
access to things like the
questions posted to it or the
reputation for certain users.
There's lots of things
that you can query.
Apps Scripts' URL Fetch makes
it easy to get that data.
So let's put two and
two together here.
We could use URL Fetch
to get the data.
We have a way to present the
data with charts and
dashboards.
You glue those two together,
you have a way to get stuff
from Stack Overflow and
automatically have it drive in
to your charts.
In this example, we'll show you
exactly how to do that.
Let me try to zoom out here.
OK.
And I'll explain this one line
by line as well because it's a
key to understanding how
simply you can do
this in Apps Script.
This is it right here.
This is all of the code
that you need.
What I'm doing here--
URL Fetch app requires a URL.
And it just gets it.
And this is the REST-based
API for Stack Overflow.
As you can see, there's a couple
of parameters here--
tags, Google Apps Script.
And I'm getting the
top answerers.
And I'm getting this
for all time.
So everyone that has answered a
question that has been asked
against the Google Apps Script
tag will be reflected
here in some way.
Now, once you get the content in
this result object, you do
a JSON parse.
The API sends the results
in a JSON string.
So you do
result.getContentText and jsonParse.
And this outputs a JavaScript
object that has key value
pairs for what you're
interested in.
And you can actually see
that running here.
Let me comment out
this logger line.
Oh, and before I get to that,
I just drill down one level
into that object and assign
it to this variable.
So this is actually the raw
output from the API that we
were able to get very easily
with one line.
You can get the post count,
the score, the name of the
user, his reputation, all
of this great stuff.
And this is for the various
users that are identified as
top answerers.
DAN LAZIN: But of course,
in raw log form, it's
pretty hard to read.
KALYAN REDDY: Exactly.
Exactly
DAN LAZIN: Which is why
we're going to get it
into a chart here.
But it's a really good way to
sort of quickly check that
you're getting the right
stuff from the API.
KALYAN REDDY: Yeah.
And it's great for debugging
purposes, too.
You build your apps one
piece at a time,
one layer at a time.
You don't want to just put
everything together and have
this giant mess where you
can't find out where the
problem is.
Now that we know we're getting
the right data, we can also
use the logger to find out
general format of the data.
You can tell that there's
objects here.
The curly braces
denote objects.
And you can drill in, and
you can find the key
names of the objects.
Like display name is to display
the user's name.
Reputation is that.
So that tells you a key piece of
information, which I use in
the rest of the script, as
you'll see in just a second.
I create a DataTable here.
I'm not using it from the
spreadsheet in this example.
I'm making it, but I'm making
it from the data
that I pulled in.
And what I do here, I make
columns for name, reputation,
post count, and score.
And these again, like I
mentioned, I got from
examining this log.
So for every top user that the
API returns, for every user,
we add a new row to this
DataTable that we set up
columns for.
And so this addRow method takes
in four parameters with
respect to the four columns
that we added.
And this is the object that we
got directly from the API.
So here's top users, user.
So we do user.
And then we drill down into
a user subobject.
And then we get the display
name key out of that.
Then we do the same
for reputation,
post count, and score.
And then it's that easy to add
a new row to your data table.
Now what we need to do is to
build a DataTable, because as
you'll remember, this
is the builder.
When we add rows to it we are
building the DataTable, still.
Then we create the DataTable,
and we create our filters.
And I want to filter by name,
because again, I want to type
in my own name to see
my reputation.
DAN LAZIN: I see
a pattern here.
KALYAN REDDY: Yeah.
So let's see.
And we'll display it in a table
chart, because like I
mentioned earlier, a table chart
is a really quick way
just to visualize things.
And then it provides you nice
ascending, descending sorts
and everything like that.
So according to the flow that we
established earlier, create
a filter, create a chart, and
then create a dashboard to
hook these things together.
And here's my dashboard
line here,
charts.newDashboardPanel.bind.
We're binding the name
filter to the chart.
And then we set the DataTable
that we built
earlier, right here.
And now this build call will
build the dashboard object.
And in the rest of the script,
it's just three simple lines
to create a very simple UI
application and add this
dashboard panel to it.
DAN LAZIN: Yes.
Just like last time.
KALYAN REDDY: Just
like last time.
And return the app.
And then that's all you need.
Now, I was working on this
earlier, so you are already
going to see a saved version
that I was working on.
And then Deploy Web App--
I've already deployed
it to this URL.
So we can just go to that.
There you go.
So in just a couple lines
of code, you can
get this nice table.
And you can organize
it by reputation.
Let's see who has the
highest here.
Eduardo has 51,046 reputation.
DAN LAZIN: Hopefully Eduardo
is watching GDL right now.
KALYAN REDDY: Yeah.
And Henrique--
he's another guy who is always
on top of questions before I
get to them.
You know, there are some very
active users in the community.
You have to go all the way
down here to find someone
who's on the team.
So here's the number of
posts that they posted
against this tag.
And here's the score
for the posts.
So let's see.
Obviously we're going
to use this filter,
check out my name.
I actually don't want
to do this now.
As you'll see, my reputation
is not as high
as it should be.
I've got to work on that.
But anyway--
DAN LAZIN: This is going to be
our sort of death match leader
board inside the office.
KALYAN REDDY: Exactly.
This is going to drive
us to do better.
That's the power of just
simple dashboards here.
You can have the whole team
refer to this and be like,
hey, I want to get higher
than that guy.
Keep on driving up
their stats.
And we actually do use something
like this for our
issue tracker as well, just to
make sure that we're all
working on it, we're all
contributing, and we're all
making the user experience
the best for our users.
And so it's just that simple.
In 20, 30 lines of code,
you can just
get something running.
Now, a slight extension to this
is the final thing that
I'm going to show to you.
This is another feature of
the Stack Overflow API.
Not only can you get the
reputation of users, you can
also get the questions that
they asked against a
particular tag.
Now, this code is a little
bit more complex.
It's a little bit more involved,
just because you
have to do some more processing
of the data after
you get it.
But it's not bad.
You can definitely follow
along and understand
everything.
This is not commented because
I just wanted to show you
without scrolling
around too much.
But I will comment this code
and put it up there.
And we'll add a link to it in
the event description so you
can access it and look at
it at your own leisure.
Now, this code is similar in
that it contains a doGet.
It'll be displayed in a web
page just like before.
It starts out by calling this
getQuestions function, which
is something that I wrote.
And all this does is it calls
another method of the Stack
Overflow API here.
And it gets everything
tagged Tag.
Sorts by creation, whatever.
So tag here is google-maps.
So we're actually fetching all
of the questions on Stack
Overflow that are tagged
google-maps from this date,
which is a Unix epoch date.
It's basically seven
days before now.
I did the calculations
and put it in there.
If I made the script more
dynamic, I could have the user
enter the date and all that.
But this is just a sample to
show you what can be done.
Like before, we get the object
by parsing the JSON that we
get from the API.
And the Total field displays the
total number of questions.
We just keep on doing this Why
loop until we've fetched all
of the questions for the past
seven days, is basically the
meat of this function.
It then appends it to an array
called Questions and returns
that to our main
function here.
In this main function, I want
to do a couple of things.
The goal of this dashboard is
I want to have the date the
questions were asked.
And we track the total number
of questions for that date
versus the number of unanswered
questions that
still remain for that date.
This is good for teams like
DevRel teams who are
patrolling these boards to see
how many questions are
slipping through that users
are still having problems
with, how much more active
we need to be.
So I set up three columns.
And you'll see the column type
is very important here,
because the date that's returned
is a string, as
you'll see in the chart later.
But these unanswered and total
are numbers, so you can pass
them in as numbers and Charts
can use them as numbers and
filter them as numbers.
Ignore this here.
This is just to log it.
Again, allowing a log is a
great way to have logging
statements in intermediate
steps to make sure,
hey, I got this far.
I'm still on track.
I'm still good to go.
DAN LAZIN: And if you want
something a little more
sophisticated, you can use the
built-in debugger in the Apps
Script editor as well.
KALYAN REDDY: Definitely.
The debugger lets you step
through and examine all of
your objects and drill
into the objects.
And that might be another
video coming up, so
stay tuned for that.
DAN LAZIN: For code as simple as
this, really, Logger.log is
all you need.
KALYAN REDDY: Exactly.
So what I'm essentially doing
here is I'm keeping track of
two objects, total and
number unanswered.
I'm looping through all of the
questions that I get back from
my API request and converting
the date that's in the
questions into a human-readable
date string,
without the time.
Because I don't really care
about the time of the question
in the date, just the day that
the question was asked.
And then I create a tally.
So I add it to the numUnanswered
object if the
questions.answer_count that I
got from the API is zero.
And otherwise I add them to
both that and the total.
So I have two things, number
unanswered for each date and
the total for each day.
Now I loop through the
number unanswered.
I can loop through
either one here.
And I create a DataTable like
you've seen before by adding a
row for each object.
And what I add here is i, which
is the key of the number
unanswered, which you can see
here is the question date,
which is a string.
That's a lot of whiches.
Anyway, you can add the row.
And then it has two other things
that you pass in to the
addRow, which is number
unanswered i, and
then the total i.
Obviously, those are the number
unanswered and total
for each day.
Build the DataTable.
Create an area chart--
now, area chart is a different
chart than what we've been
using before.
Area chart is good if you want
to do things like stacking.
That's a key difference.
So here, what my ultimate goal
in this dashboard is is to
have the unanswered at a certain
level and then show
the total as an addition
to the stacking.
So you'll see newAreaChart .
And in the builder, I call the
setStack method, which is new
for this builder.
And then I also pass
in the dimensions.
Now, just set the axis titles.
Like I said before, you can
make these charts a lot
prettier than what you're
seeing right now.
So don't think this
is all you can do.
This is only the tip of the
hat, tip of the iceberg.
DAN LAZIN: Whatever.
My hat is floating beneath
the water.
So I'm like a shark
with a hat.
KALYAN REDDY: So here
we build our table.
So another thing is I
want to make an area
chart and a table chart.
And like I said, the
table chart is just
like a sanity check.
You can see that everything's
there that you want it to.
So I created both an area
chart and a table chart.
And I set their data sources
to be the same, so they're
[? both driving ?]
the same data.
Create the application.
And you'll notice this time, I
didn't create a dashboard.
I just added the charts directly
to the application,
which is fine.
If you want to simply show
something, a dashboard allows
you to have controls
to drill into it.
This time, I'm not
using controls.
I'm just displaying this
dynamically queried result.
So you can just simply add it.
So many ways to do this.
And let's go see what
it looks like.
Like I mentioned before, I was
working on this, so I already
saved a version.
And I already deployed it.
So let's copy the web app URL.
And we'll try it out.
There you go.
Looks can be improved,
of course.
But here is our area chart.
And here is our table chart.
DAN LAZIN: That's a really
great way of sort of
visualizing where
we're sitting.
KALYAN REDDY: Yeah.
So here you see on one axis
are the dates for the last
seven days, like a query
from the API.
And then on this axis is the
number of questions.
Red is the total, and
this unanswered.
You'll notice the stacking
method that we called on the
builder shows here, where this
is 10 questions, and
then this is 36.
So it's really 36 plus 10.
That's why you get
up to 46 here.
It stacks on top.
So you'll see that the number
of unanswered for the
google-maps tag could
be better.
These are the ones that
we're covering.
These we are not covering.
DAN LAZIN: But they're still
keeping down to a fairly
consistent number per day.
KALYAN REDDY: It's consistent.
DAN LAZIN: And a lot of answered
questions there.
KALYAN REDDY: And so a key
advantage is that same code
that you used can be used for
other things as well.
Now let's go and check out a tag
that we all know and love.
Let's change this to
google-apps-script.
All right.
Now I saved it.
I need to save a new version,
because I changed the source.
Deploy as a Web App.
We deployed version 2 here.
And now let's go ahead
and see this.
And you'll see the numbers
here updated, because now
we're getting a different
number of questions.
For this tag, there are fewer
questions asked than for the
google-maps tag.
And you'll also see the
distribution is different.
The unanswered is lower.
DAN LAZIN: Go us.
KALYAN REDDY: Your table
chart again shows
easily sortable metrics.
And then this will allow
multiple people to come into
this same central dashboard area
and then make sure that
we're keeping on track
of this thing.
DAN LAZIN: That's great.
Just a reminder for anyone who's
playing along at home
that if you're just building
this for your own testing
purposes, you can also use the
Live Code link within Deploy
as a Web App to see what the
live version of your changes
is without having to redeploy
every single time you want to
make a change.
Deploying the sort of full
version is really for when you
want to share it with other
people in your organization,
other people around the world.
The dev link, the link that ends
in /dev that you'll see
in that same--
KALYAN REDDY: Right.
This is exact.
It could be /dev as well.
DAN LAZIN: Exactly.
That version will allow
you to test your
own code really quickly.
Yeah.
For demo purposes.
KALYAN REDDY: Yeah.
That's a great point, so you
can avoid that intermediate
step of creating a new
label and then
adding it and all that.
OK.
So that's about all I had
for charts today.
As you can see, it's just a
great way to quickly throw up
a dashboard and then just have
people go at it, view the data
however they want to view it.
DAN LAZIN: That is
really cool.
One thing I'd like to point
people to is I just ran
through the charts dashboard
tutorial that we have on our
site this morning.
It's really simple.
And the guys who put it together
did a nice job of
including all of the
code right at
the end of the tutorial.
So you can just paste it into
your script editor, manage
versions to save a version,
deploy as web app.
And in three steps, you have a
functional demo charts dashboard.
From there, it's a lot easier
to sort of walk through,
fiddle with the code a bit and
kind of figure out what each
of the parts are doing.
So if you go to the Apps Script
website, which is
developers.googl
e.com/apps-script, click on
Tutorials in the sidebar on the
left, and then look down
the page, you'll find a charts
dashboard tutorial.
Take a look at that.
It will just take you
five minutes to get
the basic stuff working.
And you'll see how powerful
the charts
dashboard functions are.
KALYAN REDDY: There you go.
How much easier could it be?
All right.
Well, you have a lot of choices
to continue your
education and start building
some cool things.
So go ahead and ask questions
on Stack Overflow.
Obviously, we're going to
stay on top of that,
or the Issue Tracker.
And I hope you enjoyed
this presentation.
Stay tuned for more videos
coming up from us.
Thank you.
DAN LAZIN: Thanks a lot, guys.
And thank you, Kalyan.
