Google Apps Scriptでデータを組織に流通させる：①BigQueryにクエリを叩いてSlackに投稿する

はじめに

せっかくダッシュボードを作ったものの見に行くのが面倒で、だんだん使わなくなるというのは意外とよくあることです。これを防ぐための一つの施策として、ダッシュボード化している情報の一部をコミュニケーションツールに流し込むというのがあります。データの方にいくのが面倒なのであれば、データに来てもらえばよいのです。

今回は、そのための方法として、

• ①BigQueryにクエリを叩いてSlackに投稿する（今回の記事）
• ②Looker StudioのダッシュボードキャプチャをSlackに投稿する（次回の記事）

の2種類を2回に分けて説明します。

これができると、

• シンプルなKPIの把握や、リンクに飛んで詳しく見たいものの導線設計はそれがしやすい①で
• パッと全体像を把握したいものは視覚的に理解しやすい②で

情報を取得できるので、簡易に状況を理解しつつ、より深く調べたければダッシュボード等に移動することもできますし、またその投稿を起点にしてスレッドで議論を深めることもできます。データを組織に流通させる効果的な方法として、ぜひ参考にしてもらえればと思います。

こんな方におすすめ

• 整備したデータがより組織に流通するようにしたい
• Google Apps ScriptでBigQueryのデータを活用したい
• Google Apps ScriptでLooker Studioのダッシュボードを活用したい

取り上げる題材の背景

これまではZapierを利用して、Twitterの特定のキーワードを含むツイートをSlackに投稿していました。ところが、TwitterのAPIが利用できなくなったことで、情報収集の効率が下がっていました。

しかし以前の記事（「初投稿のPVが爆増したのがtrocco®とQiita APIでよくわかった話」「Qiita APIとtrocco®で組織の成果を可視化してみる」）で書いたように、QiitaはAPIを利用して記事データを取得できるので、ではそこから取ってきたデータを利用すればいいのでは、と考えたのが発端でした。

Qiita APIで特定のキーワードを含む記事をtrocco®で取得する

詳しい取得方法については過去の記事を参照していただければと思います。ここでは、キーワード検索をするときのURLだけ説明しておきます。

• https://qiita.com/api/v2/items が最新記事を取得する際のURL
• ここで特定のキーワードに記事を限定したいときは、末尾にquery=keywordと記載する
• つまりtroccoを含む記事データを取得したい場合、https://qiita.com/api/v2/items?query=trocco となる

あとはいい感じにレスポンスのJSONデータをパースすればOKです。取得したデータはBigQueryに格納します。

BigQueryのデータをGoogle Apps Scriptで叩いて、Slackに投稿する

先に完成形をお見せしましょう。下記のコードを実行すると、Slackに次のような投稿がなされます。

このように、記事の冒頭部分がサムネイルになるので、そこを眺めて概要を掴むことができます。さらに詳しく知りたければ、リンクから詳細を確認することができます。

これを実行するためのコードは以下の通りです。

function notifyNewTroccoArticles() {
  const project_id = 'project_id'
  const query = 
      "select '- <' || url || '|' || format_date('[%m/%d] ', date(created_at, 'Asia/Tokyo')) || title || '（' || user_name || coalesce('/' || organization_name, '') || '）>' as text"
    + "from `project_name.dataset_name.table_name`"
    + "where created_at >= timestamp_sub(current_timestamp(), interval 1 day)"
    + "order by created_at"
  const query_results = getDataFromBigQuery(project_id, query)

  if (query_results==null){
    Logger.log('No Articles')
  } else {
    const token = PropertiesService.getScriptProperties().getProperty('SLACK_BOT_TOKEN')
    const channel = PropertiesService.getScriptProperties().getProperty('SLACK_CHANNEL_ID')
    const title = '*【自動投稿】Qiitaの新着trocco関連記事*'
    const text = formatText(title, query_results)
    postSlackMessage(token, channel, text, null)
  }
}


function getDataFromBigQuery(project_id, query) {
  const request = {
    'query': query,
    'useLegacySql': false
  }
  let query_results = BigQuery.Jobs.query(request, project_id)
  const job_id = query_results.jobReference.jobId

  let sleep_time_ms = 500
  while (!query_results.jobComplete){
    Utilities.sleep(sleep_time_ms)
    sleep_time_ms *= 2
    query_results = BigQuery.Jobs.getQueryResults(project_id, job_id)
  }
  Logger.log(query_results)
  return query_results.rows
}


function formatText(title, query_results) {
  let texts = ''
  for (let r of query_results){
    let each_text = '\n' + r.f[0].v
    texts += each_text
  }
  const text = title + texts
  Logger.log(text)
  return text
}


function postSlackMessage(token, channel, text, thread_ts=null) {
  const url = 'https://slack.com/api/chat.postMessage'
  const headers = {
    'contentType': 'application/json',
    'authorization': `Bearer ${token}`
  }
  const payload = {
    'channel': channel,
    'text': text
  }
  if (thread_ts){
    payload['thread_ts'] = thread_ts
  }
  const options = {
    'method': 'post',
    'headers': headers,
    'payload': payload
  }
  const res = UrlFetchApp.fetch(url, options)
  Logger.log(res)
  return res
}

コードの解説

それでは、3つに分けてコードを解説していきます。

BigQueryでクエリを叩く

関連するのは下記のコードになります。

function notifyNewTroccoArticles() {
  const project_id = 'project_id'
  const query = 
      "select '- <' || url || '|' || format_date('[%m/%d] ', date(created_at, 'Asia/Tokyo')) || title || '（' || user_name || coalesce('/' || organization_name, '') || '）>' as text"
    + "from `project_name.dataset_name.table_name`"
    + "where created_at >= timestamp_sub(current_timestamp(), interval 1 day)"
    + "order by created_at"
  const query_results = getDataFromBigQuery(project_id, query)

function getDataFromBigQuery(project_id, query) {
  const request = {
    'query': query,
    'useLegacySql': false
  }
  let query_results = BigQuery.Jobs.query(request, project_id)
  const job_id = query_results.jobReference.jobId

  let sleep_time_ms = 500
  while (!query_results.jobComplete){
    Utilities.sleep(sleep_time_ms)
    sleep_time_ms *= 2
    query_results = BigQuery.Jobs.getQueryResults(project_id, job_id)
  }
  Logger.log(query_results)
  return query_results.rows
}

ポイント

• Google Apps ScriptでのBigQueryの利用には、左側のサービスからAPIをオンにする必要がある
  
• SELECT分がやや複雑だが、これはSlackに投稿するための整形をSQLでやってしまった方が、後々の処理が共通化できて楽になるから
• Slackでは、<url|text>の形でリンクを表示させることができる
• クエリの結果でwhileループを含めているのは、クエリに時間がかかるときにはクエリ結果を取得できるようになるまでラグがあり、jobがCompleteになるまで確認し続けるため

クエリ結果を整形する

関連するのは下記のコードになります。

function formatText(title, query_results) {
  let texts = ''
  for (let r of query_results){
    let each_text = '\n' + r.f[0].v
    texts += each_text
  }
  const text = title + texts
  Logger.log(text)
  return text
}

ポイント

• r.f[0].vの部分は、BigQueryのクエリ結果として取得できるものが独特な構造をしているため
• getDataFromBigQuery()でデータをrowsに限定しているので、それ以下の部分を取得する処理になる
• ちなみにレスポンスとしては次のようなものが返ってくる

{totalRows=1,
 rows=[{f=[{v=- <https://qiita.com/SoySoySoyB/items/c4221a0a000b497448f7|[07/12] 初投稿のPVが爆増したのがtrocco®とQiita APIでよくわかった話（SoySoySoyB/primenumber_inc）>}]}],
 schema={fields=[{name=text, type=STRING, mode=NULLABLE}]},
 jobReference={location=asia-northeast1, projectId=project_id, jobId=job_********},
 totalBytesProcessed=48525,
 cacheHit=false,
 kind=bigquery#queryResponse,
 jobComplete=true
}

Slackに投稿する

関連するのは下記のコードになります。

  if (query_results==null){
    Logger.log('No Articles')
  } else {
    const token = PropertiesService.getScriptProperties().getProperty('SLACK_BOT_TOKEN')
    const channel = PropertiesService.getScriptProperties().getProperty('SLACK_CHANNEL_ID')
    const title = '*【自動投稿】Qiitaの新着trocco関連記事*'
    const text = formatText(title, query_results)
    postSlackMessage(token, channel, text, null)
  }

function postSlackMessage(token, channel, text, thread_ts=null) {
  const url = 'https://slack.com/api/chat.postMessage'
  const headers = {
    'contentType': 'application/json',
    'authorization': `Bearer ${token}`
  }
  const payload = {
    'channel': channel,
    'text': text
  }
  if (thread_ts){
    payload['thread_ts'] = thread_ts
  }
  const options = {
    'method': 'post',
    'headers': headers,
    'payload': payload
  }
  const res = UrlFetchApp.fetch(url, options)
  Logger.log(res)
  return res
}

ポイント

• クレデンシャルの情報はべた書きするのが望ましくないため、スクリプトプロパティに記載する
• Slackではhttps://api.slack.com/apps で事前にアプリを作成し、Bot Token Scopeにchat:writeの権限を付与した上で、投稿したいチャンネルにインストールしておく必要がある
• チャンネルIDは該当チャンネルの設定画面の最下部から取得できる
• 今回は使っていないが、thread_tsを指定するとスレッドへの返信として投稿できる
• SlackのAPIは色々間違っていても一応レスポンスは返ってきてエラーとして処理してくれないので、その点には注意が必要（この点には地味に苦労した）
• ちなみにUserとして投稿は可能であるし、この場合は投稿を削除できるので初期はそれで調整した方がよいが、Userの形では自動投稿の際に自分に通知が来ないので、最終的にはBotにする方がよい

さいごに

これができるといいなと思いつつ、時間がかかりそうかもと考えていたものでしたが、やってみると思いのほか簡単にできました。少し設定しておくだけでデータ活用の拡大につながるので、よろしければ使ってみてください。

参考