Email reminder for new TV Series episodes using Google Apps script

Published: by

  • Categories:

Since a long time, I wanted to find a service that mails you to remind about the new episode to be aired. As I couldn't find one, I sat and wrote one for myself using Google Apps script.

Why I chose Google Apps script?

  • I could use a RSS feed but it also contains info about the episodes I am not intereseted in. To use the RSS and filter the episodes I wanted and get them mailed to me is also a choice but for that I would need a computer on which the script will be running. So, it won't work if my desktop/laptop is switched off.
  • I don't own a VPS so I cannot run the script on that too. This script runs on google's servers.
  • I wanted to get started with javascript. (So, my code may look crappy to an experienced javascript programmer)

Basically, I use macros in Google Docs spreadsheet to do the stuff.

Using the macros, I read the name of the series from the cells (column 1) & then use tvrage.com to scrap the data from. Other cells are also updated accordingly.

Video of script in action (Sorry for thu blurry video, youtube is the culprit. Watch at 480p):-

Requirements:-

Name of the series should be according to the link of tvrage. For ex, for Breaking Bad, it should be Breaking_Bad.

A typical spreadsheet looks like this:

Setting Up:-

  • Goto this link and do File->Make a copy.
  • Before running the script first time, make sure that column D is empty because values from that column are used to see if the email should be sent or not.
  • First time you run it, you will need to authorize the script. It's a one time only thing.

  • Goto GDocs spreadsheet menu and select Tools->Script Editor. Then, Triggers->Current Script Triggers.
  • Add a trigger as shown in the image. It just means that run the main function every 6 hours. (main function actually checks from the site and send the email).

  • Now, goto Run->main to execute the main function. As it's the first time, Column D should not anything Row 2 onwards. I have configured the script to send an email around 24 hours prior to starting of episode. You can play with the time_limit variable & Triggers to suit your needs.
  • After the main function is executed, Column D should now be filled with EMAIL_SENT or EMAIL_NOT_SENT. All the series for which the time remeaning is less than time_limit, an email will also be sent to your email id.
  • If a script is run again, email will not be sent again because now the cell has EMAIL_SENT written.

If you want to have a quick look at the script:-

var emailAddress = Session.getUser().getEmail();
var time_limit = 24 //In hours
var ep_no = Array() //I know it's dirty but be it.
var time_rem = Array()
var sheet = SpreadsheetApp.getActiveSheet();

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [ {name: "Calculate", functionName: "main"}];
  ss.addMenu("Manual", menuEntries);
}


function getEpisodeList(){
  var sheet = SpreadsheetApp.getActiveSheet();  
  var all_episodes = new Array();
  for (var i=2; i<=sheet.getLastRow(); i++) //This loop gets all the labels
  {
	var cell = sheet.getRange("A"+i);
	all_episodes = all_episodes.concat(cell.getValue());
  }
  return all_episodes;
}

function main() {
  var all_episodes = getEpisodeList();
  sheet.getRange("B2:C"+sheet.getLastRow()).setValue("Calculating...");

  SpreadsheetApp.flush();
  for (var i=2; i<=all_episodes.length+1; i++){
	 time_rem = getTimeRemaining(all_episodes[i-2]);
	 sheet.getRange(i,2).setValue(time_rem);
	 sheet.getRange(i,3).setValue(ep_no);

	if (isSendNoti(time_rem,i) == true){
	  if (sheet.getRange(i,4).getValue() != "EMAIL_SENT"){
		 sendEmail(all_episodes[i-2], ep_no, time_rem); 
		 sheet.getRange(i,4).setValue("EMAIL_SENT");
	  }      
	}
	else sheet.getRange(i,4).setValue("EMAIL_NOT_SENT");
	SpreadsheetApp.flush();
  }

}

function getTimeRemaining(series_name){
  var response = UrlFetchApp.fetch("http://www.tvrage.com/"+series_name);
  var content = response.getContentText();
  var pos = content.search(series_name+"/episodes");
  var link_end_pos = pos+series_name.length+20;
  link = content.substring(pos,link_end_pos);
  ep_no = content.substring(link_end_pos+2,link_end_pos+6);
  var response = UrlFetchApp.fetch("http://www.tvrage.com/"+link);
  var content = response.getContentText();
  var start = content.search("Voting Closed") + 67;
  if (start == 66) return "Season recently finished";
  var end = content.indexOf("<",start);
  return content.substring(start,end);
}

function isSendNoti(time_left,i){

  var days = time_left.match(/^(\d+) Days/i);    
  var hours = time_left.match(/^(\d+) Hours/i);
  var mins = time_left.match(/(\d+) Min.$/i);

  if (days != null) {
  //Means reset the email thing
  sheet.getRange(i,4).setValue("EMAIL_NOT_SENT");
  return false; //No notification if no_of_days included in time remaining
  }
  if ((hours == null) && (mins == null)) return false;

  if (parseInt(hours[1]) <= time_limit) return true; 
  else return false;
  }


function sendEmail(series_name, ep_no){
  Logger.log(series_name, ep_no);
  MailApp.sendEmail(emailAddress, "EPISODE ALERT: " +series_name+ " : "+ep_no, "Episode to be aired in : "+time_rem);
}