YouTube API チャンネルが取得しているすべての動画に対して、先月分の分析情報を取得する

createLastMonthConversionReport.gs

// createReportPlaylistInfo.gsを先に実行してください
const createLastMonthConversionReport =()=> {
  const channelId = "xxxxxxxxxxxxxxxxxxxx" // YouTube チャンネルID
  const fileId = "  xxxxxxxxxxxxxxxxxxxxxxxx" // createReportPlaylistInfo.gsで使用したGoogle Sheetと同一のシートID
  const file = SpreadsheetApp.openById(fileId)
  const videoListSheet = file.getSheetByName("video list")
  const videoList = videoListSheet.getRange(2, 1, videoListSheet.getLastRow() - 1, 1).getValues().map((value) => value[0])

  const { startDate, endDate, targetDate } = getLastMonthRenge()

  const videoLists = []

  while (25 <= videoList.length) {
    videoLists.push(videoList.splice(0, 25))
  }
  if (videoList.length != 0) videoLists.push(videoList)

  const videoListConversion = []

  // https://developers.google.com/youtube/analytics/reference/reports/query?hl=ja
  let headers = []
  videoLists.forEach((videoList) => {
    const filters = "video==" + videoList.join(",")
    const metrics = [
      'views',
      'estimatedMinutesWatched',
      'subscribersGained'];
    const result = YouTubeAnalytics.Reports.query({
      ids: 'channel==' + channelId,
      dimensions: "video",
      startDate: startDate,
      endDate: endDate,
      maxResults: 25,
      filters: filters,
      sort: "-estimatedMinutesWatched",
      metrics: metrics.join(',')
    });

    if (!result.rows) {
      console.log('No rows returned.');
      return;
    }

    headers = result.columnHeaders.map(
      (column) => {
        return column.name;
      });

    videoListConversion.push(...result.rows)
  })
  const japaneseHeaders = headers.map((value) => {
    if (value.includes("video")) return "VideoId"
    if (value.includes("views")) return "視聴回数"
    if (value.includes("estimatedMinutesWatched")) return "視聴時間"
    if (value.includes("subscribersGained")) return "登録者増加数"
  })
  videoListConversion.unshift(japaneseHeaders)
  const { sheet } = createSheet(fileId, targetDate)
  sheet.getRange(1, 1, videoListConversion.length, 4).setValues(videoListConversion)
}

const getLastMonthRenge = () => {
  const now = new Date()
  // month 0 ~ 11
  const targetDate = now.getFullYear() + "/" + now.getMonth() + "月"
  const monthStartDate = Utilities.formatDate(new Date(now.getFullYear(), now.getMonth() - 1, 1), "Asia/Tokyo", "yyy-MM-dd")
  const monthEndDate = Utilities.formatDate(new Date(now.getFullYear(), now.getMonth(), 0), "Asia/Tokyo", "yyy-MM-dd")
  return {
    targetDate: targetDate,
    startDate: monthStartDate,
    endDate: monthEndDate
  }
}

const createSheet = (fileId, name) => {
  const file = SpreadsheetApp.openById(fileId)
  const isSheetExist = file.getSheetByName(name)
  if (isSheetExist) {
    file.deleteSheet(isSheetExist)
  }
  const newSheet = file.insertSheet()
  newSheet.setName(name)
  return { sheet: file.getSheetByName(name) }
}

createReportPlaylistInfo.gs

// チャンネルのプレイリストIDを取得して、すべての情報を取得して書き出す
// 34行目を自分が所持しているGoogle SheetのIDに変更してください
const createReportPlaylistInfo = () => {
  let nextPageToken = null
  const { targetDate } = getLastMonthRenge()

  const result = YouTube.Channels.list("contentDetails,statistics", {
    mine: true,
  })
  const channelInfo = result.items[0]

  const subscriberCount = channelInfo.statistics.subscriberCount
  const videoCount = channelInfo.statistics.videoCount

  const playListId = channelInfo.contentDetails.relatedPlaylists.uploads
  // https://developers.google.com/youtube/v3/docs/channels?hl=ja#contentDetails.relatedPlaylists.uploads


  const pageList = []

  // https://developers.google.com/youtube/v3/docs/playlistItems/list?hl=ja
  do {
    const pageContent = YouTube.PlaylistItems.list("contentDetails,snippet", {
      playlistId: playListId,
      maxResults: 50,
      pageToken: nextPageToken == null ? "" : nextPageToken
    })
    nextPageToken = pageContent.nextPageToken
    pageContent.items.forEach((item) => {
      pageList.push([item.contentDetails.videoId, item.snippet.title, Utilities.formatDate(new Date(item.snippet.publishedAt), 'Asia/Tokyo', 'yyyy-MM-dd')])
    })
  } while (nextPageToken != null)

  const fileId = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" //書き出したい　Google SheetのIDを記入
  const file = SpreadsheetApp.openById(fileId)

  const channelInfoSheet = file.getSheetByName("channel info")
  channelInfoSheet.getRange(2,1).setValue(subscriberCount)
  channelInfoSheet.getRange(2,2).setValue(videoCount)
  
  channelInfoSheet.getRange(channelInfoSheet.getLastRow() + 1, 4, 1, 3).setValues([[targetDate, subscriberCount, videoCount]])

  const { sheet } = createSheet(fileId, "video list")
  sheet.getRange(1, 1, 1, 3).setValues([["ページid", "タイトル", "投稿日"]])
  sheet.getRange(2, 1, pageList.length, 3).setValues(pageList)

}

const getLastMonthRenge = () => {
  const now = new Date()
  // month 0 ~ 11
  const targetDate = now.getFullYear() + "/" + now.getMonth() + "月"
  const monthStartDate = Utilities.formatDate(new Date(now.getFullYear(), now.getMonth() - 1, 1), "Asia/Tokyo", "yyy-MM-dd")
  const monthEndDate = Utilities.formatDate(new Date(now.getFullYear(), now.getMonth(), 0), "Asia/Tokyo", "yyy-MM-dd")
  return {
    targetDate: targetDate,
    startDate: monthStartDate,
    endDate: monthEndDate
  }
}

const createSheet = (fileId, name) => {
  const file = SpreadsheetApp.openById(fileId)
  const isSheetExist = file.getSheetByName(name)
  if (isSheetExist) {
    file.deleteSheet(isSheetExist)
  }
  const newSheet = file.insertSheet()
  newSheet.setName(name)
  return { sheet: file.getSheetByName(name) }
}