IMPORTVIDEO: Creating a Custom Function to Retrieve YouTube Video Details in a Google Sheet

This post helps you build a custom function that import into a sheet the details of YouTube videos given their urls.

The ability to pull data from various sources and integrate them into a single, accessible tool is one of the core strengths of Google Apps Script. In this blog post, we will explore how to integrate two popular Google products, YouTube and Google Sheets, using the YouTube Data API.

The YouTube Data API provides us with broad access to YouTube’s public data. It allows us to fetch details about videos, channels, playlists, and more.

In this post, we will focus on retrieving video information given a YouTube watch screen link. We will write a custom function in Google Apps Script to fetch this data and populate it directly into a Google Sheet. This custom function will make the feature more accessible and easy to use right from your Google Sheet.

This feature can be useful for monitoring video performance, tracking views and comments, and gathering data for research.

While this post will focus on retrieving video information, the techniques and concepts we will cover can be extended to access other types of information from YouTube as well. We will explore these additional possibilities in future posts. This post is the first of a series of six posts on Import From YouTube.

This is what the end result looks like:

1. Get a template sheet

To access the template sheet with the feature ready for use, please use the button below. All you need to do is follow the short video tutorial to initialize the script, and you’ll be good to go.

Get the template

In the following lines, I’ll provide an explanation of how I developed this feature. So, stick with me if you’re interested in more details regarding the logic behind this tool.

2. Building the core function

To begin creating a custom function in Google Apps Script that fetches YouTube video data, we first need to write a core function that retrieves the data. This function will take a YouTube video watch screen link as input, which typically looks like this: https://www.youtube.com/watch?v=gtMqg_QWMzA.

Before we delve into the function itself, it’s important to note that this function will use the YouTube service to access the required data. So we have to add this to your script from your Google Script Editor by going to Resources > Advanced Google Services > YouTube Data API > Enable.

Now, let’s break down the function getVideoData(videoLink):

function getVideoData(videoLink) {
  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];

  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)
  return result;
}

This function works as follows:

  1. It first extracts the video ID from the provided YouTube link by splitting the URL at ‘v=’ and taking the second part.
  2. It then uses the YouTube Data API’s Videos.list method to fetch the video’s statistics and snippet (which contains details like title, description, etc.) using the video ID.
  3. If the video data is not found or the returned array is empty, it returns null.
  4. If the video data is found, it extracts the first item (assuming there is only one video with the given ID).
  5. It then extracts various details from the video data, such as the title, description, channel name, view count, like count, comment count, and high-resolution thumbnail URL.
  6. It also formats the video’s publish date and time in a user-friendly format.
  7. Finally, it packages all these details into an object and returns it.

This function forms the backbone of our custom function, providing us with the capability to retrieve a wide range of data from a given YouTube video link.

3. Creating the custom function

With the core function now in place, we can convert it into a custom function that can be directly called from a Google Sheet. However, if we attempt to do so, we encounter an error indicating that the API call has failed. This is because, while the function appears ready to be used, it requires manual authorization from the script editor to access the YouTube Data API.

When we test the function from the script editor with a video link and manually run it, we are able to retrieve the video data and see the results printed in the log. This is because the manual run from the script editor prompts us to authorize the necessary permissions, allowing the function to execute successfully. However, when we attempt to call the function directly from the Google Sheet, the function fails to execute due to the lack of these permissions, and we will not even be prompted to grant such permissions. Custom functions in Google Sheets run with limited authorization and are unable to call services that require user authorization.

Don’t worry, there’s a workaround to this issue. We’ll 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 requests to it. This approach allows us to access the core function, getVideoData, even from a Google Sheet.

The key here is to create a wrapper function IMPORTVIDEO that will finally become our actually custom function, which we’ll use directly from the sheet. Whenever this custom function is called, 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. The web app, with its sufficient permissions, will then call the getVideoData function and return the results. This way, we can successfully retrieve YouTube video data from a Google Sheet.

The whole script is as follows: 


/**
 * 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) {
  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];

  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]]}
  return result;
}

Important note: Deploy the script as a web app and insert the web app URL in the script.

There are two utility functions that help us implement the logic explained above:

  • The makeRequestToWebApp function is responsible for making a request to the web app that we’ve deployed. It constructs the URL for the request by appending the parameters to the base URL of the web app. It then uses the UrlFetchApp.fetch method to make the HTTP request to the web app. The response from the web app is then parsed and returned.
  • The doGet function is a special function in Google Apps Script that is automatically executed when a GET request is made to the URL of the web app. In this script, the doGet function 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 the getVideoData function is then stringified and returned as the response of the web app.

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. However, this limit can be increased by applying for a higher quota.

Google Apps ScriptGoogle SheetsYouTube

Joseph Asinyo

Google Workspace Developer

I’m Joseph. I love building applications and writing Google scripts to automate Google Workspace for greater productivity. Learn more about me.

Related Posts

How to Create Radio Buttons in Google Sheets

This post helps you create radio buttons in Google Sheets using Google Apps Script. I cover how to add this radio button feature both horizontally and vertically.

Scroll to Top