IMPORTSEARCH: Creating a Custom Function to Retrieve YouTube Search Results Details in a Google Sheet

This post helps you build a custom function that import into a sheet the details of YouTube search results given a search term.

Welcome to the third episode of our “Import From YouTube” custom functions series. In this post, we’ll be focusing on creating a custom function named IMPORTSEACH. This function, designed to be easily accessible from your Google Sheet, will take as input a search term and the number of videos (N) to get and it will retrieve from the YouTube search results the top N videos with their details.

The details we’ll be able to fetch about each video include the video title, the description, the channel, the number of views, the number of likes, the number of comments, the publish date, the thumbnail URL, and the video link. This feature 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 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 search results data, we first need to write a core function that retrieves the data. This function will take a search term 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: getSearchData(searchTerm, numberOfVideos):

function getSearchData(searchTerm, numberOfVideos) {
  var results = [];
  var nextPageToken = "";

  while (results.length < numberOfVideos && typeof nextPageToken !== "undefined") {
    var resultsPerPage = numberOfVideos < 50 ? numberOfVideos : 50;
    
    var searchResponse = YouTube.Search.list('snippet', {
      q: searchTerm,
      maxResults: resultsPerPage,
      pageToken: nextPageToken
    });

    if (searchResponse.error) {
      return "Unable to search videos";
    }

    var videosData = searchResponse.items;

    for (var i = 0; i < videosData.length; i++) {
      var videoData = videosData[i].snippet;
      var videoId = videosData[i].id.videoId;

      if(!videoId) continue;

      var videoDetails = YouTube.Videos.list('snippet,statistics', {
        id: videoId
      });

      var videoStats = videoDetails.items[0].statistics;
    
      var views = parseInt(videoStats.viewCount);
      var likes = parseInt(videoStats.likeCount);
      var comments = parseInt(videoStats.commentCount);
      var publishDate = new Date(videoData.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 thumbnails = videoData.thumbnails;

      // Construct the YouTube video URL
      var videoUrl = 'https://www.youtube.com/watch?v=' + videoId;

      var result = [
        videoData.title,
        videoData.description,
        videoData.channelTitle,
        views,
        likes,
        comments,
        formattedDateTime,
        thumbnails.high.url,
        videoUrl 
      ];

      results.push(result);
    }

    nextPageToken = searchResponse.nextPageToken;
  }

  results = results.slice(0, numberOfVideos);

  results = {data: results}
  console.log(results)
  
  return results;
}

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

  1. It initializes an empty array results and a variable nextPageToken with an empty string. The results array is used to store the search results and nextPageToken is to handle the pagination in YouTube.
  2. It then enters a while loop that continues as long as the length of the results array is less than numberOfVideos and nextPageToken is not undefined. This is to ensure that the function retrieves the desired number of videos.
  3. Within the loop, it sets the resultsPerPage to the lesser of numberOfVideos and 50. This is because the YouTube API allows a maximum of 50 results per page. It then sends a request to the YouTube Search API with the parameters searchTerm, resultsPerPage, and nextPageToken.
  4. For each item in the search response, it retrieves the video data (snippet) and video ID. If the video ID is not defined, it continues to the next item. For each valid video, it sends a request to the YouTube Videos API to retrieve the video’s statistics.
  5. It then parses the view count, like count, and comment count from the video’s statistics and formats the video’s published date and time. It constructs the video’s URL and stores all the video’s data in an array result. This array is then pushed to the results array.
  6. After processing all items on the current page, it updates nextPageToken to the nextPageToken value from the search response. This allows it to retrieve the next page of results in the next iteration of the while loop.
  7. After the while loop, it slices the results array to the length of numberOfVideos to ensure that only the desired number of results are returned. Finally, it wraps the results array in an object and returns this object. This object contains the data of all the retrieved videos.

This function forms the backbone of our custom function.

3. Creating the IMPORTSEARCH 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 getSearchData function within the sheet.

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

The whole script is as follows: 

/**
 * IMPORT YOUTUBE SEARCH RESULTS 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 video information from YouTube API based on a search term.
 *
 * @param {string} searchTerm The term to search for.
 * @param {number} [numberOfVideos=50] The number of videos to return. Defaults to 50 if not provided.
 * @return {Object} The video information.
 * @customfunction
 */
function IMPORTSEARCH(searchTerm, numberOfVideos) {
  numberOfVideos = numberOfVideos || 50;
  var parameters = {
    "request_type": "importsearch",
    "searchTerm": searchTerm,
    "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 == "importsearch"){
    var res = getSearchData(e.parameter.searchTerm, e.parameter.numberOfVideos)
  } 

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



function getSearchData(searchTerm, numberOfVideos) {
  var results = [];
  var nextPageToken = "";

  while (results.length < numberOfVideos && typeof nextPageToken !== "undefined") {
    var resultsPerPage = numberOfVideos < 50 ? numberOfVideos : 50;
    
    var searchResponse = YouTube.Search.list('snippet', {
      q: searchTerm,
      maxResults: resultsPerPage,
      pageToken: nextPageToken
    });

    if (searchResponse.error) {
      return "Unable to search videos";
    }

    var videosData = searchResponse.items;

    for (var i = 0; i < videosData.length; i++) {
      var videoData = videosData[i].snippet;
      var videoId = videosData[i].id.videoId;

      if(!videoId) continue;

      var videoDetails = YouTube.Videos.list('snippet,statistics', {
        id: videoId
      });

      var videoStats = videoDetails.items[0].statistics;
    
      var views = parseInt(videoStats.viewCount);
      var likes = parseInt(videoStats.likeCount);
      var comments = parseInt(videoStats.commentCount);
      var publishDate = new Date(videoData.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 thumbnails = videoData.thumbnails;

      // Construct the YouTube video URL
      var videoUrl = 'https://www.youtube.com/watch?v=' + videoId;

      var result = [
        videoData.title,
        videoData.description,
        videoData.channelTitle,
        views,
        likes,
        comments,
        formattedDateTime,
        thumbnails.high.url,
        videoUrl 
      ];

      results.push(result);
    }

    nextPageToken = searchResponse.nextPageToken;
  }

  results = results.slice(0, numberOfVideos);

  results = {data: results}
  console.log(results)
  
  return results;
}

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

3. Usage capacity of IMPORTSEARCH

The function IMPORTSEARCH makes two types of requests to the YouTube Data API for each video it finds: a search.list request to get a list of videos and a videos.list request to get details about each video. To understand how many times these requests can be made within the daily quota, we need to understand the quota cost of each request type and the total quota available.

The YouTube Data API assigns a quota cost to each type of request:

  • The search.list request costs 100 units.
  • The videos.list request costs 1 unit.

By default, the YouTube Data API provides a daily quota of 10,000 units for each project.

Let’s calculate the number of times the function can be run based on these costs. First, we need to determine the total cost of each function call. The function makes numberOfVideos x videos.list requests and one search.list request. So, the total cost is numberOfVideos * 1 (videos.list cost) + 100 (search.list cost).

To find out how many times the IMPORTSEARCH function can be called in a day, we divide the daily quota by the total cost per function call: 

10,000 / (numberOfVideos + 100).

This calculation assumes that the numberOfVideos is the same for all calls and is a multiple of 50, which is the maximum results per page when the search.list method is used.

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:

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