Google App Scriptを使って管理者・従業員別に勤怠管理シートを作成した

前書き

これまで手書きで勤怠管理をして，最後に手打ちでエクセルにまとめて今月分の給料を出していた．
日吉にある01Cafeさんの勉強会でGASを使えば，無駄な作業を省いた上で更に一元管理・個別管理が容易だと学んだので実際に作ってみた．
01cafe.jp

今回作りたいもの

従業員によってGoogleFormから送られた勤怠結果を管理者用Sheetに書き込む．管理者用Sheetへの書き込み時に送信元の従業員と一致するSheet内容も編集する．

要件

管理者用Sheetと従業員用Sheetを分けることで，管理者からは全ての従業員のデータを閲覧できるが，従業員は個別のデータのみを閲覧できるようにする．

使うもの

Google App Script
Google Form
Google SpreadSheet
Google Drive

開発

従業員用のスプレッドシートを作成する．

従業員用のスプレッドシートを作成し，従業員に閲覧権限を付与する．作成したら共有リンクをコピペで保存しておく．
今回のケースだと従業員Aの分を作成して，その共有リンクを保存する．
このリンクは実装コードで使う．
ex.) https://docs.google.com/spreadsheets/d/1-OxdgIiUV3V0nulsC6WeuPETAPY55-crittoo96/edit#gid=0

GoogleFormで勤怠フォームを作成する

フォーム名	フォーム種別	必須
名前	プルダウン	true
日付	日付	true
勤務場所	ラジオボタン	true
始業時間	時刻	true
終業時間	時刻	true
休憩時間	時刻	true
業務内容	段落	true
自由記述	段落	false

フォーム内容はこのように設定した．

次にGoogle FormとSpreadSheetを連結する．このとき連結したSpreadSheetが管理者用のsheetになる．
右側のsheetアイコンをクリックしてsheetと連結した．Google Formの回答はすべてこのシートに溜まる．

管理者用シートのGASでフォームリクエストを処理する．

アイコンをクリックすると連結したシートに飛ぶ．
連結したシートから，「ツール」->「スクリプトエディタ」を選択してGASを起動．
以下が実装コード．

// フォームから送信された情報をシートに挿入する．
function writeRow(_sheet, _event) {
  _sheet.appendRow(
    [_event.namedValues['日付'] + '',
    _event.namedValues['勤務場所'] + '',
    _event.namedValues['始業時間'] + '',
    _event.namedValues['終業時間'] + '',
    _event.namedValues['休憩時間'] + '',
      'xxx',
    _event.namedValues['業務内容'] + '']
  );

  // 実務時間を求める．diffをとる
  var lastrow = _sheet.getLastRow();
  var strformula = "=D" + lastrow + "-C" + lastrow + "-E" + lastrow
  _sheet.getRange("F" + lastrow).setValue(strformula);
}

// シートの更新
function updateSheet(_sheet) {
  // 給与計算
  calcSalary(_sheet);
  
  // その他の欄
  // ...
  
  return;
}
// 給与計算のビジネスロジックを記述
function calcSalary(_sheet) {
  var formula = "=ROUNDDOWN((VALUE(B1) * 24)) * 1000";
  _sheet.getRange("B2").setNumberFormat("#,##0");
  _sheet.getRange("B2").setValue(formula);
}

// sheetを新規に作成してフォーマットする．
function createFormattedSheet(_spreadSheet, _sheetName) {
  // 作成
  _spreadSheet.insertSheet(_sheetName);
  var sheet = _spreadSheet.getSheetByName(_sheetName);

  // 初期フォーマット
  sheet.appendRow(['勤務時間']);
  sheet.appendRow(['給与']);
  sheet.appendRow(['日程', '勤務地', '始業時間', '終業時間', '休憩時間', '勤務時間', '業務内容']);

  // 罫線をひく
  var rng1 = sheet.getRange("A1:B2");
  rng1.setBorder(true, true, true, true, true, true);

  var rng3 = sheet.getRange("A3:G3");
  rng3.setBorder(true, true, true, true, true, true);

  // 勤務時間の書式設定
  sheet.getRange("B1").setNumberFormat("[h]:mm:ss");
  //勤務時間のformula設定
  sheet.getRange("B1").setValue("=SUM(F4:F)");

  return sheet;
}

// 「フォーム送信時」のトリガーに登録する関数．
function openEmployeeSheet(event) {
  var sheetUrl = '';
  // それぞれのspreadSheetと閲覧許可をシェアする
  switch (event.namedValues['名前'] + '') {
    case 'A':
      sheetUrl = "https://docs.google.com/spreadsheets/d/1-OxdgIiUV3V0nulsC6WeuPETAPY55-crittook96/edit#gid=0";
      break;
    case 'B':
      sheetUrl = '<Bさんと共有したspreadsheetsheetUrl>';
      break;
    case 'C':
      sheetUrl = '<Cさんと共有したspreadsheetsheetUrl>';
      break;

    default:
      break;
  }

  if (sheetUrl == '') return;

  var spreadSheet = SpreadsheetApp.openByUrl(sheetUrl);
  var sheetName = Utilities.formatDate(new Date(), 'Asia/Tokyo', 'MM') + '月';
  var sheet = spreadSheet.getSheetByName(sheetName);

  // sheetがないとき新規に作成する．
  // 存在する時，既存のものに書き込む
  if (sheet) {
    writeRow(sheet, event);
    updateSheet(sheet);
  } else {
    var newSheet = createFormattedSheet(spreadSheet, sheetName);
    writeRow(newSheet, event);
    updateSheet(newSheet);
  }
}

動作について

openEmployeeSheetがエントリポイント．
まずswitch文で名前に対応した従業員のsheetUrlを選択．sheetUrlからspreadSheetを取得する．
spreadSheetに今月分のsheetが既に存在するなら，データを挿入して更新．
存在しない場合，新しく今月分のsheetを作成したのち，データを挿入して更新する．

switch内部のsheetUrlはあらかじめ作成しておいた従業員用シートのurlを入れる．
給与計算については時給1000円を仮定にcalcSalaryで実装．
次にopenEmployeeSheetをトリガーに登録する．

スクリプトをトリガーに設定する．

スクリプトを動作させるためにトリガーを設定する．
ストップウォッチアイコンを押すとトリガー設定画面にいく．

トリガー設定画面から「トリガーを追加」ボタンからトリガーを下画像の条件で作成することで，フォームから値が送信され管理者用シートが更新されると同時に対象の従業員シートも更新される．

完成

実際にフォームからA,B,Cの勤怠データを送信してみると管理者用シートには全データが保存されていて，従業員AのシートにはAの勤怠データのみ表示されていることがわかる．
また従業員Aからは閲覧権限によって，従業員Aのシートのみを閲覧することができる．
勤怠管理を紙を見ながら手打ちで行う手間が減り非常に便利になった．

つぶやきとプログラミング

アメトーーク好きなWebエンジニア芸人