In this post, you will learn how to build a custom function that imports into a sheet the details of a YouTube channel given the channel name.
Take a look at how it works:
In the following lines, I’ll explain how I developed this function. So, stick with me if you’re interested in more details.
1. Get the template
Click the button below to copy the template sheet with the custom function ready for use. Once you get your copy, all you need to do is follow the short video tutorial to initialize the script.
In the following lines, I’ll explain how I developed this function. So, stick with me if you’re interested in more details.
2. Creating the core function
The first thing we need to do is enable the YouTube Data API. This advanced service provides us with broad access to YouTube’s public data. It allows us to fetch details about videos. To enable it, open your Google Script editor, then go to Resources > Advanced Google Services > YouTube Data API > Enable.
Next, let’s create a core function that takes as input a channel name and the number of videos (N) and it will retrieve the N most recent videos published by the channel.
The custom function fetches only the videos’ URLs. We can then use the custom function IMPORTVIDEO to get details of each video including the video title, the description, the channel, the number of views, the number of likes, the number of comments, the publish date, and the thumbnail URL.
function getChannelVideosData(channelName, numberOfVideos) {
// Fetch the channel's ID
var searchResponse = YouTube.Search.list('snippet', {
q: channelName,
type: 'channel',
maxResults: 1
});
// Check if the channel exists
if (searchResponse.items.length === 0) {
return "Channel not found";
}
// Get the channel's ID
var channelId = searchResponse.items[0].id.channelId;
var videos = [];
var nextPageToken = '';
do {
// Determine the number of results to request per page
var resultsPerPage = numberOfVideos < 50 ? numberOfVideos : 50;
// Fetch the most recent videos
var videoResponse = YouTube.Search.list('snippet', {
channelId: channelId,
type: 'video',
order: 'date',
maxResults: resultsPerPage,
pageToken: nextPageToken
});
videos = videos.concat(videoResponse.items.map(function (video) {
return [
video.snippet.title,
'https://www.youtube.com/watch?v=' + video.id.videoId
];
}));
nextPageToken = videoResponse.nextPageToken;
} while (nextPageToken && (videos.length < numberOfVideos));
videos = videos.slice(0, numberOfVideos);
var result = { data: videos };
console.log(result)
// Return the channel information as an array
return result;
}
3. Creating the custom function
If we try to call getChannelVideosData(), we will get an error indicating that the call to the YouTube Data API has failed. The reason is that custom functions in Google Sheets run with limited authorization and are unable to call advanced services that require user authorization, such as the YouTube Data API.
So we need to find a way to grant our custom function the necessary permissions to run. As a solution, we will first deploy the script as a web app, which will grant the necessary permissions for the function to access the YouTube Data API. Then, we’ll use the URL of the web app to make HTTPS requests to the core function getChannelVideosData().
For this reason, we need to add a few functions to the code:
- IMPORTCHANNELVIDEOS(channelName, numberOfVideos):
We will create a wrapper function IMPORTCOMMENTS that will finally become the actual custom function. Whenever this custom function is called from the sheet, instead of trying to call the core function directly (which would fail due to insufficient permissions), a request will be made to the web app we deployed. The web app, with its sufficient permissions, will then call the getChannelVideosData() function and return the results. This way, we can successfully access the YouTube Data API. - makeRequestToWebApp(parameters):
This function is responsible for making a request to the web app. It uses the UrlFetchApp.fetch method to make the HTTP request. The response from the web app is then parsed and returned. - doGet(e)
This function is automatically executed when a GET request is made to the URL of the web app. In this script, thedoGetfunction checks the request_type parameter of the request. If the request_type is “importchannelvideos”, it calls the getChannelVideosData() function with the videoLink parameter from the request. The result of thegetVideoDatafunction is then turned into a string and returned.
Here is the updated script:
/**
* CUSTOM FUNCTION TO IMPORT YOUTUBE CHANNEL VIDEOS
*
* Written with ❤️ by Joseph Asinyo
*/
// IMPORTANT NOTE: Deploy the script as a web app and insert the url below
var WEB_APP_URL = "INSERT_THE_WEB_APP_URL_HERE";
/**
* Fetches recent published videos by a channel based on a channel name.
*
* @param {string} channelName The name of the YouTube channel.
* @param {number} [numberOfVideos=50] The number of videos to return. Defaults to 50 if not provided.
* @return {Object} Recent published videos by the channel.
* @customfunction
*/
function IMPORTCHANNELVIDEOS(channelName, numberOfVideos) {
numberOfVideos = numberOfVideos || 50;
var parameters = {
"request_type": "importchannelvideos",
"channelName": channelName,
"numberOfVideos": numberOfVideos
}
var result = makeRequestToWebApp(parameters)
return result;
}
function makeRequestToWebApp(parameters) {
var url = WEB_APP_URL+"?";
for (const key of Object.keys(parameters)) {
url += key+"="+parameters[key]+"&";
}
url = url.slice(0, -1); // Remove the last "&" character
console.log(url)
var response = UrlFetchApp.fetch(url).getContentText();
var result = JSON.parse(response).data;
return result;
}
// Web Apps using as the wrapper.
function doGet(e) {
var request_type = e.parameter.request_type
if(request_type == "importchannelvideos"){
var res = getChannelVideosData(e.parameter.channelName, e.parameter.numberOfVideos)
}
return ContentService.createTextOutput(JSON.stringify(res));
}
function getChannelVideosData(channelName, numberOfVideos) {
// Fetch the channel's ID
var searchResponse = YouTube.Search.list('snippet', {
q: channelName,
type: 'channel',
maxResults: 1
});
// Check if the channel exists
if (searchResponse.items.length === 0) {
return "Channel not found";
}
// Get the channel's ID
var channelId = searchResponse.items[0].id.channelId;
var videos = [];
var nextPageToken = '';
do {
// Determine the number of results to request per page
var resultsPerPage = numberOfVideos < 50 ? numberOfVideos : 50;
// Fetch the most recent videos
var videoResponse = YouTube.Search.list('snippet', {
channelId: channelId,
type: 'video',
order: 'date',
maxResults: resultsPerPage,
pageToken: nextPageToken
});
videos = videos.concat(videoResponse.items.map(function (video) {
return [
video.snippet.title,
'https://www.youtube.com/watch?v=' + video.id.videoId
];
}));
nextPageToken = videoResponse.nextPageToken;
} while (nextPageToken && (videos.length < numberOfVideos));
videos = videos.slice(0, numberOfVideos);
var result = { data: videos };
// Return the channel information as an array
return result;
}
3. Usage capacity of IMPORTCHANNELVIDEOS
The default quota limit for the YouTube Data API is 10,000 units per day.
The Search.list method, which is used in the getChannelVideosData function has a quota cost of 100 units per request.
Now let’s calculate the quota usage of the getChannelVideosData function:
- The function makes one
Search.listrequest to fetch the channel’s ID, which costs 100 units. - The function makes a
Search.listrequest to fetch the most recent videos in a do-while loop. The maximum number of iterations of this loop depends on the numberOfVideos parameter and the YouTube Data API’s limit of 50 results per page. IfnumberOfVideosis more than 50, the loop will iterate numberOfVideos / 50 times (rounded up to the nearest whole number). Each iteration costs 100 units.
Therefore, each time the IMPORTCHANNELVIDEOS is called, it will consume:
100 + (numberOfVideos / 50) * 100 units.
Given the default quota allocation of 10,000 units per day, the function can make:
10,000 / (100 + (number of videos / 50) * 100) requests per day (rounded down to the nearest whole number).
This calculation assumes that numberOfVideos is the same for all requests made in a day.
The YouTube API daily quota resets at midnight Pacific Time. If you reach the quota limit, you can request additional quota by completing the Quota extension request form for YouTube API Services.
Useful links:
