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 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):-


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();

  for (var i=2; i<=all_episodes.length+1; i++){
	 time_rem = getTimeRemaining(all_episodes[i-2]);

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


function getTimeRemaining(series_name){
  var response = UrlFetchApp.fetch(""+series_name);
  var content = response.getContentText();
  var pos ="/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(""+link);
  var content = response.getContentText();
  var start ="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
  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);