IMPORTCOMMENTS: Creating a Custom Function to Retrieve The Comments of a YouTube Video in a Google Sheet

This post helps you build a custom function that imports into a sheet the comments of a YouTube video given the video url.

Welcome to the fifth and last episode of our “Import From YouTube” custom functions series. In this post, we’ll be focusing on creating a custom function named IMPORTCOMMENTS. This function, designed to be easily accessible from your Google Sheet, will take as input a video watch screen URL and the number of comments (N) to get and it will retrieve the N most recent comments on this video.

The custom function fetches the details of the comments including the author name, the comment, the number of likes, the author image URL, the author channel URL, and the date the comment was made.

This function can be useful for building a custom YouTube analytics dashboard or conducting research.

Take a look at the GIF below to get a clear picture of the end result.

1. Get the template sheet

To access the template sheet with the function 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. Though the template displayed in the video is “Import Video From YouTube”, the initialization process remains the same for our current function.

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 search results data, we first need to write a core function that retrieves the data. This function will take the channel name and the number of videos to retrieve.

Since will be using the YouTube Data API to access channels’ info, we need to add it to our script from your Google Script Editor by going to Resources > Advanced Google Services > YouTube Data API > Enable.

Here is our core function that actually gets the data: getCommentsData(videoLink, numberOfComments):

function getCommentsData(videoLink, numberOfComments, getReplies) {
  var videoID = videoLink.split('v=')[1];

  // Don't run on empty
  if(!videoID){return null}

  var comments = [];
  var pageToken = "";
  var response = YouTube.CommentThreads.list('id, replies, snippet', {videoId: videoID, pageToken: pageToken, maxResults: numberOfComments});
  
  for(var i in response.items) {
    var item = response.items[i];

    var authorName = item.snippet.topLevelComment.snippet.authorDisplayName
    var comment = item.snippet.topLevelComment.snippet.textDisplay.replace("'", "'")
    var likeCount = item.snippet.topLevelComment.snippet.likeCount
    var authorProfileImageUrl = item.snippet.topLevelComment.snippet.authorProfileImageUrl
    var authorChannelUrl = item.snippet.topLevelComment.snippet.authorChannelUrl
    var publishDate = new Date(item.snippet.topLevelComment.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 row = [authorName, comment, likeCount, authorProfileImageUrl, authorChannelUrl, formattedDateTime]
    comments.push(row);

  }

  comments = comments.slice(0, numberOfComments);
  
  var result= { data: comments };
  console.log(result)

  return result;
}

Here’s a step-by-step explanation of how this function works:

The function accepts two parameters: videoLink, and numberOfComments. videoLink is the URL of the YouTube video from which you want to fetch comments. numberOfComments is the maximum number of comments you want to fetch.
The function first extracts the videoID from the videoLink using the split function. It splits the videoLink into an array of two strings at ‘v=’, then selects the second string in the array (at index 1), which is the videoID. If the videoID is not present or empty, the function returns null.
The function then declares an empty comments array to hold the comments fetched from the YouTube API and a pageToken variable initialized to an empty string. This pageToken will be used for pagination to fetch more comments if the number of comments exceeds the maximum results that can be returned in a single API response.
The function makes a request to the YouTube API’s CommentThreads.list method, which returns a list of comment threads associated with the videoId. The maxResults parameter is set to numberOfComments to limit the number of comments returned.
The function then iterates over the items in the response object. Each item represents a comment thread, which includes the top-level comment and its replies. The function extracts details about the top-level comment, such as the author’s name, the comment text, the number of likes, the author’s profile image URL, the author’s channel URL, and the date and time the comment was published.
The function stores these details in an array called row and pushes this array into the comments array. This process is repeated for each comment thread in the response object.
After fetching and processing all comments, the function slices the comments array to ensure that the number of comments does not exceed numberOfComments.
Finally, the function wraps the comments array in an object with a data property and returns this object.

This function forms the backbone of our custom function.

3. Creating the IMPORTCOMMENTS custom function

Calling the core function directly from the sheet as a custom function will fail. 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. Custom functions in Google Sheets run with limited authorization and are unable to call services that require user authorization. Learn about this more here.

The workaround is that we’ll first initially deploy the script as a web app, which will provide the required permissions for the function to interact with the YouTube Data API. Afterward, we can use the web app’s URL to send requests to it. This method enables us to access indirectly the getCommentsData function within the sheet.

Here is how we will do it: We will create a wrapper function IMPORTCOMMENTS that will finally become our actual 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 getCommentsData function and return the results. This way, we can successfully retrieve the most recent comments made on the video.

The whole script is as follows:

/**
 * IMPORT YOUTUBE CHANNEL VIDEOS 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 = ""



/**
 * Fetches comment information from YouTube API based on a video link.
 *
 * @param {string} videoLink The link of the YouTube video.
 * @param {number} numberOfComments The number of comments to return.
 * @return {Object} The comment information.
 * @customfunction
 */
function IMPORTCOMMENTS(videoLink, numberOfComments) {
  numberOfComments = numberOfComments || 50;
  var parameters = {
    "request_type": "importcomments",
    "videoLink": videoLink,
    "numberOfComments": numberOfComments
  }

  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 == "importcomments"){
    var res = getCommentsData(e.parameter.videoLink, e.parameter.numberOfComments, e.parameter.getReplies)
  } 

  return ContentService.createTextOutput(JSON.stringify(res));
}



function getCommentsData(videoLink, numberOfComments, getReplies) {
  var videoID = videoLink.split('v=')[1];

  // Don't run on empty
  if(!videoID){return null}

  var comments = [];
  var pageToken = "";
  var response = YouTube.CommentThreads.list('id, replies, snippet', {videoId: videoID, pageToken: pageToken, maxResults: numberOfComments});
  
  for(var i in response.items) {
    var item = response.items[i];

    var authorName = item.snippet.topLevelComment.snippet.authorDisplayName
    var comment = item.snippet.topLevelComment.snippet.textDisplay.replace("'", "'")
    var likeCount = item.snippet.topLevelComment.snippet.likeCount
    var authorProfileImageUrl = item.snippet.topLevelComment.snippet.authorProfileImageUrl
    var authorChannelUrl = item.snippet.topLevelComment.snippet.authorChannelUrl
    var publishDate = new Date(item.snippet.topLevelComment.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 row = [authorName, comment, likeCount, authorProfileImageUrl, authorChannelUrl, formattedDateTime]
    comments.push(row);

  }

  comments = comments.slice(0, numberOfComments);
  
  var result= { data: comments };
  console.log(result)

  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 “importcomments”, it calls the getCommentsData function with the videoLink and numberOfComments parameters. The result of the getCommmentsData function is then stringified and returned as the response of the web app.

3. Usage capacity of IMPORTCOMMENTS

The number of daily requests that can be made with the function depends on the YouTube Data API’s daily quota. The default quota allocation for the YouTube Data API is 10,000 units per day.

Each API request incurs a quota cost. The CommentThreads.list method, used in the getCommentsData function has a quota cost of 1 unit. This cost is incurred irrespective of the number of comments that are fetched in a single call. So numberOfComments does not affect the quota cost. This means that each call to the custom function costs 1 unit from your daily quota.

Therefore, with the default quota allocation, you can make up to 10,000 requests per day to IMPORTCOMMENTS. This is under the assumption that no other requests are being made on the same project that consumes the quota.

It’s important to note that the 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:

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.