I host a biweekly video series on YouTube called The State of the Web and being the data nerd that I am, I wanted a way to monitor the public stats about each video to see how they're performing: number of views, likes, dislikes, comments, etc. In this post I walk you through how to set up your own dashboard to monitor the videos you care about.

You can see my finished product in this sheet. If you'd like, you can just make a copy of that sheet and plug in your own videos, but keep in mind that you may still need to follow some of these steps to get things like the YouTube API enabled.

Getting started

You'll need a blank Google Sheet to get started. Rename the default sheet to "Video Stats" so there's an identifiable name for it in the script.

Row 1 will be the headings for the table of stats:

Video Title
Video ID
Views
Likes
Dislikes
Comments
Duration

The only input needed by you is the title and ID for each video in columns A and B. Optionally, you can add the following columns for more stats:

Likes per View
Dislikes per View
Percent Likes
Percent Dislikes
Comments per View

These are calculated locally from the API results using simple Sheets formulas. For example, calculating the percent of likes uses this formula: =IFERROR(D2/(D2+E2), ""). One trick to apply the same formula to all cells in the column is to select the cell with the formula already applied, press Cmd+Shift+Down to select all following cells in the column, and press Cmd+D to apply the formula. The IFERROR function prevents division by zero errors for empty rows.

You can also format the columns to prettify their respective values. For example, the duration looks best in MM:SS format, while the calculated fields look best as percents.

Next, create a "Named Range" for the Video ID values by going to Data > Named Ranges... and adding a new range called "IDs" for range 'Video Stats'!B2:B1000. This allows the script to more easily scan the list of video IDs.

The brains of the analytics

To make the dashboard work, we need to talk to YouTube and get stats about each video. That's possible with a bit of Apps Script, which you can write by going to Tools > Script Editor.

In the newly opened editor, name your project "YouTube Analytics" and paste this script:

// This is "Sheet1" by default. Keep it in sync after any renames.
var SHEET_NAME = 'Video Stats';

// This is the named range containing all video IDs.
var VIDEO_ID_RANGE_NAME = 'IDs';

// Update these values after adding/removing columns.
var Column = {
  VIEWS: 'C',
  LIKES: 'D',
  DISLIKES: 'E',
  COMMENTS: 'F',
  DURATION: 'G'
};

// Adds a "YouTube" context menu to manually update stats.
function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var entries = [{name: "Update Stats", functionName: "updateStats"}];

  spreadsheet.addMenu("YouTube", entries);
};

function updateStats() {
  var spreadsheet = SpreadsheetApp.getActive();
  var videoIds = getVideoIds();
  var stats = getStats(videoIds.join(','));
  writeStats(stats);
}

// Gets all video IDs from the range and ignores empty values.
function getVideoIds() {
  var spreadsheet = SpreadsheetApp.getActive();
  var range = spreadsheet.getRangeByName(VIDEO_ID_RANGE_NAME);
  var values = range.getValues();
  var videoIds = [];
  for (var i = 0; i < values.length; i++) {
    var value = values[i][0];
    if (!value) {
      return videoIds;
    }
    videoIds.push(value);
  }
  return videoIds;
}

// Queries the YouTube API to get stats for all videos.
function getStats(videoIds) {
  return YouTube.Videos.list('contentDetails,statistics', {'id': videoIds}).items;
}

// Converts the API results to cells in the sheet.
function writeStats(stats) {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getSheetByName(SHEET_NAME);
  var durationPattern = new RegExp(/PT((\d+)M)?(\d+)S/);
  for (var i = 0; i < stats.length; i++) {
    var cell = sheet.setActiveCell(Column.VIEWS + (2+i));
    cell.setValue(stats[i].statistics.viewCount);
    cell = sheet.setActiveCell(Column.LIKES + (2+i));
    cell.setValue(stats[i].statistics.likeCount);
    cell = sheet.setActiveCell(Column.DISLIKES + (2+i));
    cell.setValue(stats[i].statistics.dislikeCount);
    cell = sheet.setActiveCell(Column.COMMENTS + (2+i));
    cell.setValue(stats[i].statistics.commentCount);
    cell = sheet.setActiveCell(Column.DURATION + (2+i));
    var duration = stats[i].contentDetails.duration;
    var result = durationPattern.exec(duration);
    var min = result && result[2] || '00';
    var sec = result && result[3] || '00';
    cell.setValue('00:' + min + ':' + sec);
  }
}

Refer to the comments in the code for a description of what each function does.

There are a couple more things left to do in the script editor to make everything work:

Enable the API
Set up triggers so that the stats are updated automatically

To enable the API, go to Resources > Advanced Google Services... and scroll down to and enable the YouTube Data API. You must also click the "Google Cloud Platform API Dashboard" link and search for "YouTube Data API v3" to enable it for your project.

To set up triggers, go to Edit > Current project's triggers and configure it as seen in the screenshot above. It does two things:

updates the stats every 15 minutes, so you can leave the sheet open and get the latest data
updates the stats as soon as you open the sheet

There is also a "YouTube" menu added to the sheet if you want to manually trigger an update.

Wrapping up

That's all there is to it. My sheet also has some examples of secondary sheets that summarize the stats with other tables and charts, but you can customize your dashboard any way you like.

If you own the YouTube channel on which the videos are uploaded, you may also have access to private stats like watchtime and subscription data. But the great thing about this script is that you can monitor any YouTube videos regardless of ownership — it's all public info!

Using Sheets and the YouTube API to track video analytics

Getting started

The brains of the analytics

Wrapping up