Automate YouTube Reporting with Google Sheets (FREE )

// ------------------------------
// YouTube Weekly Stats Tracker
// ------------------------------

var spreadsheetId = "1nm1ptz6Rh8NWY8-FNLVlpH_3A3qrzKDiXkyUP-WK2Z4";
var channelId = 'UCg-2EDDuh88KeKSmvQW_pMQ';
var recipients = "rajnish@outrightcrm.com";
var apiKey = 'AIzaSyC_oelNeIU2lWowaFvOPTgG5o6rnArAymQ';
var sheetName = "Sheet1";

var headers = [
  "Videos", "Shorts", "Subscriber Count Last Week", "Total Subscriber Count",
  "Subscribers Gained This Week", "Views Last Week", "Views Current Week",
  "Total Views", "Watch Hours This Week"
];

var values = [];

// ------------------------------
// Add menu on spreadsheet open
// ------------------------------
function onOpen() {
  createHeader();
  SpreadsheetApp.getUi().createMenu('YouTube Menu')
    .addItem('Create Header', 'createHeader')
    .addItem('Get Weekly Status', 'getWeeklyStatus')
    .addToUi();
}

// ------------------------------
// Create header row
// ------------------------------
function createHeader() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange('A1:I1').setValues([headers])
    .setFontSize(10)
    .setFontWeight('bold')
    .setHorizontalAlignment('center');
}

// ------------------------------
// Fetch weekly stats and update sheet
// ------------------------------
function getWeeklyStatus() {
  try {
    var sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName);
    var today = new Date();
    var oneWeekAgo = new Date(today.getTime() - 7 * 24 * 60 * 60 * 1000);

    // Reset counters
    var mainVideoCount = 0, shortVideoCount = 0, totalWatchTimeSeconds = 0;
    var subscriberCountLastWeek = 0, totalSubscriberCount = 0;
    var viewsLastWeek = 0, viewsCurrentWeek = 0, subscribersGainedThisWeek = 0;

    // Get all videos published in the last week
    var videoUrl = `https://www.googleapis.com/youtube/v3/search?part=id&channelId=${channelId}&publishedAfter=${Utilities.formatDate(oneWeekAgo, Session.getScriptTimeZone(), "yyyy-MM-dd'T'HH:mm:ss'Z'")}&type=video&key=${apiKey}`;
    var videoResponse = UrlFetchApp.fetch(videoUrl);
    var videoData = JSON.parse(videoResponse.getContentText());

    videoData.items.forEach(item => {
      var videoId = item.id.videoId;
      var durationSeconds = getVideoDuration(videoId);
      totalWatchTimeSeconds += durationSeconds;
      if (durationSeconds >= 60) mainVideoCount++; else shortVideoCount++;
    });

    // Analytics for last week
    var startDate = Utilities.formatDate(oneWeekAgo, Session.getScriptTimeZone(), 'yyyy-MM-dd');
    var endDate = Utilities.formatDate(today, Session.getScriptTimeZone(), 'yyyy-MM-dd');

    var results = YouTubeAnalytics.Reports.query({
      ids: 'channel==' + channelId,
      startDate: startDate,
      endDate: endDate,
      metrics: 'subscribersGained,views'
    });

    if (results.rows && results.rows.length > 0) {
      subscriberCountLastWeek = results.rows[0][0];
      viewsLastWeek = results.rows[0][1];
    }

    // Total subscribers & views
    var response = YouTube.Channels.list('statistics', { id: channelId });
    if (response.items && response.items.length > 0) {
      totalSubscriberCount = response.items[0].statistics.subscriberCount;
      var totalViews = response.items[0].statistics.viewCount;
      viewsCurrentWeek = totalViews - viewsLastWeek;
    }

    subscribersGainedThisWeek = totalSubscriberCount - subscriberCountLastWeek;
    var totalWatchHours = totalWatchTimeSeconds / 3600;

    // Prepare values array
    values = [
      mainVideoCount, shortVideoCount, subscriberCountLastWeek, totalSubscriberCount,
      subscribersGainedThisWeek, viewsLastWeek, viewsCurrentWeek, totalViews, totalWatchHours
    ];

    // Append to sheet
    var lastRow = sheet.getLastRow() + 1;
    sheet.getRange('A' + lastRow + ':I' + lastRow).setValues([values]);

    // Send weekly email
    sendEmailOnEdit();

  } catch (error) {
    Logger.log("Error: " + error.message);
    Browser.msgBox("Error updating weekly status: " + error.message);
  }
}

// ------------------------------
// Get video duration in seconds
// ------------------------------
function getVideoDuration(videoId) {
  var url = `https://www.googleapis.com/youtube/v3/videos?part=contentDetails&id=${videoId}&key=${apiKey}`;
  var response = UrlFetchApp.fetch(url);
  var data = JSON.parse(response.getContentText());
  return parseDuration(data.items[0].contentDetails.duration);
}

// ------------------------------
// Parse ISO 8601 duration to seconds
// ------------------------------
function parseDuration(duration) {
  var totalSeconds = 0;
  var regex = /(\d+H)|(\d+M)|(\d+S)/g;
  var matches = duration.match(regex);
  if (matches) {
    matches.forEach(part => {
      var unit = part.slice(-1);
      var value = parseInt(part.slice(0, -1));
      if (unit == 'H') totalSeconds += value * 3600;
      if (unit == 'M') totalSeconds += value * 60;
      if (unit == 'S') totalSeconds += value;
    });
  }
  return totalSeconds;
}

// ------------------------------
// Send email summary
// ------------------------------
function sendEmailOnEdit() {
  var subject = "Your weekly YouTube statistics are ready!";
  var body = "Weekly YouTube stats for '" + sheetName + "' sheet have been updated.<br><br>";
  var spreadsheetUrl = SpreadsheetApp.openById(spreadsheetId).getUrl();

  body += "You can view the updated statistics <a href='" + spreadsheetUrl + "'>here</a>.<br><br>";
  body += "<table style='border-collapse: collapse;'><thead><tr><th colspan='2' style='border: 1px solid black; padding: 8px;'>Weekly Summary</th></tr></thead><tbody>";

  headers.forEach((header, i) => {
    body += "<tr><th style='border: 1px solid black; padding: 5px; background-color: #f0f0f0;'>" + header + "</th>";
    body += "<td style='border: 1px solid black; padding: 5px;'>" + values[i] + "</td></tr>";
  });

  body += "</tbody></table>";
  MailApp.sendEmail(recipients, subject, "", { htmlBody: body });
}