How to Use the YouTube API to Fetch Video Data in Google Sheets: Automate with Google Apps Script!

function fetchAndWriteYouTubeData() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Video Information");
    if (!sheet) {
        Logger.log("Sheet 'Video Information' not found.");
        return;
    }

    // Retrieve the API key from script properties
    var apiKey = PropertiesService.getScriptProperties().getProperty('YOUTUBE_DATA_API_KEY');
    Logger.log("API Key: " + apiKey);
    
    // Retrieve the channel ID from cell B1
    var channelId = sheet.getRange("B1").getValue();
    Logger.log("Channel ID: " + channelId);
    
    // Retrieve the range from cells B2 and B3
    var startIndex = parseInt(sheet.getRange("B2").getValue()) || 1;
    var endIndex = parseInt(sheet.getRange("B3").getValue()) || 10;
    var maxResults = Math.min(endIndex - startIndex + 1, 100);
    Logger.log(`Start Index: ${startIndex}, End Index: ${endIndex}`);

    // YouTube Data API URL
    var baseUrl = `https://www.googleapis.com/youtube/v3/search?key=${apiKey}&channelId=${channelId}&part=snippet&order=date&type=video&maxResults=100`;
    
    var videos = []; // Store video data
    var nextPageToken = '';
    var totalFetched = 0;

    // Use pagination to fetch video data
    while (nextPageToken !== null && totalFetched < endIndex) {
        var url = baseUrl + (nextPageToken ? `&pageToken=${nextPageToken}` : '');
        var response = UrlFetchApp.fetch(url);
        var data = JSON.parse(response.getContentText());
        if (!data.items) {
            Logger.log("No data retrieved.");
            break;
        }

        videos = videos.concat(data.items);
        totalFetched += data.items.length;

        nextPageToken = data.nextPageToken || null;
        Logger.log(`Fetched ${data.items.length} videos, Total fetched: ${totalFetched}`);
    }

    // Extract the required range and retrieve video IDs
    var videoIds = videos.slice(startIndex - 1, endIndex).map(item => item.id.videoId).join(',');

    // Fetch detailed information (view count, likes, duration) using the videos endpoint
    var detailsUrl = `https://www.googleapis.com/youtube/v3/videos?key=${apiKey}&id=${videoIds}&part=snippet,contentDetails,statistics`;
    var detailsResponse = UrlFetchApp.fetch(detailsUrl);
    var detailsData = JSON.parse(detailsResponse.getContentText());

    // Get the next row to write data
    var lastRow = sheet.getLastRow();

    // Write data to the spreadsheet
    detailsData.items.forEach(function(item, index) {
        var videoId = item.id;
        var title = item.snippet.title;
        var publishedAt = item.snippet.publishedAt;
        var thumbnailUrl = item.snippet.thumbnails.default.url; // Default thumbnail URL
        var channelTitle = item.snippet.channelTitle;
        var viewCount = item.statistics.viewCount;
        var likeCount = item.statistics.likeCount;
        var videoUrl = `https://www.youtube.com/watch?v=${videoId}`;

        // Format the video duration from ISO 8601
        var duration = formatDuration(item.contentDetails.duration);

        Logger.log(`Writing to row ${lastRow + index + 1}: ${title}`);

        sheet.getRange(lastRow + index + 1, 1).setValue(videoId);         // Column A: Video ID
        sheet.getRange(lastRow + index + 1, 2).setValue(title);           // Column B: Title
        sheet.getRange(lastRow + index + 1, 3).setValue(publishedAt);     // Column C: Publish Date
        sheet.getRange(lastRow + index + 1, 4).setFormula(`=IMAGE("${thumbnailUrl}")`); // Column D: Thumbnail
        sheet.getRange(lastRow + index + 1, 5).setValue(channelTitle);    // Column E: Channel Title
        sheet.getRange(lastRow + index + 1, 6).setValue(viewCount);       // Column F: View Count
        sheet.getRange(lastRow + index + 1, 7).setValue(likeCount);       // Column G: Like Count
        sheet.getRange(lastRow + index + 1, 8).setValue(duration);        // Column H: Video Duration
        sheet.getRange(lastRow + index + 1, 9).setValue(videoUrl);        // Column I: Video URL
    });
}

// Convert ISO 8601 duration to "HH:MM:SS" format
function formatDuration(duration) {
    var matches = duration.match(/PT(\d+H)?(\d+M)?(\d+S)?/);
    var hours = (matches[1] ? matches[1].replace('H', '') : '00').padStart(2, '0');
    var minutes = (matches[2] ? matches[2].replace('M', '') : '00').padStart(2, '0');
    var seconds = (matches[3] ? matches[3].replace('S', '') : '00').padStart(2, '0');
    return `${hours}:${minutes}:${seconds}`;
}