Launchpad Online: Automating YouTube stats with Google Apps Script


MALE SPEAKER: You there. What’s your name? WES CHUN: Uh, it’s Wes. MALE SPEAKER: Wes, huh? You know, I’m not really
a fan of employees with one syllable names. It implies a certain
lack of conviction. What’s your last name? WES CHUN: Chun, sir. MALE SPEAKER: You’re
killing me here. All right, Wes Chun. I got a question for you. We got dozens of videos on
our YouTube channel, right? WES CHUN: Yes, sir. MALE SPEAKER: Well, I need
information about them. Who are our users? Where are they coming from? What are they doing? Are they eating anything? If so. Is it cereal. And if so, what kind? And is there a toy inside? You got that? You know what? Scratch that. Just give me view
counts of our views and those of our competitors,
and somebody around here get me some cereal. I am starving. Oh, and Wes, I’m going to
need that by lunchtime today. WES CHUN: Lunch, sir? MALE SPEAKER: Wes,
I’m not saying you’re going to be fired if
you can’t get it done by lunch, but I’m not saying you’re not
going to be fired, either. Oh, and put some pants on. I’m running a business,
not a summer camp. WES CHUN: Yes, sir. Geez. Going to each video to get the
view count is taking forever. How am I supposed to
finish by lunch time? MALE SPEAKER: Hey. Google Apps Script. It talks to YouTube. MALE SPEAKER: Wes Chun, what
is wrong with these view count reports? It’s just a bunch
of gobbledygook. Is this secret code? WES CHUN: Yes, sir. Let me refresh your
screen for you. MALE SPEAKER: Oh. Oh, that is nice. Good job, Wes Chun. I think you’re going to
find a little something extra in your
paycheck this week. It’s a ladybug. My daughters into insects. It’s a whole thing. Anyway, nice work. Hey, everybody, we’re going
to break early for lunch. Wes’s treating. WES CHUN: Actually,
it’s Wesley, sir. MALE SPEAKER: Oh,
two syllable man. I knew I liked the
cut of your jib. Good job, Wesley. WES CHUN: Do you
want this to be you? Of course you do. New to Apps Script? It’s a programmatic way to
access data in Google Apps. Already use it? Think it’s only for
Google Docs and Sheets? Think again. With the YouTube data APA, you
could be your boss’s hero, too. This is Google
engineer Wesley Chun, and I’m going to help you get
started on making an app just like the one you saw. Start by going to
your Google Drive and creating a Google Sheet. Now put on your coding hat,
and let’s go to the computer. So I’ve created a
brand new sheet here, and I’ve already added three
columns– video ID, the title, and the number of views. So go ahead and
make one of those, and add those three
columns as well. And then think of a video that
you want to get information on. I’ve already got one in mind,
so if you have one, too, just go to YouTube and then
go up to the top in the URL. You can find the video ID. And then grab that
and paste that into the very first
available cell, which is A2. Now let’s take a
look at some code by pulling down Tools
and Script Editor. And what you’ll
get, as you’ve seen before in previous
episodes, is you’re going to get presented
with a bunch of templates, and you’re going to just choose
Blank Project because our code is going to be pretty short. And what you’re
going to do is you’re going to enter code
like this, but wait until I describe each line
first before you type them in. So change the name of the
function to Get Video Info. That’s the first step. On lines two and three, we
grab the current active sheet and the data item in cell A2. So this is code that
we’ve already seen before. You need to talk to
the sheet, and then you need to get the range
of cells, and then you need to pull out
the specific values. In this particular case, we’re
only looking at one cell, and that’s A2. The magic happens
on line four, which calls the YouTube data API. And you do that by calling
YouTube.videos, then its list method. The first parameter in the
call to the list method are the video resources
that you’re interested in. Each video has multiple
data sets to choose from. For example, Statistics has
information like view count, likes, and dislikes, while
Snippet has the publish date, title, and description. There are others with useful
information, too, not just the pair that we’re
requesting here. The second parameter is easier. This is just the ID of the video
that we want to get data on. Since we’re only
asking for one video, we get back a JavaScript
array that only has one row, so we save it in line five. On line six, we pull
out the two items that we’re interested
in– the title, which comes from Snippet, and
the view count, which comes from Statistics. Finally, they’re written
back to the spreadsheet in cells B2 and C2 by using
the set values method call. And that’s it. But before we run the script,
we need to first enable access to the YouTube data API. Otherwise, we’re
not going anywhere. So go up to the Resources
menu and pull down Advanced Google Services. Before we do that, we have
to enter a project name. So let’s call it Get Video Info. Now, once you have the Advanced
Google Services dialog box, scroll all the way down and
find the YouTube data API, and turn it on. Now, this yellow
dialog box pops up that says, “You must enable
this in the Google Developers Console,” as well. So go ahead and click that. And then here, on
the API Library page, look for YouTube data API,
select it, and then press the blue Enable API button. Once it’s done, you
can go back and confirm that it has been turned on by
clicking on the Enabled APIs link up at the top. And there it is. All right. That part is done. Let’s go back to the
Advanced Google Services and then click OK
to accept that. All right. So once we’re back here, we
can click Run to execute it. And there’s the
yellow dialog box. But ah, here comes
the OAuth, where we need to give
authorization to this script to get access to your
spreadsheet data, as well as to talk to
the YouTube data API. So click Continue. You’ll see the familiar
OAuth 2 dialog, and we want this access, so
we’re going to click Allow. And once the authorization
has gone through, you’ll see the dialog box
run, and once it’s done, it disappears. And if we’re lucky, we
will see the YouTube video title and its view count. Cool. Now all we have to do
is name this thing, and then we’ll be done. Call it anything you want. I’ll call mine Video Statistics. So great. You were able to
write an Apps Script app that talks to
the YouTube data API that pulls down information
that you’re looking for. Now you can impress
your boss, too. Wow, wasn’t that cool? This isn’t as complex as the
one I ran earlier for my boss, but it’s your jump start. The point is to show
you that automation, like using the YouTube data
API, is one of the main reasons to use Apps Script. By the way, if the code
was too hard to see, it’s also in the Google
Sheet that you can copy. The link is also
in the description below if you’re on mobile. Speaking of which,
did you know that you can run your scripts from
anywhere using Apps Script’s execution API? What else can you
do with Apps Script? Well, check out the Docs which
include various quick starts to get you going. Want to know more about
the YouTube data API? Well, its Docs will let you
know what parameters to pass in and what to expect
from API calls. If you can’t get enough,
the link at the bottom points you to more of these and
other Google Developer videos. Hope we’ve shown you
what a great tool Apps Script is for automating
laborious tasks– in this case, getting information
on a bunch of videos. Want to learn more? In addition to the
Docs, stay tuned. In a few seconds, we’ll link you
to other Apps Script episodes so you can see what
else you can do with it. MALE SPEAKER: Hey, Wesley. Put these on. We’re going somewhere fancy. WES CHUN: This is
Wesley Chun, and I’ll see you the next time on
“The Launch Pad Online.”

13 thoughts on “Launchpad Online: Automating YouTube stats with Google Apps Script

  1. That is so cool Thanks for showing that to me. I think this will be useful in many ways 🙂 Thanks again Weslie Cheung I hope I spelled your name right…

  2. I like to track my youtube with other income to see how well financially I'm doing on sheets. This appears to have some scripts that I could do with. Also the acting wasn't bad at all. Nice job.

  3. I've wanted something like this for awhile, thanks so much for sharing what looks to be a helpful thing. Fingers crossed 🙂

Leave a Reply

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