Google スプレッドシートでアクセス解析を自動化

こんにちは、デジタル事業本部の藤田です。

前回の記事で Google スプレッドシートとアドオンを使用して Google アナリティクスのデータを操作する方法について書きましたが、今回はその応用編です。アクセス解析のグラフを定期的に更新し月初に前月分の解析結果 PDF をメールで自動送信する方法について書いていこうと思います。

● レポートを自動的に実行および更新する

アナリティクスアカウントへのアクセス権がある Gmail アドレスで Google ドライブにログインし、対象のスプレッドシートを開きます。せっかくなので「無題のスプレッドシート」から「アクセス解析レポート」へ名前を変更します。

「アクセス解析レポート」へ名前を変更

自動更新のスケジュールを設定するには、メニューバーから [アドオン] > Google Analytics > Schedule Reports を選択します。

Schedule Reports を選択

ダイアログが表示されます。自動更新のスケジュールを設定するには [Enable reports to run automatically.] チェックボックスをオンにします。

スケジュールを設定するダイアログ

スケジュール設定が有効になり、ドロップダウンで頻度と時間を設定することができます。ここでは１日に１回（every day）、午前８時から９時までの間に更新するように設定します。

１日に１回（every day）、午前８時から９時までの間に更新

ちなみに、スケジュールは時間、日、週、または月ごとに実行できるように設定が可能です。

ダイアログの Save ボタンを押すと設定が保存されます。これでデータ取得が定期的に実行され、グラフも自動的に更新されます。

● 当月の計測期間を自動で設定

計測期間は、 Start Date に開始日を、 End Date に終了日を入れることで設定が可能です。 Last N Days に数字をいれておけば過去 N 日分のレポートを取得できます。

計測期間の設定

ですが、どうせなら「当月の」データを取得したいものです。たとえば計測当日が5月17日であれば、開始日：5月1日〜終了日：5月31日といった具合に設定できないものでしょうか？月が変わるたびにいちいち設定を変更するのも面倒な作業です。
Google スプレッドシートは Excel とほぼ同じ関数を使用することができます。ここでは使用PCの現在のシステム日付を返す TODAY 関数を使って当月の月初と月末の日付を取得してみます。

月初の日付

月初は必ず1日なので、MONTH 関数で月を取得し日は1と組み合わせれば月初の日付が作成できます。さらに日付型の文字列を組み立てるために YEAR 関数で年も取得します。この年月日を DATE 関数の引数に指定すれば月初の日付が完成します。

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)

Start Date に当月月初の日付が入る

月末の日付

さて、月末はどうでしょう？月によって日数が違うので決めうちで日を指定することはできません。「ニシムクサムライ」を条件ごとに日数判断するというのもちょっとややこしい作業です。しかも2月はうるう年もあり28日とは限りません。
というわけでこんな方法を思いつきました。当月の月末を無理矢理取得するのではなく、発想を変えて「翌月1日の前日」という考え方で解決します。つまり5月17日であれば、「6月1日の前日」が月末（5月31日）となるという考え方です。月初の日付を取得する方法の応用となります。

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1

End Date に当月月末の日付が入る

ちなみに計測当日が12月のある日だった場合、MONTH 関数は「12」を返すので1を足すと「13」になってしまいますが、DATE 関数は有効な月の範囲にあてはまらない数値は再計算してくれます。つまり =DATE(2017,13,1) と引数を指定しても、ちゃんと「2018/1/1」と日付型の結果を返してくれます。これで、月が変わっても「当月の」データを取得することができます！

● レポートを PDF で書き出してメール送信

さらに、月が変わる前に1か月分のレポートを PDF で書き出すことも可能です。こちらは Google Apps Script にて行います。メニューバーから [ツール] > スクリプトエディタを選択しプロジェクトにコードを書きます。

スクリプトエディタを選択

以下は Google Apps Script でスプレッドシートの任意のシートを PDF に変換しメールを送信するコードです。ここでは2〜6枚目の5シートが対象です。

function emailSpreadsheetAsPDF() {
// 対象となるファイルとシート
var activeSpreadSheet = SpreadsheetApp.getActiveSpreadsheet(); // アクティブなスプレッドシート
var spreadSheetName = activeSpreadSheet.getName(); // スプレッドシートの名前
var spreadSheetId = activeSpreadSheet.getId(); // スプレッドシートのID

// ベース URL
var spreadSheetUrl = "https://docs.google.com/spreadsheets/d/activeSheetID/".replace("activeSheetID", spreadSheetId);

// PDF 書き出しオプション
    + 'exportFormat=pdf&format=pdf' // エクスポート： pdf / csv / xls / xlsx
    + '&size=A4'                    // 用紙サイズ
    + '&fzr=false'                  // true なら各ページに行見出しを含める
    + '&gridlines=false'            // false ならグリッド線なし
    + '&printtitle=true'            // true ならドキュメントのタイトルを含める
    + '&sheetnames=true'            // true ならシート名を含める
    + '&pagenumbers=true'           // true ならページ番号を含める
    + '&fitw=true'                  // true なら幅に合わせる
    + '&portrait=true'              // true なら縦、false なら横
    + '&range=A10%3AJ40'            // 範囲 A10:J40
    + '&gid=';                      // シートID

// （自分のアカウントの）メールアドレス
var myEmail = Session.getActiveUser().getEmail();

// スプレッドシートの名前を件名にする
var subject = spreadSheetName

// メールの本文
var body = 'この PDF は Google SpreadSheet より自動作成しています。' + '\r' + spreadSheetUrl + 'edit';

try{
    var sheets = activeSpreadSheet.getSheets();
    var blobs = [];

    // シートの2から6枚の5シート分を書き出す
    for (var i=0; i<5; i++) {

    // シートごとに書き出す
    var sh = sheets[i+1]; // 最初のシートは省く
    var sheetId = sh.getSheetId();
    var sheetName = sh.getName();

    var fetchUrl = spreadSheetUrl + ext_opt + sheetId;
    // ファイルをUrlFetchで要求
    var fetchOption = {
      "headers" : { Authorization: 'Bearer ' + ScriptApp.getOAuthToken() },  // OAuth
      "muteHttpExceptions" : true 
    };
    blobs[i] = UrlFetchApp.fetch(fetchUrl,fetchOption).getBlob().setName(spreadSheetName + "_" + sheetName + ".pdf");
    // ファイルをドライブに保存
    // DriveApp.createFile(blobs[i]);
  }

  // メール送信を実行
  MailApp.sendEmail(myEmail, subject, body, {attachments : blobs});

} catch(e){
  Logger.log( "ファイル生成に失敗しました" + e);
}
}

・メールの宛先はログインしている Gmail アカウントです。
・ファイルを Google ドライブに保存する場合は52行目のコメントをはずします。

※コードを実行するためには承認（Authorization）が必要です。

承認を確認するダイアログ

● メール送信のスケジュール

定期的にスクリプトを実行するにはトリガーを使います。トリガーは特定のイベントを受け取った時や、特定の時間または定期的な間隔でスクリプトを実行することができる機能です。メニューバーから [編集] > 現在のプロジェクトのトリガーを選択するか、ツールバーの時計のアイコンをクリックします。

現在のプロジェクトのトリガー

ダイアログがあらわれるのでトリガーが設定されていません。今すぐ追加するにはここをクリックしてください。というリンクをクリックします。

トリガー設定ダイアログ

実行でトリガーするスクリプトを選択し、イベントで [時間主導型] または [スプレッドシートから] のどちらかを選択します。この場合スプレッドシートはスクリプトがひも付けされているシートのことを指します。時間またはスケジュールを設定し、保存ボタンを押せば設定完了です。ちなみに、通知をクリックして、トリガー機能が失敗した場合にメールが届くオプションも設定可能です。

毎月１日午前０〜１時の間にメールが送信される設定

これでアクセス解析のグラフ結果 PDF が定期的にメール送信されるようになります。

● 毎月１日午前０〜１時の間のみ前月分を集計させたい

しかし、よく考えると「当月の」データを取得するようにしているため、月が変わると前月1か月分の集計がクリアされてしまいます。この場合は、スプレッドシート上で「日付が１日で午前０〜１時の間」は前月分のデータを取得するようにします。

月初の日付（改）

DAY 関数で日付が１日かどうかを、 HOUR 関数と NOW 関数で０時台かどうかを判断しています。

=IF(AND(DAY(TODAY())=1,HOUR(NOW())=0),DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),DATE(YEAR(TODAY()),MONTH(TODAY()),1))

月末の日付（改）

ここは「翌月1日の前日」のままですが TODAY 関数で取得する日付を上記の月初の日付に変えておきます。

=DATE(YEAR(B5),MONTH(B5)+1,1)-1

これで完成です！翌月の０〜１時は前月分を集計してレポートを作成し、それ以降は当月のデータを更新していきます。

● PDF以外にも

今回の例ではスプレッドシートを PDF に変換して送信しましたが、それ以外にも Excel（xlsx, xls）形式や csv 形式に変換することも可能です。ユニークイベント数をもとにピボットテーブル一覧を作成し、Excel 形式でメール送信なんてこともできます。
解析結果を定期的にグラフでチェックすれば思わぬ気づきが得られることも多々あるので、アイデア次第で自動レポート作成の活用幅は拡がりそうですね。

↓↓↓GA4に対応した記事を公開しました↓↓↓

: Googleスプレッドシートでアクセス解析を自動化【GA4編】

こんにちは、デジタル事業本部の藤田です。 2017年ごろ、タクトシステムのブログでGoogleアナリティクスの記事を公開しました。 Google スプレッドシートで Google アナリティクスを ...