How to Bulk Scrape Youtube Video Details using Google Sheet

How to Bulk Scrape Youtube Video Details Using Google Sheet

by

Do you want to keep track on Videos on Youtube? But, you can’t possibly visit each video you want to track and check them one by one. Well, here’s a solution for you. Here’s a Google sheet script that tracks the view count, comment count, like count, of the Youtube video URL’s you feed into it.

On our previous tutorial I showed you on how to bulk check URL’s if they are alive or not using only Google sheet. Today, we are going to create a Youtube scraper that scrapes data from the Youtube video URLs. This script will scrape data from any youtube video URL such as the title, views, comment count, and like count using only Google Sheets using Youtube API.

Link to my Google Sheet that has already the script you may simply “MAKE A COPY” or follow the each instruction here. https://docs.google.com/spreadsheets/d/1VA4K3MnVfYiIu1EtA5PEGOZVHSBQQOWYCtUvUFSw3XE/edit?usp=sharing

Why Should You Use This Tool As A Tracker?

Here are the best solutions this script solves!

  • Automated Gathering of Data: The script automatically scrapes the data for each video URL you feed into it, saving you time and effort compared to manually searching for each video and entering its information one by one.
  • Easy to use: Google Sheets is user-friendly and easy to use, so you don’t need to have any technical knowledge to get started.
  • Customizable: The script can be modified to gather additional data if needed, making it customizable to fit your specific needs.
  • Accessible: Google Sheets is accessible from anywhere as long as you have internet connection, making it easy to access your data from multiple devices.
  • Collaborative: Google Sheets allows multiple people to access and edit the data in real-time, making it a great tool for teams working on the same project.
  • 100% Free: Both Google Sheets and the YouTube API are free to use though it has some limitations but they its still FREE. Making this tool a cost-effective solution for keeping track of your video data.

On this tutorial we are going to be using the Youtube API from console.cloud.google.com. You have to create an account with Cloud Google. Using Youtube API has some limitations such as you can only make 10,000 request per day. As someone that only tracks video details this is more than enough.

Let’s get started.

Step 1: Generate Youtube API Key.

  • To generate an API key simply go to Google Cloud Console https://console.cloud.google.com
  • Create a new project, and name it to whatever you want. Click on the Project you created, make sure you are on the Projects Dashboard before you make changes.
  • Go to the API Library and find the YouTube Data API V3. Click on it and enable it.
  • Go back to your Project Dashboard and click on API and Services, under it find the Credentials.
  • Then create new API key.
  • Copy the API key and paste it in the script, replacing YOUR_API_KEY on the script on Step 3.

Step 2: Creating a new Google Sheet

  • Open Google Drive and click on the New button to create a new Google Sheet.
  • Give your sheet a name and click on Create.
  • On the column A, enter the Youtube Video URL you want to track.

Step 3: Creating a script to scrape the video details

  • On the same sheet, go to the Tools menu in Google Sheets and click on Script editor.
  • In the script editor, paste the following scraping code you can also find the code below on the description of this video.
function getYouTubeInfo() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();

  for (var i = 0; i < data.length; i++) {
    var url = data[i][0];
    if (url == "") {
      continue;
    }

    var videoId = extractVideoId(url);
    if (!videoId) {
      continue;
    }

    var video = getVideoData(videoId);
    if (!video) {
      continue;
    }

    sheet.getRange(i + 1, 2).setValue(video.title);
    sheet.getRange(i + 1, 3).setValue(video.viewCount);
    sheet.getRange(i + 1, 4).setValue(video.commentCount);
    sheet.getRange(i + 1, 5).setValue(video.likeCount);
  }
}

function extractVideoId(url) {
  var regExp = /^.*(youtu\.be\/|v\/|u\/\w\/|embed\/|watch\?v=|&v=)([^#&?]*).*/;
  var match = url.match(regExp);

  if (match && match[2].length == 11) {
    return match[2];
  }

  return null;
}

function getVideoData(videoId) {
  var apiKey = "YOUR_API_KEY";
  var url = "https://www.googleapis.com/youtube/v3/videos?part=snippet,statistics&id=" + videoId + "&key=" + apiKey;

  var response = UrlFetchApp.fetch(url);
  var data = JSON.parse(response.getContentText());

  if (data.items.length == 0) {
    return null;
  }

  var video = data.items[0];
  return {
    title: video.snippet.title,
    viewCount: video.statistics.viewCount,
    commentCount: video.statistics.commentCount,
    likeCount: video.statistics.likeCount
  };
}

You will notice that YOUR_API_KEY is empty, replaced it with what you have geneated on the step 1.
Save it, and leave it for now.

Step 4: Running the Script.

When you already have entered the video URL’s on column A and everything is correct, you may click on the “RUN” button on the Apps Script tab, check if the scripts run smoothly with no errors like the image below. Go back to your Sheet, and see the details are propagated as expected.

Here’s A Detailed Video Tutorial

Please note that you can only make 10,000 of API request per day when using this approached.

Leave a Comment