スプレッドシートにYouTube動画データを自動取得する方法～Google Apps Script × YouTube Data API v3～

function myFunction() {
  // API KEYを入力ください
  const apiKey = "{API KEY}";

  const baseUrl = "https://www.googleapis.com/youtube/v3/";
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const workSheet = ss.getActiveSheet();

  // シート名と一致するチャンネルIDを「設定」から取得
  const sheetName = workSheet.getSheetName();
  const channelIDSheet = ss.getSheetByName("設定");
  const values = channelIDSheet.getDataRange().getValues();
  const channelID = getChannelID(values);

  // シートをクリアにする
  workSheet.clear();

  // ヘッダー行を書き込み
  workSheet.appendRow(["タイトル", "説明", "長さ", "視聴回数", "高評価", "リンク", "日付"])

  // YouTubeデータ取得＆書き込み
  let list_nextpageID = undefined;
  let responseJson = "";
  do {
    responseJson = getVideos(channelID, list_nextpageID);
    list_nextpageID = responseJson.nextPageToken;
    if(responseJson.items.length != 0) {
      fillOutSheet(responseJson);
    }
  } while (list_nextpageID != undefined);

  /**
   * チャンネルIDを取得する
   * */ 
  function getChannelID(values){
    let channelID = "";
    for (let row of values) {
      if (row[0] == sheetName) {
        // カスタムURLかどうか判定
        const patternChannelId = /UC[\w-]{22}/
        const patternChannelMatched = row[1].match(patternChannelId)?.[0]
        if (patternChannelMatched) { // チャンネルIDパターンに一致した場合
          channelID = row[1];
          break

        } else { // カスタムURLの場合（チャンネルIDパターンに一致しない場合）
          dataURL = baseUrl + "search?part=snippet&type=channel&q="+ row[1] +"&key=" + apiKey;
          const response = UrlFetchApp.fetch(dataURL);
          const json = JSON.parse(response.getContentText());
          channelID = json.items[0].id.channelId
          break
        }
      }
    }
    return channelID;
  }

  /**
  チャンネルの動画情報を取得
  */
  function getVideos(channelID, pageToken) {
    // YouTube API呼び出し
    let dataURL = "";
    if (pageToken == undefined) {
      dataURL = baseUrl + "search?part=snippet&maxResults=50&order=date&channelId=" + channelID + "&key=" + apiKey;
    } else {
      dataURL = baseUrl + "search?part=snippet&maxResults=50&order=date&channelId=" + channelID + "&key=" + apiKey + "&pageToken=" + pageToken;
    }

    const response = UrlFetchApp.fetch(dataURL);
    return JSON.parse(response.getContentText());
  }

  /**
   * チャンネルの動画情報を１つ１つシートに書き込む
   **/
  function fillOutSheet(responseJson) {
    let videoInfoAll = [];
    // レスポンス（チャンネル動画群）を１つ１つ分解
    for (let cnt = 0; cnt < responseJson.items.length; cnt++) {
      const targetVideo = responseJson.items[cnt]
      const videoId = targetVideo.id.videoId;
      if (videoId != undefined) {
        const title = targetVideo.snippet.title;
        const description = targetVideo.snippet.description;
        const publishedAt = targetVideo.snippet.publishedAt;

        const videoInfo = getVideoInfo(videoId)
        const videoTime = videoInfo[0];
        const viewCount = videoInfo[1];
        const likeCount = videoInfo[2];
        const formulaLink = '=HYPERLINK("https://www.youtube.com/watch?v=' + videoId + '","リンク")';

        videoInfoAll.push([title, description, videoTime, viewCount, likeCount, formulaLink, publishedAt]);
      }
    }
    // 書き込み処理
    const lastRowNumber = workSheet.getLastRow();
    console.log(lastRowNumber)
    workSheet.getRange(lastRowNumber + 1, 1, videoInfoAll.length, 7).setValues(videoInfoAll);
  }

  /**
  動画の情報を取得
  */
  function getVideoInfo(videoId) {
    const dataURL = baseUrl + "videos?part=snippet,contentDetails,statistics,status&id=" + videoId + "&key=" + apiKey;

    const response = UrlFetchApp.fetch(dataURL);
    const responseJson = JSON.parse(response.getContentText());

    const videoInfo = responseJson.items[0];
    const duration = videoInfo.contentDetails.duration;
    const viewCount = videoInfo.statistics.viewCount;
    const likeCount = videoInfo.statistics.likeCount;
    const dislikeCount = videoInfo.statistics.dislikeCount;

    return [convertDurationTime(duration), viewCount, likeCount, dislikeCount];
  }

  /**
  動画時間のコンバート処理
  */
  function convertDurationTime(duration) {
    const reg = new RegExp('^PT([0-9]*H)?([0-9]*M)?([0-9]*S)?');
    const regResult = duration.match(reg);
    let hour = "";
    try{
      hour = regResult[1];
    } catch {
      return duration
    }
    let minutes = regResult[2];
    let sec = regResult[3];

    if (hour == undefined) { hour = '00'; }
    else {
      hour = hour.split('H')[0];
      if (hour.length == 1) { hour = '0' + hour; }
    }

    if (minutes == undefined) { minutes = '00'; }
    else {
      minutes = minutes.split('M')[0];
      if (minutes.length == 1) { minutes = '0' + minutes; }
    }

    if (sec == undefined) { sec = '00'; }
    else {
      sec = sec.split('S')[0];
      if (sec.length == 1) { sec = '0' + sec; }
    }

    return hour + ":" + minutes + ":" + sec
  }
}