In this post, you will learn how to build a custom function that imports the details of YouTube videos into a sheet, given their URLs.
This is what the end result looks like:
This function can be useful for monitoring video performance, tracking the number of views and comments, and gathering data for research.
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 a YouTube video watch screen link as input and outputs the video’s data. This function is the backbone of our custom function.
function getVideoData(videoLink) {
// Getting the ID of the video
var videoID = videoLink.split('v=')[1];
// Don't run on empty
if(!videoID){return null}
// Make the request
var vidData = YouTube.Videos.list("statistics, snippet", {id: videoID}).items;
if (!vidData|vidData.length<1){return null}
// Get the first item
var video = vidData[0];
// Parsing the video details
var title = video.snippet.title;
var description = video.snippet.description;
var channelName = video.snippet.channelTitle;
var views = parseInt(video.statistics.viewCount);
var likes = parseInt(video.statistics.likeCount);
var comments = parseInt(video.statistics.commentCount);
var thumbnail = video.snippet.thumbnails.high.url;
var publishDate = new Date(video.snippet.publishedAt);
var formattedDate = publishDate.toLocaleDateString("en-US", { month: "2-digit", day: "2-digit", year: "numeric" });
var formattedTime = publishDate.toLocaleTimeString("en-US", { hour: "2-digit", minute: "2-digit" });
var formattedDateTime = formattedDate + " " + formattedTime;
var result = {data:[[title, description, channelName, views, likes, comments, formattedDateTime, thumbnail]]}
console.log(result)
// Returning the video's details
return result;
}3. Creating the custom function
If we try to call getVideoData(), 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 getVideoData().
For this reason, we need to add a few functions to the code:
- IMPORTVIDEO(videoLink):
We will create a wrapper function IMPORTVIDEO 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 getVideoData() 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 “importvideo”, it calls the getVideoData() 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:
/**
* IMPORT VIDEO FROM YOUTUBE CUSTOM FUNCTION
*
* 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 video information from YouTube API.
*
* @param {string} videoId The ID of the YouTube video.
* @return The video information.
* @customfunction
*/
function IMPORTVIDEO(videoLink) {
var parameters = {
"request_type": "importvideo",
"videoLink": videoLink
}
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;
}
function doGet(e) {
var request_type = e.parameter.request_type
if(request_type == "importvideo"){
var res = getVideoData(e.parameter.videoLink)
}
return ContentService.createTextOutput(JSON.stringify(res));
}
function getVideoData(videoLink) {
// Getting the ID of the video
var videoID = videoLink.split('v=')[1];
// Don't run on empty
if(!videoID){return null}
// Make the request
var vidData = YouTube.Videos.list("statistics, snippet", {id: videoID}).items;
if (!vidData|vidData.length<1){return null}
// Get the first item
var video = vidData[0];
// Parsing the video details
var title = video.snippet.title;
var description = video.snippet.description;
var channelName = video.snippet.channelTitle;
var views = parseInt(video.statistics.viewCount);
var likes = parseInt(video.statistics.likeCount);
var comments = parseInt(video.statistics.commentCount);
var thumbnail = video.snippet.thumbnails.high.url;
var publishDate = new Date(video.snippet.publishedAt);
var formattedDate = publishDate.toLocaleDateString("en-US", { month: "2-digit", day: "2-digit", year: "numeric" });
var formattedTime = publishDate.toLocaleTimeString("en-US", { hour: "2-digit", minute: "2-digit" });
var formattedDateTime = formattedDate + " " + formattedTime;
var result = {data:[[title, description, channelName, views, likes, comments, formattedDateTime, thumbnail]]}
console.log(result)
// Returning the video's details
return result;
}3. Usage capacity of the custom function
The number of times the getVideoData function can be used within a day depends on the quota limits set by the YouTube Data API.
The default quota limit for the YouTube Data API is 10,000 units per day. Each API request, including invalid ones, incurs a quota cost of at least one point, depending on the method used.
The getVideoData function makes a request to the YouTube Data API’s Videos.list method, which costs 1 unit. This means that, under normal circumstances, you can call the IMPORTVIDEO function 10,000 times within a day.