How to Bulk Scrape Video Length Details of Youtuve videos using Google Sheet

How to Bulk Retrieve Video Length of Youtube Videos using Google Sheet

by

Do you often deal with a large number of YouTube URLs in a Google Spreadsheet. And you have the need to determine the length of the videos, but you do not have the time to go to them one by one to manually check it. Sounds tedious, right?

With that in mind, here’s a google sheet script I have created that will automate this process for you. By integrating this script with your Google Spreadsheet, you’ll be able to fetch the duration of each YouTube video just by having its URL in the sheet. Let me guide you through its functionalities.

Don’t forget to checkout how to retrieve the Video Title, View count, Like count, and comment count of bulk youtube videos on this tutorial.

The Script Overview

The script comprises four main functions:

  • getYoutubeVideoDuration(): The primary function that scans through YouTube URLs in your spreadsheet and populates the durations next to them.
  • extractVideoId(url): Extracts the video ID from a YouTube URL.
  • fetchVideoDuration(videoId): Fetches the video’s duration using its video ID.
  • convertISO8601ToTime(duration): Converts the fetched ISO8601 duration format to a more readable format.

Google Sheet CODE:

function getYoutubeVideoDuration() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

// Start from row 2 to skip the header
var range = sheet.getRange("A2:A" + sheet.getLastRow());
var values = range.getValues();
var results = [];

for (var i = 0; i < values.length; i++) {
var videoId = extractVideoId(values[i][0]);
if (videoId) {
var duration = fetchVideoDuration(videoId);
results.push([duration]);
} else {
results.push(["Invalid URL"]);
}
}

// Output results starting from B2
sheet.getRange("B2:B" + (results.length + 1)).setValues(results);
}

function extractVideoId(url) {
var videoId = null;
var regex = /(?:https?:\/\/)?(?:www.)?youtube.com\/watch\?v=([\w-]+)/i;
var match = url.match(regex);
if (match) {
videoId = match[1];
}
return videoId;
}

function fetchVideoDuration(videoId) {
var apiKey = 'API_KEY';
var url = "https://www.googleapis.com/youtube/v3/videos?id=" + videoId + "&key=" + apiKey + "&part=contentDetails";
var response = UrlFetchApp.fetch(url);
var json = JSON.parse(response.getContentText());

if (json.items && json.items.length > 0) {
return convertISO8601ToTime(json.items[0].contentDetails.duration);
} else {
return "Not Found";
}
}

function convertISO8601ToTime(duration) {
var match = duration.match(/PT(\d+H)?(\d+M)?(\d+S)?/);
var hours = (parseInt(match[1]) || 0);
var minutes = (parseInt(match[2]) || 0);
var seconds = (parseInt(match[3]) || 0);
return hours + "h " + minutes + "m " + seconds + "s";
}

How to Use

Step 1:

Open your Google Spreadsheet. Click on Extensions > Apps Script to open the script editor. Copy and paste the provided script below and save it.

Make sure to replace the API_KEY part with your own API key which you can find when you create a project when Google Cloud Console. Read below how to acquire the API key.

Step 2:

In your spreadsheet, make sure to paste the YouTube URLs in column A, starting from row 2 (assuming row 1 is for headers).

Step 3:

In the Google Apps Script editor, click on the play button after selecting getYoutubeVideoDuration. This will execute the function.

Step 4:

Once you run the script, you’ll find the durations of the YouTube videos populated in column B of your spreadsheet. If a URL is invalid or the video isn’t found, you’ll see “Invalid URL” or “Not Found” respectively.

How to Acquire Google API Key:

Step 1: Accessing Google Cloud Console

  • Navigate to the Google Cloud Console.
  • Sign in with your Google account. If you don’t have a Google Cloud account yet, you’ll be prompted to create one.

Step 2: Create a New Project

  • In the top right corner, you’ll see the project dropdown. Click on it.
  • In the modal that appears, click on the NEW PROJECT button at the top right.
  • Give your project a name and select a billing account if you have one.

(Note:The YouTube Data API does have free quotas, but you might be asked to enable billing.)

  • Click CREATE.

Step 3: Enable YouTube Data API v3

  • In the Google Cloud Console dashboard, click on the navigation menu (three horizontal lines) in the top left corner.
  • Go to APIs & Services > Library.
  • In the search bar, type “YouTube Data API v3” and select it from the dropdown.
  • Click the ENABLE button on the YouTube Data API v3 page.

Step 4: Create API Credentials

  • Once the API is enabled, you’ll be taken to its dashboard. Click on the CREATE CREDENTIALS button.
  • Choose API key from the dropdown.
  • The console will generate an API key for you. You’ll see a modal with the API key. Copy this key and store it somewhere safe.

This script aims to save you the manual labor of checking each video’s duration and helps you focus on what’s essential. I hope it streamlines your workflow as much as it did mine. Feel free to modify it according to your needs and reach out if you have any questions or suggestions. Happy automating!

Leave a Comment