背景
ここ数年YouTuberさん達が、多くの優良コンテンツを提供してくださっています。
それを利用して勉強している方も多いでしょう。
私もサンゼさん(@SANZE_motion)のチュートリアルをコツコツ進めて映像編集の勉強中ですが、「どのくらい完了したか」を常に把握していると勉強も捗るということでこんなシートを作りました。
→ SANZEさんチュートリアル 完了メモ （※ コピーしてお使いいただけます！どうぞ！）
機能:
- 完了したものに✓を入れることで完了率が分かる。
- ハイパーリンクから動画を開くことができる。
- 新しい動画が投稿されたら自動的にリストに追加される。
本記事ではこの仕組みや作り方を解説します。
この記事の要約
必要なもの
- Googleアカウント
やること
- YouTube Data API keyの取得
- スクリプトの作成（コピペでOK)
- スプレッドシートを整える
- (任意) 配布用に共有設定をする
得られるもの
- ↑のようなスプレッドシート
仕組み
-
Google spreadsheetに作られたGoogle Apps Scriptが定期実行される。
(例: 1日1回などお好きな頻度で。下記、APIの使用料金についてを参照。)
-
Google Apps ScriptはYouTube Data APIを使用して、動画情報を取得する。
-
Google Apps Scriptは取得した情報をGoogle spreadsheet内に書き込む。
※ 書き込み先のスプレッドシートは事前に手動で整えておきます。
作り方
事前準備
[前提] Googleアカウントが必要です。取得しましょう。
YouTube Data API key の取得
大抵のAPIを使用するにはkeyを取得する必要があります。
今回も必要なので以下の手順で取得しましょう。
-
YouTube Data API v3をクリックします。
-
「有効にする」をクリックします。
-
画面左の「認証情報」→ 画面上部の「＋認証情報を作成」をクリックします。
-
作成されたAPIキーをテキストファイルなどにコピー・ペーストして保存します。他人に使用されないよう大切に保管してください。
-
任意の名前を付け、「キーを制限」をクリックし「YouTube Data API v3」を選択し保存します。（万が一APIキーを盗まれても他のAPI使用などで使用されることがなくなります。）
-
API keyが取得できたので、このページは閉じてOKです。
スクリプトを書く
準備
-
後で必要になるので、対象のYouTubeチャンネルのIDを確認してどこかにコピペしておきます。以下はサンゼさんの例。
-
新しいGoogleスプレッドシートを開き、任意の名前付けます。
-
タイトル行を作っておきます。
-
「ツール」→「スクリプトエディタ」を開きます。
-
任意の名前を付けて、既に入力されているコードを削除しておきます。この部分にコードを書いていきます。
一旦、処理の流れを確認
以下の流れで記述していきます。
- パラメータを設定。
- スプレッドシートに記録済みの情報を取得。
- YouTube Data APIを叩いてチャンネル情報を取得。
- スプレッドシートに未記録の情報だけを抜き出す。
- スプレッドシートに書き込む。
実際にスクリプトのコードを書いていく
※ よく分からない人・使えればOKな人はコピペするだけでOKです。
※ ただし冒頭「パラメータを設定」部分の4項目はご自身の環境に合わせて変えてください。
※「Ctrl + S」でこまめにセーブしましょう。
function get_video_list() {
// パラメータを設定 ==================================================
// 自分が作成したAPI key
var apikey = 'AAAAAAAAAAAAYouTubeAPITokenAAAAAAAAAAAA';
// 書き込み先のシート名
var sheetName = "シート1"
// 自分がチェックしたいチャンネルのID
var channelID = "UCOnr1C39WO22p98X8L6Q4uA";
// チェックする範囲の設定
// true : 最新50件のみチェックする。 (2回目以降の実行(定期実行時)はtrueを推奨)
// false: 対象チャンネルの全動画を取得する。(初回実行時はfalseにする)
var getNewVideoOnly = false;
// スプレッドシートに記録済みの情報を取得 ==================================================
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName(sheetName);
var recorded_video_IDs_array = [];
if (sheet.getLastRow() > 2) {
recorded_video_IDs_array = sheet.getRange(2,4,sheet.getLastRow()-1,1).getValues() || []; //getRange(行番号, 列番号, 行数, 列数)
}
else {
recorded_video_IDs_array = [];
}
var recorded_video_IDs_hash = {};
for (var i=0; i<recorded_video_IDs_array.length; i++) {
recorded_video_IDs_hash[recorded_video_IDs_array[i]] = 1;
}
// YouTube Data APIを叩いてチャンネル情報を取得 ==================================================
// 「アップロードされた動画リスト」のIDを取得
var baseUrl = "https://www.googleapis.com/youtube/v3/";
var dataURL = baseUrl + 'channels?part=contentDetails&id=' + channelID + '&key=' + apikey;
var response = UrlFetchApp.fetch(dataURL);
var responseJson = JSON.parse(response.getContentText());
var uploads_video_play_list_ID = responseJson.items[0].contentDetails.relatedPlaylists.uploads;
var page_token = '';
var record_data = [];
// 各動画の情報を取得
while (page_token !== undefined) {
dataURL = baseUrl + 'playlistItems?part=snippet&playlistId=' + uploads_video_play_list_ID +
'&maxResults=50&order=date&pageToken=' + page_token + '&key=' + apikey;
//'&maxResults=50&order=date&key=' + apikey;
response = UrlFetchApp.fetch(dataURL);
responseJson = JSON.parse(response.getContentText());
page_token = responseJson.nextPageToken;
// スプレッドシートに未記録の情報だけを抜き出す ==================================================
for (var i=0; i<responseJson.items.length; i++) {
// 動画情報取得
var video_id = responseJson.items[i].snippet.resourceId.videoId;
var video_title = responseJson.items[i].snippet.title;
var thumbnail_url = responseJson.items[i].snippet.thumbnails.default.url;
// 記録済みならスキップ
if (recorded_video_IDs_hash[video_id]) {
continue;
}
// 他のスキップする条件があれば記述
//(サンゼさんのチャンネルの場合、タイトルが「数字.」から始まるものがチュートリアルなのでそれ以外はスキップ。）
/*
if (! video_title.match(/[0-9]+\./)) {
continue;
}
*/
// スプレッドシートに書き込む文字列を整える
var video_url = 'https://www.youtube.com/watch?v=' + video_id;
var cell_value_title = '=hyperlink("' + video_url + '","' + video_title + '")';
var cell_value_thumbnail = '=image("' + thumbnail_url + '")';
// 動画の時間を取得
var video_duration = '';
dataURL = baseUrl + 'videos?part=contentDetails&id=' + video_id + '&key=' + apikey;
response = UrlFetchApp.fetch(dataURL);
var responseJson_video = JSON.parse(response.getContentText());
video_duration = convertDurationTime(responseJson_video.items[0].contentDetails.duration);
record_data.push([cell_value_thumbnail, cell_value_title, video_duration, video_id]);
}
// 新しい動画のみ取得する場合、次のページを検索しない
if (getNewVideoOnly) {
page_token = undefined;
}
}
// スプレッドシートに書き込む ==================================================
if (0 < record_data.length) {
var last_row = sheet.getRange("B:B").getValues().filter(String).length + 1;
sheet.getRange(last_row, 1, record_data.length, 4).setValues(record_data.reverse());
}
}
function convertDurationTime(duration) {
var reg = new RegExp('^PT([0-9]*H)?([0-9]*M)?([0-9]*S)?');
var regResult = duration.match(reg);
var hour = regResult[1];
var minutes = regResult[2];
var 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
}
↓↓↓ コピペするとこうなります。↓↓↓
Ctrl + Sで保存しましょう。
動作確認する
-
「関数名を選択 ▼」をクリックして「get_video_list」を選択、左側にある実行ボタン「▶」をクリックします。
-
初回のみ以下の画面が表示されます。以下の手順で許可を与えてください。
-
「許可を確認」をクリック。
-
自身のアカウントを選択。
-
「詳細」をクリック。
-
「YouTubeチェックリスト（安全ではないページ）に移動」をクリック。
-
「許可」をクリック。
-
-
少し(通常1分未満。チャンネルの動画数による)待つ。スプレッドシートに情報が記述されるのを確認する。
正常に動作した例:
-
次のステップで定期実行を設定して自動化しましょう。
トリガー(実行するタイミング)を設定する
自動実行するタイミングを「○時間に1回実行」や「毎日○時～○時に実行」などを設定できます。
トリガーの設定手順
-
「編集」→「現在のプロジェクトのトリガー」をクリック。
-
「＋ トリガーを追加」をクリック。
-
イベントのソースを選択で「時間主導型」を選び、お好みの頻度に設定、「保存」をクリックします。
-
頻度については以下のAPIの使用料金についても参照してください。
APIの使用料金について
本記事の使用規模では余裕で無料範囲です。(チャンネルの動画投稿数によりますが・・・)
が、トリガー頻度の設定次第では無料範囲を使い切ってしまうので適切に設定しましょう。
2020年9月時点では10,000クエリ/日が無料のようです。
1実行で消費するクエリは取得する動画数+αなので（動画100本あれば105クエリ程度）、
初回 (全動画情報を取得する場合)は多くのクエリを消費しますが、2回目以降（増加分のみ取得する場合）は少なくなります。
新しい動画がない場合も2クエリ消費されます。5分に1回実行でも576クエリ/日なので余裕で無料範囲です。
（通常1時間に1回でもほぼリアルタイムと言えるでしょう）
実際の使用状況はこちらを参考に確認できます。
スプレッドシートを整える
情報が取得できたので、使いやすいように整えましょう。
自動的に情報が書き込まれるシートを編集すると（列を変えると）正しく動作しません。
新しいスプレッドシートを作成して参照するようにしましょう。
（配布しない場合、新しい「スプレッドシート」ではなく「シート」を追加するだけでOKです）。
手順
-
新しいスプレッドシート（シート）を作成します
-
A1~E3に以下のように入力します。デザインや文字はお好みで変えてください。
-
A4セルに以下の関数を入力して、自動取得したデータを参照します。
※ 参照先のURL、シート名はご自身で作成したものに置き換えてください。=IMPORTRANGE("https://docs.google.com/spreadsheets/d/.....","シート1!A2:C1000") -
「データ」→「データの入力規則」をクリックし、以下のように設定します。
-
D4セルをD5~D1000 (適切な範囲へ）コピペします。
-
C1セルに以下の関数を入力します。 (適切な範囲を設定）
=COUNTIF($D$4:$D$1000,"✔") -
D1セルに以下の関数を入力します。 (適切な範囲を設定）
=C1/sum($C$1:$C$2) -
C1, D1セルに入力した関数をC2, D2セルにコピペします。
-
列の幅・高さ、センタリング、%表示などをお好みで設定します。
-
一般公開する場合、参照元（自動更新されるもの）のスプレッドシートとデザインを整えたスプレッドシートを誰でも閲覧できるよう権限を付与します。
※ 権限が「閲覧者」になっていることを確認！
-
完成！