Google Apps Scriptを使ってBigQueryのクエリ結果をSlackに投稿する

TRILL開発部の石田です。

delyでは様々な情報をSlackに流して共有しているのですが、今回はTRILLで行っているBigQueryのクエリ結果のSlack投稿について紹介します。

背景

delyでは、透明性を大事にする取り組みとして、経営指標をオープンにSlackに流しています。

参考: dely会社紹介資料 / クラシルに関わるエンジニア・デザイナー募集 / dely - Speaker Deck

経営指標に限らず、アプリのパフォーマンス結果（クラッシュ率や速度など）を開発者だけでなくビジネスチームも含めて確認しています。

課題

TRILLではGoogleAnalyticsやFirebaseを使ってログを取得しています。取得したログの結果は、GoogleAnalyticsとFirebaseの各管理画面から確認することができます。

しかし、欲しい情報を確認するためには管理画面を深く辿らなければならないことがあります。また、もっと細かい粒度で分析するために、rawデータを使いたいときもあります。

そこで、GoogleAnalyticsやFirebaseのrawデータを加工してSlackに投稿することで、簡単に欲しい情報を確認できるようにしました。

やったこと

GoogleAnalyticsやFirebaseをBigQueryに連携し、BigQueryにrawデータを流し、BigQueryのクエリ結果をSlackに投稿するようにしました。 BigQueryのクエリ結果はGoogleスプレッドシートに書き込み、欲しい情報を溜めていくようにしています。

全体像

全体の構成は下図のようになります。

f:id:trill_tech:20200925175442p:plain

まず、Google Apps Script (以下GAS) からBigQueryにクエリを投げ、その結果をスプレッドシートに書き込みます。次にスプレッドシートからデータを取得し、Slackに投稿します。スプレッドシートにはデータが溜まっているので、先週比、先月比など所望の差分データを取り出すことができます。

BigQueryのクエリ結果をスプレッドシートに書き込む

まず、GASからBigQueryにアクセスできるようにする必要があります。メニューの [リソース] → [Googleの拡張サービス] を選択し、BigQueryを有効にします。

以下のコードは、BigQueryのクエリ結果をスプレッドシートに書き込むサンプルとなります。スプレッドシートのセルA1に、 COUNT(*) の結果が書き込まれます。

function runQuery() {
  var projectId = 'GCPのプロジェクトID';
  var sql = '\
    #standardSQL\n\
    SELECT COUNT(*)\
    FROM "BigQueryのテーブル名"';
  var resource = {query: sql};
  var queryResults = BigQuery.Jobs.query(resource, projectId);
  var jobId = queryResults.getJobReference().getJobId();

  while (!queryResults.getJobComplete()) {
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
    Utilities.sleep(1000);
  }

  var spreadsheetId = 'スプレッドシートのID';
  var spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  var sheet = spreadsheet.getSheetByName('シート名');
  sheet.getRange('A1').setValue(queryResults.rows[0].f[0].v);
}

GASから実行するとスプレッドシートとBigQueryのアクセス許可ダイアログが表示され、許可すると対象のスプレッドシートにクエリ結果が書き込まれます。

スプレッドシートの値をSlackに投稿する

以下のコードは、スプレッドシートの値をSlackに投稿するサンプルとなります。実行するとセルA1に書き込んだ値をSlackに投稿します。

function post() {
  var spreadsheetId = 'スプレッドシートのID';
  var spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  var sheet = spreadsheet.getSheetByName('シート名');
  var result = sheet.getRange('A1').getValue();

  var data = {
    'text': result
  };
  var options = {
    'method' : 'post',
    'contentType': 'application/json',
    'payload' : JSON.stringify(data)
  };
  var webhookUrl = 'SlackのWebhook URL';
  UrlFetchApp.fetch(webhookUrl, options);
}