MNTSQ Techブログ

リーガルテック・カンパニー「MNTSQ（モンテスキュー）」のTechブログです。

トップ > 社員の健康を支える技術

社員の健康を支える技術

MNTSQ Tech Blog TOP > 記事一覧 > 社員の健康を支える技術

GoogleスプレッドシートとGoogle Apps Scriptで当番表を作る

この記事では、Googleスプレッドシートで当番表を作り、Google Apps Scriptで当番をSlack通知する機能を実装する。

この記事は以下の記事の続編である。 未読の方は先に読んでおくことをお勧めしたいところだが、実はあまり関係が無い。

note.com

西村、サラダ技術顧問に就任するってよ

さて、私はある日サラダ技術顧問に就任する運びとなった。 そこで任されたのが当番表の作成である。 弊社では「黙っていても健康が運ばれくる」という触れ込みの元、サラダのサブスクリプションの会員を増やし、サラダ宅配の労力の低減を図ろうとしている。

これまでは当日の朝にランダムに抽選されており、予定が読めずその時に予定がある人に債権が溜まっていく問題があった。 債権が溜まってもずっと解消されない状態が慢性化しており、抽選されても取りに行かなくても良くなってしまい、あまり精神衛生上良くなかった。 この当番制はそれを解決する試みである。

当番表を作ること自体は難しくはない。 おもむろにGoogleスプレッドシートを開き、日付と当番の列を作り、そこに土日祝を避けて当番を順番に入力していくだけである。

とは言え、Googleスプレッドシートを開いて誰が当番かなど誰も確認しないので、誰もサラダを取りに行かない事態が発生してしまう。 そこでSlack通知をすることになった。 Google Apps Script（GAS）の出番である。

developers.google.com

Googleスプレッドシートのサンプル

サンプルとして、以下のようなGoogleスプレッドシートを利用している。

「当番表」シート
「当番表」シート

「当番リスト」シート
「当番リスト」シート

Slack IDの取得方法

SlackのIDについては、Slackで対象の人の名前をクリックする→「全プロフィールを表示する」→「その他」→「メンバーIDをコピー」から可能だ。

板谷隆平のSlackプロフィールへのリンク

SlackのメンバーIDをコピーする

これが結構めんどくさいのだが、Slackの仕様変更によりSlack IDを利用しないと通知できないようになっている。

スクリプトを書く

スクリプト エディタを開く

通知を導入したいスプレッドシートを開く。 そこからスクリプトエディタを開く。 メニューから「ツール」→「スクリプト エディタ」から開くことができる。

「スクリプト エディタ」を開くメニュー

ちなみにここの画面遷移は微妙で、複数アカウントで利用している場合はうまく遷移できない事がある。 その場合はhttps://script.google.com/d/hogehoge...のhttps://script.google.com/とhogehogeの間にu/0/やu/1/など入れてみると良い。

すると以下のような画面が開いたはずだ。

まっさらな「スクリプト エディタ」

ここからは一応テックブログらしく、コードらしきものを貼っていきたい。

以下の箇所にコードを入力していって欲しい。

function myFunction() {
// この部分
}

Google Apps Scriptでスプレッドシートを読み込む

まずシートを読み込む。

 const spreadsheet = SpreadsheetApp.openById('シートのID')

「シートのID」は、URLの https://docs.google.com/spreadsheets/d/12345asdfghj-ASDFGHJK2345678hjklasdfg/edit#gid=0 となっている「12345asdfghj-ASDFGHJK2345678hjklasdfg」の部分だ。 ハイフンなど挟まっていてダブルクリックで選択できないのが難点である。 次に、読み込んだスプレッドシートから「当番表」シートを開く。

 const sheet = spreadsheet.getSheetByName('当番表')

シートから入力のある最後の行を取得する。

 const lastRow = sheet.getDataRange().getLastRow()

とりあえず一覧をコンソールに出力してみるとしよう。

 for (var i = 2; i <= lastRow; i++) {
   console.log(sheet.getRange(i, 1).getValue())
   console.log(sheet.getRange(i, 2).getValue())
 }

ここまでの全体像は以下のようになる。

function myFunction() {
 const spreadsheet = SpreadsheetApp.openById('シートのID')
 const sheet = spreadsheet.getSheetByName('当番表')
 const lastRow = sheet.getDataRange().getLastRow()
 for (var i = 2; i <= lastRow; i++) {
   console.log(sheet.getRange(i, 1).getValue())
   console.log(sheet.getRange(i, 2).getValue())
 }
}

ここまで書き終えたら、上の「実行」ボタンをクリックしてみよう。

Google Apps Scriptsの「実行」ボタンをクリックする

実行時に小難しい権限確認のウィンドウが表示されるが、「許可」すればOKだ。 すると以下のような実行ログが表示されるはずだ。

Google Apps Scriptsの実行結果

これを参考に、今日の日付の担当者を取得しよう。 JavaScriptなので、Date()クラスが利用できる。

 const nowDate = new Date()

ただ、出力してみると、現在時刻なので00:00:00ではなくてマッチできない事が分かる。

Sun Apr 12 2021 11:39:44 GMT+0900 (Japan Standard Time)

これをマッチさせる方法はいくつかあるが、ここでは文字列にしてしまう事でマッチさせる事にする。

 const nowDateStr = Utilities.formatDate(nowDate, 'JST', 'yyyy/MM/dd')

すると以下のような形式で取得できる。

2021/04/12

それでは先ほどのforループを更新して、当日の当番を取得しよう。

 for (var i = 2; i <= lastRow; i++) {
   const dateVal = Utilities.formatDate(sheet.getRange(i, 1).getValue(), 'JST', 'yyyy/MM/dd')
   if (dateVal === nowDateStr) {
     console.log(sheet.getRange(i, 2).getValue())
     break
   }
 }

実行すると当日の当番が取得できたはずだ。

山田

Google Apps ScriptからSlackに通知する

続いてSlack通知を実装していこう。

「当番リスト」シートからSlack IDを取得する

まず先に、先ほどの当番を変数に格納しておく。

 var toubanUser = null
 for (var i = 2; i <= lastRow; i++) {
   const dateVal = Utilities.formatDate(sheet.getRange(i, 1).getValue(), 'JST', 'yyyy/MM/dd')
   if (dateVal === nowDateStr) {
     toubanUser = sheet.getRange(i, 2).getValue()
     break
   }
 }

次に、当番を取得するのと同じ方法でSlack IDを取得しよう。

 const slackIDSheet = spreadsheet.getSheetByName('当番リスト')
 const slackIDLastRow = slackIDSheet.getDataRange().getLastRow()
 for (var i = 2; i <= slackIDLastRow; i++) {
   const cellVal = slackIDSheet.getRange(i, 1).getValue()
   if (cellVal === toubanUser) {
     console.log(slackIDSheet.getRange(i, 2).getValue())
     break
   }
 }

これで当番が取得できたはずだ。 取得できたら、先ほどと同様に変数に格納するようにスクリプトを更新する。

 var todayToubanID = null
 for (var i = 2; i <= slackIDLastRow; i++) {
   const cellVal = slackIDSheet.getRange(i, 1).getValue()
   if (cellVal === toubanUser) {
     todayToubanID = slackIDSheet.getRange(i, 2).getValue()
     break
   }
 }

SlackのIncoming Webhook URLを発行する

取得した情報をベースに、Slackに通知していくのだが、そのためにはIncoming Webhook URLを発行する必要がある。

Slackの左上から、「その他管理項目」→「アプリを管理する」

Slack Appを開く

するとSlack App Directoryが表示されるので、「カスタムインテグレーション」をクリック。

Slackのカスタムインテグレーションを開く

ここでIncoming Webhookを追加するのだが、弊社は追加設定済みのため、以下を参考に追加いただきたい。

slack.com

なお、Incoming Webhookは現時点で非推奨で、Slack Appの利用が推奨されているのだが、まだ移行ができていないため、ご了承いただきたい。

「Slackに追加」をクリックする。

Incoming WebhookをSlackに追加

投稿したいチャンネルを選択して、「Incoming Webhookインテグレーションの追加」をする。

Incoming WebhookをSlackに追加

すると以下のようにWebhook URLが発行されるので、それを利用する。

Incoming WebhookをSlackに追加

Slack通知を実装する

必要情報が揃ったので、Slackへの通知を実装していこう。 まず先ほど取得したWebhook URLと投稿先のSlackチャンネルを定数として宣言する。

 const SLACK_WEBHOOK_URL = 'https://hooks.slack.com/services/HOGE/FUGA/PIYO
 const SLACK_CHANNEL = '#touban'

Slackに送信する文字列を組み立てていく。 まず表示する文字列を作る。

 var msgStr = ''
 if (todayToubanID !== null) {
   msgStr = "今日の当番は <@" + todayToubanID + "> です！\n"
 }

ポストするチャンネルを含んだJSONを組み立てる。

 const jsonData = {
   "text": msgStr,
   "channel": SLACK_CHANNEL
 }
 const payload = JSON.stringify(jsonData)

Google Apps Scriptでメソッド渡す際に必要な諸情報を含めたハッシュを作る。

 const options = {
   "method": "post",
   "contentType": "application/json",
   "payload": payload
 }

それをWebhook URLにPOSTする。

UrlFetchApp.fetch(SLACK_WEBHOOK_URL, options)

書けたら実行してみよう。 実行する際に追加の権限を求めるダイアログが表示されるので、「許可」する。 すると、以下のような投稿がSlackに表示されたはずだ。

当日の当番がGoogle Apps ScriptからSlackに通知された

おめでとう、これで実装は完了だ。

スクリプトの全体像

全体としては以下のようなスクリプトになったと思われる。

function myFunction() {
 const spreadsheet = SpreadsheetApp.openById('シートのID')
 const sheet = spreadsheet.getSheetByName('当番表')
 const lastRow = sheet.getDataRange().getLastRow()
 const nowDate = new Date()
 const nowDateStr = Utilities.formatDate(nowDate, 'JST', 'yyyy/MM/dd')
 
 var toubanUser = null
 for (var i = 2; i <= lastRow; i++) {
   const dateVal = Utilities.formatDate(sheet.getRange(i, 1).getValue(), 'JST', 'yyyy/MM/dd')
   if (dateVal === nowDateStr) {
     toubanUser = sheet.getRange(i, 2).getValue()
     break
   }
 }
 
 const slackIDSheet = spreadsheet.getSheetByName('当番リスト')
 const slackIDLastRow = slackIDSheet.getDataRange().getLastRow()
 var todayToubanID = null
 for (var i = 2; i <= slackIDLastRow; i++) {
   const cellVal = slackIDSheet.getRange(i, 1).getValue()
   if (cellVal === toubanUser) {
     todayToubanID = slackIDSheet.getRange(i, 2).getValue()
     break
   }
 }
 
 const SLACK_WEBHOOK_URL = 'https://hooks.slack.com/services/HOGE/FUGA/PIYO'
 const SLACK_CHANNEL = '#touban'
 
 var msgStr = ''
 if (todayToubanID !== null) {
   msgStr = "今日の当番は <@" + todayToubanID + "> です！\n"
 }
 const jsonData = {
   "text": msgStr,
   "channel": SLACK_CHANNEL
 }
 const payload = JSON.stringify(jsonData)
 const options = {
   "method": "post",
   "contentType": "application/json",
   "payload": payload
 }
 UrlFetchApp.fetch(SLACK_WEBHOOK_URL, options)
}

定期実行を仕掛ける

最後に忘れてはならないのが、定期実行である。 毎朝手動で誰かが実行ボタンを押すのは避けたいからだ。

まずスクリプトエディタの左メニューから「トリガー」をクリックする。

Google Apps Scriptのトリガーの設定画面に移動

続いて右下の「トリガーを追加」をクリック。

Google Apps Scriptにトリガーを追加する

以下のように設定した:

  • 実行する関数を選択: myFunction
  • デプロイ時に実行: Head
  • イベントのソースを選択: 時間主導型
  • 時間ベースのトリガーのタイプを選択: 日付ベースのタイマー
  • 時刻を選択: 午前9時〜10時
  • エラー通知設定: 今すぐ通知を受け取る

Google Apps Scriptのトリガーの設定画面

動作確認はメソッドの日付やトリガーの時刻を編集して行うと良いだろう。

機能拡張編

事前に通知する

初期実装としては上記のとおりだが、以降も地道な改善が成されている。 当日の朝に当番が判明したところで、予定が入ったなどの場合に対応ができないため、前日に通知されることになった。 部分のみだが、次の日を取得するには、以下のようにする。

var dt = new Date()
dt.setDate(dt.getDate() + 1)

複数人への通知に対応する

このスクリプトは様々な当番がある箇所に流用されている。 弊社ではブログポストを当番制で回しており、来週は誰が書くのかという通知に利用している。 また、隔週でデプロイを行っており、その担当の通知にも利用している。

デプロイは複数人で担当して行っているため、複数人に対してメンションをする必要がある。その場合は、以下のように配列などに代入すれば良い。

     toubanUser = sheet.getRange(i, 2).getValue()
     // ↓
     slackIDLastCol = slackIDSheet.getDataRange().getLastRow()
     const toubanUsers = new Set()
     for (var j = 2; j <= slackIDLastCol; j++) {
       const val = sheet.getRange(i, j).getValue()
       if (val !== null && val !== '') {
         toubanUsers.add(val)
       }
     }

Slack IDの検索も同様にループ処理をする。 そしてSlackに送信するメッセージ文字列の組み立てをループ処理に変更すると良い。

 var msgStr = '来週のデプロイ担当は'
 for (i = 0; i < nextWeekToubanIDs.length; i++) {
   msgStr += " <@" + nextWeekToubanIDs[i] + "> "
 }
 msgStr += 'です！'

スプレッドシートで当番を管理する際の問題点

色々便利なGoogle Apps Script（GAS）とスプレッドシートだが、問題が無いわけではない。 スプレッドシートは別のスプレッドシートのシートを参照できないので、このような使い方をした場合、Slack IDのシートが通知したいスプレッドシートの分だけ増殖してしまう。 この問題に対する解決策は現状見つけられていない。

このエントリーで対応していないポイント

このエントリーで解説していない部分があるので、まとめておく。ぜひともトライしてみて欲しい。 Qiitaなどで記事にしていただいても良いだろう。

Slack Appへの移行

先にも述べた通りだが、カスタムインテグレーションは非推奨となっており、そのうち利用できなくなる可能性がある。 それを回避するためにはSlack Appへの移行をする必要がある。

当番表で設定した日数を過ぎた場合の対応

設定した最後の日の担当まで来るとサイレントに通知が来なくなる。これを回避するためには、最後の日を検知して「そろそろ設定が無くなりそうだ」と通知すると良い。

当番を自動で決める処理を入れる

基本的に順番に割り振っていくことが予想されるので、切れれば自動的に補充されるような処理も検討できるだろう。 そこで問題になってくるのが祝日の処理だろう。

土日の通知を避ける

1日前の通知だと、つまり月曜日のサラダ当番は日曜日に通知されてしまう。 これを回避するためには、土日を判定してその次の日をピックする必要があるだろう。

仲間募集中

弊社ではこのように、日常の些細な作業にもITエンジニア的なアプローチで改善を試みている。 ローマは一日してならず、運用改善も同様に地道な努力の積み重ねなのである。 弊社はこういった地道な改善ができるエンジニアを募集中である。

hrmos.co

hrmos.co

hrmos.co

hrmos.co

なお#saladチャンネルで利用しているBROS TOKYOのサラダもオススメだ。 1ヶ月契約して毎日食べ続けると800円弱というお手頃価格で、結構ボリュームのあるサラダを頼めて、好きなトッピングを3点選べて、体にも良いのでオススメだ。ちなみに私は契約していない。

BROS TOKYO

この記事を書いた人

Yuki Nishimura

雑食系エンジニア

記事一覧へ