フォトラクション採用責任者の野田です。アドベント・カレンダー18日目の記事として、「SlackでValuesスタンプが誰のどんな投稿に押されているのか数えて、獲得数が多い人を表彰できるようにしてみた」というネタを思いつき、Botを作ってみました。
GASなどは使わずノーコードで作成できるので、世の中のValues定着に頭を悩ませている人事担当の方のお役に立てれば嬉しいです！
Valuesのスタンプを数えようと考えた背景
フォトラクションは7月にValuesの再策定を行いました。これまで書籍を題材にしてValuesの元となる価値観の勉強会をしたり、締め会ではValuesの体現者を表彰するなど、組織にValuesを定着させるために連続的に施策を行ってきました。
結果として、Valuesを目にする頻度が増えて慣れてきた部分はあると思います。
しかし、Valuesは日々の業務の中で使われてこそ意味があります。そこで、日々のSlackの中で誰のどんな行動がValuesの体現として称賛されているのか可視化出来ないかと考えました。
また、スタンプが集まると称賛されるらしいという文化を作ることで、積極的にValuesを体現しよう、仲間のValuesを体現した行動を称賛しようという風にベクトルが向かうと良いなとも思っています。
可視化のためのアプローチ
- Zapierを利用しValuesのスタンプをトリガーとしてスプレッドシートにデータベースを構築する
- 関数を駆使してデータベースを整形し、スタンプを獲得している数が多い人と投稿を毎月自動的にソートする
- Slackのワークフロービルダーを使い、スプレッドシートから取得した情報を取り込んだ文章を、任意のチャンネルに日付をトリガーとして投稿する
アウトプットのイメージ
こんなポストを毎月指定の時期に自動投稿する
Zapierを利用しValuesのスタンプをトリガーとしてスプレッドシートにデータベースを構築する
（1）Slack上で押されたValuesのスタンプをトリガーに設定する
みなさんZapierはご存知でしょうか。ノーコードで様々なアプリと連携して自動化をしてくれる便利なサービスです。ZapierでSlackとGoogleスプレッドシートを連携させてまずはデータベースを作成します。
①タイムスタンプ②押されたスタンプ③スタンプを獲得した人④スタンプが押された投稿の4つが格納されていれば今回の目的は果たせるため、指定されたスタンプ（今回は各Valuesのスタンプ）が押されると、上記4つの情報をスプレッドシートにセル追加するように設定をします。
NewReactionAddedをトリガーとして設定します。
トリガーに設定するスタンプを設定します。画像では最速挙動のスタンプを設定していますが、後ほど全てのスタンプで設定をする必要があります。
この時、チャンネルを指定すれば任意のチャンネルのみで集計、指定しなければ全チャンネルで集計となります。
例えばValues用のチャンネルを作って公式に「このチャンネルでのスタンプは集計して表彰に利用します」とアナウンスすることで、そのチャンネルに注目を集めるやり方もあると思います。今回は全てのチャンネルで集計しています。
これにてトリガーの設定は完了です。
（2）必要な情報をスプレッドシートに送信し、データベースを作成する
（1）でトリガーを設定したら、今度はスプレッドシートに送信する設定です。+のボタンを押してステップを追加してGoogle スプレッドシートを選択します。先程のデータをスプレッドシート上に追加したいので、アクションは「2. Create Spreadsheet Row in Google Sheets」を選択してください。
手順に沿ってアカウントを設定して、データを蓄積するシートを選択します。（シートは事前に作成しておきます）送信するデータは以下の4つです。
1.Message User Name
2.Message Ts
3.Reaction
4.MessageText
以上でZapierの設定は完了です。これでSlackに指定されたスタンプが押される度にスプレッドシートにデータが飛ぶようになりました。
関数を駆使してデータベースを整形し、スタンプを獲得している数が多い人と投稿を毎月自動的にソートする
続いて、データベースを整形してSlackに自動投稿を飛ばす準備をします。ここまでで、スプレッドシートには以下のようなデータベースが出来ています。
方針としてはシンプルにCOUNTIFS関数を使って当月に追加されたデータを数えて、Rank関数で順位付けしQuery関数を使って抜き出します。
大きく以下の手順です。
【下準備】
①元データを参照し、編集用のデータベースを作成する
②COUNTIFS関数で拾えるようにタイムスタンプを変換する
【スタンプを集めた人を集計する】
③COUNTIFS関数で当月に獲得したスタンプの獲得数を数える
④Rank関数でランク付けする
⑤Query関数で1位の投稿を集める
【スタンプを集めた投稿を集計する】
⑥Query関数で当月にポストされた投稿を抜き出す
⑦UNIQUE関数で⑥を列挙し、COUNTIFS関数で集計し、Rank関数でランク付けする
⑧Query関数で1位の投稿を集める
※⑦と⑧は上と同じ手順です
【下準備】
Zapierから飛ばしてきたシートに関数を事前に入れておいても、最下部にセルが追加されたタイミングで下部の関数が削除されてしまうため、編集用のシートを用意します。
シンプルに（='DB'!）でセルを参照するだけだと同期性が悪く、毎回セルを上書きしないとデータが最新に更新されなかったので、より同期性が強いIMPORTRANGE関数を使います。
一番左上のセルに関数を入力すると、指定したURLの指定したシートの情報を自動で同期してくれます
任意のスプレッドシートのシートをまるっと参照できる便利な関数で、シートごとに権限を分けたいときなどに役立ちます。（詳しい構文などは既出の記事に譲ります）
続いて、Zapierで飛ばしたタイムスタンプですが、このままだと何のことかわからないためyyyy/mm/ddの形に置き換えます。これは後ほどCOUNTIFS関数で拾うために必要な処理です。
タイムスタンプのままだと1636934988等の数字の羅列ですが、処理を入れると2021/10/25という日付になります。
詳細かっ飛ばしますが、=(B2+32400)/86400+date(1970,1,1)という処理を入れてください。こうすることでタイムスタンプが現在の日付に変換されます。詳しくはこちらの記事を。
後ほどの行程でCOUNTIFS関数を利用して当月の投稿を集計するのですが、その際に各投稿の年と月を条件で設定する必要があるため、yyyy/mm/ddに変換したら年、月の列をつくってYear関数とMonth関数でそれぞれ月と年を抜き出しておいてください。
こんな感じになります。空白がエラーになるのを防ぐためにIF関数を入れているためわかりにくいですが、シンプルにYEAR関数とMONTH関数を使っているだけです
【スタンプを集めた人を集計する】
続いて当月にスタンプを集めた人の集計を行います。ここまでくればあとは結構シンプルです。集計用の新しいシートを作り、オーソドックスに一番左側の列に集計したい人名、一番上のセルに集計したいスタンプ名を設定して、COUNTIFS関数でDBから数えていきます。
こんな感じです。
以下2点ワンポイントです。
①UsernameはUNIQUE関数で自動取得させる
人名の列挙は自動的に行いたいので、DBのUsernameの列をUNIQUE関数で検索させると良いと思います。こうすることで、新しいユーザーが追加されても自動的に一番左の列に追加されるので手動を挟む必要がなくなり自動BOT化できます。
②日付はtoday関数を参照させる
このシートのそもそもの目的は、毎月任意の日付になったらSlackのワークフローを使って自動投稿をさせることです。したがって、Slackに投稿する時の日付でカウントされている必要があります。
そこで、適当な場所にtoday関数で現在の日時を表示しておき、year関数やmonth関数を使って現在日時を表示し、COUNTIFS関数で条件を拾うときに、ここの日付を拾うようにしましょう。当月ではなく前月の数字を集計したい場合は=MONTH(TODAY())-1 のようにしておけばOKです。
【スタンプを集めた投稿を集計する】
先程は一番左の列をusernameにしてCOUNTIFS関数で集計しましたが、今回は一番左の列を投稿にして集計していきます。（その先でやることは同様なので省略します。）
投稿文を一番左の列に自動で集めるやり方として、今回はQuery関数を利用します。
Query関数はデータベースの中から「○列の値がXXの時、△列の値を返す」といったことができる便利な関数です。例えば先程のデータベースから「F列が2021年、G列が12月のとき、投稿のセルの値を返す」ということが出来ます。
このQuery関数にUNIQUE関数を加えることで当月に投稿されたポストの原文を重複なく列挙することが出来ます。
Query関数の構文は自由度が高く長くなるので、こちらの記事をご参照ください。
最後にRank関数で数えたスタンプの数を順位付けします。
【Slackに投稿できる形に整える】
この後の行程で、Slackのワークフロービルダーを活用して投稿文を作るのですが、その際に参照できる形にシートを整えましょう。
一番上のセルに順番に項目を記載して、その下に各項目の一番のデータを返しておきましょう。前までの行程で作ったシートをQuery関数で検索させるのが一番楽だと思います。
Slackのワークフロービルダーを使ってスプレッドシートの値を取得し、自動投稿する
ここまできたら後は簡単です。Slackにはワークフロービルダーという機能があり、様々なものをトリガーに自動投稿するBotを作ることができます。
今回は「毎月最初の月曜日」をトリガーとして、ここまでのプロセスで作成してきたスプレッドシートから値を取得し、文中に挿入して自動投稿文を作成しました。
雷のマークからワークフロービルダーを立ち上げて
参照するスプレッドシートや参照したいセルを選択します
すると、スプレッドシートから取得した値を変数として文中に挿入できるようになるので、挿入しながら文章を作成します。
保存したら完成です！
これで毎月決まった時期がやってくると、前月に各Valuesスタンプを最も獲得した人と投稿を調べて自動投稿してくれるbotが完成しました。
ノーコードでも簡単にBOTは作れる！
Zapierを利用することで簡単にデータベースを作成することができるので、多少スプレッドシートの作法に慣れている人であればアイディア次第で簡単にこのようなBOTを作ることが出来ます。
ちなみに、Zapierの無料プランではなんと月に100回までしかスタンプを数えてくれないので、本格導入される方はお気をつけください,,,（約2,000円で750回、5,000円で2,000回数えられるので許容範囲かなと思います）
フォトラクションのValuesが気になるよという方は是非以下の記事もご覧ください！
それでは！
株式会社フォトラクションでは一緒に働く仲間を募集しています
