GIGAスクール構想の影響もあり、Google Workspace を利用するユーザーが急増しています。
せっかくなので、業務効率化としてGoogle スプレッドシートを使って勤怠表を作成したいと思ったことはないでしょうか。
勤怠の計算自体はそこまで難しくないのですが、勤怠情報をどんどん追加していくと、計算式を設定している範囲を超えてしまい計算範囲を手動で調整する必要がでてきます。
この記事では、手動で調整する手間をできる限り削減し、勤怠表を自動化する方法を紹介します。
勤怠システムは企業によって計算方法が違うと思いますので、各自お好みにカスタマイズしてください。
【上級者向け】Google Apps Scriptを利用した勤怠表の作り方
このセクションではGoogle Apps Script（GAS）を使った勤怠表の作成方法を紹介します。
GASを使えば、スプレッドシートのデフォルトの関数だけでは補えない機能を作成することができ、完全自動化を実現できます。
ただし、GASはJavaScriptで記述する必要があるので、やや内容は高度になります。
各自カスタマイズできるよう、ポイントをかいつまんでわかりやすく説明していきますので是非挑戦してみてください。
完成品はこちら
準備
- GASを利用した勤怠表の作り方 を開いて参考にしながら作業してください。
- ”入力”と”集計”という名前のシートを作成する。
- 氏名、日付、開始時刻、、終了時刻、休憩時間の順番にカラム名を追加する。
- B列をクリックして範囲選択し、”表示形式”タブ > ”数字” > ”日付”をクリック。
- 同様にC列、D列の表示形式を”経過時間”にする。
- ”集計” シートのA1セルに以下の関数を入力
=QUERY('入力'!A:F, "select A,B,C,D,E",1)- ”ツール”タブ > ”スクリプトエディタ” を開く。
- 以下のコードをコピペして”Ctrl + s” で保存する。
// 計算を行うシート
SHEET_NAME = '集計'
// 計算に利用するデータの最初の行と列を指定
START_ROW = 2 // 最小値は2
START_COLUMN = 3 // 最小値は1
// START_COLUMN列から（START_COLUMN + TARGET_COLUMNS）列までの範囲をデータとして取得する
TARGET_COLUMNS = 3 // 最小値は1
// 実働時間のカラム名
WORKING_HOURS_NAME = '実働時間 [h]'
// 労働時間に換算できる最小の時間（分）
INTERVAL = 15
function update() {
const book = SpreadsheetApp.getActiveSpreadsheet()
const sheet = book.getSheetByName(SHEET_NAME)
const lastRow = sheet.getLastRow()
const range = sheet.getRange(START_ROW, START_COLUMN, lastRow, TARGET_COLUMNS)
const values = range.getValues()
for (const [i, v] of values.entries()){
const startTime = v[0]
const endTime = v[1]
const restTime = v[2] ?? 0
if (i === 0)
sheet.getRange(START_ROW - 1, START_COLUMN + TARGET_COLUMNS).setValue(WORKING_HOURS_NAME)
if (!startTime || !endTime) continue
const diffMin = (endTime.getTime() - startTime.getTime())/1000/60
const workingHours = calculateWorkingHours(diffMin, 100, INTERVAL, restTime)
sheet.getRange(START_ROW + i, START_COLUMN + TARGET_COLUMNS).setValue(workingHours)
}
}
/**
* 任意の桁で切り捨てする関数
* @param {number} min minに調整した時間を入力
* @param {number} base 100で少数第2桁以下を切り捨て
* @param {number} interval 何分以下切り捨てにするかの値（15分以下切り捨てなら15入力）
* @param {number} restTime 休憩時間（h）
* @return {number} hour
*/
function calculateWorkingHours(minute, base, interval, restTime) {
// interval以下の時間は切り捨て
const floorMinute = Math.floor(minute/interval) * interval
const hour = floorMinute/60 - restTime
// 小数点の桁数調整
return Math.floor(hour * base) / base
}- サイドバーの”トリガー” > ”トリガーを追加” ボタンを押下
- 「イベントの種類を選択」を”編集時”に変更し、”保存”を押下。
スプレッドシート関数の解説
私の場合、入力シートと集計シートは別にしたかったので、入力シートの値を一旦集計シートに持ってくる関数を利用しています。
使っているのはQUERY関数というものでSQL風にスプレッドシートの値を取得することができます。
EXCELでいうところのパワークエリですね。
関数の構文としては QUERY(データ, クエリ, [見出し]) となっており、
=QUERY('入力'!A:F, "select A,B,C,D,E",1)で、入力シートのAからF列のうち、A,B,C,D,E列を取得を取得し、1行目を見出し行として認識しなさい、という命令になります。
この関数一行で入力の値を集計シートに持ってくることができます。
スクリプトの解説
ここで紹介しているスプレッドシートと同じ構成で勤怠表を作成した場合は編集の必要はありません。
もし、列を追加したり休憩時間などを修正する場合、いくつかの数字を変更する必要があります。
編集していい部分は以下となります。
// 計算を行うシート
SHEET_NAME = '集計'
// 計算に利用するデータの最初の行と列を指定
START_ROW = 2 // 最小値は2
START_COLUMN = 3 // 最小値は1
// START_COLUMN列から（START_COLUMN + TARGET_COLUMNS）列までの範囲をデータとして取得する
TARGET_COLUMNS = 3 // 最小値は1
// 実働時間のカラム名
WORKING_HOURS_NAME = '実働時間 [h]'
// 労働時間に換算できる最小の時間（分）
INTERVAL = 15このスクリプトで少しややこしいのが使用するデータ範囲の指定方法です。
GASでは
const range = sheet.getRange(START_ROW, START_COLUMN, lastRow, TARGET_COLUMNS)のように、getRangeを使用しますが、
getRange(開始行, 開始列, 最終行, 開始列から右へ何列目までを取得するかの値)という公式となっているので、この”開始列から右へ何列目までを取得するかの値”が少しわかりにくいので注意が必要です。
勤務時間は15分や30分ごとに加算されていく会社も多いかと思います。
その計算は以下の関数で算出しておりますが、ここはいじる必要はないので、INTERVALの値を15や30に変更してください。INTERVAL以下の勤務時間は切り捨てられます。
function calculateWorkingHours(minute, base, interval, restTime) {
// interval以下の時間は切り捨て
const floorMinute = Math.floor(minute/interval) * interval
const hour = floorMinute/60 - restTime
// 小数点の桁数調整
return Math.floor(hour * base) / base
}スプレッドシートにどんどん情報が追加されていく場合、最終行を取得する必要がでてきます。
その処理はgetLastRow()で取得しています。
おわりに
簡易版であればスプレッドシートの関数のみで勤怠表を作ることができますし、GASを使えば更に高度なことも可能です。
今回紹介した勤怠表をアレンジすれば勤務先にあったものを作成できると思います。是非チャレンジしてみてください。