IMPORTCHANNELVIDEOS: Creating a Custom Function to Retrieve a YouTube Channel Videos in a Google Sheet

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

Welcome to the fourth episode of our “Import From YouTube” custom functions series. In this post, we’ll be focusing on creating a custom function named IMPORTCHANNELVIDEOS. This function, designed to be easily accessible from your Google Sheet, will take as input a channel name and the number of videos (N) to get 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.

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: getChannelVideosData(channelName, numberOfVideos):

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;
}

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

The function takes two parameters: channelName and numberOfVideos. channelName is the name of the YouTube channel you want to fetch videos from, and numberOfVideos is the number of videos you want to fetch from that channel.
The function first calls the YouTube.Search.list method to search for the channel by its name. It sets the q parameter to the channel name, the type parameter to ‘channel’, and the maxResults parameter to 1, which means it will return the first result that matches the search query. This is because a channel name is unique and there should only be one result for a given channel name.
It then checks if the search response has any items. If it doesn’t, it means the channel doesn’t exist, and the function returns a string “Channel not found”. If the channel does exist, the function gets the channel’s ID from the search response and stores it in the channelId variable.
The function then initializes an empty array videos to store the video data and a variable nextPageToken to handle pagination.
The function enters a do-while loop where it fetches the most recent videos from the channel. It determines the number of results to request per page (resultsPerPage), which is the smaller of numberOfVideos and 50, as the maximum number of results that can be fetched in a single request is 50.
It calls the YouTube.Search.list method again, this time setting the channelId parameter to the channel’s ID, the type parameter to ‘video’, the order parameter to ‘date’ to get the most recent videos, and the maxResults parameter to resultsPerPage. It also sets the pageToken parameter to nextPageToken to handle pagination.
It then maps over the items in the search response, creating an array for each video that contains the video’s title and URL, and concatenates these arrays to the videos array.
The function then updates nextPageToken with the nextPageToken from the search response. The loop continues until there are no more pages of results to fetch (i.e., nextPageToken is falsy) or the number of videos fetched is equal to or greater than numberOfVideos.
After the loop, the function slices the videos array to only include the first numberOfVideos videos. Finally, the function returns an object with a data property that contains the videos array.

This function forms the backbone of our custom function.

3. Creating the IMPORTCHANNELVIDEOS 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 getChannelVideosData function within the sheet.

Here is how we will do it: We will create a wrapper function IMPORTCHANNELVIDEOS 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 getChannelVideosData function and return the results. This way, we can successfully retrieve the most recent videos published by the channel in our Google Sheet.

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 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;
}

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 “importchannelvidoes”, it calls the getChannelVideosData function with the channelName and numberOfVideos parameters. The result of the getChannelData function is then stringified and returned as the response of the web app.

3. Usage capacity of IMPORTCHANNELVIDEOS

The custom function makes two types of requests to the YouTube Data API:

A Search.list request to fetch the channel’s ID.
A Search.list request inside a do-while loop to fetch the most recent videos from the channel.

To determine the number of requests this function can make daily considering the YouTube Data API quota limit, we need to understand the quota system of the YouTube Data API.

The YouTube Data API uses a quota to ensure fair usage. All API requests, including invalid requests, incur at least a one-point quota cost. Projects that enable the YouTube Data API have a default quota allocation of 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.list request to fetch the channel’s ID, which costs 100 units.
The function makes a Search.list request 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. If numberOfVideos is more than 50, the loop will iterate numberOfVideos / 50 times (rounded up to the nearest whole number). Each iteration costs 100 units.

Therefore, the total quota usage of the function is 100 + (numberOfVideos / 50) * 100 units.

Given the default quota allocation of 10,000 units per day, the function can make 10,000 / (100 + (numberOfVideos / 50) * 100) requests per day (rounded down to the nearest whole number).

Please note that this calculation assumes that numberOfVideos is the same for all requests made in a day and that no other requests are made using the same API key. If numberOfVideos varies between requests or other requests are made using the same API key, the number of requests that can be made with this function will be less.

Also, this calculation doesn’t account for the possibility of the channel not existing or the channel having fewer videos than numberOfVideos. In these cases, the function will make fewer requests and use less 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.