How to get information about all videos of a channel to Google Sheets with Google Apps Script

Question

I want to list all videos of a YouTube channel in Google Sheets and see statistics if possible. How to achieve this with Google Apps Script?

Answer 1

The end product of the following code:

• Takes a channel ID as input
• Returns statistics provided by YouTube Data API into the active sheet

YouTube Data API v3 credentials will be required to proceed. See:

• YouTube Data API Overview
• Obtaining Credentials

Once you get the API key you're set. This is the strategy:

1. There is a playlist named uploads for every channel on YouTube. Find that playlist's ID. Channels.list will be used for this. See Channels.list API here.
2. Get ID of every video in this playlist using PlaylistItems.list and put them into an array. See PlaylistItems.list API here.
3. Use join() to concatenate video IDs into a string and pass this string into a function that utilizes Videos.list. See Videos.list API here.
4. Implement an UI element to list all videos continuously.
   • This is necessary since YouTube Data API provides 50 results per page.
   • To invoke a new API call every 50 rows, setFormula() should be used.
   • When invoked from the script, setFormula() can't modify other cells' values. Please see more information here.

Here is how to do all these:

1. Create a new Google Apps Script project. (Tools > Script Editor)
2. Paste the following code into your script file.

var API_KEY='YOUR_API_KEY'
var CHANNELS_URL = 'https://www.googleapis.com/youtube/v3/channels'
var PLAYLIST_ITEMS_URL = 'https://youtube.googleapis.com/youtube/v3/playlistItems'
var VIDEOS_URL =  'https://youtube.googleapis.com/youtube/v3/videos'

/** 
 * @param {string} ID of the channel
 * @return {string} ID of the playlist that contains all video uploads
 */
function GET_UPLOADS_PLAYLIST_ID(channel_id){
  url_uploads_id = CHANNELS_URL + "?part=contentDetails&id=" + channel_id + "&key=" + API_KEY;
  return ImportJSON(url_uploads_id, "/items/contentDetails/relatedPlaylists/uploads", "noHeaders")
}

/** 
 * @param {string} ID of a playlist
 * @return {string} URL to be used in the API call
 */
function GET_VIDEO_INFO(playlist_id){
  items_url = PLAYLIST_ITEMS_URL + "?part=contentDetails&maxResults=50&playlistId=" + playlist_id + "&key=" + API_KEY;
  return items_url;
}

/** 
 * @param {string} ID of a playlist
 * @return {array} Two dimensional array that contains all video IDs and total page number
 * 
 * See pagination here: https://developers.google.com/youtube/v3/guides/implementation/pagination
 */
function GET_ALL_VIDEOS_IDS_AS_ARRAY(channel_id){
  var uploads_id = GET_UPLOADS_PLAYLIST_ID(channel_id)
  var video_ids = []
  var page_count = 0
  var next_page_token = ""
  api_url = GET_VIDEO_INFO(uploads_id) + "&pageToken=" + next_page_token
  while (1){
    page_count += 1
    video_ids.push(ImportJSON(api_url, "/items/contentDetails/videoId", "noHeaders"))
    next_page_token = ImportJSON(api_url, "/nextPageToken", "noHeaders")
    api_url = GET_VIDEO_INFO(uploads_id) + "&pageToken=" + next_page_token
    if(ImportJSON(api_url, "/nextPageToken", "noHeaders").length == 0){
      page_count += 1
      video_ids.push(ImportJSON(api_url, "/items/contentDetails/videoId", "noHeaders"))
      break
    } 
  }
  return [video_ids, page_count]
}

/**
 * @param {string} ID of a channel
 * Fetches statistics to the active sheet
 */
function  MULTIPLE_IDS_TO_STATS(channel_id){
  var row = 'A'
  var array = GET_ALL_VIDEOS_IDS_AS_ARRAY(channel_id)
  var page_number = array[1]

  for (var i = 0; i < page_number; i++){
    var col = i*51 + 1
    var cell = row + col
    var video_ids = array[0][i].join()
    var api_url = VIDEOS_URL + "?part=snippet,statistics" + "&fields=items(id,snippet(title,description,tags),statistics)" +"&id=" + video_ids + "&key=" + API_KEY;
    if (col>2){
      formula = '=ImportJSON("' + api_url + '" , "", "noHeaders") '
    } else {
      formula = '=ImportJSON("' + api_url + '" , "", "") '
    }
    SpreadsheetApp.getActiveSheet().getRange(cell).setFormula(formula)
  }
  
}

3. Click on the plus icon next to the "Files" and choose "Script". Rename your new script file as "ImportJSON" and paste this code.
4. Click on the plus icon next to the "Files" and choose "Script". Rename your new script file as "menu" and paste the following code. This is to create the necessary UI element.

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('VIDEO LISTER')
      .addItem('LIST VIDEOS', 'videoLister')
      .addToUi();
}

function videoLister() {
  var ui = SpreadsheetApp.getUi(); // Same variations.

  var result = ui.prompt(
      'Enter the channel ID',
      ui.ButtonSet.OK_CANCEL);

  // Process the user's response.
  var button = result.getSelectedButton();
  var text = result.getResponseText();
  if (button == ui.Button.OK) {
    // User clicked "OK".
    MULTIPLE_IDS_TO_STATS(text)
  } else if (button == ui.Button.CANCEL) {
    // User clicked "Cancel".
    ui.alert('I didn\'t get any channel ID');
  } 
}

5. Save your files and refresh your Spreadsheet file. You should see a new menu item in the top bar named "VIDEO LISTER". Provide the channel ID and let the script do the work.