Creating a Television Show Tracker With Google Sheets

Creating a Television Show Tracker With Google Sheets

I’ve been doing a lot of spreadsheets lately and thought it might be appropriate to create a tracker with a little more detail than the text file I was listing shows I watched in. I decided to list each show I’ve watched recently along with checkboxes to indicate which shows I’ve started watching, finished watching, and which have been canceled. The finished result is above – I’ll go through each column in a little more detail and share some of the technical aspects of the spreadsheet.

The title column is straightforward enough; just a plain text column with the title of each show (there are currently 20 on the list). I added alternating highlighting to most of the columns to improve readability and make the sheet more visually appealing.

The next three columns correspond to the show title; “Started” indicates which shows I began watching, “Finished” indicates which shows I have finished watching, and “Ended” shows which series have concluded or been canceled.

Below each of these three columns is a percentage measure that displays the number of boxes that are checked in proportion to the total number of checkboxes. The formula for this (Column B) is:

=COUNTIF(B2:B24, TRUE)/(COUNTIF(B2:B24, TRUE)+COUNTIF(B2:B24, FALSE))

This simply counts the number of “TRUE” cells (checked boxes) and divides it by the total number of “TRUE” and “FALSE” cells (all boxes).

Column E uses a trick I’ve started including in most of my spreadsheets – combining the above formula (adapted for a row instead of a column) with a horizontal sparkline, a small graph contained within one cell of a spreadsheet. This can be used to create progress bars that automatically update as checkboxes are checked.

=SPARKLINE(COUNTIF(A2:D2, TRUE)/(COUNTIF(A2:D2, TRUE)+COUNTIF(A2:D2, FALSE)), {"charttype","bar";"color1","lightgreen";"max",1})

Together, the four columns look like this:


The rest of the columns in the spreadsheet are even more fun. I wanted to automatically pull some data about each series to get a better picture of what I have watched without having to manually look up hundreds of statistics. I settled on the API from The Open Movie Database (OMDb), which provides detailed information about thousands of movies and television shows.

For example, the query http://www.omdbapi.com/?t=Ozark will return a JSON object about the series with info such as the years it ran (“2017-“), the runtime of each episode (“60 min”), and the show’s IMDB rating (“8.4”).

Unfortunately, Google Sheets doesn’t natively provide a way to import JSON data from RESTful APIs or other sources. Fortunately, other developers have created scripts to do this with minimal suffering on the user’s end. The best one I found is ImportJSON by Brad Jasper. It only takes a couple of minutes to import into a Google Sheets project by opening the script editor and copy-pasting the code. Several JSON-importing functions are then immediately available.

To use the script in this spreadsheet, I used CONCATENATE to build an API query using the title of the show and retrieve specific pieces of information about each one. Cell G2 contains the formula1:

=ImportJSON(CONCATENATE("http://www.omdbapi.com/?t=",SUBSTITUTE($A2, " ", "+"),"&apikey="),"/Year","noHeaders")

$A2 refers to the cell containing the title of the show (“$” locks the column so when we duplicate the formula later it continues to use column A). We then replace the spaces with plus signs and combine it into one string to use as a query – something like http://www.omdbapi.com/?t=Mr.+Robot&apikey=.... This is passed to the ImportJSON function, which runs the query. “/Year” tells the function to only retrieve the Year property from the returned object. “noHeaders” gets rid of the headers that come with the data. The result is a single cell reading “2015-2019”.

The formula is copied down column G for all the titles. Then, I did the same for the average runtime, rating, IMDB rating, and the number of seasons. For the last two columns, I used the VALUE function to convert the text result to a number. Averages are also computed for IMDB rating and seasons.


There was also a Genre column, but it interfered with the spacing of the rows so I have it hidden. Based on the data pulled from the API, we can create charts to summarize the different properties of the shows. This donut2 chart3 displays the percentage of shows with different maturity ratings.

Alternatively, here’s a histogram of IMDB ratings:

And the number of seasons (NCIS is the outlier):

I hope you enjoyed this unnecessary but entertaining project – I have plenty more that I’ll be writing posts about soon. I hope everyone is staying safe and healthy during the pandemic.

  1. I’ve redacted the API key; it’s easy enough to get one for yourself from
    http://www.omdbapi.com/apikey.aspx

  2. https://xkcd.com/2031/
  3. Apparently these are very polarizing. I think they are the only acceptable way to display pie charts.