1. 概要
シフト作りやシフトリマインドはスプシ&GASで管理するのが無料で楽ちんです！
この記事ではその作り方からテンプレートまで公開します！
今回はシフトの募集＆作成に関してです．リマインド機能（slackとの連携）の実装は別記事（こちら）で紹介します．
2. イントロ
2-a. スプシやGASってなに？
スプシ：Google Spread Sheetのこと．Googleアカウントに紐づくサービスで，Excelとほぼ同じことができる．Excelとちがうのは他のGoogleアカウントと共有してみんなで一斉に編集できたり，次に説明するGASとの連携が取りやすい．
GAS：Google App Scriptのこと．Javascriptに似たプログラミング言語で，様々な他のサービスとの連携が取りやすい．とくにGoogleのサービスとの相性がよい．
2-b. 要件定義
- 今回のバイトは，時間ごとに交代することはなく，日付ごとに募集するものとする．
- 月１回，バイトの人達にシフトの希望日を調査し，翌月のシフトを組む．
- 募集する人数は，日付ごとに予め自分で設定する．
- 出してもらった希望日数のうち，一定割合以上はシフトを入れるようにする．
2-c. システム
システム構成を説明していきます．
- 【Form作成】GASによりシフト希望募集Form（Google Form）を自動作成．
- 【シフト表作成】翌月のシフト表（枠組みのみ）をスプシで自動作成．そこに各日付の募集人数を手動で入力．
- 【シフト決定】みんながFormを送信し終えたら，自動でFormの内容からシフトを決定し，2のスプシに書き込む．
※slackとの連携部分は別記事で紹介します．
3. 実装
3-a. 下準備
DBの作成
今回のデータベースはGoogle Driveということになります．
上図のようにバイトの情報を保存するworkerフォルダ，シフト表を保存するscheduleフォルダ，フォームを保存するFormフォルダ，を作ります．
バイトメンバーの記入
workerディレクトリにworkerスプシ（Spread Sheet）を作成します．
今回作成するシフト表には氏名と電話番号のみで十分でしたが，今後の機能拡張のためにstatusカラムも作りました．
マスタシートには，statusのデータ入力規則用のカラムがありますが，今回はworkersシートだけ考えれば大丈夫です．
GASのタイムゾーン設定
これをやらないと，日付がおかしなことになります．
上で作ったworkerスプシのメニューバーの拡張機能から，App Scriptを開きます．
プロジェクトの設定の全般設定からタイムゾーンを日本に設定し，その下の「appsscript.json」マニフェスト ファイルをエディタで表示するのチェックボックスをチェックします✅
次にエディタに行き，appsscript.jsonのtimeZoneを東京に設定します．
appsscript.json
{
"timeZone": "Asia/Tokyo",
"dependencies": {
},
"exceptionLogging": "STACKDRIVER",
"runtimeVersion": "V8"
}
3-b. GASプログラミング
Form生成GAS，シフト表生成GAS，シフト決定GASの順番で説明していきます．
以下，すべてworkerスプシの拡張機能からApp Scriptに入って実装します．
Form生成GAS
createForm.gsを作成し，コードを書いていきます．
コード内での実装手順は，
-
workerシートからメンバー情報を読み取り，配列に入れる． - 来月の日数を取得
- GASでフォームを作成してドライブの
formディレクトリに保存． - フォームの質問内容を書き込む．
※以下のコードではformフォルダのフォルダIDを自分で調べて記入する必要があることに注意．
createForm.gs
function createForm() {
//workerスプシを取得
const ss=SpreadsheetApp.getActiveSpreadsheet();
//workersシートの値を取得
const values=ss.getSheetByName("workers").getDataRange().getValues();
console.log(values);
//バイトメンバーの名前を別の配列に格納
const names = [];
for(let i=1; i<values.length;i++){
names.push(values[i][0]+values[i][1]);
}
console.log(names);
//現在の年月日を取得
let now = new Date();
//翌月を取得
let nextmonth = now.getMonth()+1;
now.setMonth(nextmonth)
let month = now.getMonth()+1;
let year = now.getFullYear();
//来月の最終日を取得
let lastDay = getLastDay(year,month);
//yyyy/mm/ddとなるようにmonthを整形
month = ("0" + month).slice(-2);
console.log(year.toString()+month.toString()+"の"+lastDay.toString()+"日間のシフト");
const days=[];
//日付の配列生成
for (let k=0;k<lastDay;k++){
days.push(month.toString() +"/"+ (("0"+(k+1)).slice(-2)).toString());
}
//ここからGoogle Formの作成
let form = FormApp.create("schedule_form_"+year.toString()+month.toString()).setTitle(year.toString()+"年"+month.toString()+"月シフト募集");
//プロパティストアにフォルダIDを格納する
PropertiesService.getScriptProperties().setProperty('FOLDER_ID','ここにformフォルダのフォルダIDを入力');
//プロパティストアに格納したFOLDER_IDを使って、指定のフォルダにフォームを作成する
const id=PropertiesService.getScriptProperties().getProperty('FOLDER_ID');
const formFile=DriveApp.getFileById(form.getId());
DriveApp.getFolderById(id).addFile(formFile);
DriveApp.getRootFolder().removeFile(formFile);
//質問１
let Q1 = form.addListItem();
Q1.setTitle("氏名");
Q1.setRequired(true);
Q1.setHelpText("あなたの氏名を選択してください。存在しない場合はバイトリーダに問い合わせてください。");
Q1.setChoiceValues(names);
//質問２
let Q2 = form.addCheckboxItem();
Q2.setTitle("シフト希望日");
Q2.setRequired(true);
Q2.setHelpText("希望する全てのシフト日を選択してください");
Q2.setChoiceValues(days);
//備考
let Q3 = form.addTextItem();
Q3.setTitle("備考");
Q3.setRequired(false);
Q3.setHelpText("シフトに関して何か要望がある方はこちらに記述をお願いします．");
}
//年月を入力すると，その月の最終日を返す関数
const getLastDay = (year,month) => {
return new Date(year,month,0).getDate();
};
GASを実行し，formフォルダにschedule_form_年月フォームが作成されていることを確認します．
これをメンバーに配布してシフト希望日を提出してもらいます．
シフト表生成GAS
下図のようなシフト表をGASで作成していきます．
一番上にシフトの名前，二行目にメンバーの氏名，三行目に電話番号が入ります．
createSpreadSheet.gsを作成し，コードを書いていきます．
コード内での実装手順は，
-
workerスプシのworkersシートからメンバー情報を読み取り，配列に入れる． -
schedule_年_月スプシを作りscheduleシートを作成． - シフト表をプログラミングで描画．キモいけど頑張る．
※以下のコードではscheduleフォルダのフォルダIDを自分で調べて記入する必要があることに注意．
createSpreadSheet.gs
function createSpreadSheet() {//翌月のシフトスプレッドシートを作る
let now = new Date();
//翌月を取得
let nextmonth = now.getMonth()+1;
now.setMonth(nextmonth)
let month = now.getMonth()+1;
let year = now.getFullYear();
let lastDay = getLastDay(year,month);
month = ("0" + month).slice(-2);
//翌月のシフトシート作成
const ss = SpreadsheetApp.create("schedule_"+year.toString()+month.toString());
const file = DriveApp.getFileById(ss.getId());
const folderId = 'ここにscheduleフォルダのフォルダIDを入力';
DriveApp.getFolderById(folderId).addFile(file);
DriveApp.getRootFolder().removeFile(file);
//現在のworkerを取得
const workerSS = SpreadsheetApp.getActiveSpreadsheet();
const workerValues=workerSS.getSheetByName("workers").getDataRange().getValues();
const workerNum = workerValues.length - 1;
console.log(workerValues);
//翌月シフトシートの一行目を結合
ss.insertSheet('schedule',0);
const nextSS = ss.getSheetByName('schedule');
nextSS.getRange(1,1,1,workerNum+4).merge();
nextSS.setRowHeight(1,36)
let titleRange = nextSS.getRange("A1")
titleRange.setHorizontalAlignment("center");
titleRange.setFontSize(14);
titleRange.setFontWeight("bold");
titleRange.setValue(year.toString()+"年"+month.toString()+"月バイトシフト");
//二行目に名前を挿入
nextSS.setRowHeight(2,60);
nextSS.setColumnWidths(1,2,24);
nextSS.setColumnWidths(3,workerNum,44);
nextSS.setColumnWidths(2+workerNum+1,2,60);
for(let k=1;k<workerNum+1;k++){
let nameRange = nextSS.getRange(2,2+k,1,1);
nameRange.setValue(workerValues[k][0]+'\n'+workerValues[k][1]);
}
let nameRange = nextSS.getRange(2,3,1,workerNum);
nameRange.setHorizontalAlignment("center");
nameRange.setVerticalAlignment("middle");
nameRange.setFontSize(11);
//三行目に電話番号を挿入
nextSS.setRowHeight(3,72);
for(let k=1;k<workerNum+1;k++){
let phoneRange = nextSS.getRange(3,2+k,1,1);
phoneRange.setValue(workerValues[k][3].slice(0,4)+'\n'+workerValues[k][3].slice(4,9)+'\n'+workerValues[k][3].slice(9,13));
}
let phoneRange = nextSS.getRange(3,3,1,workerNum);
phoneRange.setWrapStrategy(SpreadsheetApp.WrapStrategy.WRAP)
phoneRange.setHorizontalAlignment("center");
phoneRange.setVerticalAlignment("middle");
phoneRange.setFontSize(11);
let shortRange = nextSS.getRange(3,3+workerNum,1,1);
shortRange.setValue('不足日');
let numOfRecruitRange = nextSS.getRange(3,4+workerNum,1,1);
numOfRecruitRange.setValue('募集人数');
//一列目に日付をセット
for(let d=1;d<lastDay+1;d++){
let dtstr = year.toString() + '/' + month.toString() + '/' + d.toString();
console.log(dtstr);
let dt = new Date(dtstr);
console.log(dt);
let dateRange = nextSS.getRange(3+d,1,1,1);
dateRange.setValue(Utilities.formatDate(dt,'JST','dd'));
}
//二列目に曜日をセット
const arrayDay = ['日', '月', '火', '水', '木', '金', '土'];
for(let d=1;d<lastDay+1;d++){
let dtstr = year.toString() + '/' + month.toString() + '/' + d.toString();
console.log(dtstr);
let dt = new Date(dtstr);
console.log(dt);
let dayRange = nextSS.getRange(3+d,2,1,1);
dayRange.setValue(arrayDay[dt.getDay()]);
}
//格子枠線を追加
let borderRange = nextSS.getRange(2,1,lastDay+2,workerNum+3);
borderRange.setBorder(true,true,true,true,true,true,null,SpreadsheetApp.BorderStyle.SOLID);
}
GASを実行してscheduleフォルダにschedule_年月スプシが生成されていることを確認します．
そして，募集人数のセルに日付ごとの募集人数を記入します．
シフト決定GAS
ここまで来たら，以下の２条件のもと進めます．
- メンバーのフォーム回答がすべて終了している．
- シフト表スプシが作成され，募集人数が記入されている．
最後の大GASプログラミングです．
createSchedule.gsを作成し，コードを書いていきます．
コード内での実装手順は，
-
formフォルダから，該当するフォームを探索し，内容を配列に格納．日付ごとに候補者たちの名前を格納． -
scheduleフォルダから，該当するスプシを探索し，各日の募集人数を配列に格納． - 募集人数分だけ日付ごとにランダムに候補者から選出．
- 全員のシフト希望日採用率が一定割合rを超えるまで最大n回繰り返す．（rとnは要チューニング）
- 決定したら，
scheduleシートに書き込むアラートをだす．workerスプシでアラートを許可すれば，scheduleシートに自動で入力． - 更に，シフト表に自動で不足人数が表示されるように，不足日カラムに関数を入力．
- 不足している日の行が自動で色付けされるように条件付き書式をGASで設定．
※以下のコードではformフォルダとscheduleのフォルダIDを自分で調べて記入する必要があることに注意．
createSchedule.gs
function createSchedule() {//翌月のシフト募集フォームから内容を抽出し，バイトを決め，scheduleに書き出す
//現在のworkerを取得
const workerSS = SpreadsheetApp.getActiveSpreadsheet();
const workerValues=workerSS.getSheetByName("workers").getDataRange().getValues();
const workerNum = workerValues.length - 1;
console.log(workerValues);
let now = new Date();
//翌月を取得
let nextmonth = now.getMonth()+1;
now.setMonth(nextmonth)
let month = now.getMonth()+1;
let year = now.getFullYear();
let lastDay = getLastDay(year,month);
month = ("0" + month).slice(-2);
//フォームを取得
let formFolder = DriveApp.getFolderById('ここにformフォルダのフォルダIDを入力');
let formFiles = formFolder.getFiles();//("schedule_form_"+year.toString()+month.toString()).hasNext();
let formFileName = "schedule_form_"+year.toString()+month.toString()
console.log(formFileName);
let fileName;
let fileId;
let fileURL;
//翌月用のフォームをGoogleDrive内で探索
while(formFiles.hasNext()){
let file = formFiles.next();
fileName = file.getName(); // ファイル名
fileId = file.getId(); // ファイルID
fileURL = file.getUrl(); // ファイルURL
if (fileName == formFileName)
{
console.log("Form hitted !")
break;
}
else
{
fileName = null;
fileId = null;
fileURL = null;
}
}
console.log([fileName,fileId,fileURL]);
let form = FormApp.openById(fileId)
//フォームのレスポンスを取得
let formResponses = form.getResponses();
// 人の名前と希望日を格納する配列
let responseArray = [];
//formの中身を抽出
for(let i=0; i<formResponses.length;i++) {
let itemResponses = formResponses[i].getItemResponses();
let responses = [];
for(let j=0; j<itemResponses.length;j++){
let response = itemResponses[j].getResponse();
responses.push(response);
}
responseArray[i] = responses;
}
// 人の名前と希望日数を格納する配列
let numOfPrefferedDays = [];
for(let p=0; p<workerNum; p++){
numOfPrefferedDays.push([workerValues[p+1][0]+workerValues[p+1][1]]);
let formAnswered=false;
for(let q=0;q<formResponses.length;q++){
if(responseArray[q][0]===workerValues[p+1][0]+workerValues[p+1][1]){
numOfPrefferedDays[p].push(responseArray[q][1].length)
formAnswered=true;
}
}
if(formAnswered===false){
numOfPrefferedDays[p].push(0);
}
}
console.log(responseArray);
console.log(numOfPrefferedDays);
// 日付毎に並び替えて候補者を選出
let candidates = [];
for(let i=0;i<lastDay;i++){
let candidate = [];
for(let j=0;j<formResponses.length;j++){
if(responseArray[j][1].includes(month.toString() +"/"+ (("0"+(i+1)).slice(-2)).toString())){
candidate.push(responseArray[j][0]);
}
}
candidates.push(candidate);
}
console.log(candidates);
//↓ここからは，作成したシートから募集人数を読み取って，それをもとに候補者からシフトに入る人を決定する．
let scheduleFolder = DriveApp.getFolderById('ここにscheduleフォルダのフォルダIDを入力');
let scheduleFiles = scheduleFolder.getFiles();
let scheduleFileName = "schedule_"+year.toString()+month.toString();
let scheduleName;
let scheduleId;
let scheduleURL;
while(scheduleFiles.hasNext()){
let file = scheduleFiles.next();
scheduleName = file.getName(); // ファイル名
scheduleId = file.getId(); // ファイルID
scheduleURL = file.getUrl(); // ファイルURL
if (scheduleName == scheduleFileName)
{
console.log("Schedule hitted !")
break;
}
else
{
scheduleName = null;
scheduleId = null;
scheduleURL = null;
}
}
console.log([scheduleName,scheduleId,scheduleURL]);
const scheduleSS = SpreadsheetApp.openById(scheduleId).getSheetByName('schedule');
const numOfRecruitRange = scheduleSS.getRange(4,4+workerNum,lastDay,1);
const numOfRecruitValues = numOfRecruitRange.getValues();
console.log(numOfRecruitValues);
let elected = []; //日付毎のバイト選出者
let shortage = []; //日付毎のバイト不足人数
let numOfElectedDays = [];
let minRate = ["",1]; //もっともシフト希望の採用率が低かった人の名前とその割合を格納する
let ok=false;
const n=10000; //バイト抽選試行回数
const r=0.42 //最低バイト採用率
for(let t=0;t<n;t++){//シフトを出した全員の採用率がrを超えるまで，日付ごとにランダムに希望者を入れていく．
for(let k=0;k<lastDay;k++){
if (candidates[k].length == 0) {
elected[k] = [];
shortage[k] = numOfRecruitValues[k][0];
}
else if(candidates[k].length < numOfRecruitValues[k][0]){
elected[k] = candidates[k];
shortage[k] = numOfRecruitValues[k][0] - candidates[k].length
}
else{
//募集人数分のランダムな数の配列を作成
let randomNums = [];
for (let m=0;m<numOfRecruitValues[k][0];m++){
while(true){
let randomNum = Math.floor(Math.random()*candidates[k].length);
if(!randomNums.includes(randomNum)){
randomNums.push(randomNum);
break;
}
}
}
elected[k] = []
for (let l=0;l<numOfRecruitValues[k][0];l++){
elected[k].push(candidates[k][randomNums[l]]);
}
shortage[k] = 0;
}
}
for(let p=0;p<workerNum;p++){
numOfElectedDays.push([workerValues[p+1][0]+workerValues[p+1][1]]);
let numOfDays = 0;
for(let d=0;d<lastDay;d++){
if(elected[d].includes(workerValues[p+1][0]+workerValues[p+1][1])){
numOfDays = numOfDays+1;
}
}
numOfElectedDays[p].push(numOfDays);
}
ok=false;
for(let p=0;p<workerNum;p++){
if(numOfPrefferedDays[p][1] !== 0){
if(numOfElectedDays[p][1]/numOfPrefferedDays[p][1] > r){
//console.log(numOfElectedDays[p][0],numOfElectedDays[p][1]/numOfPrefferedDays[p][1]);
if(numOfElectedDays[p][1]/numOfPrefferedDays[p][1]<minRate[1]){
minRate[0] = numOfElectedDays[p][0];
minRate[1] = numOfElectedDays[p][1]/numOfPrefferedDays[p][1];
}
ok=true;
}
else{
//console.log("Bottle Neck is "+numOfElectedDays[p][0],numOfElectedDays[p][1]/numOfPrefferedDays[p][1]);
ok=false;
break;
}
}
}
if(ok){
break;
}
else{
//シフトが適切に決まらなかった時，配列をリセットする．
elected=[];
shortage=[];
numOfElectedDays=[];
minRate=["",1];
}
}
if(ok){
console.log(elected);
console.log(numOfElectedDays);
console.log(shortage);
console.log(minRate);
console.log("workerシートにて、scheduleシートへの書き込みを許可してください。")
let overWright = Browser.msgBox("スプレッドシートにシフトを上書きしますか？",Browser.Buttons.OK_CANCEL);
if(overWright==="ok"){
// シフトの記入
let resetRange = scheduleSS.getRange(4,3,lastDay,workerNum);
resetRange.setValue("");
resetRange.setHorizontalAlignment("center");
for(let d=0;d<lastDay;d++){
for(let i=0;i<elected[d].length;i++){
for(let p=0;p<workerNum;p++){
if(elected[d][i]===workerValues[p+1][0]+workerValues[p+1][1]){
let setRange = scheduleSS.getRange(4+d,3+p,1,1);
setRange.setValue("◯");
}
}
}
}
// 不足日の記入し，条件付き書式を設定
scheduleSS.clearConditionalFormatRules();// 条件付き書式のリセット
for(let d=0;d<lastDay;d++){
let rowNumber = 4+d;
let shortRange = scheduleSS.getRange(4+d,3+workerNum,1,1);
let colorRange = scheduleSS.getRange("A"+ rowNumber.toString()+":"+String.fromCharCode(68+workerNum)+rowNumber.toString());
console.log("A"+ rowNumber.toString()+":"+String.fromCharCode(68+workerNum)+rowNumber.toString());
// 不足と条件付きの設定
shortRange.setValue("=IF(COUNTA(C"+ rowNumber.toString()+":"+String.fromCharCode(66+workerNum)+rowNumber.toString()+")<"
+String.fromCharCode(68+workerNum)+rowNumber.toString()+",\"不足\",\"\")");
let rule = SpreadsheetApp.newConditionalFormatRule()
.whenFormulaSatisfied("=IF(COUNTA(C"+ rowNumber.toString()+":"+String.fromCharCode(66+workerNum)+rowNumber.toString()+")<"
+String.fromCharCode(68+workerNum)+rowNumber.toString()+",true,false)")
.setBackground("#FFFF00")
.setRanges([colorRange]).build();
let rules = scheduleSS.getConditionalFormatRules();
rules.push(rule);
scheduleSS.setConditionalFormatRules(rules);
}
}
else{
console.log("scheduleシートへの書き込みが拒否されました。")
}
}
else{
//シフトが決まらなかった時
console.log("Not determined");
}
}
GASを実行して，シフトが入力されていることを確認します．
実行して入力をする時は上書き入力することになるので，必ずアラートが入ります．
なお，シフト表の◯部分を編集しても，不足日のセルが関数になっているために動的に変わります．ちょこっと修正したい場合は手動で行えば大丈夫です．
まとめ
この記事では，GASを用いてシフト希望日募集フォーム作成，自動シフト組み＆表生成を行った．
今回は日付ごとにシフトを募集したが，シフトが時間制の場合さらなる改良が必要となる．
次回はGASをSlackと連携し，フォーム作成の通知やリマインドの自動送信を実装する．