前回：消灯当番を決めてくれるSlackのBotを作る① ～とりあえず返事だけしてくれ編～
前回はGASを用いて、Slackの投稿からキーワードを拾い、返事をする機能を作りました。
今回はさらに、スプレッドシートを用いて、電話を取った回数を記録したり、中間結果を見るための機能を追加したいと思います。
#使用する引き金について
前回の記事にも書いた通り、電話を取った回数は、特定のスタンプを投稿した回数を集計していました。今回のSlackBotでも、スタンプを拾って動作するようにしたいと思います。
各スタンプの役割は以下の通りとなります。どれも似たり寄ったりで分かりにくいですが・・・。
「TEL取ってない」とか完全におふざけのつもりだったのになぁ
また、それぞれのスタンプは以下のような名前になっています。（実際には異なりますが、記事用に変えています）
TEL取った → :totta:
TEL取ってない → :tottenai:
TEL取って → :tore:
これらのスタンプを、あらかじめOutgoing Webhookの引き金に設定しておきます。
これでOKです。次は必要な表をスプレッドシートに用意します。
#スプレッドシートに表を用意する
今回はこのような表を用意しました。
user_name：Outgoing Webhookから送られてくる値。詳細は後述
表示名：Botの投稿で用いる名前。Slackで表示されているユーザー名ではない。
回数：各々が電話を取った回数を記録する列。
合計：回数の合計。表計算関数「=SUM(C2:C7)」で集計。
合計の行は、前回で記述した、このルールで必要になります。
4.全員の回数の合計が、1年目社員の総合人数を下回った場合は、電話応対の回数に関わらず全員で抽選。
これでスプレッドシートの用意は終了です。
次はいよいよ、GASで処理を実装します。
#GASの実装
##引き金ごとに処理を分岐させる
前述した通り、スタンプごとに異なる処理をしますが、Outgoing Webhookはその判別をしてくれないので、GASのコード上で分岐する必要があります。
しかし、そもそもコード内でどうやって引き金を見るのか・・・ということになりますが、前回の記事で実装したコードをもう一度見てみます。
function doPost(e){
slack();
}
このdoPostメソッドの引数となっている「e」に、様々な情報が詰まっています。
どんな情報が入っているのかを紹介しますが・・・ちょっと内容が多いので、必要な分だけ紹介します。
{
"parameter": {
"channel_name": "消灯当番",
"user_name": "user.a",
"trigger_word": ":totta:",
"text": ":totta:よ～"
}
}
channel_name：投稿を拾ったチャンネルの名前
user_name：投稿したユーザーの名前
trigger_word：どの引き金を拾ったか
text：投稿内容の文章
それぞれの情報は、以下のように使うことができます。
var trigger = e.parameter.trigger_word;
これで、変数triggerに、「:totta:」という文字列が格納されました。こんな感じです。
では、今回使うGASのコードに、処理の分岐を実装します。
function doPost(e){
// 拾われた引き金を保持
var trigger = e.parameter.trigger_word;
// 引き金ごとに分岐
switch(trigger) {
case ":totta:":
// ここに回数を1増やす処理
break;
case ":tottenai:":
// ここに回数を1減らす処理
break;
case ":tore:":
// ここに中間発表を投稿する処理
break;
default:
// それ以外だった場合は終了する
return;
}
}
これでスタンプごとに処理を分けることができるようになりました。
##GASでスプレッドシートにアクセスする方法
次に、それぞれの処理を実装したいと思いますが、その前に、GASのコード内でスプレッドシートにアクセスする方法を確認したいと思います。
アクセス方法はこちらの記事に詳しく記載されています。参考にさせていただきました。
Google Apps Script で Spreadsheet にアクセスする方法まとめ：
https://qiita.com/negito6/items/c64a7a8589faaffcfdcf
今回はGASとスプレッドシートを紐づけているので、以下の方法を取ります。
var sheet = SpreadsheetApp.getActiveSheet();
これで、変数sheetにスプレッドシートの情報が格納されました。このsheetを用いて、データを取得したり渡したりします。
##データの受け渡し
###セルの指定
スプレッドシートのセルの指定には、getRange関数を使用します・・・が、
このgetRange関数、同じ関数名で種類が複数あり、とてもややこしいです。使い間違えないようにしましょう。
var sheet = SpreadsheetApp.getActiveSheet();
var cell;
// getRange(行番号, 列番号)
// 4行B列（B4）を指定
cell = sheet.getRange(4, 2);
// getRange(開始行番号, 列番号, 指定する範囲の行数)
// C列の2行目から6行まで（C2:C6）の5行分を指定
cell = sheet.getRange(2, 3, 5);
// getRange(開始行番号, 開始列番号, 指定する範囲の行数, 指定する範囲の列数)
// 1行B列から6行E列まで（B1:E6）の6行4列を指定
cell = sheet.getRange(1, 2, 6, 4);
// getRange(セル番号を表す文字列)
// 3行A列（A3）を指定
cell = sheet.getRange("A3");
これらの構文を適宜使い分ける必要があります。難しいですね・・・。
###値の取得
セルを指定できたからと言って、まだ値を取得できたわけではありません。次の2つの場合に応じて、異なる関数を用いて値を取得します。
####1つのセルを指定した場合
こちらの場合はgetValue関数を使います。
var sheet = SpreadsheetApp.getActiveSheet();
// A2のセルを指定
var cell = sheet.getRange(2, 1);
var user_name = cell.getValue();
// user_name == "user.a"
これで2行B列の「user.a」が取得できました。
####複数のセルを取得した場合
複数のセルを指定した場合は、getValues関数を使います。
「s」が付きます。お忘れなく。
var sheet = SpreadsheetApp.getActiveSheet();
// A2:C3を指定
var cell = sheet.getRange(2, 1, 2, 3);
var data = cell.getValues();
// data == [["user.a", "A", 0], ["user.b", "B", 0]]
getValues関数を用いた場合は、値が二次元配列となって返されます。
指定した範囲が1行でも二次元配列となります。ご注意ください。
###セルの値を変更する
スプレッドシートの値を変更するときは、setValue関数を使います。
セルに入力する値は、関数の引数で指定します。
var sheet = SpreadsheetApp.getActiveSheet();
// C2のセルを指定
var cell = sheet.getRange(2, 3);
// C2のセルに1を入力
cell.setValue(1);
また、例によって、複数のセルを指定した場合はsetValues関数を使います。
こちらは、二次元配列を引数にして、入力する値を指定します。
var sheet = SpreadsheetApp.getActiveSheet();
// C2:C7を指定
var cell = sheet.getRange(2, 3, 6);
// C2から、１～６の整数を順に入力
// 二次元配列にしなければならないので注意
cell.setValues([[1], [2], [3], [4], [5], [6]]);
とりあえず、今回使うのはこれくらいでしょう。次は、これらを使って、実際に消灯くんを実装していきます。
##回数を1つ増やす
この処理の流れは、以下のようになります。
:totta:を投稿したユーザーの名前（user_name）を見る
↓
そのユーザー名がある行を探す
↓
その行の回数（C列）を取得
↓
取得した値に1を足して、その計算結果を同じセルに入力
これを順番に実装していきます。
投稿したユーザー名を見る
これは、先述したようにe.parameter.user_nameで見ることができるので、doPost関数の冒頭で、変数を定義します。
ついでに、同期社員の人数や消灯当番の人数などを、定数として定義してしまいましょう。
// スプレッドシート
var SHEET = SpreadsheetApp.getActiveSheet();
// 同期社員の人数
var MEMBER_NUM = 6;
// 消灯当番の人数
var SWITCHER_NUM = 2;
// 表の値の開始行
var ROW_FIRST = 2;
// 項目ごとの列番号
var COL_USER_NAME = 1;
var COL_DISPLAY_NAME = 2;
var COL_COUNT = 3;
function doPost(e) {
var trigger = e.parameter.trigger_word;
var userName = e.parameter.user_name;
switch(trigger) {
...
}
}
...
「命名が下手」などの指摘はご遠慮ください。泣いてしまいます。
そのユーザー名がある行を探す
こちらの処理は、後述する「回数を1減らす」でも使うので、共通で使う関数を定義します。
「指定した値があるセルを探す」という既存の関数があるかもしれませんが、探すのが面倒なので難しいので、シンプルにfor文で探索することにします。
function rowByName(userName) {
// user_nameの列のデータを取得
var names = SHEET.getRange(ROW_FIRST, COL_USER_NAME, MEMBERS_NUM).getValues();
for(var i = 0; i < MEMBERS_NUM; i++){
// user_nameが一致した場合
if(names[i][0] == userName){
// 行番号を返して強制終了
return i + 2;
}
}
}
その行の回数（C列）を取得
↓
取得した値に1を足して、その計算結果を同じセルに入力
こちらの処理は、rowByName関数で返された値（行番号）を利用します。
// 引数：投稿者名
function countUp(userName) {
// user_nameがある行番号
var row = rowByName(userName);
// 回数が入っているセル
var cell = SHEET.getRange(row, COL_COUNT);
// そのセルの値
var count = cell.getValue();
// 回数を1つ増やして、セルに入力
cell.setValue(count + 1);
}
そして、いま定義した関数を、switch文の「:totta:」のcaseに埋め込みます。
function doPost(e){
var trigger = e.parameter.trigger_word;
var userName = e.parameter.user_name;
switch(trigger) {
case ":totta:":
countUp(userName);
break;
case ":tottenai:":
break;
case ":tore:":
break;
default:
return;
}
}
これで、「:totta:」のスタンプが投稿されると、スプレッドシートの値が更新されるはずです。確認は後にして、どんどん実装します。
##回数を1つ減らす
こちらは、「+1」を「-1」にするだけなので、大きな違いはありません。
ただ、回数が0を下回るとややこしくなるかもしれないので、条件を使って回避します。
function countDown(userName) {
var row = rowByName(userName);
var cell = SHEET.getRange(row, COL_COUNT);
var count = cell.getValue();
// 取得した値が0以下の場合は強制終了
if(count <= 0) {
return;
}
// 回数を1つ減らして、セルに入力
cell.setValue(count - 1);
}
先ほどと同様にswitch文に埋め込んでおきます。（割愛）
##現在の中間結果を投稿する
こちらは、スプレッドシートの表示名と回数を全て表示するようにします。
・・・が、その前に、Slackに投稿する関数をイジります。
// 引数：投稿する文章
function slack(text){
var options =
{
"method" : "post",
"contentType" : "application/json",
"payload" : JSON.stringify(
{
"text" : text,
}
)
};
var url = "https://hooks.slack.com/services/TKJG8LZTN/XXXXXXXXX/XXXXXXXXXXXXXXXXXXXXXXXX";
UrlFetchApp.fetch(url,options);
}
前回の記事で実装したものでは、引数を指定せず、関数内に直接、投稿文を書いていました。
今回の変更によって、slack("やっほー")と実行すると、「やっほー」と投稿されるようになりました。わかりやすいですね。
これを使って、中間結果を投稿する機能を実装します。
function postCurrentlyResult() {
// B2:C7（全員分の表示名と回数）のすべての値を取得
var results = SHEET.getRange(ROW_FIRST, COL_DISPLAY_NAME, MEMBERS_NUM, 2).getValues();
// 投稿用の文章
// 1行目に断りを入れる
var text = "現在の中間結果です。\n";
for(var i = 0; i < MEMBERS_NUM; i++){
// 「A:1回」という感じの文を想定
text += results[i][0] + ":" + results[i][1] + "回\n";
}
// 投稿
slack(text);
}
##最終確認
postCurrentlyResult関数をswitch文に埋め込み、countUpおよびcountDownにSlack投稿機能を加え、最終的にできあがったコードが、以下の通りです。（長いので折りたたんでいます）
最終的なコード
var SHEET = SpreadsheetApp.getActiveSheet();
var MEMBERS_NUM = 6;
var SWITCHERS_NUM = 2;
var ROW_FIRST = 2;
var COL_USER_NAME = 1;
var COL_DISPLAY_NAME = 2;
var COL_COUNT = 3;
function doPost(e){
var trigger = e.parameter.trigger_word;
var userName = e.parameter.user_name;
switch(trigger) {
case ":totta:":
countUp(userName);
break;
case ":tottenai:":
countDown(userName);
break;
case ":tore:":
postCurrentlyResult();
break;
default:
return;
}
}
function rowByName(userName) {
var names = SHEET.getRange(ROW_FIRST, COL_USER_NAME, MEMBERS_NUM).getValues();
for(var i = 0; i < MEMBERS_NUM; i++){
if(names[i][0] == userName){
return i + 2;
}
}
}
function countUp(userName) {
var row = rowByName(userName);
var cell = SHEET.getRange(row, COL_COUNT);
var count = cell.getValue();
cell.setValue(count + 1);
// 表示名を取得
var displayName = SHEET.getRange(row, COL_DISPLAY_NAME).getValue();
slack(displayName + "さんの回数を1増やしました。");
}
function countDown(userName) {
var row = rowByName(userName);
var cell = SHEET.getRange(row, COL_COUNT);
var count = cell.getValue();
if(count <= 0) {
return;
}
cell.setValue(count - 1);
// 表示名を取得
var displayName = SHEET.getRange(row, COL_DISPLAY_NAME).getValue();
slack(displayName + "さんの回数を1減らしました。");
}
function postCurrentlyResult() {
var results = SHEET.getRange(ROW_FIRST, COL_DISPLAY_NAME, MEMBERS_NUM, 2).getValues();
var text = "現在の中間結果です。\n";
for(var i = 0; i < MEMBERS_NUM; i++){
text += results[i][0] + ":" + results[i][1] + "回\n";
}
slack(text);
}
function slack(text){
var options =
{
"method" : "post",
"contentType" : "application/json",
"payload" : JSON.stringify(
{
"text" : text,
}
)
};
var url = "https://hooks.slack.com/services/TKJG8LZTN/XXXXXXXXX/XXXXXXXXXXXXXXXXXXXXXXXX";
UrlFetchApp.fetch(url,options);
}
完璧・・・っ！圧倒的完璧・・・っ！
#まとめ
今回は、スプレッドシートにアクセスしながら、電話を取ったときに回数を増やす処理を追加しました。
次回は、毎週の消灯当番を自動で決める処理を実装します。
少々難解なロジックと、GASのトリガー機能（決まった時刻に実行する機能）を使います。
次回⇒消灯当番を決めてくれるSlackのBotを作る③ ～トリガーで決まった時間に当番を発表編～
#参考
【Google Apps Script入門】セルの取得・変更をする
https://uxmilk.jp/25841