ゆるおたノート

Tomorrow is another day.

【Google Analytics × Google Apps Script】総合ランキングを別シートに転記して、毎日チェックしたい

GASを使ってGoogle Analyticsのデータを日々Slackに飛ばしています。
毎日自分のデータを見ることが出来て結構楽しいですね。
yuru-wota.hateblo.jp

だんだん欲が出てきたので、今度は「総合ランキング」シートを作ってみました。 Google Analyticsからインポートしてみた

ただ、これはこれで良いですが、下の方まで行くと見出しが見られないのです。
行や列を固定してみると、再Runの時にエラーになります。

「だったらGoogle Analyticsで見なさい」と言われそうですが、
ちょっと重いし…あんまり見た目いじれないし…ちょっと不便だなぁ…

あと、空いたセルにちょっとした関数を足そうと思っても
翌朝には初期化されます。(悲)

そんなわけで、せっかく作ったのに使えなくなってしまうのは困るので、
他のシートに転記することにして運用したいと思います。

今日のコードを使うと…

こんな感じになります。
「最低限、見られる」感じかなと思います。

進捗_20190516

完成イメージ

最終的にこんな感じを目指します。

値を調整したり、色を付けたり。

PVで言うと何故か「記事一覧」が1番多いのですが、リライト前の記事も
ぽつぽつとアクセスが続いてたりします。
嬉しいですが、「どげんかせんといかん」てやつですねぇ…

また、新しめの記事もちょいちょい上位に入っているのが最近のモチベーションです。
数字は小さくても結構嬉しいものですね。皆様いつもありがとうございます!

1. 準備

さて、実際の作業に入ります。
前回のつづきとして進めるので、必要に応じてご参照頂ければ幸いです。

Spreadsheet

1. Google Analyticsのレポートを追加

まずはReport Configurationシートにレポートを追加します。
今回の設定はこちら。

※この時点で何のこっちゃな方は、隣IT様(外部サイト)のこちらの記事をご参照ください。

Report Name Comp. Ranking*1
Start Date ブログ開始日
End Date =today()-1
Metrics ga:pageviews,
ga:avgTimeOnPage,
ga:users,
ga:newUsers,
ga:sessions,
ga:pageviewsPerSession,
ga:avgSessionDuration,
ga:bounceRate
Dimenstions ga:pageTitle,
ga:pagePath
Order -ga:pageviews,
-ga:avgTimeOnPage*2
2. スケジュールを設定

変更せず、そのままにします。

3. コンテナバインドスクリプトスクリプトファイルを追加

前回のプロジェクトにスクリプトファイルを追加します。
名前は「03_CompRanking」としました。

2. コード

1-1 メイン

GASに慣れていない方へザックリ流れをご説明すると、こんな感じです。

  1. レポートの値をまとめて取得
  2. 値を少し調整
  3. 前日分として別のSpreadsheetに転記
  4. シートの書式や表示を調整
function transcriptReport() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // レポートを取得
  var dataSheet = ss.getSheetByName('Comp. Ranking');
  var dataRange = dataSheet.getDataRange();
  var rawData   = dataRange.getValues();

  const FIRST_ROW = 15; // 見出しから
  var lastRow     = dataRange.getLastRow();

  var transValues = editReportValues(rawData, FIRST_ROW, lastRow);
  var transSS     = SpreadsheetApp.openById('xxxxxxxx');
  
  var today = new Date();
  // 前日分としてシート名を設定
  var momentToday = Moment.moment(today).add(-1, 'day').format('YYYYMMDD')

  deleteTempSheetName(momentToday, transSS);
  
  transcriptToNewSheet(transSS, transValues, momentToday);
}

1-2 値を調整

後で使いやすいように、いろいろ調整します。

var editReportValues = function(values, FIRST_ROW, lastRow) {
  var firstIndex = FIRST_ROW - 1;
  var lastIndex  = lastRow - 1;
  var returnValues = [];
  
  for (var i = firstIndex; i < lastIndex; i++) {
    var currentRowValues = values[i];
    
    if (i !== firstIndex) {
      editPageTitleAndUrl(currentRowValues)
    }
    
    currentRowValues = inputPageCategory(currentRowValues); // ページ種別の追加
    
    var rank =  i - 14;
    currentRowValues.unshift(rank); // 順位を追加
    
    returnValues.push(currentRowValues);
    returnValues[0][0] = 'Rank';
  }
    
  return returnValues;
};
1-2-1 文字列をいじる

タイトルが冗長だったりURLが一部だけだったりするので、見やすいように
編集しておきます。

var editPageTitleAndUrl = function(rowValues) {
  rowValues[0] = rowValues[0].replace(' - ゆるオタクのすすめ', '');
  rowValues[1] = 'https://yuru-wota.hateblo.jp' + rowValues[1];
};
1-2-2 ページの種別を追加

ザっと書いてしまったのですが、ちょっと分岐が多すぎて…

コメントにあるように「オブジェクト」とかを使ったら
もう少しスッキリ出来るんじゃないかなーと思っています(希望的観測)。

修正が出来たら後ほど更新しますね。

var inputPageCategory = function(rowValues) {
  // オブジェクトの方がスッキリできるかも
  switch (true) {
    case (rowValues[0].indexOf('Page Title') !== -1):
      rowValues.unshift('Type');
      break;
      
    case (rowValues[0].indexOf('カテゴリーの記事一覧') !== -1):
      rowValues.unshift('カテゴリ');
      break;
      
    case (rowValues[0].indexOf('日間の記事一覧') !== -1):
      rowValues.unshift('日別');
      break;
      
    case (rowValues[0].indexOf('ヶ月間の記事一覧') !== -1):
      rowValues.unshift('月別');
      break;
      
    case (rowValues[0].indexOf('年間の記事一覧') !== -1):
      rowValues.unshift('年間');
      break;
      
    case (rowValues[0].indexOf('記事一覧') !== -1):
      rowValues.unshift('Archive');
      break;
      
    case (rowValues[0].indexOf('not found') !== -1):
      rowValues.unshift('Error');
      break;
      
    case (rowValues[1].indexOf('/reporting/') !== -1):
      rowValues.unshift('Report');
      break;
      
    default:
      rowValues.unshift('記事');
  }
  
  return rowValues;
};

1-3 (エラー対策)シートを更新

エラー等で同じ日付のシートと当たってしまったら、改めて作成することにします。

var deleteTempSheetName = function(sheetName, ss) {

  var Sheets = ss.getSheets();
  for (var i = 0; i < Sheets.length; i++){
    if (Sheets[i].getSheetName() === sheetName) {
      ss.deleteSheet(Sheets[i]);
    }
  }
  
  return;
};

全消しして上書きでも良さそうですね。

1-4 別のSpreadSheetに転記

ここから転記に入ります。

数値の書式設定も独立できると嬉しいのだけど…列も直接指定してるし…
時間かかりそうなのでここは保留します…

var transcriptToNewSheet = function(ss, values, SheetName) {
  // 最後尾にシートを作成
  var sheetCounts = ss.getNumSheets();
  var transSheet = ss.insertSheet(SheetName, sheetCounts);
  
  var lastRow_transSheet = values.length;
  var transSheetLastCol  = values[1].length;
  var transDataRange     = transSheet.getRange(1, 1, lastRow_transSheet, transSheetLastCol);
  transDataRange.setValues(values);
  
  // 書式設定
  var rankCol        = transSheet.getRange('A:A').setNumberFormat('#,000 ');
  var pvCol          = transSheet.getRange('E:E').setNumberFormat('#,##0 ');
  var avgTimeCol     = transSheet.getRange('F:F').setNumberFormat('#,##0.00 "sec. "');
  var someNumCols    = transSheet.getRange('G:I').setNumberFormat('#,##0 ');
  var pagesCol       = transSheet.getRange('J:J').setNumberFormat('#,##0.00 "pages "');
  var avgDurationCol = transSheet.getRange('K:K').setNumberFormat('#,##0.00 "sec. "');
  var bounceRateCol  = transSheet.getRange('L:L').setNumberFormat('#,##0 % ');

  adjustColumnWidths(transSheet);

  // 見出し行の書式を設定
  setHeaderFormat(transSheet, transSheetLastCol)

  return;
};

ちなみに、書式設定は「Excelとほぼ同じように使える」*3ので、
勉強コストが抑えられて良いですね。Googlesさんさすがです。

1-4-1 列幅を調整

文法はこんな感じです。

sheet.setColumnWidths(●●行目, ●●行分, ●●px)

メソッドチェーンというものを初めて使いました…!(嬉)

var adjustColumnWidths = function(sheet) {
  sheet.setColumnWidths( 1, 1,  40)  // Rank
       .setColumnWidths( 2, 1,  60)  // Type
       .setColumnWidths( 3, 2, 280)  // Page Title, Page Url
       .setColumnWidths( 5, 1,  60)  // PV
       .setColumnWidths( 6, 1, 100)  // Avg. Tme on Page
       .setColumnWidths( 7, 3,  60)  // Users, New Users, Sessions
       .setColumnWidths(10, 1, 100)  // Pages/Sessions
       .setColumnWidths(11, 1, 100)  // Avg. Session Duration
       .setColumnWidths(12, 1,  80); // Bounce Rate
  return;
};
1-4-2 見出し行に色付け

メソッドチェーンというものをh(略)(嬉)

var setHeaderFormat = function (sheet, lastCol) {
  var headerRange = sheet.getRange(1, 1, 1, lastCol);
  const DEEP_BLUE = '#20124d';
  const WHITE     = '#ffffff';
  headerRange.setBackground(DEEP_BLUE)
             .setFontColor(WHITE)
             .setFontWeight('Bold');
  
  return;
};

3. トリガーを設定

とりあえず転記は出来るようになったので、元のシートが
更新されたら動くように設定します。

実行する関数 transcriptReport
デプロイ時に実行 Header
イベントのソース 時間主導型
時間ベースのトリガー 日付ベースのタイマー
時刻 午前6時~7時 (GMT+09:00)
エラー通知設定 1週間おき

※追加予定

  • 条件付き書式
    セルの値によって、シートに色付けしていきます。
    0(ゼロ)を薄くしたり、数値の大小で赤くしたり。

  • 行・列の固定
    今回の1番の目的です。
    画面をスクロールしても見出しがついてくるように。

  • フィルターを設定
    目的(&気分)によって、自由に並び替えが出来るようにします。

乞うご期待…!

後記

VBAと同じで、動きを目で見てプログラミングしていくのが
結構楽しいです。

それに加えて、GAS(というかG Suite)はWebサービス
簡単に連携できたりするので、達成感2割増し。
初心者の特権かもしれないけど。

会社によっては使えないところもあったりしますが、
上手く使い分けできるよう、どちらもスキル上げていきたいですね。

参照

書籍

組み込みオブジェクトとSheetクラス・Rangeクラスで
度々お世話になっております…!
読むたびに新しいことを知るような気が…

Webサイト

日付を変えれば総合ランキングに早変わり! tonari-it.com

「めとりくす」とか「でぃめんしょん」とかの和訳一覧です。
最終更新がちょっと前ですが、まだ大丈夫そうですね。 ummmummm.hatenablog.com

みんな大好きリファレンス。 developers.google.com

developers.google.com

developer.mozilla.org

最終的に、慣れているindexof()にしましたが、条件が多いなら
正規表現test()が良いみたいです。
iwb.jp

手前味噌ですが…昔の自分、ありがとう。
yuru-wota.hateblo.jp

注釈

*1:レポート名が英語なのは、ほんの少しでも英語に触れる時間を増やしたいから…

*2:PVが同じなら、avgTimeOnPage(ページの滞在時間)でさらにソートします。

*3: 曜日とかは少し混乱するのですが。