google-apps-script - 用于 YouTube 分析的 Google 脚本

Question

我有一个运行一些功能的脚本，主要目标是从 YouTube 视频中检索数据，包括观看次数、不喜欢次数、喜欢次数以及其他重要指标，以便我们可以正确分析所有数据。

默认情况下，所有指标都正确插入到我的 Google 工作表中，除了：

• AVERAGE_VIEW_DURATION_SECONDS
• SUBSCRIBERS_GAINED
• 分享
• AUDIENCE_RETENTION_PERCENTAGE
• CARD_IMPRESSIONS
• CARD_CLICK_RATE

脚本中是否有任何错误？

// 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: 'D',
  LIKES: 'E',
  DISLIKES: 'F',
  COMMENTS: 'G',
  DURATION: 'H',
  AVERAGE_VIEW_DURATION_SECONDS: 'I',
  SUBSCRIBERS_GAINED: 'J',
  SHARES: 'K',
  AUDIENCE_RETENTION_PERCENTAGE: 'L',
  CARD_IMPRESSIONS: 'M',
  CARD_CLICK_RATE: 'N'
};

// 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);
    cell = sheet.setActiveCell(Column.AVERAGE_VIEW_DURATION_SECONDS + (2+i));
    cell.setValue(stats[i].statistics.averageCount);
    cell = sheet.setActiveCell(Column.SUBSCRIBERS_GAINED + (2+i));
    cell.setValue(stats[i].statistics.subscriberCount);
    cell = sheet.setActiveCell(Column.SHARES + (2+i));
    cell.setValue(stats[i].statistics.shareCount);
    cell = sheet.setActiveCell(Column.AUDIENCE_RETENTION_PERCENTAGE + (2+i));
    cell.setValue(stats[i].statistics.audienceCount);
    cell = sheet.setActiveCell(Column.CARD_IMPRESSIONS + (2+i));
    cell.setValue(stats[i].statistics.impressionCount);
    cell = sheet.setActiveCell(Column.CARD_CLICK_RATE + (2+i));
    cell.setValue(stats[i].statistics.clickCount);
  }
}

标签： google-apps-scriptgoogle-sheets

Answer 1

检查添加的日志时确认，某些列未填充的原因是因为它不存在于使用Videos.list 方法获取的视频资源中。

对于缺少的字段，您需要使用基于并获取以下指标的Youtube Analytics API Reports Query：channel idaverageViewDuration, subscribersGained, shares, relativeRetentionPerformance, cardImpressions, cardClickRate

注意：列出的某些指标可能会被弃用