Google Apps Script × BigQueryで定期的にSlackにグラフ画像を投稿 #GAS #BigQuery #Slack

ネイティブエンジニアの桐山です。

最近はFlutterアプリを作っていますが今回はネイティブの領域から離れ、「Google Apps Script × BigQueryでSlackにグラフ画像を投稿する」やり方について書いてみます！

できあがるもの

休日、祝日を除く特定の時間に最新のデータで生成されたチャート画像がSlackに投稿されるようになります。

※ 数値は適当です。

なぜやったか

2021年まではRedash公式Slack Botが存在していたため、KPIや利用状況などのチャートをSlackに投稿することは比較的容易でしたが、2022年1月現在そのような類似Botが存在していないため、Slackにチャート画像を投稿するためのハードルが高めです。

KPIをまとめているダッシュボードを毎日見にいくのもイマイチだなと思い、弊社ではGoogleのBigQueryも併用しているため、Google Apps Scriptを使えばもしかして…と思いチャレンジしてみたら思っていたより簡単に実現できました。

cloud.google.com

※ 以降、Google Apps ScriptはGAS、BigQueryはBQと表記します。

仕組み

1. GASからBQでクエリを叩く（スケジュール実行）。
2. GASでBQの結果をスプレッドシートに反映させる。
3. 反映された結果から、GASでグラフを生成。
4. GASで、生成された画像をSlackの特定のチャンネルにメッセージ付きで投稿する。

前提・制限

• GASを記述するスプレッドシートのオーナーアカウントがBQにアクセスできる権限を持っている必要があります。
• 複雑なグラフは生成エラーになる場合があります（後述）。

手順

1. BQでクエリを作成します。
2. スプレッドシートを用意します。
3. 次セクションのスプレッドシートの構造を参考にシートを用意します。
4. SlackBotを作成します。
   1. Slackの管理画面からslack app directoryへ移動。
   2. Botsで検索。
      • 
   3. Slack に追加をクリック。
   4. 表示に従い、必要な情報を入力。
   5. xoxb-12345678-1234567890-hogeのようなトークンはメモしておく。
5. 投稿したいSlackチャンネルの「設定→インテグレーション→アプリを追加する」から、上で作成したBotを追加。
   • 
6. 次々セクションのGASのコードを参考にコードを書く（ほぼコピペで動くはずです）。
   1. コード内の<トークンはこちら>を、上記4-5でメモしておいたトークンに差し替える。
   2. コード内の<Slackチャンネル名>を、上記5で設定したSlackチャンネル名に書き換える。
7. GASコード編集画面の左、サービスからBigQueryを追加。
8. GASコード編集画面の左、ライブラリからSlackAppを追加。
   • ライブラリID1on93YOYfSmV92R5q59NpKmsyWIQD8qnoLYk-gkQBI92C58SPyA2x1-bqで検索。
9. GASを実行して結果を確認。
   • 初回は権限確認が表示されるので許可する。
10. 必要に応じてスケジュール設定を行う。

スプレッドシートの構造

queryシート

	A	B
1	project_id	query
2	<BQのプロジェクトID>	<SQL文>

プロジェクトID

https://console.cloud.google.com/bigquery?project=XXXXXXXのXXXXXXXにあたる部分。

resultシート

結果とグラフが書き出されます。

GASのコード

const slackBotToken = '<トークンはこちら>';

function main() {
    if (_isHoliday()) return;

    const chartImage = _runQuery();
    _postSlackbot(chartImage);
}

function _runQuery() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const querySheet = spreadsheet.getSheetByName("query");
  const resultSheet = spreadsheet.getSheetByName("result");
  const projectId =  querySheet.getRange("A2").getValue();
  const query =  querySheet.getRange("B2").getValue();

  /* 公式（https://developers.google.com/apps-script/advanced/bigquery）コピペ ここから */

  const request = {
    query: query,
    useLegacySql: false
  };
  let queryResults = BigQuery.Jobs.query(request, projectId);
  const jobId = queryResults.jobReference.jobId;

  // Check on status of the Query Job.
  let sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
  }

  // Get all the rows of results.
  let rows = queryResults.rows;
  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, {
      pageToken: queryResults.pageToken
    });
    rows = rows.concat(queryResults.rows);
  }

  if (!rows) {
    console.log('No rows returned.');
    return;
  }

  // Append the headers.
  const headers = queryResults.schema.fields.map(function(field) {
    return field.name;
  });
  resultSheet.appendRow(headers);

  // Append the results.
  const data = new Array(rows.length);
  for (let i = 0; i < rows.length; i++) {
    const cols = rows[i].f;
    data[i] = new Array(cols.length);
    for (let j = 0; j < cols.length; j++) {
      data[i][j] = cols[j].v;
    }
  }  

  /* 公式コピペ ここまで */

  const range = resultSheet.getRange(2, 1, rows.length, headers.length);
  range.setValues(data);

  const charts = resultSheet.getCharts();
  if (charts.length != 0) {
    resultSheet.removeChart(charts[0]);
  }
  
  const chart = resultSheet
      .newChart()
      .addRange(range)
      .setChartType(Charts.ChartType.COMBO)
      .setPosition(1,5,0,0)
      .setOption('title', 'hogeグラフ')
      .setOption('width', 1500)
      .setOption('height', 700)
      .setOption('series', {
        0: {
          color: 'lightskyblue',
          labelInLegend: 'foo数',
          dataLabel: 'value',
        },
        1: {
          color: 'red',
          labelInLegend: 'var率',
          targetAxisIndex: 1,
          dataLabel: 'value',
        },
      })
      .build();
  resultSheet.insertChart(chart);

  const chartImage = chart.getBlob().getAs('image/png').setName('chart.png');

  return chartImage;
}

function _postSlackbot(chartImage) {
    const slackApp = SlackApp.create(slackBotToken)
    const options = {
        channels: '#<Slackチャンネル名>',
        initial_comment: '好きな投稿文',
        title: "chart.png"
    };
    slackApp.filesUpload(chartImage, options);
}

function _isHoliday() {
    const date = new Date();

    const day = date.getDay();
    if (day === 0 || day === 6) return true;

    const id = 'ja.japanese#holiday@group.v.calendar.google.com'
    const calendar = CalendarApp.getCalendarById(id);
    const events = calendar.getEventsForDay(date);
    // なんらかのイベントがある＝祝日
    if (events.length) {
        return true;
    }

    return false;
}

グラフ書き出し部分の説明

const chart = resultSheet
  .newChart()
  .addRange(range) // データが存在するRangeを指定
  .setChartType(Charts.ChartType.COMBO) // 複数種類のグラフをミックス（この例では棒グラフと折れ線）
  .setPosition(1,5,0,0) // 1行目E列を始点として書き出す
  .setOption('title', '好きなタイトル')
  .setOption('width', 1500) // 適当
  .setOption('height', 700) // 適当
  .setOption('series', { // 複数データを表示する際の設定方法
    0: {
      color: 'lightskyblue',
      labelInLegend: 'hoge数',
      dataLabel: 'value', // データラベルをつける設定
    },
    1: {
      color: 'red',
      labelInLegend: 'foo率',
      targetAxisIndex: 1, // 縦方向のグラフが複数ある場合に、0=左側/1=右側に表示する設定
      dataLabel: 'value',
    },
  })
  .build();

単体の棒グラフだとこんな感じ

const chart = resultSheet
  .newChart()
  .addRange(range)
  .setChartType(Charts.ChartType.COLUMN) // 棒グラフ
  .setPosition(1,5,0,0)
  .setOption('title', '好きなタイトル')
  .setOption('width', 1500)
  .setOption('height', 700)
  .setOption('series', {
    0: {
      color: 'lightskyblue',
      labelInLegend: 'hoge数',
      dataLabel: 'value',
    },
  })
  .build();

やってみて

• GASからBQを叩くコードについては公式に記載があったため、まるっとコピペで動きました。
• グラフの書き出しについては、GASのAPIドキュメントに記載がない設定方法があったためかなり時間を使ってしまいました。。
• Charts.ChartType.COMBOについてはGAS側のバグがあるっぽく、「棒グラフ2個/折れ線グラフ1個」を一つのグラフに書き出せませんでした（設定してもなぜか「棒グラフ1個/折れ線グラフ2個」になる）。。
• 日毎に視覚的に数値を追うと新たな気づきも出てきたりして面白いですね！

参考リンク

developers.google.com

developers.google.com

tonari-it.com

stackoverflow.com