IMPORTCHANNEL: Creating a Custom Function to Retrieve YouTube Channel Details in a Google Sheet

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

Welcome to the second episode of our “Import From YouTube” custom functions series. In this post, we’ll be focusing on creating a custom function named IMPORTCHANNEL. This function, designed to be easily accessible from your Google Sheet, will take a YouTube channel name as input and retrieve a wealth of details about the channel.

The details we’ll be able to fetch include the channel’s name, description, number of views, number of subscribers, the total number of published videos, the date of channel creation, the thumbnail URL, and the link to the channel. This feature can be useful for tracking channel performance, conducting research, or simply staying updated with your favorite YouTube channels.

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

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 channel name.

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: getChannelData(channelName):

function getChannelData(channelName) {
  // 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;

  // Fetch the channel information
  var channelInfo = YouTube.Channels.list('snippet,statistics', {
    id: channelId
  });

  var publishDate = new Date(channelInfo.items[0].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;

  // Extract desired information from the response
  var channelDetails = {
    title: channelInfo.items[0].snippet.title,
    description: channelInfo.items[0].snippet.description,
    viewCount: parseInt(channelInfo.items[0].statistics.viewCount),
    subscriberCount: parseInt(channelInfo.items[0].statistics.subscriberCount),
    videoCount: parseInt(channelInfo.items[0].statistics.videoCount),
    publishAt: formattedDateTime, 
    thumbnail: channelInfo.items[0].snippet.thumbnails.high.url
  };

  var result = {data: [[
    channelDetails.title,
    channelDetails.description,
    channelDetails.viewCount,
    channelDetails.subscriberCount,
    channelDetails.videoCount,
    channelDetails.publishAt,
    channelDetails.thumbnail
  ]]};

  // Return the channel information as an array
  console.log(result);
  return result;
}

Let’s break down this function:

  1. The function begins by using the YouTube.Search.list method to search for the channel by its name. The ‘snippet’ part of the method call specifies that we want the search results to include the channel’s snippet data, and the ‘type: ‘channel” part specifies that we’re looking for a channel. The ‘maxResults: 1’ part limits the search to the first result.
  2. If the channel doesn’t exist (i.e., the search results are empty), the function returns a message saying “Channel not found”.
  3. If the channel does exist, the function extracts the channel’s ID from the search results.
  4. The function then uses the YouTube.Channels.list method and the channel ID to fetch the channel’s details. The ‘snippet,statistics’ part of the method call specifies that we want the channel’s snippet data and statistics.
  5. The function formats the channel’s publish date and time into a user-friendly format.
  6. The function then extracts the desired information from the channel’s details and then packages all these details into an object and returns it.

This function forms the backbone of our custom function.

3. Creating the IMPORTCHANNEL 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 the indirectly the getChannelData within the sheet.

Here is how we will do it: We will create a wrapper function IMPORTCHANNEL 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 getChannelData function and return the results. This way, we can successfully retrieve YouTube channel data from a Google Sheet.

The whole script is as follows: 

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



/**
 * Fetches channel information from YouTube API based on a channel name.
 *
 * @param {string} channelName The name of the YouTube channel.
 * @return {Object} The channel information.
 * @customfunction
 */
function IMPORTCHANNEL(channelName) {
  var parameters = {
    "request_type": "importchannel",
    "channelName": channelName,
  }
  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 == "importchannel"){
    var res = getChannelData(e.parameter.channelName)
  }

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




function getChannelData(channelName) {
  // 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;

  // Fetch the channel information
  var channelInfo = YouTube.Channels.list('snippet,statistics', {
    id: channelId
  });

  var publishDate = new Date(channelInfo.items[0].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;

  // Extract desired information from the response
  var channelDetails = {
    title: channelInfo.items[0].snippet.title,
    description: channelInfo.items[0].snippet.description,
    viewCount: parseInt(channelInfo.items[0].statistics.viewCount),
    subscriberCount: parseInt(channelInfo.items[0].statistics.subscriberCount),
    videoCount: parseInt(channelInfo.items[0].statistics.videoCount),
    publishAt: formattedDateTime, 
    thumbnail: channelInfo.items[0].snippet.thumbnails.high.url
  };

  var result = {data: [[
    channelDetails.title,
    channelDetails.description,
    channelDetails.viewCount,
    channelDetails.subscriberCount,
    channelDetails.videoCount,
    channelDetails.publishAt,
    channelDetails.thumbnail
  ]]};

  // Return the channel information as an array
  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 “importchannel”, it calls the getChannelData function with the channelName parameter from the request. The result of the getChannelData 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 getChannelData function makes two API calls: one to the YouTube.Search.list method and another to the YouTube.Channels.list method. Each one these methods cost 1 unit, so each call of the custom function will cost 2units.

This means that, under normal circumstances, you can call the IMPORTCHANNEL function 5,000 times within a day (10,000 units divided by 2 units per function call). 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

Scroll to Top