Up next in 10
Buy the full source code of application Here
https://procodestore.com/index.php/product/google-sheets-youtube-data-api-tutorial-using-google-apps-script/
Show More Show Less View Video Transcript
0:00
Uh hello guys, welcome to this tutorial.
0:02
So in this tutorial, I will be adding a
0:05
new menu item inside our Google sheet
0:08
and we will be displaying a YouTube
0:10
channel data inside our Google sheet
0:13
with the help of Google app script. So
0:15
this is a short little demo application
0:17
that we will be building. You can see
0:18
that there are three fields out there
0:21
ID, title of the YouTube channel and
0:23
view count. And there is a new menu item
0:25
which is embedded inside Google sheet
0:28
YouTube data. So with the help of this I
0:30
can just add specific channels here. It
0:33
will be prompting me to enter the
0:35
channel name. Let's suppose I enter a
0:37
channel username which is direct
0:39
banners. So basically this is a YouTube
0:41
channel. If you go to this channel you
0:43
will find this username direct ponus.
0:45
And now if I click on okay it will tell
0:47
me the channel ID the channel title
0:50
which is direct punus and the total
0:52
number of views that this channel has
0:54
got over its lifetime. I can repeat for
0:57
a static channel which I have added this
1:00
as a menu item of Google developers
1:02
data. If I enter this you will see that
1:04
it will automatically tell me the Google
1:06
developers and how many views it has
1:08
got. This is the YouTube channel that
1:10
I'm talking about Google developers and
1:13
once again I can add a another ID which
1:16
was the new Boston. This is again a
1:19
programming channel
1:21
popular programming channel. So you will
1:23
see that it it will automatically tell
1:25
me the total number of views, title of
1:27
the channel and the ID of the channel.
1:30
So in this tutorial we will be building
1:31
this tutori in this this application. So
1:34
first of all guys you just need to
1:37
create a blank sheet Google sheet. So
1:39
first of all write here Google sheet and
1:41
the very first link which comes in click
1:43
this and we will be redirected to this
1:45
screen. So go to Google sheet and here
1:48
you just need to create a blank sheet.
1:51
Click on the click on this option and
1:53
here you will be redirected to the
1:55
screen and now in order to go to the
1:58
Google app script section here click on
2:00
the tools section here tools option and
2:03
click this option which is script editor
2:05
and uh you will be redirected to a
2:08
screen where you will be writing your
2:09
code in order to add this functionality.
2:13
So
2:15
this is the area here Google script code
2:18
and let me just delete all the code here
2:21
uh start from scratch. So we will be
2:23
making a custom function which will be
2:25
on open. So when we open the Google
2:28
sheet we will be writing uh executing
2:30
this code inside this function. So we
2:33
will be adding some values to it. We
2:37
will be targeting the first cell inside
2:38
this Google sheet. So for that we have
2:41
uh declared a variable of first cell and
2:44
inside this we will be using the
2:46
spreadsheet app class
2:51
and there is a method inside this which
2:53
is uh get active sheet. So basically it
2:57
will get the reference of the active
2:59
sheet which has opened right now and
3:01
after that we just need to execute
3:03
another method which will be get range
3:06
and here we just need to provide the row
3:08
and the value. So we will be providing 1
3:10
comma 1 and inside this we just need to
3:15
get its value. So we will be invoking
3:18
this method which will be get value. So
3:21
now we have successfully got the cell
3:23
here and now we just need to uh append
3:26
some values to it. So we will be
3:29
declaring this if condition. So if it is
3:31
not equal to ID.
3:34
So in that case we just need to
3:38
append some values to it. So in order to
3:40
append a row we will be first of all be
3:43
making our row. So inside this row there
3:46
will be three options. The ID of the
3:47
channel and the title of the channel. So
3:51
title and the third property will be
3:53
view count. So total number of views
3:55
that the YouTube channel has got. So
3:57
view count. So this is three columns
4:00
that we have specified here. So this is
4:01
header row. And now in order to add this
4:04
row in or in Google sheet we will be
4:07
again be using spreadsheet class
4:10
spreadsheet app and there is a method
4:12
inside this. First of all we will get
4:15
the reference by calling get active
4:17
sheet and then on top of that we will be
4:20
calling a method which will be append
4:22
row and here we will pass our header row
4:25
variable. So it will add this row inside
4:28
our Google sheet. And now after adding
4:31
this row, we just need to make a menu
4:34
option here. Right here inside our
4:35
Google sheet. So in order to add a
4:38
separate menu inside Google sheet, we
4:41
will be writing a simple code. We will
4:44
be declaring a UI variable. And inside
4:46
this, we will be again be calling
4:48
spreadsheet app. And there is a method
4:50
which will be getting a reference to the
4:52
user interface. Spreadsheet app dot get
4:54
UI. And uh after that we just need to
4:57
create a uh create menu and inside this
5:01
we just need to pass uh the name of the
5:04
menu that we need to create. So in this
5:06
case we just need to create YouTube
5:07
data. So after specifying this value we
5:11
just need to add some items to our menu.
5:13
So we will be invoking this add item
5:15
function and here we will be writing the
5:17
caption which will be add channel data.
5:20
So this is will this will be the first
5:22
option
5:24
and here we will be writing a separate
5:26
function name that I will create later
5:28
on. So this function name will be equal
5:30
to get channel. So here we just need to
5:33
write in single quotes get channel.
5:36
Similarly we will be adding another item
5:38
to it and this time the caption will be
5:44
in single quotes
5:46
uh Google developers. So this will be a
5:48
static value
5:52
and the function will be
5:56
get Google developer
6:00
channel.
6:03
So after this we just need to add this
6:06
to the UI. So we will be calling this
6:07
method add to UI. That's it. And now
6:10
once I execute this you will see a menu
6:13
being added. First of all I need to save
6:15
the project. Let me just give a name to
6:17
it of menu 2. Click on okay. And now
6:22
when I run this,
6:26
it will ask for the permissions. So
6:28
first of all, when you run this, you
6:30
need to grant the permissions. So I will
6:32
select my Google account and then I need
6:35
to click on advanced.
6:38
Select this and then you just need to
6:41
grant this permission. Click on allow.
6:44
And uh once you grant the permission
6:46
once you again run this
6:49
running the function.
6:52
So it has successfully run this. And now
6:54
what you will find here a separate menu
6:56
item is added to your Google sheet which
6:59
is YouTube data. And it contains two
7:01
options add channel data and Google
7:03
developers. And now we just need to
7:05
create these separate functions get
7:07
channel and get Google developer channel
7:10
channel. So now we will be creating this
7:13
functions here. So first function we
7:15
will create is uh get channel. So
7:20
let me just create a space here.
7:22
Function get channel.
7:27
So inside this guys we just need to
7:31
uh prompt user to enter a user uh
7:34
channel name. So inside this we need to
7:36
again create some user interface. So we
7:39
will be again be calling spreadsheet app
7:42
get UI
7:45
again and then we will be prompting user
7:48
to enter a channel name. So we will be
7:50
declaring a channel name variable and
7:52
inside this we will be using the prompt
7:54
method in order to prompt the user. So
7:56
here we just need to apply some string
7:58
here enter the channel name. So this
8:01
will be seen by the user and uh inside
8:05
this
8:06
we need to call a simple method which
8:08
will be getting the response test that
8:10
is entered by the user get response
8:12
text. So the channel name which the user
8:16
types will be stored inside this channel
8:18
name variable and now we just need to uh
8:22
execute this custom function which will
8:24
be channel list by username.
8:29
So inside this guys we will we will be
8:31
passing our values which the user has
8:34
typed here. So the first value it
8:37
receives is the part variable. So this
8:40
is very much important snippet,
8:43
content details,
8:45
comma statistics.
8:48
So this is uh you can read this inside
8:50
YouTube data API
8:52
uh documentation. And the second
8:54
argument it takes as a parameter. So
8:56
inside double braces we will be
8:58
specifying this field which is for
9:00
username and inside this we will put
9:03
colon and we will be specifying our
9:06
channel name. So [snorts]
9:10
let me just make it as single quotes. I
9:12
forgot here. So I need to specify here
9:16
channel name.
9:18
That's it. And now we just need to
9:20
create this custom function which will
9:21
be getting our channel information
9:25
for this channel name. So we will be
9:27
creating this function. So function
9:29
channel list by username.
9:38
So now guys we just need to create this
9:40
function channel list by username. So it
9:42
expects two argument. First we are
9:44
passing the part variable, the params.
9:47
So params will be containing this
9:49
parameter which we have passed for
9:50
username.
9:52
And we now we will be making a simple
9:56
uh request to the API which will be
9:58
YouTube dot channels
10:05
dot list. So this is a list method and
10:08
inside this we will be passing our uh
10:11
part variable which will be snippet
10:14
content details statistics and the
10:16
second argument we will pass is the
10:18
params
10:21
that's it and now we will be getting a
10:23
response and now we just need to
10:24
construct our response which will be a
10:27
separate row
10:29
data row so this will contain three
10:31
values first is the channel id which
10:34
will be channel do ID.
10:38
Oh, sorry. First of all, we just need to
10:40
get the channel. So, declare this
10:42
channel variable. This will be response
10:45
dot
10:47
items. And this is we will be grabbing
10:50
the first item in the array. So, zero
10:52
index. And now we just need to uh
10:57
specify our data row variable.
11:00
So inside this the first value will be
11:03
channel do id
11:06
and then we will be getting the title by
11:08
channel dots snippet dot title. The
11:12
third property will be channel
11:14
statistics
11:18
dot view count. So it will get the total
11:20
number of views that the YouTube channel
11:22
has got. So first it it will get theuh
11:26
channel ID. Second, it will get the
11:28
channel title by channel snippet.title.
11:31
And thirdly, it will get the total
11:32
number of views which is located inside
11:35
this property which is
11:36
channel.statistics dot viewcount. And
11:38
after getting these three values, we
11:40
just need to add this inside our Google
11:42
sheet. In order to add this, we will be
11:45
calling spreadsheet
11:49
app dot get. First of all, we will get
11:52
the reference of the active spreadsheet.
11:54
After getting the reference, we will be
11:56
calling a method which will be append
11:58
row. So basically it will be adding a
12:01
new row to the Google sheet. So we will
12:05
pass our data row. That's it. And now
12:08
let me just show you what has happened
12:10
inside the application. If I run this.
12:12
So first of all go to resources and here
12:15
you just need to go to advanced Google
12:17
services. And first of all, you just
12:19
need to uh
12:22
allow the YouTube data API. You need to
12:25
uh switch on this option here. Just go
12:27
down. Once you just find this API,
12:30
YouTube data API. So just switch on this
12:34
option and click on okay. You need to
12:38
switch off on this API YouTube data API
12:40
for this to work. So click on okay.
12:44
After you turn on this API, you need to
12:46
run this function here.
12:49
So now it will uh run this function. So
12:51
it has executed successfully. And now if
12:54
you go to Google sheet and now if you
12:57
select this option of add ch channel
12:59
data and now it will prompt you to enter
13:01
a channel name. Let's suppose I enter a
13:03
channel name direct penis
13:07
and I click on okay. You will see that
13:11
it is telling me that YouTube is not
13:13
defined.
13:36
Let me again run this.
13:41
Let me refresh this.
13:54
Now guys, I will be showing you the
13:57
example here. If I click this YouTube
13:59
data option here and add channel data, I
14:02
will be prompted to enter a channel
14:05
name. I will be entering this username
14:07
direct banner. So if I click okay, you
14:10
will see that it will populate the value
14:13
channel ID, channel title and the total
14:15
number of views it has got. Let me just
14:17
show you one other example. Let's
14:19
suppose I enter the new Boston. This is
14:22
a programming channel very popular
14:26
and click on okay. You will again see
14:29
this option being populated here. So now
14:32
we just need to
14:34
uh make this separate function for
14:36
Google developers data. So now I will be
14:39
making a another function which will be
14:43
get Google developers
14:47
channel. I have located this. This is a
14:49
function.
14:51
So we just need to create this function
14:53
guys. So let me just copy the function
14:55
name.
14:59
So function copy the name and inside
15:03
this function guys we we just need to
15:06
copy paste the code that we have written
15:08
inside uh channel list by username. We
15:12
just need to uh let me just call our
15:17
channel list by username method again.
15:22
Let me just copy this line.
15:24
copy
15:27
and paste it line because in this case
15:30
we are specifying a static value which
15:32
is Google developers.
15:34
So here for username we are not
15:36
providing a dynamic value we are
15:38
specifying a
15:41
static value which is Google developers.
15:43
That's it. And now if I
15:48
select on open function again once again
15:50
save the file code on line 26. Oh sorry
15:55
guys I have made a mistake here. I have
15:57
not closed the single quotes here.
15:59
So
16:02
again run this. So it is running this.
16:06
So now if I go back to my spreadsheet
16:08
now select this option of add Google
16:10
developers data. So once I select this
16:12
you will see that it will be grabbing
16:14
the data about Google developers YouTube
16:16
channel and populate this data inside
16:18
our Google sheet. So in this example
16:20
guys we have shown this very simple
16:22
example that how to interact with
16:23
YouTube data API and store the result
16:25
inside our Google sheet. So thanks very
16:28
much for watching this