はじめに

はじめまして、こんにちは。決済認証システム開発事業部の杉本と申します。

事業部では、キャッシュレス・カードレスな決済システムと、それを利用するために前提となるユーザー認証システムを主たる対象として、サービスおよびアプリケーションを設計・開発・運用しております。

私個人としては、この他に会社の労働環境を改善するプロジェクトにも参画しており、今回の記事では、そこで提議された問題に対して開発したプログラムを紹介いたします。

開発の経緯

問題

弊社では、毎日18時に共同ゴミ箱のゴミ出しを行っており、そのために毎日4-5人の社員が「掃除当番」として割り当てられています。

日々の当番はなるべく複数の部署で構成されるようにしており、10分程度ではありますが、業務内ではなかなか話さない他部の社員と、交流を持つ機会としての側面もあります。

しかしこの当番、忘れがちなのです。

ゴミの量に対して作業者の頭数が減ってしまうと、当然ながら一人あたりの負担が増えてしまいます。最悪、全員が忘れてしまうと、ゴミが溢れます（集積場はビルの共有エリアにあり、時間が遅くなると閉鎖されてしまうので、ゴミを出すことができなくなります）。

そこで、「掃除当番を忘れないように対策を考えたい」と、改善プロジェクトに提案がなされました。

解決策

当日17時頃に、担当者にメンションしてSlackで通知する。

これまで、掃除当番はNAS上のExcelファイルで担当者が管理し、そこから手作業で社内グループウェアに予定登録していました。当日の朝に「あ、今日当番なんだ」と確認していても、18時になると別のタスクに意識がとられていることもあります。

残念ながら、社内グループウェアからSlackへの連携はできなさそうだったので、それが可能なGoogle Apps Script（GAS）を利用してのSlack通知を検討しました。

社内ではG Suiteも利用しているので、この際、掃除当番の管理もGoogleスプレッドシートに移行し、そこで当番の生成もできれば担当者の省力化につながるのでは、と考えました。

開発

設計

検索してみると、GMOさんの技術ブログに詳しい記事がありました。

techblog.gmo-ap.jp

こちらを参照させていただき、今回の設計は以下のようにいたしました。

Googleスプレッドシートで日毎に行を持つ掃除当番表を作成
別シートで社員リストとSlackUserIDを管理
当日の日付で掃除当番表を検索し、対象の社員のSlackUserIDを取得してSlackメンションを作成、Slackに投稿するスクリプトを開発
開発したスクリプトを毎日17時頃に実行するトリガーを設定

ついでに、社員リストから掃除当番を生成するスクリプトも作成し、スプレッドシート上のボタンから実行できるようにします。

用意するもの

Googleスプレッドシート

社員リスト（employee_info）

employee_id	family_name	first_name	family_name_pronounce	first_name_pronounce	section_key	slack_user_id

section_keyは所属部署を示す値です。

slack_user_idは一人ひとり手作業で確認したので大変でした……。

祝日リスト（holidays）
掃除当番表

date	member_1	member_2	member_3	member_4	member_5

dateは営業日のみを記載するため、最初のレコードに起点となる日付を入力したら、あとは以下の関数で営業日を展開します。

=WORKDAY($A$2,row()-2,holidays!$A$2:$A$30)

member_nには社員リストのemployee_idが入ります。

Google Apps Script

大きく分けてふたつです。

掃除当番をSlackに通知するスクリプト（postSweeperList）
社員リストから掃除当番を生成し当番表に記録するスクリプト（makeSweeperList）

Slack

通知用チャンネル
Incoming Webhookアプリ

アプリ作成時に提示されるWebhook URLは保存しておきましょう。

実装

それではGASを書いていきましょう。

外から渡される入力値は現状ありませんが、バリデーションやトークンの照合などのセキュリティ記述は省略します。

当日の掃除当番を取得してSlack通知

通知用チャンネル・Incoming Webhookアプリを用意したら、アプリを指定してPOST通信を投げる機能を作ります。

// 渡されたtextをSlackに投稿する
function postSlack(text){
  var url = "https://hooks.slack.com/services/[Incoming Webhook作成時に提示されるURL]";
  var options = {
    "method" : "POST",
    "headers": {"Content-type": "application/json"},
    "payload" : '{"text":"' + text + '"}'
  };
  UrlFetchApp.fetch(url, options);
}

次に、これを利用するための値……つまり「当日の掃除当番に対するメンション」を作成する機能を用意します。

Slackのメンションを作成するには、各ユーザーのシステムID（≠アカウント名、表示名）が必要になります。

qiita.com

// 当日の掃除当番をSlackに通知する
function postSweeperList(){
  // 取得用シートを設定
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetSweeper = ss.getSheetByName(getDate(new Date(), 'yyyy'));

  // 掃除当番=メンション対象を格納する配列を用意
  var mentions = [];
  
  // 当日の掃除当番を記録した行を取得
  var targetRow = findRow(sheetSweeper, getDate(new Date(), DATE_FORMAT), 1, true);
  if (targetRow == 0) {
    // 休日など、当番表にない日付にプログラムが実行された場合には何もせず終了する
    return;
  }
  // [Date, member_1, member_2, member_3, member_4, member_5]
  var targetList = sheetSweeper.getRange(targetRow, 1, 1, sheetSweeper.getLastColumn()).getValues()[0]; 

  // 当番のemployee_idから、employee_infoに記録したSlackUserIDを取得して配列に格納する
  var sheetEmployee = ss.getSheetByName('employee_info');
  for(var j=1;j<targetList.length;j++){
    if (targetList[j] != '') {
      var memberRow = findRow(sheetEmployee, targetList[j], 1);
      if (memberRow != 0) {
        var memberInfo = sheetEmployee.getRange(memberRow, 1, 1, sheetEmployee.getLastColumn()).getValues()[0];
        mentions[mentions.length] = '<@' + memberInfo[6] + '>';
      }
    }
  }

  // SlackUserIDの配列を展開してPOSTする文章を作成し、Slackに通知
  postSlack(mentions.join(' ') + '\r\n今日のお掃除当番です！');
}

外に出しているUtil的メソッドは以下になります。

getDate

Dateクラスの日付データについて、タイムゾーンとフォーマットを合わせるために利用しています。

findRow

シート・探索値・探索カラムを指定して、行番号を返却するメソッドです。

以下の記事で紹介されている作り方をほぼそのまま拝借していますが、日付で検索する場合は型を揃える必要があるため、第4引数にフラグを持たせています。

tonari-it.com

// 日付の型
var DATE_FORMAT = "yyyy/MM/dd";

// 日時情報を指定のフォーマットに直す
function getDate(date, format){
  return Utilities.formatDate(date, 'Asia/Tokyo', format);
}

// スプレッドシートから特定の値を含む行の番号を取得
// https://tonari-it.com/gas-spreadsheet-find/
function findRow(sheet, val, col, isDate){
  var dat = sheet.getDataRange().getValues();
  
  for(var i=1;i<dat.length;i++){
    // 日付で検索する場合は、スプレッドシートの日付セルと型を合わせる必要がある
    if(isDate){
      if(getDate(new Date(dat[i][col-1]), DATE_FORMAT) === getDate(new Date(val), DATE_FORMAT)){
        return i+1;
      }
    }else if(dat[i][col-1] === val){
      return i+1;
    }
  }
  return 0;
}

最後に、このプログラムが毎日17時頃に実行されるよう、プロジェクトのトリガーを設定します。

これで、毎日17時頃になると、以下のような通知が設定したチャンネルに飛ぶようになります。アプリ名称や文面はお好みで。

※手動実行した際の記録のため、実行時刻はずれています。

社員リストから掃除当番を生成し当番表に記録

せっかくスプレッドシート上に対象者のリストを用意したので、今後はプログラムで当番の割当が行えるよう、「employee_idの当番リストを当番表の未定部分に埋めていく」スクリプトを作成します。

選出は以下のポリシーで行います。

一日の掃除当番は基本4名、ただし4で割ると余ってしまう場合は、解消するまで5名を割り当てる
一日の掃除当番の過半数が同じ部署の人間になった場合は再抽選する（社員交流の機会とするため）
- ただし、部署によって人数に偏りがあるので、3回抽選しても解消されない場合は仕方ない偏りと判断して先に進める
生成したリストを、当番表の当番未定日の先頭行から流し込む

そして、このスクリプトの実行ボタンをスプレッドシート上に配置して、ボタンひとつでこの先の掃除当番が生成できるようにいたします。

既存シートに余計な記述を増やしてgetLastRow()などの値がずれることを危惧して、流し込みの開始日は未定日の先頭といたしましたが、日付指定用のセルを用意すれば、指定の日付から流し込むように作ることも可能です。

qiita.com

// 一日に割り当てる社員の人数
var NUMBER_ASSIGNEE = 4;

// 社員リストから掃除当番を生成する
function makeSweeperList(){
  // 社員リストを取得し、シャッフルする
  // [[employee_id,family_name,first_name,family_name_pronounce,first_name_pronounce,section_key,slack_user_id],...]
  var listEmployee = shuffle(getEmployeeList());
   
  // 記録用の配列を作成
  var listSweep = [];
  
  // 抽選する
  var i = 0;
  while(0 < listEmployee.length){
    // 一日の割当人数を全うした場合に余りが発生する場合は、余りが解消するまで割当人数を1人増やしておく
    var tmpNumAssignee = (0 < (listEmployee.length % NUMBER_ASSIGNEE)) ? NUMBER_ASSIGNEE+1 : NUMBER_ASSIGNEE;

    // 当日の当番が特定の部署に偏らないように抽出する
    var imbalanced = true;
    checkbalance: for(var j=0;imbalanced;j++){
      var counts = {};
      for(var k=0;k<tmpNumAssignee;k++){
        // 先頭から割当対象にする(後でshiftして実際に抽出する)
        // https://qiita.com/y-temp4/items/76ba73831268358c88d8
        var sectAssigned = listEmployee[k][5];
        counts[sectAssigned] = (counts[sectAssigned]) ? counts[sectAssigned]+1 : 1;

        // 過半数が同一部署だった場合は不均衡と見做して再抽選
        if(Math.ceil(NUMBER_ASSIGNEE / 2) < counts[sectAssigned]){
          // 抽選は3回まで、最終周は再抽選しない(特定の部署が終盤に残っている場合、無限ループになるため)
          if((j+1 < 3) && (tmpNumAssignee < listEmployee.length)){
            listEmployee = shuffle(listEmployee);
            continue checkbalance;
          }
        }
      }

      // 全員格納した状態で不均衡がなければ処理を抜ける
      imbalanced = false;
    }

    listSweep[i] = [];
    for(var k=0;k<tmpNumAssignee;k++){
      var tmpInfo = listEmployee.shift();
      listSweep[i][k] = tmpInfo;
    }
    i++;
  }
  
  // 当番表から担当が割り当てられていない最初の行を取得
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetSweeper = ss.getSheetByName(getDate(new Date(), 'yyyy'));
  var startRow = findRow(sheetSweeper, '', 2);
  var startDate = getDate(new Date(sheetSweeper.getRange(startRow, 1, 1, sheetSweeper.getLastColumn()).getValues()[0][0]), DATE_FORMAT); 
  
  // 抽選結果をスプレッドシートの列に記入していく
  var listColumn = ["B","C","D","E","F"];
  for(var m=0;m<listSweep.length;m++){
    for(var n=0;n<listSweep[m].length;n++){
      sheetSweeper.getRange(listColumn[n] + (startRow+m)).setValue(listSweep[m][n][0]);
    }
  }

  // 掃除当番を割り当てたことをSlackに通知
  postSlack(startDate + "から" + listSweep.length + "日分の当番を割り当てました。");
}

Util的メソッドは以下の2点です。

getEmployeeList

employee_infoシートから、見出しを除く全行を取得して返却します。

shuffle

配列をランダムに並び替える機能です。

下記のサイトで紹介されている書き方を拝借しました。

https://www.nxworld.net/tips/js-array-shuffle.html

// 社員リストを取得
function getEmployeeList(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetEmployee = ss.getSheetByName('employee_info');
  
  // employee_infoから見出しを除く全行を取得して返却
  return sheetEmployee.getRange(2, 1, sheetEmployee.getLastRow()-1, sheetEmployee.getLastColumn()).getValues();;
}

// 配列の中身をシャッフル
// https://www.nxworld.net/tips/js-array-shuffle.html
function shuffle(array){
  for(var i=array.length-1; i>=0; i--) {
    var j = Math.floor(Math.random() * (i + 1));
    [array[i], array[j]] = [array[j], array[i]];
  }
  return array;
}