【GAS】全自動シャッフルランチ運営システムを作ってみた #GoogleSpreadSheet

シャッフルランチで交流増加

皆さん、ランチしてますか？
組織の規模が大きくなると、部署間の交流が少なくなりがち

そこで登場するのがシャッフルランチ
名前の通り、参加者をシャッフルしてチーム分けを行い、ランチを食べるというものです。

今回はこのシャッフルランチをGASを使って全自動で運営するシステムを作ってみました。

想定する仕様

特定の日の午前中(8:30とか)に登録用のGoogleフォームを送信する
参加者は1のフォームに氏名、性別、所属部署を入力する
お昼前（11:00とか）にフォームを締め切る
2の情報を元に、できるだけ所属が被らないように参加者を均等に割り振る
それぞれの参加者に対して、チーム名、参加者一覧を送る

「できるだけ所属が被らない」がこのシャッフルランチのミソです。これによって組織内の知らない人とランチする可能性が高まります。

また、登録しておいて欠席する人を減らすために、フォームは当日に回答してもらいます。1週間前に決めてしまうと忘れてしまいますし、他の予定が入ってしまいますから。

ではさっそく作っていきましょう。

まず、フォームを作成し、スプレッドシートに紐付ける

こんな感じのフォームを作り...

こんな感じでシートに記録します。

同じスプレッドシート内に「組み合わせ記録用のシート」も用意してください。

次にコードを書いていきます

GASはスプレッドシートのツールのスクリプトエディタから書くことができます。適当なプロジェクト名を入れて始めましょう。

フォームを締め切る

function setShuffleLunch() {
  //シャッフルランチのURL
  var form = FormApp.openByUrl(
     'google formのurlをここに書く'
  );
  //募集を締め切る。
  form.setAcceptingResponses(false);

  ...

まずはフォームを締め切ります。メンバー決定後に登録できたら紛らわしいですからね。

当日分の行を特定する

回答が記録されるシートの中から当日の回答が記録されている行を特定します。

var ss=SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("回答が記録されるシートの名前");//
var todaySheet = ss.getSheetByName("今日の組み合わせを入力するシートの名前");
var data = sheet.getDataRange().getValues();//getDataRange()でシートの中で値が入ってるもの全てを配列に格納する。
var today=Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyy年M月d日');
var start=0;
var end=0; 
for(var i=1;i<data.length;i++){
    var d1=Utilities.formatDate(new Date(data[i][0]), 'Asia/Tokyo', 'yyyy年M月d日');
    if(d1!=today){
      continue;
    }else if(start==0){
      start=i;
      end=i;
    }else{
      end=i;
    }
}

単純に2行目(i=1)から見ていき、今日の日付が見つかったら最初に見つかった行と最後に見つかった行を更新していくというロジックになっています。

同じ日かどうかを判定するために、日付をフォーマットする必要があるのか疑わしいですが

別のシートに今日のデータを移し替える

操作するために、該当データを別のシートに移し替えます。

// 当日の参加者の数を数える
var count=end-start+1;
// グループの数を割り出す。
var groupNum=getGroupNum(count);
// 当日の該当データを取得
data=sheet.getRange(start+1,2,count,4).getValues();
// 以前のデータを削除
todaySheet.getRange(2,1,1000,20).setValue("");
// 当日のデータを登録
todaySheet.getRange(2,2,count,4).setValues(data);

参加者からグループの数を割り出す必要があるのですが、それをgetGroupNumという関数で出しています。

function getGroupNum(count){
  // 5人以下 →1グループ
  // 6人以上 →3,4人でグループ
  // 12人以上→4,5人でグループ
  if(count<6){
    return 1;
  }else if(count<12){
    return Math.ceil(count/4);
  }else{
    return Math.ceil(count/5);
  }
}

getRange(開始行、開始列、行数、列数)でrangeを取得できるので、値を取得して、シートを綺麗にして、貼り付けています。

抽出したメンバーにチームを割り当てる

ここからがこのプログラムの肝です。所属ができるだけ被らないように、均等にチーム分けをしていきます。

今回は所属別にソートした後に、A,B,C,A,B,Cと順番に割り当てるという方法を採用します。

より最適な方法があるかもしれませんが、思いついたのがそれだったので。

//所属別にソートする
var range = todaySheet.getRange(2,1,count,5);
range.sort([{column: 5}]);

//シートに挿入する用
var result=[];

// 順番に割り当てていく。
for(var i=start;i<=end;i++){
  var Team=getTeamName(i%groupNum);
  result.push([Team]);
}
//　チームを今日の組み合わせに反映させる
todaySheet.getRange(2,1,count).setValues(result);

//チーム別にソートする
var range = todaySheet.getRange(2,1,count,5);
range.sort([{column: 1}]);

GASを書いていていつも思うのですが、どこまでをシート側の操作でやって、どこまでをGASでやろうか悩ましいものがあります。

最初にrange.sortで所属別にソートします。

次に、開始行から終了行まで、「行をグループの数で割った余り」でチームを分類していき、チーム名を配列に格納していきます。チーム名はgetTeamName(num)という関数で数字を元にして、表計算ソフトでおなじみのAから始まり、Z,AA,ABと増えるアルファベットを使っています。

function getTeamName(num){
  num++;
  var ret="";  
  var str = 'ABCDEFGHIJKLMNOPQRSTUVXXYZ';
  while(num>0){
    tmp=(num-1)%26;
    ret=str.slice(tmp,tmp+1)+ret;
    num=Math.floor((num-1)/26)
  }
  return ret;
}

そして、その配列を先程の所属別にソートが済んだシートの1列目に入れていきます。すると、できるだけ所属が被らないようにチーム分けが行なえます。

最後に、チーム別にソートすれば完成と、言いたいところですがまだやることが残っています。

結果をメールで送信する

ここで終わってしまっては、シートを元に各々に組み合わせを連絡するというタスクが発生してしまうので、メールで送信するところまでGASでやってしまいましょう。

var mail_data=todaySheet.getDataRange().getValues();

var team=[];  
var email={};
var name={};

for(var i=0;i<groupNum;i++){
  team.push(getTeamName(i));
  email[getTeamName(i)]=[];
  name[getTeamName(i)]=[];
}

for(var i=1;i<count+1;i++){
  email[mail_data[i][0]].push(mail_data[i][1]);
  // キー太　太郎（営業部）となるように結合させておく。
  name[mail_data[i][0]].push(mail_data[i][2]+"("+mail_data[i][4]+")");
}    

for(var i=0;i<groupNum;i++){
  var place=places[i%places.length];
  var rests=random(restaurants,3);
  var email_to=email[team[i]].join(",");
  var body="本日もご参加いただき誠にありがとうございます！\n"
          +"あなたは"+team[i]+"チームとなりました。以下が参加メンバーです。\n\n"
          +name[team[i]].join("\n")
  MailApp.sendEmail(email_to, '本日（'+today+'）のシャッフルランチ【チーム'+team[i]+'】', body,{cc:"メールを確認したい場合はここにアドレスを入れる"});
}

ここまでの内容を1つの関数の中に入れて(setShuffleLunch()とか)、トリガーを設定すれば自動的に集計から告知までを行ってくれます。

フォームの回答を受け付けるコードを書く

このままだとシャッフルランチの開催以降、フォームが回答を受け付けないままなので、下の関数を書いてトリガーを設定して自動で受け付けるようにする必要があります。

function updateForm() {
   var form = FormApp.openByUrl('google formのURL');
   form.setAcceptingResponses(true);
   //formのURLをメールやslack等で送る処理を書く
}

最後にメールやslack等に投稿するようにしておけば、全自動でシャッフルランチを運用することができます！

以上で終了です！お疲れ様でした。

応用編の考察

シャッフルランチということで知らない人同士を集めることになるので、待ち合わせ場所やランチの場所決めでグダることが予想されます。そこで、待ち合わせ場所とランチの場所の候補も一緒に教えてくれるように改良したので、続編として紹介するかもしれないです。

あと、このフォームで性別を聞いている通り、性別もチーム分けの要素として利用しようと思ったのですが、今の組織が男女比率が偏っているので、均等にすると絶対ぼっちになって同性と仲良くなれない現象が起きてしまうというのと、所属と性別の両方がばらけるアルゴリズムが思いつかないという理由（こっちが本音）で特に利用していません。

注意事項

GASで1日にGmailを送ることができる回数に制限があるので注意しましょう。
- 人数が多いようであれば、メーリングリストに送るなどの工夫がいりそうです。