Getting the details of uploads to any YouTube channel into a Google Sheet

As of yesterday TED have uploaded 1,903 videos totaling 1,622,120 seconds of playtime which have been viewed 428,117,012 times and received 4,360,903 likes.

If you’d like to play with the data you can find it in this YouTube Channel Summary – TEDtalksDirector Google Sheet … and if you would like similar data for your or someone else’s channel make a copy of this YouTube Channel Summary Google Sheet and follow the setup instructions.

Setup

To get this working there are a couple of hoops to jump through. As the YouTube API is an Advanced Service and it must be enabled before use. For this project to do this you need to be in the Script Editor then:

  1. Open Tools > Script editor and then click Resources > Advanced Google Services…
  2. Scroll down to YouTube Data API to turn it on then click the ‘Google Developers Console link:
    Enabling Advanced Services
  3. In the Google Developers Console find and turn on the YouTube Data API. After it’s enabled you can close the Console window
  4. Finally, assuming you’ve got the desired username set in cell B1 Run > writeYTChannelSummaryToSheet.

Note: if the channel has a lot of videos the script will automatically start running again after five seconds until it get everything.

How it was made

I made this template following a request from Brian Bennett:

.. so let look in more detail at how you access YouTube data in Google Apps Script. I’ve already highlighted the need to activate the YouTube integration as an advanced service. For advanced services there is less documentation on the Google Apps Script site and generally you are better looking at the API documentation for the service. In this case the YouTube Data API jumping in to the PlaylistItems list examples there is a JavaScript solution to retrieve a channels upload list. As Google Apps Script uses the JavaScript syntax this provides a useful starting point to structure our project. Something to bear in mind is that the JavaScript YouTube client and Google Apps Script are different, so in Javascript you prepare a request which is executed. In Apps Script you can prepare variable if you like but we can jump straight to the execute bit. Below are two examples of the same code in JavaScript and Apps Script

JavaScript

function requestUserUploadsPlaylistId() {
  // See https://developers.google.com/youtube/v3/docs/channels/list
  var request = gapi.client.youtube.channels.list({
    mine: true,
    part: 'contentDetails'
  });
  request.execute(function(response) {
    playlistId = response.result.items[0].contentDetails.relatedPlaylists.uploads;
    requestVideoPlaylist(playlistId);
  });
}

Google Apps Script

function requestUserUploadsPlaylistId() {
  // See https://developers.google.com/youtube/v3/docs/channels/list
  var response = YouTube.Channels.list('contentDetails', {mine:true});
  playlistId = response.items[0].contentDetails.relatedPlaylists.uploads;
  requestVideoPlaylist(playlistId);
}

The eagle-eyed among you may have spotted that on the Google Apps Script site there is a  Retrieve YouTube Uploads example which might be a better starting point. I have to admit I missed this at the time but still believe for advanced services  getting to know the service api docs will be better for you in the long run.

So how do we go from var request = gapi.client.youtube.channels.list() to var response = YouTube.Channels.list(). This is where autocomplete will make your task so much easier. In the App Script editor typing ‘YouTube’ followed by a period ‘.’ brings up the next available options in the call (Tip: pressing Ctrl+Space will list all the available services). Here’s an example for the Analytics service:

autocomplete

If there are parameters required these will be indicated as well as what is returned. Using this with the list example we can see it’s expecting a string part and an optionalArgs object.

autocomplete YouTube

This is when the YouTube Data API reference comes in handy as it lists all the required and option parameters and the values it expects. Some other nice features of this documentation is the option to try a call to the api from the page. This is useful to test values and see a shape of the data returned.

Everyone of course has there own way of working but hopefully you found this useful, so go forth and make your own YouTube Data mashups

Enjoy!