【GAS応用編】スプレッドシート勤怠管理を完全自動化

「中抜けや日またぎ勤務、休日出勤など、イレギュラーな勤怠を手作業で修正するのが大変…」
「より複雑な勤務体系に対応できる勤怠管理を、コストをかけずに実現したい…」
多くの企業で働き方が多様化する一方、勤怠管理が複雑化し、担当者の方の負担が増えているのではないでしょうか。

本記事は、そんなお悩みを解決するための「応用編」です。

GoogleスプレッドシートとGoogle Apps Script (GAS) を活用し、複雑な勤務パターンに対応しながら管理業務まで効率化する、一歩進んだ勤怠管理の仕組みを徹底解説します。

この記事を読み終える頃には、自社の運用に最適化された勤怠管理の仕組みを構築できるようになっているはずです。

【はじめての方へ】

本記事は、基本的な勤怠管理の仕組みを解説した「基本編」の内容を習得していることを前提としています。Googleフォームでの打刻や基本的な関数の設定に不安がある方は、まずはこちらの記事からご覧ください。

なぜ「応用編」が必要なのか？基本編の課題点
【応用1】打刻の精度を上げる：丸め処理とミス修正
- 打刻時間を15分や30分単位で丸める
- 打刻の順番間違いを自動で修正する
【応用2】多様な働き方に対応する：中抜け・日またぎ・複数回休憩
- 構成変更：PIVOTをやめて「縦持ちデータ」で管理する
- 中抜け・日またぎ勤務に対応する
【応用3】複雑な給与計算を自動化する：休日・深夜労働
【応用4】管理業務を効率化する：GASによるレポートとリマインド
- 打刻漏れ防止のリマインド通知
- 週次・月次の自動集計レポート（超過アラート付き）
まとめ

なぜ「応用編」が必要なのか？基本編の課題点

まずは、基本編で構築したシステムの課題点を振り返ってみましょう。

1日1回の出退勤しか想定していない： 中抜けや複数回の休憩に対応できない。
日またぎ勤務が計算できない： 夜勤など、出勤日と退勤日が異なる勤務の労働時間が正しく計算されない。
打刻ミスに弱い： 出勤と退勤の打刻順を間違えると、手動での修正が必要になる。
割増賃金の計算が手動： 休日出勤や深夜労働といった、給与計算に関わる重要な項目を自動で判別・集計できない。
能動的な管理が必要： 打刻漏れのチェックや、月次の集計レポート作成などを手動で行う必要がある。

これらの課題は、多くの企業で日常的に発生しうるものです。応用編では、これらの課題を一つひとつ解決し、勤怠管理の「完全自動化」を目指します。

【応用1】打刻の精度を上げる：丸め処理とミス修正

まずは、日々の打刻データをより正確かつ管理しやすくするためのテクニックをご紹介します。
前提として、勤怠データは基本編と同じものを使用します。

打刻時間を15分や30分単位で丸める

給与計算のルール上、労働時間を15分や30分単位で切り捨て・切り上げしている企業は多いでしょう。この処理を関数で自動化します。

ここでは、出勤時刻は切り上げ、退勤時刻は切り捨てで処理する方法を解説します。これにより、労働時間が過大に計上されるのを防ぎます。

1.「勤怠計算簿」シートに作業列を追加

G列とH列に、「丸め後出勤」「丸め後退勤」の列を新しく追加します。

2.CEILING関数で出勤時刻を切り上げる

「丸め後出勤」列の2行目に、以下の数式を入力します。CEILING（シーリング）関数は、指定した基準値の倍数になるように数値を切り上げる関数です。

=ARRAYFORMULA(
  IF(
    C2:C = "",
    "",
    CEILING(C2:C, "0:15")
  )
)

CEILING(C2:C, “0:15”) の部分で、C列の出勤時刻を15分単位（”0:15″）で切り上げています。
ARRAYFORMULAとIF関数を組み合わせることで、C列に新しい打刻データが追加されるたびに、この計算が自動で適用されます。

3.FLOOR関数で退勤時刻を切り捨てる

「丸め後退勤」列の2行目に、以下の数式を入力します。FLOOR（フロア）関数は、指定した基準値の倍数になるように数値を切り捨てる関数です。

=ARRAYFORMULA(
  IF(
    F2:F = "",
    "",
    FLOOR(F2:F, "0:15")
  )
)

FLOOR(F2:F, “0:15”) の部分で、F列の退勤時刻を15分単位で切り捨てています。

これで、手作業での時間修正は不要になります。

打刻の順番間違いを自動で修正する

「朝、出勤ボタンを押し忘れて、退勤時に間違えて出勤ボタンを押してしまった…」といったヒューマンエラーはつきものです。

このような順番間違いを、「その日の最初の打刻を出勤」「最後の打刻を退勤」と見なすことで自動修正します。
※この処理は、休憩時間などを考慮せず、あくまでその日の活動開始時刻と終了時刻を把握するためのシンプルな方法です。

QUERY関数を使い、従業員ごと・日付ごとに、その日の最小時刻（最初の打刻）と最大時刻（最後の打刻）を抽出します。

=QUERY(
  'フォームの回答'!A:F,
  "SELECT D, E, MIN(F), MAX(F) 
   WHERE D IS NOT NULL 
   GROUP BY D, E 
   LABEL MIN(F) '出勤時刻', MAX(F) '退勤時刻'"
)

SELECT D, E, MIN(F), MAX(F)：氏名(D)、日付(E)、打刻時刻(F)の最小値、打刻時刻(F)の最大値を抽出します。
GROUP BY D, E：氏名と日付でデータをグループ化します。
LABEL MIN(F) ‘出勤時刻’, MAX(F) ‘退勤時刻’：抽出された最小値と最大値の列のヘッダー（見出し）を、それぞれ「出勤時刻」「退勤時刻」に変更します。

これにより、例えば「出勤」を2回打刻してしまったり、打刻種別を間違えたりしても、その日の活動時間（最初の打刻から最後の打刻まで）が正しく記録されます。

【注意】この方法は「中抜け」には対応していません！

この方法は、1日に複数回の出勤・退勤がある「中抜け」には対応できません。中抜けに対応する方法は次の章で詳しく解説します。

【応用2】多様な働き方に対応する：中抜け・日またぎ・複数回休憩

ここからは、より柔軟な働き方に対応するためのカスタマイズです。基本編の構成を大きく変更する必要がありますが、得られるメリットは絶大です。

構成変更：PIVOTをやめて「縦持ちデータ」で管理する

基本編ではQUERY関数のPIVOTを使い、横一列で出勤・退勤時刻を管理していました。しかし、この方法では複数回の打刻に対応できません。

そこで、打刻データを「縦持ち」のまま管理する方法に切り替えます。

1.「勤怠データ」シートを新設

「フォームの回答」シートから必要なデータだけをQUERY関数で抽出し、整理するためのシートを新たに作成します。整理・加工する役割まで、すべてこのシートで担います。

2.元データを抽出・整形する

まず、A1セルに以下の数式を入力します。
この数式だけで、タイムスタンプからの日付・時刻の抽出、並べ替えまでを自動で行います。

={
  "日付", "氏名", "打刻種別", "打刻時刻";
  QUERY(
    ARRAYFORMULA(
      IF(
        'フォームの回答'!A2:A = "",,
        {
          INT('フォームの回答'!A2:A),
          'フォームの回答'!D2:D,
          'フォームの回答'!B2:B,
          'フォームの回答'!A2:A - INT('フォームの回答'!A2:A)
        }
      )
    ),
    "SELECT * WHERE Col2 IS NOT NULL ORDER BY Col1, Col2, Col4"
  )
}

{… ; …}：ヘッダー（見出し）と実データを縦に結合しています。
ARRAYFORMULA({…})：{}（波括弧）を使い、複数の計算結果を仮想的な1つのテーブルとしてまとめています。
- INT(‘フォームの回答’!A2:A)：タイムスタンプから日付を抽出します。
- ‘フォームの回答’!D2:D：氏名を抽出します。
- ‘フォームの回答’!B2:B：打刻種別を抽出します。
- ‘フォームの回答’!A2:A – INT(…)：タイムスタンプから時刻を抽出します。
QUERY(…)：作成された仮想テーブルを並べ替えます。Col1は1列目（日付）、Col2は2列目（氏名）を指します。
これにより、「フォームの回答」シートに計算式を入れる必要は一切なくなり、「元データ」と「計算」を安全に分離することができます。

3.打刻時刻をルールに応じて丸める

次に、E列に「丸め後時刻」という列を新設します。E2セルに以下の数式を入力すると、打刻種別に応じて時刻が自動で丸められます。

出勤・休憩終了： 15分単位で切り上げ
退勤・休憩開始： 15分単位で切り捨て

=ARRAYFORMULA(
  IF(A2:A = "", "",
    IF(
      (C2:C = "1.出勤") + (C2:C = "3.休憩終了"),
      CEILING(D2:D, "0:15"),
      FLOOR(D2:D, "0:15")
    )
  )
)

IF関数を使い、C列の打刻種別に応じて条件分岐させています。ARRAYFORMULA内で「または」の条件を表現するために、(C2:C = “1.出勤”) + (C2:C = “3.休憩終了”) のように条件を + でつないでいます。
CEILING関数が切り上げ、FLOOR関数が切り捨ての処理を行います。
これ以降の計算は、すべてこのE列の「丸め後時刻」を参照していきます。

4.「勤怠計算簿」シートを再設計

ここが、より複雑な勤務体系に対応するための重要なポイントです。1回の勤務（出勤から退勤まで）が1行にまとまるようにシートを設計し直します。

「誰が」「いつ勤務を開始したか」のリストを自動生成します。出勤日を基準にすることで、1回の勤務を1行のデータとして正確に扱えるようになります。

「勤怠計算簿」シートのA2セルに以下の数式を入力してください。

=SORT(
  UNIQUE(
    FILTER(
      {'勤怠データ'!B2:B, '勤怠データ'!A2:A},
      '勤怠データ'!C2:C = "1.出勤"
    )
  )
)

FILTER(…)：「勤怠データ」シートから、打刻種別が「1.出勤」の行だけを抽出します。
UNIQUE(…)：抽出したリストから重複を削除し、「誰が」「いつ出勤したか」のユニークなリストを作成します。
SORT(…)：リストを日付順・氏名順に並べ替えます。

中抜け・日またぎ勤務に対応する

「縦持ち」データとSUMIFS関数を組み合わせることで、中抜けや日またぎ勤務など、複数日にまたがる打刻にも対応します。SUMIFS（サムイフス）関数は、複数の条件に一致する数値の合計を求める関数です。

1.各打刻種別の合計時刻を計算

A列・B列の「勤務日」リストを元に、各勤務に対応する打刻時刻を合計します。ここでは、日またぎ勤務も考慮して勤務開始日とその翌日の打刻を合計の対象とします。

C2セル（出勤時刻の合計）には以下の数式を入力します。

=IF(
  $A2="",,
  SUMIFS(
    '勤怠データ'!$E:$E,
    '勤怠データ'!$B:$B, $A2,
    '勤怠データ'!$A:$A, $B2,
    '勤怠データ'!$C:$C, "1.出勤"
  )
)

出勤は必ず勤務日当日に打刻されるため、日付の条件は$B2（勤務日）のみです。

D2セル（退勤時刻の合計）には以下の数式を入力します。

=IF(
  $A2="",,
  SUMIFS(
    '勤怠データ'!$E:$E,
    '勤怠データ'!$B:$B, $A2,
    '勤怠データ'!$A:$A, ">="&$B2,
    '勤怠データ'!$A:$A, "<"&$B2+2,
    '勤怠データ'!$C:$C, "4.退勤"
  )
)

‘勤怠データ’!$A:$A, “>=”&$B2 と ‘勤怠データ’!$A:$A, “<“&$B2+2 の条件で、勤務日とその翌日の退勤打刻を合計の対象にしています。

同様に、休憩開始（E列）、休憩終了（F列）の列にも、退勤と同じ日付条件のSUMIFS関数を設定してください。”4.退勤”の部分はそれぞれの打刻種別に書き換えます。

2.実労働時間を計算

実労働時間は「(退勤時刻の合計 – 出勤時刻の合計) – (休憩終了時刻の合計 – 休憩開始時刻の合計)」で求められます。

G2セルに以下の数式を入力することで、日またぎ勤務など、退勤時刻が出勤時刻より早くなる場合も自動で正しく計算します。

=IF(
  D2 < C2,
  (D2 + 1) - C2 - (F2 - E2),
  (D2 - C2) - (F2 - E2)
)

IF(D2 < C2, …)：退勤時刻(D2)が出勤時刻(C2)より小さい（＝日付をまたいでいる）かどうかを判定します。
(D2 + 1) – C2 – (F2 – E2)：日付をまたいでいる場合、退勤時刻に1（24時間）を足してから、出勤時刻と休憩時間を引きます。
(D2 – C2) – (F2 – E2)：日付をまたいでいない場合は、通常の計算を行います。

この数式を下の行へオートフィルでコピーすれば、1日に何度中抜けや休憩をしても、日またぎ勤務があっても、総労働時間が正確に計算されるようになります。

【応用3】複雑な給与計算を自動化する：休日・深夜労働

割増賃金の計算に不可欠な、休日出勤と深夜労働の時間を自動で算出します。

準備：「祝日リスト」シートの作成

まず、日本の祝日を判定するためのリストを作成します。

新しいシートを作成し、「祝日リスト」と名前を付けます。
内閣府の「国民の祝日」ページなどから、該当年（できれば数年分）の祝日データをコピーし、A列に日付、B列に祝日名を貼り付けます。

休日出勤を自動で判別・集計する

WEEKDAY関数とCOUNTIF関数を使い、その日が「土日」または「祝日」であるかを判定します。

「勤怠計算簿」シートに「休日労働」列を追加し、以下の数式を入力します。

=IF(
  OR(
    WEEKDAY(B2, 2) >= 6,
    COUNTIF('祝日リスト'!$A:$A, B2) > 0
  ),
  G2,
  0
)

WEEKDAY(B2, 2) >= 6：WEEKDAY関数は曜日を数値で返します。種類を2にすると月曜=1〜日曜=7となるため、>=6で土日を判定しています。
COUNTIF(‘祝日リスト’!$A:$A, B2) > 0：COUNTIF関数で、B2の日付が「祝日リスト」にいくつ存在するかを数えます。1以上であれば祝日と判定します。
OR(…)：上記のどちらかの条件を満たす場合に、その日の実労働時間（G2）を「休日労働」として計上します。

深夜労働時間を自動で計算する

深夜労働（原則22:00〜翌5:00）の時間を計算します。日またぎ勤務も考慮した、少し複雑ですが非常に強力な計算式です。

この計算式は1日に1回の出退勤を想定しています。中抜けなど複数回の打刻がある場合、深夜労働時間が正しく計算されない可能性があります。

「勤怠計算簿」シートに「深夜労働」列を追加し、以下の数式を入力します。

=MAX(0, MIN(IF(D2<C2, D2+1, D2), "29:00") - MAX(C2, "22:00")) + MAX(0, MIN(IF(D2<C2, D2+1, D2), "5:00") - C2)

この数式は2つのパートに分かれています。

MAX(0, MIN(…, “29:00”) – MAX(C2, “22:00”))
- 22:00から翌5:00（29:00）までの間の労働時間を計算します。
- MAX(C2, “22:00”)：出勤時刻と22:00のうち、遅い方の時刻を取得します。
- MIN(…, “29:00”)：退勤時刻（日またぎ考慮）と29:00のうち、早い方の時刻を取得します。
- この2つの時刻の差分が、22:00以降の労働時間になります。
MAX(0, MIN(…, “5:00”) – C2)
- 0:00から5:00までの間の労働時間を計算します。こちらは主に前日から勤務している場合が対象です。

休憩時間が深夜帯にかかる場合は、別途その時間を差し引く処理が必要です。

【応用4】管理業務を効率化する：GASによるレポートとリマインド

最後に、GASを使って管理業務を自動化します。これにより、勤怠データのチェックや報告の手間を大幅に削減できます。

打刻漏れ防止のリマインド通知

夕方の定刻になっても退勤打刻がない従業員に対し、自動でリマインドを送信します。

スプレッドシートのメニューから「拡張機能」>「Apps Script」を開きます。
以下のスクリプトを貼り付けます。

// --- 設定項目 ---
// 通知を送信したい管理者のメールアドレスを設定
const NOTIFICATION_EMAIL = 'manager@example.com'; 
// --- 設定項目ここまで ---

function remindMissingClockOut() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('勤怠データ');
  const today = new Date();
  const todayString = Utilities.formatDate(today, 'JST', 'yyyy/MM/dd'); // 日付の書式をスプレッドシートに合わせる

  const data = sheet.getDataRange().getValues();
  const clockedIn = {}; // 出勤打刻した従業員を記録するオブジェクト

  // 今日の打刻データをチェック
  for (let i = 1; i < data.length; i++) {
    const rowDate = new Date(data[i][0]);
    const rowDateString = Utilities.formatDate(rowDate, 'JST', 'yyyy/MM/dd');
    
    if (rowDateString === todayString) {
      const employeeName = data[i][1];
      const clockType = data[i][2];

      if (clockType === '1.出勤') {
        clockedIn[employeeName] = true;
      } else if (clockType === '4.退勤') {
        delete clockedIn[employeeName]; // 退勤したらリストから削除
      }
    }
  }

  // 退勤打刻がない従業員に通知
  const missingEmployees = Object.keys(clockedIn);
  if (missingEmployees.length > 0) {
    const subject = '【勤怠管理】退勤打刻漏れリマインド';
    const body = `お疲れ様です。\n\n以下の従業員の退勤打刻がありません。\n\n- ${missingEmployees.join('\n- ')}\n\n打刻漏れにご注意ください。`;
    
    MailApp.sendEmail(NOTIFICATION_EMAIL, subject, body);
  }
}

トリガーを設定
- Apps Scriptエディタの左メニューから「トリガー」（時計アイコン）を選択します。
- 「トリガーを追加」をクリックし、以下のように設定します。
  - 実行する関数：remindMissingClockOut
  - イベントのソース：時間主導型
  - トリガーのタイプ：日付ベースのタイマー
  - 時刻を選択：午後6時〜7時など、リマインドしたい時刻を設定
- 保存して完了です。

週次・月次の自動集計レポート（超過アラート付き）

QUERY関数で月次の集計表を別シートに作成し、その内容をGmailに自動送信しますさらに、総労働時間が規定を超えた従業員がいる場合は、警告メッセージも合わせて通知します。

集計シートの作成

「月次レポート」シートなどを新しく作成し、次のように設定します。

・手順1-A：集計したい月を指定する

B1セルに、集計したい月の日付を1日付けで入力します。（例：2025/8/1）
この日付を切り替えることで、表示するレポートの月を変更できます。

・手順1-B：指定した月のデータを集計する

A3セルに以下のQUERY関数を入力します。

=QUERY(
  '勤怠計算簿'!A:J,
  "SELECT A, SUM(G), SUM(H), SUM(I) 
   WHERE 
     A IS NOT NULL AND 
     B >= date '"&TEXT(B1, "yyyy-mm-dd")&"' AND 
     B <= date '"&TEXT(EOMONTH(B1, 0), "yyyy-mm-dd")&"'
   GROUP BY A 
   LABEL A '氏名', SUM(G) '総労働時間', SUM(H) '休日労働', SUM(I) '深夜労働'",
  1
)

WHERE句に、B1セルの日付を元に月の絞り込み条件を追加しました。
EOMONTH(B1, 0)でB1セルの月末日を求め、B >= 月初日 AND B <= 月末日とすることで、指定した月のデータのみを抽出します。

・手順1-C：【重要】合計時間の表示形式を変更する

QUERY関数で合計された時間（B列、C列、D列）が24時間を超えると、正しく表示されません。（例：32時間30分が8時間30分になる）
これを解決するため、セルの表示形式を変更します。

「月次レポート」シートのB列、C列、D列を選択します。
メニューの「表示形式」>「数字」>「カスタム数値形式」を選択します。
入力欄に [h]:mm と入力し、「適用」をクリックします。

メール通知スクリプトの作成

以下のスクリプトをApps Scriptに追加します。

// --- 設定項目 ---
// 通知を送信したい管理者のメールアドレスを設定
// ※この設定は、上の「打刻漏れ防止リマインド」のスクリプトと共通です。
const NOTIFICATION_EMAIL = 'manager@example.com'; 
// --- 設定項目ここまで ---

function sendMonthlyReport() {
  // --- 設定項目 ---
  // 総労働時間のアラート基準（時間）を自社の規定に合わせて設定
  const monthlyHourThreshold = 180; 
  // --- 設定項目ここまで ---

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadsheet.getSheetByName('月次レポート');
  
  // スクリプト実行時に、B1セルを前月の1日に自動で設定
  const today = new Date();
  const firstDayOfLastMonth = new Date(today.getFullYear(), today.getMonth() - 1, 1);
  sheet.getRange("B1").setValue(firstDayOfLastMonth);
  SpreadsheetApp.flush(); //変更を即時反映

  // 4行目以降にデータがなければ処理を終了
  if (sheet.getLastRow() < 4) return; 

  // 4行目から最終行までのデータを取得
  const data = sheet.getRange(4, 1, sheet.getLastRow() - 3, sheet.getLastColumn()).getDisplayValues();
  
  // レポートのテーブル部分をHTMLで作成
  let table = '<table border="1" style="border-collapse: collapse; padding: 4px;">';
  table += '<tr style="background-color: #f2f2f2;"><th>氏名</th><th>総労働時間</th><th>休日労働</th><th>深夜労働</th></tr>';
  
  const overtimeEmployees = [];

  for (let i = 0; i < data.length; i++) {
    const employeeName = data[i][0];
    const totalHoursStr = data[i][1];
    const holidayHoursStr = data[i][2];
    const lateNightHoursStr = data[i][3];
    
    table += `<tr><td>${employeeName}</td><td>${totalHoursStr}</td><td>${holidayHoursStr}</td><td>${lateNightHoursStr}</td></tr>`;
    
    const parts = totalHoursStr.split(':');
    const totalHours = parseInt(parts[0], 10) + (parseInt(parts[1], 10) / 60);

    if (totalHours > monthlyHourThreshold) {
      overtimeEmployees.push(`- ${employeeName}さん (総労働時間: ${totalHoursStr})`);
    }
  }
  table += '</table>';

  const subject = `【勤怠レポート】${firstDayOfLastMonth.getMonth() + 1}月度の労働時間サマリー`;
  let body = 'お疲れ様です。<br><br>当月の各従業員の労働時間サマリーです。<br><br>';

  if (overtimeEmployees.length > 0) {
    body += `<p style="color: red;"><b>【警告】以下の従業員の総労働時間が規定（${monthlyHourThreshold}時間）を超えています。</b></p>`;
    body += overtimeEmployees.join('<br>') + '<br><br>';
  }
  
  body += table;
  body += '<br>ご確認のほど、よろしくお願いいたします。';

  // GmailにHTML形式でメールを送信
  MailApp.sendEmail({
    to: NOTIFICATION_EMAIL,
    subject: subject,
    htmlBody: body
  });
}