google-apps-script - 如何使用 Google Apps 脚本将有关频道的所有视频的信息获取到 Google 表格

Question

我想在 Google 表格中列出 YouTube 频道的所有视频，并尽可能查看统计信息。如何使用 Google Apps 脚本实现这一目标？

标签： google-apps-scriptgoogle-sheetsyoutube-data-api

Answer 1

以下代码的最终产品：

• 将频道 ID 作为输入
• 将 YouTube 数据 API 提供的统计信息返回到活动工作表中

需要 YouTube 数据 API v3 凭据才能继续。看：

• YouTube 数据 API 概览
• 获取凭证

获得 API 密钥后，您就设置好了。这是策略：

1. uploadsYouTube 上的每个频道都有一个播放列表。找到该播放列表的 ID。Channels.list将用于此。请参阅此处Channels.list的API 。
2. 使用获取此播放列表中每个视频的 IDPlaylistItems.list并将它们放入数组中。请参阅此处PlaylistItems.list的API 。
3. 用于join()将视频 ID 连接成一个字符串，并将该字符串传递给一个使用Videos.list. 请参阅此处Videos.list的API 。
4. 实现一个 UI 元素以连续列出所有视频。
   • 这是必要的，因为 YouTube 数据 API 每页提供 50 个结果。
   • 要每 50 行调用一次新的 API 调用，setFormula()应该使用。
   • 从脚本调用时，setFormula()不能修改其他单元格的值。请在此处查看更多信息。

以下是如何执行所有这些操作：

1. 创建一个新的 Google Apps 脚本项目。（工具 > 脚本编辑器）
2. 将以下代码粘贴到您的脚本文件中。

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. 单击“文件”旁边的加号图标，然后选择“脚本”。将新脚本文件重命名为“ImportJSON”并粘贴此代码。
4. 单击“文件”旁边的加号图标，然后选择“脚本”。将新脚本文件重命名为“menu”并粘贴以下代码。这是创建必要的 UI 元素。

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. 保存您的文件并刷新您的电子表格文件。您应该会在顶部栏中看到一个名为“VIDEO LISTER”的新菜单项。提供频道 ID 并让脚本完成工作。