Apps Scripts Code

Copy Code

var spreadsheetId = "1nm1ptz6Rh8NWY8-FNLVlpH_3A3qrzKDiXkyUP-WK2Z4";
var channelId = 'UCg-2EDDuh88KeKSmvQW_pMQ';
var recipients = "[email protected]";
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 today = new Date();
var dayOfWeek = today.getDay();
var daysToSubtract = (dayOfWeek + 6) % 7;
var oneWeekAgo = new Date(today.getTime() - (7 + daysToSubtract) * 24 * 60 * 60 * 1000);
var spreadsheet = SpreadsheetApp.openById(spreadsheetId);
var sheet = spreadsheet.getSheetByName(sheetName);
var subscriberCountLastWeek = 0;
var viewsLastWeek = 0;
var viewsCurrentWeek = 0;
var totalViews = 0;
var mainVideoCount = 0;
var shortVideoCount = 0;
var totalWatchTimeMinutes = 0;
var subscribersGainedThisWeek = 0;
var duration = 0;
var totalSubscriberCount = 0;
var values = [];

function onOpen() {
  createHeader();
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('YouTube Menu')
    .addItem('Create Header', 'createHeader')
    .addItem('Get Weekly Status', 'getWeeklyStatus')
    .addToUi();
}

function createHeader() {
  var active_sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  active_sheet.getRange('A1:I1').setValues([headers]);
  var headerRange = active_sheet.getRange('A1:I1');
  headerRange.setFontSize(10);
  headerRange.setFontWeight('bold');
  headerRange.setHorizontalAlignment('center');
}

function getWeeklyStatus() {
  var lastRowC = sheet.getRange('C:C').getValues().filter(String).length;
  var lastRowD = sheet.getRange('D:D').getValues().filter(String).length;
  var startRow = Math.max(lastRowC, lastRowD) + 1;
  try {
    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());
    for (var i = 0; i < videoData.items.length; i++) {
      var videoId = videoData.items[i].id.videoId;
      var videoDuration = getVideoDuration(videoId, apiKey);
      totalWatchTimeMinutes += videoDuration;
      if (videoDuration >= 60) {
        mainVideoCount++;
      } else {
        shortVideoCount++;
      }
    }

    var endDate = new Date();
    endDate.setDate(endDate.getDate() - endDate.getDay() - 1);
    var startDate = new Date(endDate);
    startDate.setDate(startDate.getDate() - 6);
    var startDateStr = Utilities.formatDate(startDate, Session.getScriptTimeZone(), 'yyyy-MM-dd');
    var endDateStr = Utilities.formatDate(endDate, Session.getScriptTimeZone(), 'yyyy-MM-dd');

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

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

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

    subscribersGainedThisWeek = totalSubscriberCount - subscriberCountLastWeek;
    viewsCurrentWeek = totalViews - viewsLastWeek;
    var totalWatchHours = totalWatchTimeMinutes / 60 / 60;

    values[0] = mainVideoCount;
    values[1] = shortVideoCount;
    values[2] = subscriberCountLastWeek;
    values[3] = totalSubscriberCount;
    values[4] = subscribersGainedThisWeek;
    values[5] = viewsLastWeek;
    values[6] = viewsCurrentWeek;
    values[7] = totalViews;
    values[8] = totalWatchHours;

    var lastRow = sheet.getLastRow();
    var startRow = lastRow + 1;

    sheet.getRange('A' + startRow).setValue(mainVideoCount);
    sheet.getRange('B' + startRow).setValue(shortVideoCount);
    sheet.getRange('C' + startRow).setValue(subscriberCountLastWeek);
    sheet.getRange('D' + startRow).setValue(totalSubscriberCount);
    sheet.getRange('E' + startRow).setValue(subscribersGainedThisWeek);
    sheet.getRange('F' + startRow).setValue(viewsLastWeek);
    sheet.getRange('G' + startRow).setValue(viewsCurrentWeek);
    sheet.getRange('H' + startRow).setValue(totalViews);
    sheet.getRange('I' + startRow).setValue(totalWatchHours);
  } catch (error) {
    Logger.log("Error: " + error.message);
    Browser.msgBox("Error updating weekly status: " + error.message);
  }
  sendEmailOnEdit();
}

function getVideoDuration(videoId, apiKey) {
  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());
  duration = parseDuration(data.items[0].contentDetails.duration);
  return duration;
}

function parseDuration(durationString) {
  var matches = durationString.match(/[0-9]+[HMS]/g);
  matches.forEach(function (part) {
    var unit = part.charAt(part.length - 1);
    var value = parseInt(part.substring(0, part.length - 1));
    if (unit == 'H') {
      duration += value * 3600;
    } else if (unit == 'M') {
      duration += value * 60;
    } else if (unit == 'S') {
      duration += value;
    }
  });
  return duration;
}

function sendEmailOnEdit() {
  var subject = "Hey there, Exciting news, your weekly YouTube statistics are ready!";
  var body = "Your weekly YouTube statistics for '" + sheetName + "' sheet have been updated for the week of " +
    Utilities.formatDate(oneWeekAgo, Session.getScriptTimeZone(), "dd.MM.yyyy") + " - " +
    Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "dd.MM.yyyy") + ".<br><br>";

  var spreadsheetUrl = SpreadsheetApp.openById(spreadsheetId).getUrl();
  body += "You can view the updated statistics <a href='" + spreadsheetUrl + "'>here</a>.<br><br>";

  var columnRows = "<table style='border-collapse: collapse;'><thead><tr><th colspan='2' style='border: 1px solid black; padding: 8px;'>Weekly Summary</th></tr></thead><tbody>";
  for (var i = 0; i < headers.length; i++) {
    var columnName = headers[i];
    var columnValue = values[i];
    columnRows += "<tr>" +
      "<th style='border: 1px solid black; padding: 5px; background-color: #f0f0f0;'>" + columnName + "</th>" +
      "<td style='border: 1px solid black; padding: 5px;'>" + columnValue + "</td>" +
      "</tr>";
  }
  columnRows += "</tbody></table>";
  body += "The following changes were made to the sheet:<br><br>" + columnRows;

  MailApp.sendEmail(recipients, subject, "", { htmlBody: body });
}