Building a Charts Dashboard with Google Apps Script


Uploaded by GoogleDevelopers on 11.12.2012

Transcript:

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.