シャッフルランチの自動化

背景

現在所属している会社では毎月1回、社内コミュニケーションの促進のためにシャッフルランチを開催しております。まだ社員数が少ない時は参加者を手動でグループ分けしていたのですが、社員数の増加により手動にも限界がきましたので、GASを利用して自動でグループ分けする仕組みを導入しました。

要件

• 出来るだけ部署が被らないようにチーム分けをする
• 参加者のみgoogleカレンダーにスケジュールを登録する
• 運営メンバーはエンジニアではないので、誰でも利用出来るツールにする

構成

設定手順

Googleフォームの設定

1. Googleフォームにて新規のフォームを作成します。
2. 質問は2つで名前は記述式テキスト（必須項目）、所属部署はプルダウン（必須項目）に設定します。
3. 設定にて[メールアドレスを収集する]と［信頼できるドメインのユーザーに限定する］にチェックを入れます。
4. 回答タブにて「スプレッドシートの作成」をクリックします。

スプレッドシートの設定

1. 上記で作成したGoogleフォームの結果が記入されるスプレッドシートを開きます。
2. シート名を「フォームの回答1」から「シャッフルランチ回答用」に変更します。
3. 新たにシートを2つ作成します。
4. 名前を「イベント登録用」、「組み合わせ記録用」

Google Apps Scriptの設定

スクリプト全体

// メニューに表示させる
function onOpen(e){
    var arr = [
        {name: "組み合わせ作成", functionName: "listAllMain"}
    ];
    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    spreadsheet.addMenu("スクリプト", arr);
}


// メイン処理
function listAllMain() {
  setShuffleLunch();
  addressCopy();
  createEvents();
}


function setShuffleLunch() {
  //シャッフルランチのURL
  var form = FormApp.openByUrl(
     'Googleフォームの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(start==0){
      start=i;
      end=i;
    }else{
      end=i;
    }
}

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

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);
  }
}

//所属別にソートする
var range = todaySheet.getRange(2,1,count,7);
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,7);
range.sort([{column: 1}]); 

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;
}

function addressCopy(){
  //フォーム回答者のアドレスをコピーする
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('シャッフルランチ回答用');
  var rangeToCopy = sheet.getRange('B:B');
  //コピーしたアドレスをイベント登録用シートに貼り付ける
  var targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('イベント登録用');
  var targetToCopy = targetSheet.getRange('E1');
  rangeToCopy.copyTo(targetToCopy);
}

function createEvents() {
  var calendar = CalendarApp.getDefaultCalendar();
  var values = SpreadsheetApp.getActive().getSheetByName('イベント登録用').getDataRange().getValues();

  //イベント登録用シートの情報を参照してスケジュールを登録する
  for(var i = 1; i < values.length; i++){
    var title = values[1][0];
    var startTime = new Date(values[1][1]);
    startTime.setHours(values[1][2].getHours());
    startTime.setMinutes(values[1][2].getMinutes());
    
    var endTime = new Date(startTime);
    endTime.setHours(endTime.getHours() + values[1][3].getHours());
    endTime.setMinutes(endTime.getMinutes() + values[1][3].getMinutes());
 
    var option = {
      guests:values[i][4]
    }
    calendar.createEvent(title, startTime, endTime, option);
  }
}

シャッフルの仕組みは以下の方の記事を参考にさせて頂きました。

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

上記スクリプトを保存した後に関数：onOpen(e)を実行すると、スプレッドシート上に「スクリプト」というメニューが増え、そちらをクリックすると「組み合わせ作成」が見えるようになります。こちらをクリックするとスクリプトが実行され、組み合わせの作成とカレンダー登録が自動で行われます。

実行前に

こちらでスクリプトの設定は完了したのですが、実行する前に以下の点を都度確認してください。

Googleカレンダーに登録するイベント

スクリプト内でスプレッドシートの「イベント登録用」シートを参照するようにしていますので、以下の画像のようにスケジュールを設定してください。

組み合わせ記録用シートの退避

そのまま実行すると組み合わせ記録用シートに組み合わせが上書かれてしまうので、過去の組み合わせを参照したい場合は、シートをコピーするなりして退避しておいてください。

おわりに

この仕組みを導入することにより、シャッフルランチ開催の手間が省け格段に楽になります。
普段はコードを書いたりしないので、コードが汚いと思いますがご容赦ください。

本エントリが、シャッフルランチを運営していて困っている方々の何かしらの助けになれば幸いです。