Daily YouTube Trailer Views… With A Little Help From Reddit

A quick and dirty hack that pastes any new Youtube links into one sheet using a Google Sheet script follows:function getredditdata(userData) {  var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheets = ss.getSheets(); SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Reddit-Youtube-Links”).activate(); //need to activate the sheet where we are writing too.  var lmt= 50 //limit results var urlz = ‘https://www.reddit.com/r/movies/search.json?sort=top&q=flair%3ATrailers&t=week&limit=' + lmt //reddit json api — uses a search on Flair = Trailers for the week var currentTime = new Date();  var response = UrlFetchApp.fetch(urlz); // get feed var json = response.getContentText(); // generic var data = JSON.parse(json); //uses JSON specific parse method   try {for (var i = 0; i < lmt ; i++ ) { var lastRow = ss.getLastRow(); var exist = ss.getRange(“B2:” + “B” + lastRow).getValues(); var stats = []; var test = true;  stats.push(data.data.children[i].data.url); //video url stats.push(data.data.children[i].data.title); //video title stats.push(currentTime);for (var j = 0; j < exist.length ; j++ ) { Logger.log(stats[1] === exist[j][0]) if(stats[1] === exist[j][0]) {test = false }}  if ( (stats[0].toLowerCase().indexOf(“yout”) >=0) && (stats[1].toLowerCase().indexOf(“official”) >=0) && (test)) {SpreadsheetApp.getActiveSheet().appendRow(stats)} //adds this to the last row of the sheet that is active and is a Youtube link} } catch(error) {} // do nothing}Now that we have a growing list of Youtube links (the 11-digit URL code) we can use Youtube’s API to get the number of views each of those vidoes has had and paste the results into a Stats sheet for easy analysis:function getYTdata(userData) {  //adapted from this blog post — https://bionicteaching.com/youtube-api-to-google-spreadsheets/ var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheets = ss.getSheets(); var sheet = ss.getSheetByName(“Data”); //or whatever you name your sheet var apiKey = ‘Lol in your dreams’; //leave the single quotes — This is my YouTube API key, this needs to be set up in developer console var urls = sheet.getRange(“A2:A”).getValues(); // get range of YouTube video URLs, this will be looped throughfor (var i = 0; i < urls.length ; i++ ) { SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Stats”).activate(); //need to activate the sheet where we are writing too.  var vidId = urls[i];  var url = ‘https://www.googleapis.com/youtube/v3/videos?id=' + vidId + ‘&key=’ + apiKey + ‘&part=snippet,contentDetails,statistics,status’; //  var currentTime = new Date();  var response = UrlFetchApp.fetch(url); // get feed var json = response.getContentText(); //  var data = JSON.parse(json); //uses JSON specific parse method Logger.log(data.items[0]) if (data.items[0] != undefined){ var stats = [];  stats.push(data.items[0].snippet.title); //video title stats.push(data.items[0].statistics.viewCount); //view count stats.push(data.items[0].statistics.likeCount); //like count stats.push(data.items[0].statistics.dislikeCount); //dislike count stats.push(data.items[0].statistics.commentCount); //comment count stats.push(data.items[0].contentDetails.duration); //duration but in a weird format PT6M44S is 6 min 44 secs stats.push(data.items[0].snippet.publishedAt); //publish date stats.push(data.items[0].snippet.channelTitle); //title of the channel or user? stats.push(vidId[0]); //vidId inherits the list characteristics so to call it we have to use list method — note: lists start at 0 in this language stats.push(currentTime);    Logger.log(data.items[0]) SpreadsheetApp.getActiveSheet().appendRow(stats) //adds this to the last row of the sheet that is active } }}The best part of using Google Sheets is that you can easily setup a trigger for this code to run every day automatically..Now I have a daily update that automatically updates a growing list of films and everyday it tells me how many times each video has been viewed:Everything the light touches…. More details

Leave a Reply