小さな組織から大きな組織まで、出欠や勤怠をまとめた一覧表を用意している場合が多い。よくある方式が、大きなホワイトボードに氏名が並んでいて、マグネットで自分がどこにいるのかを示す形である。少人数のうちはホワイトボードでもいいが、100人を超えるような組織で、かつ居場所が散在している場合には、自分の所在を変更するためホワイトボードまで行くだけで相当な手間がある。ホワイトボードから離れた場所にいるものには他人の情報を確認できないし、忘れたまま出かけてしまった場合は残っている人に連絡して情報を書き換えてもらう必要がある。そもそも在宅勤務・テレワークの同僚がいれば、休みなのか働いているのかホワイトボードでは共有できない。このような不便を解消するため、Google Apps Scriptを使って所在情報を共有する仕組みを開発した。
所在情報をまとめるスプレッドシートを作成し、さらに各ユーザーの利便性のためWEBアプリケーションを経由してスプレッドシートに記録されている自信の情報を読み書きできるようにした。さらに、 外部から所定のメールアドレスへメールを送り、GmailをGASから操作して所所在情報を書き換えられるようにした。 所在情報を社外から書き換えたい場合でも G Suiteに社内ネットワークからしかアクセスできないという制限を踏まえたためである。
スプレッドシートの構成は以下の通り。
所在の情報の確認でも最も短時間で反応する必要がある場面を総務部門の担当者が代表電話で連絡を受けた場合と判断し、氏名をあいうえお順で並べたシートを本体とした。
A列 ID 検索に使う
B列 氏名
C列 読み仮名
D列 本日の所在
E列 本日の所在 付加情報、連絡事項
F列 本日の所在 更新時刻
G列 翌日の所在
H列 翌日の所在 付加情報、連絡事項
I列 翌日の所在 更新時刻
J列 予定の最終有効日
これとは別に、所属部門ごとに氏名を並べて所在を見やすくしたシートを作成した。各人の漢字氏名を検索キーとして、あいうえお順のシートからINDEX関数とMATCH関数を使って情報を反映した。
勤怠程度の情報ならGoogle Apps Script で勤怠フォームを作って出欠確認を自動化するやGAS｜オフィスの業務を改善・効率化した活用事例を紹介する（1）｜非エンジニアのための「Google Apps Script」あたりを参考にした方がいいかもしれない。前者のリンクにも書かれているように、システムを使う場所、組織の都合に適したシステム構築が必要である。
まずはWEBアプリケーションに設定するスクリプトを以下に示す。検索のキーとなるidは、全角数字で入力されても半角数字に変換されて処理される。利用者のリテラシーが高くない場合、全角数字と半角数字の区別がつかない可能性があるためだ。ユーザー数が増えれば、その分だけリテラシーの高くないユーザーを含む可能性が高まる。
GASのHtmlServiceでhtmlファイル側に変数を渡したいときは、13行目のようにする。
//参考ページhttps://www.indetail.co.jp/blog/181219/
//https://dackdive.hateblo.jp/entry/2015/02/01/010540
function doGet(e) {
// 表示したいHTMLのファイル名を指定(拡張子は記載しない)
var id = e.parameter.id;
if(!id){
//idがない場合はid入力欄を表示
var html = HtmlService.createTemplateFromFile('inputid');
}else{
//idがある場合は、現在の記入内容と情報記入欄を表示
var html = HtmlService.createTemplateFromFile('index');
html.id = toNumber(id);
}
//htmlを出力
return html.evaluate();
}
function doPost(e) {
//POSTで渡された値を取得
var id = toNumber(e.parameter.id);
var target = e.parameter.target;
// SSIDからスプレッドシートの取得
var sheetname = 'あいうえお順';
var sheet = getSheet(sheetname);
//id対象者の行数を取得
var row = rowInterest(id, 'ID');
//id対象者の情報を取得
var info = getData(id);
//本日の情報を処理
if(target === 'today'){
info.placeToday = e.parameter.whereIs;
info.commentToday = e.parameter.comment;
info.dateTodayChange = new Date();
//書き込み配列を用意
var writeArray = [[info.placeToday,info.commentToday,info.dateTodayChange]]
var column = 4;
}
//翌日の情報を処理
if(target === 'NextDay'){
info.placeNextDay = e.parameter.whereIs;
info.commentNextDay = e.parameter.comment;
info.dateNextDayChange = new Date();
var dateString = e.parameter.validDate;
dateString += "T23:59:59+0900";
info.validDate = new Date(dateString);
//予定の取り消し
if(info.placeNextDay === 'delete'){
info.placeNextDay = '';
info.commentNextDay = '';
info.validDate = '';
}
//書き込み配列を用意
var writeArray = [[info.placeNextDay,info.commentNextDay,info.dateNextDayChange, info.validDate]]
var column = 7;
}
//書き込み
sheet.getRange(row, column,1,writeArray[0].length).setValues(writeArray);
SpreadsheetApp.flush()
// スプレッドシートのデータ挿入後、元の画面に戻す
var html = HtmlService.createTemplateFromFile('index');
html.id = id;
html.info = info;
return html.evaluate();
}
function getSheet(name){
// SSIDからスプレッドシートの取得
var ssId = 'ここにファイルIDを記入する';
var ss = SpreadsheetApp.openById(ssId);
// 指定されたシート名からシートを取得して返却
var sheet = ss.getSheetByName(name);
return sheet;
}
function getData(id) {
// 指定したシートからデータを取得
var data = getSheet('あいうえお順').getDataRange().getValues();
// 該当ユーザーの情報を取得
var row = rowInterest(toNumber(id), 'ID') ;
var info = {
ID:data[row -1][0],
fullname:data[row -1][1],
fullnameKana:data[row -1][2],
placeToday:data[row -1][3],
commentToday:data[row -1][4],
dateTodayChange:data[row -1][5],
placeNextDay:data[row -1][6],
commentNextDay:data[row -1][7],
dateNextDayChange:data[row -1][8],
validDate:data[row -1][9]
};
info.hasToday = info.placeToday !== '';
info.hasNextDay = info.placeNextDay !== '';
return info;
}
function rowInterest(keyValue, keyType){
// SSIDからスプレッドシートの取得
var sheetname = 'あいうえお順';
var sheet = getSheet(sheetname);
var data =sheet.getDataRange().getValues();
var rowInterest = 0;
var columnInterest = 0;
if(keyType ==='ID'){
columnInterest = 0;
}
var data =sheet.getDataRange().getValues();
for(var i=1;i<data.length;i++){
if(keyValue === data[i][columnInterest]){
rowInterest = i;
}
}
Logger.log(rowInterest);
return rowInterest + 1;
}
//毎日AM0時から人が動き出すまでの時間に実行する。
//本日の所在を消去し、値を空白にする。
//翌日の所在が記入されていれば、その内容を本日の所在に転記する。
//翌日の所在の最終有効日を当日の日付と比較し、最終日であれば翌日の所在を消去する。
function monitoringDaily(){
var sheetname = 'あいうえお順';
var sheet = getSheet(sheetname);
var data =sheet.getDataRange().getValues();
//現在時刻
var date = new Date();
//本日の所在を消去し、値を空白にする。
var lastrow = data.length;
sheet.getRange(4,4,lastrow-3,3).clearContent();
//翌日の所在が記入されていれば、その内容を本日の所在に転記する。
for(var i=3;i<data.length;i++){
var info = {
ID:data[i][0],
fullname:data[i][1],
fullnameKana:data[i][2],
placeToday:data[i][3],
commentToday:data[i][4],
dateTodayChange:data[i][5],
placeNextDay:data[i][6],
commentNextDay:data[i][7],
dateNextDayChange:data[i][8],
validDate:data[i][9]
};
info.hasToday = info.placeToday !== '';
info.hasNextDay = info.placeNextDay !== '';
if(info.hasNextDay){
//書き込み配列を用意
var writeArray = [[info.placeNextDay,info.commentNextDay,info.dateNextDayChange]]
var column = 4;
sheet.getRange(i + 1, column,1,writeArray[0].length).setValues(writeArray);
}
//翌日の所在の最終有効日を当日の日付と比較し、最終日であれば予定を消去する。
Logger.log(info.validDate < date);
if(info.validDate < date){
var column = 7;
sheet.getRange(i + 1, column,1,4).clearContent();
}
}
}
function toNumber(str){
str = String(str);
var numVal = str.replace(/[０-９]/g, function(s) {
return String.fromCharCode(s.charCodeAt(0) - 65248);
});
return Number(numVal);
}
function getNextDay(){
var date = new Date();
date.setDate(date.getDate() + 1);
var dateNextDay = date.getFullYear() + '-' + (date.getMonth() + 1) + '-' + date.getDate();
return dateNextDay;
}
次に、WEBアプリケーションで使用するHTMLのテンプレートを示す。idを未指定の場合はidを入力するページを、idが指定されていれば現在の情報の確認と新しい情報の入力ページを表示する。
formで与える選択肢の値は適切に変更する。日付を入力させる欄は、予め翌日の日付が入るようにした。
以下の例では、所在地の選択肢が事業所1~5であるが、たとえば選択肢のひとつに「在宅勤務」を用意しておけば、在宅勤務者の勤務開始や終了をほかの従業員と同様に情報共有できる。
//ID入力用
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<h2>IDを入力してください</h2>
<form method="get" action="ここにWEBアプリケーションのURLを記入する">
<p>ID: <input type="text" name="id" value="" size="20" /></p>
<input type="submit" value="送信する">
</form>
</body>
</html>
//IDをキーとして情報の確認と書き換えをする
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<style>
h2 {background-color:palegreen;}
table{
border: solid 1px #000000;
border-collapse: collapse;
}
th {border: solid 1px #000000}
td {border: solid 1px #000000}
</style>
</head>
<body>
<table>
<?
// スプレッドシートからデータを取得
// 該当ユーザーの情報を取得
var info = getData(id);
// 本日の予定の見出し作成
output.append('<tr>');
output.append('<th>氏名</th>');
output.append('<th>本日の所在</th>');
output.append('<th>本日の所在 連絡事項</th>');
output.append('<th>本日の所在 更新時刻</th>');
output.append('</tr>');
//本日情報に値があれば表示
if(info.hasToday){
output.append('<tr>');
output.append('<th>' + info.fullname + '</th>');
output.append('<th>' + info.placeToday + '</th>');
output.append('<th>' + info.commentToday + '</th>');
output.append('<th>' + Utilities.formatDate(info.dateTodayChange, 'JST', 'yyyy年M月d日 H時m分') + '</th>');
output.append('</tr>');
}else{
output.append('<tr>');
output.append('<th>' + info.fullname + '</th>');
output.append('<th>未入力</th>');
output.append('<th></th>');
output.append('<th></th>');
output.append('</tr>');
}
// 翌日の予定の見出し作成
output.append('<tr>');
output.append('<th>予定の有効期限</th>');
output.append('<th>翌日の所在</th>');
output.append('<th>翌日の所在 連絡事項</th>');
output.append('<th>翌日の所在 更新時刻</th>');
output.append('</tr>');
//本日情報に値があれば表示
if(info.hasNextDay){
output.append('<tr>');
output.append('<th>' + Utilities.formatDate(info.validDate, 'JST', 'yyyy年M月d日 H時m分') + '</th>');
output.append('<th>' + info.placeNextDay + '</th>');
output.append('<th>' + info.commentNextDay + '</th>');
output.append('<th>' + Utilities.formatDate(info.dateNextDayChange, 'JST', 'yyyy年M月d日 H時m分') + '</th>');
output.append('</tr>');
}else{
output.append('<tr>');
output.append('<th></th>');
output.append('<th>未入力</th>');
output.append('<th></th>');
output.append('<th></th>');
output.append('</tr>');
}
?>
</table>
<h3>所在情報を更新するには、以下のフォームに記入して、更新ボタンを押してください。</h3>
<h2>本日の所在情報を登録する</h2>
<form method="post" action="ここにWEBアプリケーションのURLを記入する。末尾はexec?id=<?= id ?>">
<input type="hidden" name="target" value= "today">
<input type="hidden" name="id" value= "<?= id ?>">
所在：<label><input type="radio" name="whereIs" value="事業所1">事業所1</label>
<label><input type="radio" name="whereIs" value="事業所2">事業所2</label>
<label><input type="radio" name="whereIs" value="事業所3">事業所3</label>
<label><input type="radio" name="whereIs" value="事業所4">事業所4</label>
<label><input type="radio" name="whereIs" value="事業所5">事業所5</label><br><br>
<label><input type="radio" name="whereIs" value="退勤" required>退勤</label>
<label><input type="radio" name="whereIs" value="出張">出張</label>
<label><input type="radio" name="whereIs" value="休暇">休暇</label>
<label><input type="radio" name="whereIs" value="未出社">未出社(詳細は下の連絡事項欄に記入する)</label><br><br>
<label>連絡事項: <input type="text" name="comment"></label><br><br>
<input type="submit" value="本日の所在を更新する">
</form>
<h2>翌日の所在情報を登録する</h2>
<form method="post" action="ここにWEBアプリケーションのURLを記入する。末尾はexec?id=<?= id ?>">
<input type="hidden" name="target" value= "NextDay">
<input type="hidden" name="id" value= "<?= id ?>">
<label><input type="radio" name="whereIs" value="出張">出張</label>
<label><input type="radio" name="whereIs" value="休暇" required>休暇</label>
<label><input type="radio" name="whereIs" value="時差出勤">時差出勤</label>
<label><input type="radio" name="whereIs" value="delete">予定の取り消し</label><br><br>
<label>連絡事項: <input type="text" name="comment"></label><br><br>
予定の最終有効日: <input type="date" name="validDate" value="<?= getNextDay() ?>"><br><br>
<input type="submit" value="翌日以降の所在を更新する">
</form>
</body>
</html>
メールを読み書きして所在情報を書き換えるスクリプトを以下に示す。社外から所在情報を書き換えたいケースとして、突然の休暇、公共交通機関の事故など出社が遅れる、あるいは退勤処理をし忘れて退勤したという3通りを想定した。1分、5分など、一定時間ごとに発火するトリガーをかけておく。
GoogleフォームはG Suiteの外からでも回答できるので、フォームのスクリプトと組み合わせれば外部から勤務場所を入力するのに使える。このときは社員IDなどを予め入力したフォームのURLを配布しておくとよい。
function getMail(){
var sheetname = 'あいうえお順';
var sheet = getSheet(sheetname);
var start = 0;
var max = 500;
var threads = GmailApp.search('label:shozai is:unread',start,max);
for(var n in threads){
var thread = threads[n];
var msgs = thread.getMessages();
for(m in msgs){
var msg = msgs[m];
var id = msg.getSubject();
var body = msg.getBody();
var date = msg.getDate();
//所在情報に反映
id = toNumber(id);
var row = rowInterest(id, 'ID');
//メール本文に「休暇」があれば休暇、「退勤」があれば退勤、それ以外は未出社に設定する
var whereIs = '未出社';
if ( body.match(/休暇/)) {
whereIs = '休暇';
}
if ( body.match(/退勤/)) {
whereIs = '退勤';
}
//書き込み配列を用意
var writeArray = [[whereIs,body,date]]
var column = 4;
sheet.getRange(row, column,1,writeArray[0].length).setValues(writeArray);
//メールを既読にする
thread.markRead();
}
}
}
