In my new-ish role as a Developer Advocate, I’ve had the opportunity to build some pretty cool things and share them with audiences at conferences and meetups. Many of these talks are recorded and added to YouTube where anyone can watch them. Occasionally, viewers of these videos ask questions in the YouTube comments section, which (YouTube comment reputation aside) I find truly exciting. In fact, I try my hardest to answer each and every one!

Until recently, however, I have had no way to be notified when a viewer comments on my videos. Why? Because the built-in notification options for YouTube are designed for a user’s own channels, videos, and comments. Whereas, the videos I am interested in are distributed through several channels, Google and non-Google, none of which I personally own.

Because of this, I decided to create my own solution: a spreadsheet that tracks basic data (number of views, likes, comments) for YouTube videos I care about, no matter the originating channel, and emails me a notification when new comments are added to the videos. To automate this tracker, I relied on trusty Apps Script, and it was actually fairly straightforward to build. I used three main Apps Script services:

The YouTube advanced service to collect data on a specific video (such as number of views and comment data) via the YouTube Data API. The Spreadsheet service to read YouTube URLs in my tracker spreadsheet and update data about the videos retrieved from the YouTube Data API. The Gmail service to send notification emails when there were new comments or replies on the videos.

My current YouTube tracker. Everything in gray is automatically updated with the data from the YouTube Data API.

If you want to head directly to the solution, you can find a template and instructions here. The rest of the post covers how it works and walks through how to set it up, broken down into these four milestones:

Creating a video tracker spreadsheet Enabling the YouTube Advanced Service Reviewing the Apps Script code Setting up a trigger

Creating a video tracker spreadsheet

I originally created this tracker to be used only by me, and you’ll see I chose to truly “keep it simple”. The only input on my spreadsheet is in column A, where I provide URLs to videos I would like to track. I also name the tab with my email address, which is ultimately used for the email notifications. That’s it!

Action time: Create your own spreadsheet tracker.

Make of copy of the spreadsheet here. Change the name of the tab to the full email address where you’d like to receive notifications. Locate URLs of videos you would like to track and add them in column A below cell A1.

Of course the sheet has no supporting data yet, but don’t worry, it’s coming :)

Enabling the YouTube Advanced Service

The data I care about for my videos (number of views and comments) is available using the YouTube Data API. I normally use UrlFetch to access external APIs from Apps Script. This method can be challenging to use because I need to configure authentication and build out my API requests and headers. Luckily, a number of Google APIs, including the YouTube Data API, are available as “advanced services” in Apps Script. Using the YouTube Advanced Service massively simplifies my solution by allowing me to use methods like YouTube.Videos.list directly in Apps Script.

To see how easy it is, check out the code for my function that collects details from the YouTube Data API for a particular video:

function getVideoDetails(videoId) {

var part = “snippet, statistics”;

var response = YouTube.Videos.list(part,

{‘id’: videoId});

return response;

}

Before I can use an advanced service, I need to activate it in my Apps Script project. If the service is not enabled and I try to run the code, I will get an error that says ReferenceError: “YouTube” is not defined .

Action time: Activate the YouTube Advanced Service in your spreadsheet by following these instructions:

From the spreadsheet, open the script editor by selecting Tools > Script editor. In the script editor, select Resources > Advanced Google services. In the Advanced Google Service dialog that appears, click the on/off switch next to the YouTube Data API service. Click OK.

Reviewing the Apps Script code

In the previous section, I showed you a key piece of the Apps Script code that accesses the YouTube Data API, but let’s look a little closer at the rest of the code. I have two code files, code.gs and email.html .

Starting with code.gs , I have four functions:

extractVideoIdFromUrl : This takes a given video URL and extracts the video ID from the URL. It works when the URL follows the https://www.youtube.com/watch?v=K0EsaKQ1iLY format. This function is important because the YouTube Data API requires the video ID in its incoming requests. getVideoDetails : This takes a given video ID and requests the details about the video from the YouTube Data API. It requests two resources from the API: snippet provides data needed for columns C-E and statistics provides data needed for columns F-H. sendEmailNotificationTemplate : This takes a given email address and array of video data, assembles an email notification using a template ( email.html ), and sends the email notification to the given email address. markVideos : This is my primary function which makes use of the three above functions. It loops through each video to extract the video ID ( extractVideoIdFromUrl ), requests data for each video from YouTube ( getVideoDetails ), updates data in columns C through H, builds an array that includes any videos that have new comments, and sends an email notification summarizing all videos with new comments ( sendEmailNotificationTemplate ). The function loops through each tab in the spreadsheet to perform these tasks across all tabs.

My second code file, email.html , is a template for my email notification. Previous to building this solution, I had zero experience with writing HTML, but luckily there is an HTML sample that shows how to use Apps Script within the HTML template. Using Apps Script here is necessary for turning the array of videos into a table included in the email notification.

The Apps Script in the HTML template turns the array of email content into an HTML table.

My logic for sending an email notification is simple: did the number of comments on a video increase since the last time I ran the code? If yes, then add the video to my email notification. I chose not to include the new comments directly in my email notification. This is certainly something you could change the code to do, but is far more complex (you will need to account for moderation of comments, pagination of the API response, etc). Inspired by the philosophy of lagom, I made the solution just as complex as needed to fit my needs.

One final note on the code…

Since I’ve embedded the email notifications action into my markVideos function, I created a flag in line 2 of code.gs where, by changing the flag from ’Y’ to ’N’ , I can turn off email notifications while continuing to allow the function to update the video details in the spreadsheet.

Action time: Now that we’ve taken a look at the code, let’s try running it manually.

From the script editor, choose markVideos from the select box in the toolbar, then click ▶. You should see the details added in columns C through H, and you will receive an email for any videos that have more than zero comments. When running the function in the future, you will only receive an email with videos that currently have more comments than the last time the script was run.

An example email notification.

Setting up a trigger

Finally, it’s time to decide how often I want to update the data and check for new comments. Using a time-driven trigger in Apps Script, I can trigger the script to run as frequently as every minute to as little as once a month, without needing to run it manually. So far, once a day has been working quite well for me. While I could use code to set up triggers, this case is simple enough to create and manage the trigger manually.

Action time: Set up a daily trigger:

From the script editor, choose Edit > Current project’s triggers. Click the link that says: “No triggers set up. Click here to add one now.” Under Run, select the markVideos function. Under Select event source, choose Time-driven. Under type, select Day timer. Then select time of day, such as 2am to 3am. Optionally, click Notifications to configure how and when you are contacted by email if your triggered function fails. Click Save.

When the script runs each day, it updates all of the video details and sends one email notification (to the email address used as the tab name) summarizing videos that have new comments.

Next steps

Feel free to take this tracker a step (or steps) further. You can easily do additional analysis on the data and create charts directly in Sheets or Data Studio. You can also duplicate the first tab to create multiple tabs for several team members and aggregate statistics up to the team level. A template and all the code is available to help you get started!