AppSScript Code
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 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(); // Get the active sheet
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' // We are not retrieving watch time directly here
});
var rows = results.rows;
Logger.log( 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; // Dividing by 60 twice to convert minutes to hours
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;
Logger.log(values);
var lastRow = sheet.getLastRow();
var startRow = lastRow + 1;
Logger.log(startRow);
try {
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);
Logger.log("Weekly status updated successfully."+mainVideoCount);
} catch (error) {
Logger.log("Error: " + error.message);
Browser.msgBox("Error updating weekly status: " + error.message);
}
Logger.log("Weekly status updated successfully.");
} 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") + ".
";
// Get the URL of the spreadsheet
var spreadsheetUrl = SpreadsheetApp.openById(spreadsheetId).getUrl();
body += "You can view the updated statistics <a href='" + spreadsheetUrl + "'>Sheet link here</a>.<br><br>";
";
var user = Session.getActiveUser().getEmail();
var header = "
"; var columnRows=''; for (var i = 0; i < headers.length; i++) { var columnName = headers[i]; var columnValue = values[i]; columnRows += ""; } var footer = "
<table style="border-collapse: collapse;">
<tbody>
<tr>
<th style="border: 1px solid black; padding: 5px;">" + columnName + "</th>
<td style="border: 1px solid black; padding: 5px;">" + columnValue + "</td>
</tr>
</tbody>
</table>
"; body += "The following changes were made to the sheet:
" + header + columnRows + footer; MailApp.sendEmail(recipients, subject, "", { htmlBody: body }); }