Google Apps ScriptTemplate

IMPORTSEARCH: Retrieve YouTube Search Results Data in a Google Sheet

2 months ago
2 months ago
0 Comments

In this post, you will learn how to build a custom function that imports into a sheet the data of YouTube search results given a search term.

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

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

1. Get the template

Click the button below to copy the template sheet with the custom function ready for use. Once you get your copy, all you need to do is follow the short video tutorial to initialize the script.

Get template

In the following lines, I’ll explain how I developed this function. So, stick with me if you’re interested in more details.

2. Creating the core function

The first thing we need to do is enable the YouTube Data API. This advanced service provides us with broad access to YouTube’s public data. It allows us to fetch details about videos. To enable it, open your Google Script editor, then go to Resources > Advanced Google Services > YouTube Data API > Enable.

Next, let’s create a core function that takes as input a search term and the number of videos (N) and it retrieves from the YouTube search results the top N videos with their data. This function is the backbone of our custom function.

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

3. Creating the custom function

If we try to call getSearchData(), we will get an error indicating that the call to the YouTube Data API has failed. The reason is that custom functions in Google Sheets run with limited authorization and are unable to call advanced services that require user authorization, such as the YouTube Data API.

So we need to find a way to grant our custom function the necessary permissions to run. As a solution, we will 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 will use the URL of the web app to make HTTPS requests to the core function getSearchData().

For this reason, we need to add a few functions to the code:

  • IMPORTSEARCH(searchTerm, numberOfVideos):
    We will create a wrapper function IMPORTSEARCH that will finally become the actual custom function. Whenever this custom function is called from the sheet, 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 we deployed. The web app, with its sufficient permissions, will then call the getSearchData() function and return the results. This way, we can successfully access the YouTube Data API.
  • makeRequestToWebApp(parameters):
    This function is responsible for making a request to the web app. It uses the UrlFetchApp.fetch method to make the HTTP request. The response from the web app is then parsed and returned. 
  • doGet(e) 
    This function 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 videoLink parameter from the request. The result of the getVideoData function is then turned into a string and returned.

Here is the updated script:

/**
 * CUSTOM FUNCTION TO IMPORT YOUTUBE SEARCH RESULTS
 * 
 * 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 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;
}

3. Usage capacity of the custom function

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

The function IMPORTSEARCH makes two types of requests to the YouTube Data API: a Search.list request to get a list of videos and a Videos.list request to get details about each video.

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.

So every time the IMPORTSEARCH custom function is called, it consumes the following amount of units:

100 + numberOfVideos * 1

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 per call + 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:

Tags:
Shares:

Leave a Reply

Your email address will not be published. Required fields are marked *