スプレッドシートでYouTubeの再生数日次データから前日比ランキングを作成

業務効率化
スポンサーリンク

好きなアーティストがYouTubeにカヴァー曲を歌う動画をアップしているのですが、スプレッドシートで、投稿日とか曲名とかジャンルとかをまとめてます。

再生数もどのくらいなのかも取得してます。

前回、「スプレッドシートでYouTube動画の総再生数を定点観測してみた」という記事を書きましたが、『総再生数』の定点観測でした。

しかし、総再生数のを定点観測していたら「なんかこの日の総再生数多いけど、何が伸びたんだろうか」と気になったが、たくさんの動画から探すことが困難だったので、1動画ごとに定点観測することにしました。

YouTubeの再生数取得に関しては下記の記事をごらんください。

YouTube動画の再生数をスプレッドシートで自動取得して業務効率アップ！
スプレッドシートでYouTubeの情報を引っ張りたくて調べてみたら、AppsScriptを使用することが判明しました。 ちょっと難しいそうでしたが、しっかりやり方を見ればできましたので備忘録として書いてみます。 ※スプレッドシートに関しては...
okachanblog.com
2022.03.16

再生数を定点記録する

まずは1動画ごとに定点観測するシートを作ります。

（1）①の『元データミラー』というシートの内容を、②の『曲別再生数定点』に1日1回貼り付けを行うようにします。いつの再生数なのかがわかるように、③の『集計日時』も一緒に貼り付けるスクリプトを登録します。

（2）上メニューから　拡張機能　＞　AppsScript　をクリックします。

（3）下記のコードを手順に沿って追加します。

①にコードを貼り付けます。（ほかのコードがすでにある場合は注意）
②1行目のfunction viewsStamp02() {の『viewsStamp02』はスクリプトの名称になるので管理しやすい名前にします。
③のプルダウンに②の名称が表示されます。②で登録した名称を選択します。
④『実行』をクリックすると、スクリプトが実装されます。
⑤実行の結果がページ下部の『実行ログ』に表示されます。エラーがないか確認しましょう。

  • 3行目のgetSheetByName('元データのミラー');の『元データのミラー』はコピー元の方のシート名です。対象のシート名を入力してください。
  • 4行目のgetSheetByName('曲別再生数定点');の『曲別再生数定点』は貼り付ける方のシート名です。対象のシート名を入力してください。
  • 6行目のgetRange('A:E')の『A:E』はコピーしたい列です。対象の列を入力してください。
  • 14行目の"A2:E"はどこからコピーするか指定します。1行目は見出しなので『A2:E』と指定してます。
  • 26行目のgetRange('A:E')の『A:E』は貼り付けたい列です。対象の列を入力してください。
  • 37行目のValue_cell = "A" + lastRow + ":" + "E" + lastRow_mirror は曲名再生数などの情報を貼り付けたい位置です。A:Eに貼り付けたいのでこの値にしてます。
  • 38行目の"F" + lastRow +　":"　+ "F"　+ lastRow_mirror は集計日時を貼り付けたい位置です。F:Fに貼り付けたいのでこの値にしてます。
function viewsStamp02() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName('元データのミラー');
  var sheet_data = spreadsheet.getSheetByName('曲別再生数定点');

  var rangeData_mirror = sheet.getRange('A:E').getValues();
  var lastRow_mirror = 0;
  for(var i=0; i<rangeData_mirror.length; i++){
    if(rangeData_mirror[i][0]){
      lastRow_mirror = i + 1;
    }
  }

  lastRow_mirror_ranges = "A2:E" + lastRow_mirror
  Logger.log(lastRow_mirror_ranges);

  var range = sheet.getRange(lastRow_mirror_ranges);
  var values = range.getValues();
  Logger.log(values);

  var date = new Date();
  timeStamp = Utilities.formatDate( date, 'Asia/Tokyo', 'yyyy/MM/dd');
  // 今日の日付を表示
  Logger.log(timeStamp);

  var rangeData = sheet_data.getRange('A:E').getValues();
  var lastRow = 0;
  for(var i=0; i<rangeData.length; i++){
    if(rangeData[i][0]){
      lastRow = i + 1;
    }
  }
  
  lastRow_mirror = lastRow_mirror + lastRow　-1
　lastRow = lastRow + 1
 
  var Value_cell = "A" + lastRow + ":" + "E" + lastRow_mirror
  var timeStamp_cell = "F" + lastRow +　":"　+ "F"　+ lastRow_mirror
  Logger.log(Value_cell);

  Logger.log(lastRow);
  sheet_data.getRange(Value_cell).setValues(values)
  sheet_data.getRange(timeStamp_cell).setValue(timeStamp)

}

定期的に書き込むように設定する

（1）左メニューにストップウォッチみたいなアイコンがあるのでクリック

（2）右下の『トリガーを追加』をクリックします

（3）トリガー（時限）の詳細設定を行います。

①スクリプト名を選択します。今回は『viewsStamp』
②『時間手動型』を選択します。
③『日付ベースのタイマー』を選択します。
④どのくらいの間隔で取得するかを設定します。今回私は『午後 11 時～午前 0 時』にしました。
『保存』をクリックして設定完了です。

1日のさいごに、現時点の動画ごとの再生数を書き込み1日ごとを定点観測した結果が下記のように表示されました。おまけに、前日比なんかも追加してみました。

ぜひ、この記事を参考に集計を楽しんでください！

前日比を表示させる。

前日比を表示させるために、日次で取得しつづけたデータを並び替えてわかりやすくします。

（1）シートを新しく作り、①定点観測したデータを並び替えて読み込むために=SORT('シート名'!A2:F,1,FALSE)を入力します。

曲名や歌手名が同じものも存在するので、一意性のあるYouTubeIDで並び替えてます。
②のように、同IDのものだけが固まって並び替えられます。

（2）読み込んだ列の右側に1列追加して、前日からの増加数を表示させます。
追加した列に=IF(A3=A2,E3-E2,0)を記載します。下記のような内容の関数です。

  • １行前のIDと同様の場合（A列）、前日の再生数と引き算をして数値を出す
  • １行前のIDが一致しない場合は0を表示する

（3）①の定点観測した曲ごとの再生数から、前日比を表示させました。
②では、=SORT(A3:G,7,FALSE)を入力して、③で前日比の大きい順に並び替えます。

おわりに

と、いうことで『スプレッドシートでYouTubeの再生数日次データから前日比ランキングを作成』でした。

スプレッドシートには他にもたくさんの便利機能があるので、紹介していきたいと思います。

この記事のスクショ元のスプレッドシート『STGVのカヴァー曲まとめ（スプレッドシート関数サンプル）』、『STGVのカヴァー曲まとめ（スプレッドシート関数サンプル）_再生数定点』で公開してます。
他にもいろいろな関数を使用してますので、参考に見て行ってください。

このスプレッドシートで収集・集計しているのは、私の応援している大分県の仲良しミュージシャンSTGVのメンバーの方がYouTubeチャンネルにアップしているカヴァー曲の情報です。

データを使用して、カヴァー曲のまとめ記事を書いています。
『おすすめカヴァーが付いている記事一覧』でまとめています。好きな曲、アーティスト、ジャンルがあったらぜひぜひ聴いていってください。

コメント