DEV Community

loading...

Using Sheets and the YouTube API to track video analytics

rick_viscomi profile image Rick Viscomi Updated on ・4 min read

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.

the finished spreadsheet

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:

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

enable the YT API

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.

setting up triggers

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

YT menu

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!

Discussion (34)

pic
Editor guide
Collapse
sorinamzu profile image
Sorin Amzu

Rick, this works great.
Problem is: I'm toying around with the idea of having multiple sheets. I created a new one, renamed it something else, replaced that name in your YouTube Analytics scripts, but it doesn't work on this new sheet.
It seems to copy existing values from the original Sheet1.

Any way around this?

Thanks a million!

Collapse
rick_viscomi profile image
Rick Viscomi Author

Hard to tell without looking at the whole spreadsheet. Are you able to share it with me?

Collapse
sorinamzu profile image
Sorin Amzu

Here is the sheet: docs.google.com/spreadsheets/d/1kj...

Here is the modified script, trying to get data for the first sheet (Stats For Video): script.google.com/d/1fkQ6lOM3fNnH7...

Cheers!

Thread Thread
rick_viscomi profile image
Rick Viscomi Author

Ah, the issue is that the "IDs" named range is pointing to the old 'Video Stats' sheet.

Thread Thread
sorinamzu profile image
Sorin Amzu

Now I don't know what to think any more :(
I've renamed the sheet back to the original name. I've copy-pasted your script again.
docs.google.com/spreadsheets/d/1kj...
I get this error: TypeError: Cannot call method "getValues" of null. at getVideoIds(Code:35) at updateStats(Code:26)

What am I doing wrong?

Thread Thread
rick_viscomi profile image
Rick Viscomi Author • Edited

Go to "Data > Named ranges..." to define a range of cells. There should be one named "IDs" spanning B2:B1000. This corresponds to the VIDEO_ID_RANGE_NAME variable in the script.

Thread Thread
sorinamzu profile image
Sorin Amzu

Yup. Works like a charm now.
Can this be modified to track several sheets? Or would I need to create a script for each sheet?

Thanks again!

Thread Thread
rick_viscomi profile image
Rick Viscomi Author

The script needs to know which video IDs to look up, so if you want to spread the IDs over multiple sheets, you can create multiple named ranges and iterate through each one. No need for multiple scripts.

Collapse
ruesseltier profile image
René Kurfürst

Thanks for the very detailed and nice structured post. I have done it that way and it worked fine, until I came to the 51st row of my table. Is there a limitation by 50? I tried to find a break point in the code but didn't found one.

Collapse
rick_viscomi profile image
Rick Viscomi Author

Two things come to mind:

  1. The named range may be ending on B50 rather than B1000.
  2. The YouTube API may have limitations on the number of video IDs in a single request. I can't find the definitive answer, but the docs have more info on quota: developers.google.com/youtube/v3/g...
Collapse
joeallam profile image
Joe Allam

Hey Rick, thanks for a fantastic article — I've got mine all set up looking beautiful! I've also run into the 50 API calls limit and upon research found that it definitely is the ID call with a specific limit, which can't be changed.

To get around this, I've decided to create different sheets for each year's video uploads. However, it seems overkill to duplicate the script for each sheet. I noticed in another reply you mentioned iterating through multiple named ranges. How would I go about referencing either multiple named ranges, or triggering the script multiple times (in the event all named ranges combined are over 50)?

Thanks!

Collapse
pranay profile image
pranay

Hi Rick,

thank you for this article. However, I'm experiencing a problem with the code and I'm utterly new to coding. When I'm trying to run the code, it is not happening and an error message pops up TypeError: Cannot call method "getValues" of null.

Please tell me where I could have gone wrong and also how to rectify it. Thanks a ton.

Pranay

Collapse
aakankshadp profile image
aakanksha-dp

I am facing the same issue. Did you fix this? If yes, please help me on how to fix it.

Collapse
f97c621c553743a profile image
Shiva Kumar

Hey I am trying to track my youtube usage across the computer and phone. Given this approach is awesome and works well, it is very cumbersome to manually input the video titles and the Vid IDs. I do have the YouTube history enabled, I want only a suggestion from you about the approach I can take to be able to log the Videos I have watched and also on which dates based on my history.

Thank you

Collapse
nosim12 profile image
norman simon

Hi Rick,
First of all thank you very much for this post.

I have an issue with the code, it looks like when a video ID is wrong in the list (for example if the video has been deleted from youtube), the script assigns to this wrong ID the data of the following ID.
I think that the function GetVideoIds builds the list skipping this kind of wrong ID. This results in a list of Ids smaller than my column of Ids.
Then, when we get data with others functions, the data is pasted in front of the wrong video.
I would like to add a condition "If the ID doesn't correpond to a valid video --> Do not just skip it but addd a message or a 0"
Unfortunately, I am not good enough in this language to do so.
Could you please assist me with this ?

Thank you very much !

Norman

Collapse
dickeytyler profile image
Tyler Dickey

Hey Rick! I'm having some issues setting up the triggers for this sheet. Once I set the trigger up as shown in your article I get caught in this endless loop of having to log into my google account and give the script permission to get data from YouTube. Any tips?

Collapse
ms86uk profile image
Marco Scarpa • Edited

Hi Rick, I'm getting this error:

TypeError: Cannot call method "setActiveCell" of null.

This is all the script. I've just changed the column names. Name range "IDs" start from row 7 to 200 and on N7 I've my first YT ID...

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

// 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: 'V',
LIKES: 'Y',
DISLIKES: 'Z',
COMMENTS: 'X',
DURATION: 'O'
};

// 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);
}
}

Collapse
rick_viscomi profile image
Rick Viscomi Author

The error is saying that getSheetByName("YT Stats 3.2") is coming up empty. Did you rename it without updating the SHEET_NAME variable?

Collapse
chan_kuris profile image
KurisC

docs.google.com/spreadsheets/d/1ui...

If you check through the videos yourself, you will notice that at "Our Summer" and below, the views are aligned with the wrong video ID, but I am not sure what is causing this?

Thank You :D

Collapse
olekstomek profile image
olekstomek

Hi, thanks for showing us the use of Google Apps Script. ;)
I use your code and spreadsheet and adapted it in my solution which allows you to check the statistics of videos in the given YouTube playlist. This is the link to the repository: github.com/olekstomek/gscript-trac...

Collapse
dakotageek profile image
DakotaGeek

Hi Rick, I'm following your instructions and although everything appears correct, I'm getting the following error when running the script.
"API call to youtube.videos.list failed with error: No filter selected. Expected one of: chart, id, myRated, idParam (line 49, file "Code")"

Line 49 in the script is an exact copy-paste from your article as follows:
"return YouTube.Videos.list('contentDetails,statistics', {'id': videoIds}).items;"

Any thoughts on why this error would occur?

Collapse
dakotageek profile image
DakotaGeek

Some of the other posts here answered my question. This is an example of hitting the limit of 50 requests to the API.

Since the Named Range is 999 entries long, perhaps Rick could update his script to iterate through 999 entries in chunks of 50?

Collapse
retrojammed profile image
Jeremy Jamm

Hello! Everything in this guide worked perfectly. I do have a question, however. Is it possible to implement lines of code to automatically extract the last X amount of videos from the channel? Would be very helpful. Thanks!

Collapse
jonesadamd profile image
Adam J.

Hi Rick,
Great article and example of tracking YouTube video's with the API, but any chance you can update it to include pagetokens for more than 50 lines.

Or can we create code to split the getVideoIds() and writeStats(stats) for a maximum of 50 then continue down the range?

Thank you

Collapse
chan_kuris profile image
KurisC

Hello Rick, when hitting 50 videos, the script no longer works, is there any way around this?

Collapse
pixelgroove profile image
Udi Sabach

Hi. I had this working, and now getting an error "API call to youtube.videos.list failed with error: The request specifies an invalid filter parameter. (line 49, file "Code")".

Any how to fix this?

Collapse
pixelgroove profile image
Udi Sabach

I noticed the issue is that i was hitting the 50 video limit. Once i reduced the list, it worked.

Collapse
max_alexander_ profile image
Max🔥Alexander

Awesome awesome awesome! Thank you! How do I capture A 24 hour snapshot and a 7 day snapshot of a video?

Collapse
ms86uk profile image
Marco Scarpa

Rick, since yesterday I'm receiving this error:

  1. That’s an error.

Error: disabled_client

The OAuth client was disabled.

Collapse
wrldwzrd89 profile image
Eric Ahnell

This looks real interesting - will give this a try and see how it goes!

Collapse
_lobeira_today profile image
A Lobeira today

Hi, my name is Fran. Is it possible to use this way to track channel subs instead video statistics? Thanks in advance.

Collapse
bhagiradh1993 profile image
bhagiradh akuthota

I'm facing this particular issue. Can anyone please help me

*GoogleJsonResponseException: API call to youtube.videos.list failed with error: The request specifies an invalid filter parameter.
*

Collapse
aakankshadp profile image
aakanksha-dp

Hi Rick,

I am getting this error.
TypeError: Cannot read property 'getValues' of null (line 35, file "Youtube Analytics")

Please help me understand how to fix this.

Collapse
userpo633 profile image
UserPo633

Rick. I got a lot of help with your post. However, I want to collect the Subscriber with the channel ID. I tried changing your CODE but it didn't work well. Is there any way to collect the Subscriber with the channel ID?