GASを使ってGoogle Analyticsのデータを日々Slackに飛ばしています。
毎日自分のデータを見ることが出来て結構楽しいですね。
yuru-wota.hateblo.jp
だんだん欲が出てきたので、今度は「総合ランキング」シートを作ってみました。
ただ、これはこれで良いですが、下の方まで行くと見出しが見られないのです。
行や列を固定してみると、再Runの時にエラーになります。
「だったらGoogle Analyticsで見なさい」と言われそうですが、
ちょっと重いし…あんまり見た目いじれないし…ちょっと不便だなぁ…
あと、空いたセルにちょっとした関数を足そうと思っても
翌朝には初期化されます。(悲)
そんなわけで、せっかく作ったのに使えなくなってしまうのは困るので、
他のシートに転記することにして運用したいと思います。
今日のコードを使うと…
こんな感じになります。
「最低限、見られる」感じかなと思います。
完成イメージ
最終的にこんな感じを目指します。
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に慣れていない方へザックリ流れをご説明すると、こんな感じです。
- レポートの値をまとめて取得
- 値を少し調整
- 前日分として別のSpreadsheetに転記
- シートの書式や表示を調整
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
最終的に、慣れているindexof()
にしましたが、条件が多いなら
正規表現とtest()
が良いみたいです。
iwb.jp
手前味噌ですが…昔の自分、ありがとう。
yuru-wota.hateblo.jp