Create Your Own YouTube Analytics Dashboard SUPER FAST with Google Apps Script

In my day job as a personal trainer, I find myself doing a lot of repetitive tasks. When writing a training program, there’s SO MUCH clicking to format a Google Sheet well enough to make a professional product.

So I started playing around with macros and found it easy to augment a macro with Google Apps Script.

When recording a macro, the code is saved in the “Script Editor”.

Tools > Script editor will open the script editor.

The Google Apps Script Editor.

And I come to find out that this Script Editor can do a lot for us!

In this post, we’re going to make your own personal YouTube dashboard.

Table of Contents
Create a New Google Sheet
Find a YouTube Video ID
Write the Script
- Line By Line Code Analysis
Add the YouTube Service to the Script
Run the Script
Next Ideas

Create a New Google Sheet

We’ll need a place to store your YouTube data. Create a new sheet and give it three headings in the first columns:

Video ID
Title
Views

The first three cells have the headers “Video ID”, “Title”, and “Views”.

Find a YouTube Video ID

First, we’re going to need a video to analyze.

I went and grabbed one of my top performers: Elbow Pain During Push Ups. Let’s examine the link:

https://www.youtube.com/watch?v=2TPwtBl6_KQ

That value after the ?v= is the ID of the video.

https://www.youtube.com/watch?v=**2TPwtBl6_KQ**

So we’re going to copy that for our Google Sheet and paste it into cell A2.

The video ID has been pasted in cell A2.

Write the Script

The inspiration for this post came from another post by Wesley Chun on the Google Developers blog. It’s a few years old now, but the code mostly works. The user interface has changed a bit, though, so I’m going to walk your through it.

Open up the Script Editor from your Google Sheet: Tools > Script Editor.

And type in the following code:

function getVideoInfo() {
  let sheet = SpreadsheetApp.getActiveSheet();
  let vid = sheet.getRange("A2").getValue();
  let data = YouTube.Videos.list('snippet, statistics', {id: vid});
  let item = data.items[0];
  let info = [item.snippet.title, item.statistics.viewCount];
  sheet.getRange("B2:C2").setValues([info]);
}

Wesley does a great job walking through the code in the post, but I’ll do so here for completeness.

Line By Line Code Analysis

function getVideoInfo() {

Here we’re declaring the function we’re going to run.

let sheet = SpreadsheetApp.getActiveSheet();

This tells the script what sheet we’re working on and is tied to our current sheet

let vid = sheet.getRange("A2").getValue();

This selects the video ID we just pasted in. If we need it, we can use the vid variable.

let data = YouTube.Videos.list('snippet, statistics', {id: vid});

Here we’re using the YouTube Data API to grab info about our video.
We use the YouTube.Videos.list method.
- 'snippet, statistics' tells the API what we want it to give us.
- The snippet property contains the channelId, title, description, tags, and categoryId properties.
- The statistics property contains the viewCount, likeCount, dislikeCount, favoriteCount, and commentCount properties.
And we pass in the video ID from the vid variable we just set.

let item = data.items[0];

We’ve only asked for one video, so our response from the YouTube Data API has only given us one video in our array (at index 0).

let info = [item.snippet.title, item.statistics.viewCount];

Grab the title and view count from the JSON response data.

sheet.getRange("B2:C2").setValues([info]);

Select our B2:C2 range.
Add the two values from the info variable we just set.

Add the YouTube Service to the Script

This is where things start to look a little different from Wesley’s original article. Here’s how we do it in April 2021.

Click + in Services.

The plus button for adding a new service.

Scroll down and select the YouTube Data API.

The Add a Service dialog box. The YouTube Data API v3 contains a link to the documentation link and can be identified with “YouTube”.

Then click “Add” and you should see it in the left menu.

The YouTube service is showing.

Run the Script

Then we can “Run” the script.

The run button is towards the top of the window.

A dialog box will open asking for you to authenticate your Google Account.

You may also get a warning that executing the code from this sheet is not safe. Chrome likes to warn you by hiding the ability to go on and showing a blue “Back to Safety” button. I was able to bypass this by clicking “Advanced” and proceeding anyways.

Once you’ve done that, you might have to click “Run” one more time.

When it runs, the Execution Log should open at the bottom of the window.

Execution log shows execution started and completed.

And if all went well, you’ll see what I’ve pasted above.

Check the Google Sheet…

The video title has been placed in cell B2. The video view count has been placed in C2.

And there’s our data!

Next Ideas

Track your top ten videos and find their views.
Track your content marketing videos to see which perform best.
Test different methods of engagement to see which videos get the most comments, likes, and dislikes.
Send these stats to your Gmail.
Store a new file for every month with the highest performing videos of the month.
Grab data from a playlist you’ve created (perhaps a tutorial series?)
Retrieve the IDs for your most popular videos and get all the data you want about them.
Create visual tables and graphs inside your Google Sheet to better visualize your data.
Share your results with a friend to hold yourself accountable.

Any other ideas? How are you using Google Apps Script? Share in a comment below!

Table of Contents