Building a Charts Dashboard with Google Apps Script

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,
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 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, 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.

24 thoughts on “Building a Charts Dashboard with Google Apps Script

  1. almost two years ago but still interesting.
    "an equation is for eternity" … and a code is…

  2. i keep getting this error "missing ; statement line 15" but my code is prefect what can be wrong? please help 

  3. THanks very much Mr Kalyan Reddi & Dan. THis was really useful vedio for me. You effort not wasted at all. THanks again. BYe, Can I have more samples on this Charts..

  4. I just copied the code and follow all the steps and is not working. as Michael state the uiApp is being deprecated and is not working.


  5. Tutorial still works as of today even though the app UI and associated components are deprecated – worth the time for anyone who wants to get familiar with google apps script to pull and display data dynamically from a google sheets spreadsheet (linking the google sheet by its ID, declaring variables and how to pass info into them) and ultimately displaying a "user dashboard" to manipulate/display the underlying data of the spreadsheet. Great job and helped a lot!

  6. PS – if doesn't work, run your code through debugger – I had quite a few typos (and missing semi-colons) before it finally ran through without a problem and displayed the "dashboard" – don't give up.

  7. PPS – I could not get the Stackoverflow query part (second half of video) to work – I think that one is bogged down by deprecation issues with Stack Overflow.

  8. Google, please can you add a notice that this video tutorial is out of date because the uiApp has been deprecated in favour of HTML Service. When you have time, why not do another video this time using HTML service. And then, please, please, please add a link to the new video in the video description!

  9. 2018 and still no updated video. I've been working on a dashboard but can't seem to figure out why it isn't working. Frustrating haha. The examples google gives works, but when pulling data from a google spreadsheet and trying to link columns to certain charts it's not working.. sigh..

Leave a Reply

Your email address will not be published. Required fields are marked *