GASで面倒な勤怠管理をサクッと自動集計させる方法【仕組み理解編】

今回の記事では『めんどうな勤怠集計をなくしたい』と考えている方に向けて、エクセルの入力作業をなくして集計を自動にさせる方法をご紹介していきます。

尚、先にお伝えしておくと、このプロジェクトは『僕はあなたから一切のお金をいただくことなく、完全無料で勤怠管理を自動集計する仕組み』をお伝えしますので、最後まで読んでみてくださいね。

まずはこちらをご覧ください。

この状況をあなた自身が作れるようになり、勤怠の管理を自動化させることが、このプロジェクトの目的となります。

勤怠集計の自動化で僕ができること

勤怠集計の自動化に向けて僕ができることは以下の通りです。

といったところです。

比較的管理がめんどくさいと言われる運送会社で、勤怠管理の自動運用を2年ほど行い、何度もトラブルシューティングを行っているため、自社管理のためのノウハウはほぼ整っています。

自社でシステムを構築、自動集計で管理する壮大なメリット３つ

自社でシステムを構築、自動集計で管理する壮大なメリットは以下の通りです。

大事なことですので順番に解説します。

メリット①：圧倒的なお金の節約ができる

今回のプロジェクトでは、Googleの仕組みを活用して勤怠の自動集計をさせるため、この仕組みを自身で構築・運用を行うことで、本来であれば毎月かかってくるはずのシステムを『全部無料』で、めんどうな集計作業がすべてなくなります。

少しだけネット検索してみましたが、一般的なクラウド管理システムにかかる費用は、以下の通りです。

これは自社で管理をする人件費以外に、外部のシステムにかかる費用です。

調べていて『冗談でしょ？』って思いましたが、大企業が相手であれば扱う金額も跳ね上がるので支払ってくれる会社もあるのかなーという感じです。

なので、規模の小さい会社であれば『タイムカードをエクセルで入力して集計をする』という管理が、まだまだ主流なのもうなずけます。

今回のプロジェクトでは、僕が構築した運用のノウハウをすべて放出しますので、それで自社システムの構築をしていただければ、『システムにかかるお金はかからない(=僕には1円も払っていただく必要がない)』ので、破格というか意味不明なレベル(=圧倒的なメリット)であることは間違いないと思います。笑

メリット②：圧倒的な時間の節約ができる

自社システムの構築は、知識のある方なら意外と頑張ればつくることができます。

しかし、最初から作ろうとすると実際の運用をイメージして計算式を組み込んだり、組み込んだ計算式がイメージ通りに稼働するのか確認が必須となります。

また、一から作るとなると、例えばですが

みたいな疑問が出てきた時、たいてい挫折することになってしまいます。

メリット③：簡単に運用できる仕組みを構築できる

ぶっちゃけた話をすると、勤怠の集計はどんなシステムを使おうと、基本的に出てくる数字は同じです。しかし、会社によっては、就業規則によっては『出て来て欲しい数字』がでてこなければ使い物になりません。

そのため、ある程度簡単に編集したりできなければせっかく導入したシステムも使い物にならないという事態に陥りかねません。

そうした事実を知らず、安易にシステムの導入を行うと、後々めんどうなことが起きてしまうため、ある程度自社で運用が行えるシステムである必要があります。

これは、クラウド型のシステムに共通することですが、自社運用をするためのカスタマイズ性高くありません。一部、別途費用を支払うことで、管理体制に合わせたカスタマイズが行えるものもありますが、費用負担はかなり大きくなってしまいます。

逆に、カスタマイズ性を求めたシステムを導入しようと考えると、オンプレミス型という1000人規模のシステムになってしまいます。

大それた仕組みである必要はなく、自社にあった運用ができるかどうかが重要です。

ノウハウを無料で提供している理由は？

というわけで、実際に構築をしていきましょう！、、、と言いたいところですが、1点だけ引っ掛かってることがありすよね？

そう、『なんでそんなノウハウを提供しているの？』『どうせ後から高いお金を請求するんだろ！』という疑問です。

冒頭にも書いてますが、今回の企画では、僕はあなたからお金をもらうつもりはありませんので安心してください。

このプロジェクトの目的はあくまで、ノウハウ提供ですので、Googleの仕組みを活用すれば、こんなことが実現できると知ってもらうことが一番の目的です。

言ってみれば広告です。

勤怠管理の自動化ノウハウを無料で提供するワケ

僕は、このシステムの販売をしています。

実際、完成されたものを提供してお金をいただいています。また、導入を支援したり、運用のサポートをすることで、同様にお金をいただいています。

しかし、いきなり購入をするのはハードルが上がるため、そもそも、大手のクラウドシステムのように大それたものでもないため、むしろ知ってもらった方が、本当に必要としている人に届けられると考えています。

このプロジェクトを通して、実際に運用できる方が増えれば価値が伝わりやすいですし、今後、運用ができるようになった人を対象に（運用ができる人なら紹介もしやすいと思いますので）、販売代理店のようなカタチで紹介していただける仕組みにしたいとも考えています。（これは追い追いですが…笑）

なんにせよ、知ってもらうことが一番の目的なので、今回無料でノウハウを提供しています。

もちろん、自社システムにはデメリットもあります

ノウハウを無料で提供している理由にもつながりますが、自社システムは運用が行えることが大前提の仕組みになります。そのため、ノウハウだけ手に入れても運用をできなければ意味がありません。

運用がしっかりできるようになるためにも、予め仕組みを理解しておいた方が、より運用がしやすくなります。

自社でシステムを内製化する主なデメリットは以下の通りです。

自社で運用を行うためには、仕組みの理解が対前提となりますので、このあとのステップを順番に進んでください。

構築までの３ステップ

今回紹介する仕組みは、Googleの仕組みを活用して勤怠の自動集計をさせます。

仕組みとしては単純で、次の３つのステップで運用を行います。

たった３ステップで、簡単にシステムは出来上がります。

このたった3つのステップを理解して自動集計の仕組みを理解すれば、もはやクラウドシステムを使う理由が見当たりません。

ステップ①：仕組みに必要なものを知る

まずは仕組みに必要なものを知りましょう。

これらを使って、時間を送信、集計を自動化させていきます。

ステップ②：それぞれを作成＆接続

(1).まずはスプレッドシートを作成します

スプレッドシートに必要な項目は「従業員名簿」と「従業員ごとの勤怠管理シート」のみ。

従業員名簿の氏名と、個別シートのシート名をそろえます。

実際の運用ではこの画像のように、実際に使っているといろいろ追加したくなると思いますので、運送会社で使っているデモ版を置いておくので参考にしてみてください。

デモサイトでは実際にひと月分、運送会社の勤怠集計が行われたものがそのまま確認できます。

従業員名簿の名前や、単価などの機密情報は変更されていますが、勤務時間に関してはそのままになっています。残業時間があまり好ましくない人が数名いますが、あえて事実のままにしています。

クリックした先は、デモ用勤怠シートになっていて、デモ用送信フォームと連動していますので、フォームから送信すると、時間が反映されることが確認できると思います。

デモ版の管理シートは、主に「従業員名簿」「給与元データ集計」「案件一覧」「各従業員の勤怠シート」から成っています。

作成の参考にしてください。

(2).次にフォームを作成します

スプレッドシートから作成すると、自動的に接続した状態になります。

フォームに必要な項目は、「氏名」と「出勤」「退勤」が必要です。

氏名の項目をプルダウンで作成、従業員名簿から氏名をコピーします。

出退勤は、ラジオボタンで選択式にします。

(3).フォームとスプレッドシートをつなぐ

スプレッドシートからフォームを作成すると、自動的につながりますが、フォームから作った場合は手動で接続する必要があります。

スプレッドシートの編集画面から、「回答」「スプレッドシートにリンク」をクリック。

接続したいスプレッドシートを選択します。

ここまでは、ただ作るだけなのでエクセルとたいして変わりません。

フォームとスプレッドシートが接続されていると、送信結果がタイムスタンプと共に表示されます。

これだけでもタイムカードいらずになりますね＾＾

(4).GASをセットして時間を飛ばす

このタイムスタンプで飛んでくる時間を、勤怠管理の集計に使います。

これなら履歴が全部でてくるので打ち間違いの確認や不正防止としても機能するため、そのままタイムスタンプの時間を勤怠シートの「出勤」と「退勤」に飛ばします。

あとは、スプレッドシートが自動で計算してくれるという簡易的な仕組みです。

スプレッドシートなら計算式を入れておくだけで、簡単に編集ができるため、会社の就業規則に合わせて計算式を組めば、給与計算まで簡単につくることができます。

GASのセットも文章にすると長いけど、やることは『この動画のように』たった40秒ほどで終わります。

動画内でも行っていますが、具体的には以下の3点を行っています。

一番重要で、一番大変なGASのコードですが、実は、ChatGPTを活用するとサクッと作ってくれたりします。

聞き方にコツが要りますが、やりたいことを明確にして具体的に質問すれば簡単に作れたりします。

GASのコードを記述

以下は、実際に販売しているGASのコードになりますので、参考にしてみてください。

sendReportで、{}内のデータを、スプレッドシートの指定の位置に飛ばしているイメージです。

function sendReport(e) {
  
  // フォームの回答データ
  let time1 = e.namedValues['タイムスタンプ'];
  let name = e.namedValues['氏名'];
  let work = e.namedValues['作業内容'];
  let attendance = e.namedValues['出勤・退勤'];

  // スプレッドシート読み込み
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let m_sheet = ss.getSheetByName('案件一覧');
  let sheet = ss.getSheetByName(name); /* 氏名に対応するシート */

  // 日によって対象行を決める
  let today = new Date(); /* 今日の日付 例：2022/06/01 */
  let d = Utilities.formatDate(today, "Asia/Tokyo", "dd"); /* 例：1 */
  let row = Number(d) +8; /* 例：9 */

  // タイムスタンプの時間表示
  let time = Utilities.formatDate(new Date(time1), "Asia/Tokyo", "H:mm"); /* 例：15:30 */
  let time_h = Utilities.formatDate(new Date(time1), "Asia/Tokyo", "H"); /* 例：15 */

  // フォーム回答のシート記入
  if(!sheet.getRange(row, 3).getValue()){
    sheet.getRange(row, 3).setValue(work); /* 作業内容 */
  }
  
  if(attendance == '出勤'){
    sheet.getRange(row, 6).setValue(time); /* 出勤 */
  }

  if(attendance == '退勤' && time_h <= 5){
    let split = time.split(":");
    let h = split[0]; /* ○時 */
    let m = split[1] /* ○分 */
    let h_tf = Number(h) +24; /* 1時→25時 */
    let time_t = h_tf +":" + m; /* 1:30→25:30 */

    sheet.getRange(row -1, 12).setValue(time_t); /* 退勤 （0:00から5:59の場合、前日に表示する） */
  }else if(attendance == '退勤'){
    sheet.getRange(row, 12).setValue(time);
  }

  sheet.getRange(row,9).setValue(60); /* 休憩 */

  // 案件一覧から作業内容をもとに出庫・帰庫の基礎勤務時間を取得する
  let targetRange = m_sheet.getRange('B:B');
  let textFinder = targetRange.createTextFinder(work);
  let find = textFinder.findAll();

  find.forEach(function(rng){
    let cell = rng.getA1Notation();
    let sliceRow = cell.slice(1);

    let delivery1 = m_sheet.getRange(sliceRow, 3).getValue();
    let delivery2 = delivery1.setMinutes(delivery1.getMinutes());
    let delivery  = Utilities.formatDate(delivery1, "Asia/Tokyo", "H:mm");

    sheet.getRange(row, 8).setValue(delivery); /* 出庫 */

    let home1 = m_sheet.getRange(sliceRow, 4).getValue();
    let home2 = home1.setMinutes(home1.getMinutes());
    let home  = Utilities.formatDate(home1, "Asia/Tokyo", "H:mm");

    sheet.getRange(row, 10).setValue(home); /* 帰庫 */
  });
  
}

以下、細かく見ていきましょう。

GASコードの詳細

  // フォームの回答データ
  let time1 = e.namedValues['タイムスタンプ'];
  let name = e.namedValues['氏名'];
  let work = e.namedValues['作業内容'];
  let attendance = e.namedValues['出勤・退勤'];

フォームからは「氏名」「作業内容」「出勤・退勤」のデータが送られてきます。「タイムスタンプ」は、送信した時間が記録されるデータです。

  // スプレッドシート読み込み
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let m_sheet = ss.getSheetByName('案件一覧');
  let sheet = ss.getSheetByName(name); /* 氏名に対応するシート */

これらのデータを各従業員ごとのシートにそれぞれ反映させるようにしています。

  // 日によって対象行を決める
  let today = new Date(); /* 今日の日付 例：2022/06/01 */
  let d = Utilities.formatDate(today, "Asia/Tokyo", "dd"); /* 例：1 */
  let row = Number(d) +8; /* 例：9 */

このコードは、特定の日付に基づいてスプレッドシート内の特定の行を特定するための計算を行っています。

デモページでは、1日が9行目になので、Number(d) +8と指定しています。

  // タイムスタンプの時間表示
  let time = Utilities.formatDate(new Date(time1), "Asia/Tokyo", "H:mm"); /* 例：15:30 */
  let time_h = Utilities.formatDate(new Date(time1), "Asia/Tokyo", "H"); /* 例：15 */

このコードは、タイムスタンプから時間と分、または時間のみを抽出し、指定されたタイムゾーンの時間を表示します。

  // フォーム回答のシート記入
  if(!sheet.getRange(row, 3).getValue()){
    sheet.getRange(row, 3).setValue(work); /* 作業内容 */
  }
  
  if(attendance == '出勤'){
    sheet.getRange(row, 6).setValue(time); /* 出勤 */
  }

  if(attendance == '退勤' && time_h <= 5){
    let split = time.split(":");
    let h = split[0]; /* ○時 */
    let m = split[1] /* ○分 */
    let h_tf = Number(h) +24; /* 1時→25時 */
    let time_t = h_tf +":" + m; /* 1:30→25:30 */

    sheet.getRange(row -1, 12).setValue(time_t); /* 退勤 （0:00から5:59の場合、前日に表示する） */
  }else if(attendance == '退勤'){
    sheet.getRange(row, 12).setValue(time);
  }

  sheet.getRange(row,9).setValue(60); /* 休憩 */

このコードは、作業内容を３列目、出勤時間を6列目、退勤時間を12列目に指定しています。

休憩時間は自動で60分が反映させるようになっています。【sheet.getRange(row,9).setValue(“”); /* 休憩 */】このように「60→””」にすれば空欄を指定できます。

また、夜勤後の退勤時間を『前日』に表示するように指定しています。【if(attendance == ‘退勤’ && time_h <= 5)】の「5」は0:00から5:59の場合、前日に表示するように指定しています。通常の退勤と被らない時間を指定することで、柔軟な計算が可能になります。

  // 案件一覧から作業内容をもとに出庫・帰庫の基礎勤務時間を取得する
  let targetRange = m_sheet.getRange('B:B');
  let textFinder = targetRange.createTextFinder(work);
  let find = textFinder.findAll();

  find.forEach(function(rng){
    let cell = rng.getA1Notation();
    let sliceRow = cell.slice(1);

    let delivery1 = m_sheet.getRange(sliceRow, 3).getValue();
    let delivery2 = delivery1.setMinutes(delivery1.getMinutes());
    let delivery  = Utilities.formatDate(delivery1, "Asia/Tokyo", "H:mm");

    sheet.getRange(row, 8).setValue(delivery); /* 出庫 */

    let home1 = m_sheet.getRange(sliceRow, 4).getValue();
    let home2 = home1.setMinutes(home1.getMinutes());
    let home  = Utilities.formatDate(home1, "Asia/Tokyo", "H:mm");

    sheet.getRange(row, 10).setValue(home); /* 帰庫 */
  });

このコードでは、「案件一覧」というシートのB列に記載されている内容を、各従業員のシートの3列目に飛ばすように指定しています。同時に、案件一覧に記載されているC列（3列目）とD列（4列目）に記載された時間を、それぞれ各従業員シートの8列目と10列目に飛ばすように指定しています。

これにより、案件一覧で指定した勤務時間を、指定勤務時間として管理できるようにしました。

シフト制の働き方や、勤務帯がバラバラの職場では、このように作業内容を選択させることで出勤時間を指定できるので、勝手に早出にならないような管理が簡単にできます。

トリガーのセットとアカウントの許可

メニュー「拡張機能」から「App Script」を選択。

設定を選択して、「appsscript.json」マニフェスト ファイルをエディタで表示する、に「チェック」を入れる。

時計マークから「トリガーを追加」をクリック、「フォーム送信時」を選択して保存。

自身のアカウントを選択して、トリガー（フォーム送信時）をセットして記述したGASを起動するように接続します。

ステップ③：毎月コピーして運用

これで、運用をするための原紙が完成しましたので、あとは毎月この原紙をコピーして管理を行うことで簡単に運用することができます。

基本的には、コピーをして運用を行うため難しい知識が必要は必要ありません。

そのため、担当者が属人化することもなく、会社として管理を行うことは可能です。

注意点としては、原紙をコピーして運用をしなければ、時間がすでに反映されたものを使いまわすことになるため、原紙で従業員名簿を最新にして管理を行い、毎月の勤怠はコピーして運用をするということくらいだと思います。

まとめ

今回の記事は、面倒な勤怠集計を完全に自動化させる方法です。

単純な自動化であれば、クラウドシステムを使用する方が信頼性は高いし、法律への対応もシステム側が考えてくれます。

しかし、今回お伝えした内容は、そういったデメリットを踏まえつつ自社で内製化してしまおうというノウハウです。

勤怠の集計自体は誰でも簡単に行えるものですが、唯一『入力作業』という、必ずヒューマンエラーが起こる作業だけがなかなか自動化できませんでした。

クラウドシステムは、その部分に対してお金を払っているといっても過言ではありません。

今回ご紹介したものであれば、管理費は一切かからない上に、管理時間の短縮につながり、さらに、一番ネックだったヒューマンエラーの対応も可能になります。

集計するスプレッドシートさえあれば、設定自体は10分もかかりませんので、ぜひ、自社管理型の自動集計システムを構築してみてください。

質問のある方や詳細情報を知りたい方は、LINEからご連絡ください。

感想をいただけると嬉しくなって、より大きなノウハウを提供しちゃうと思います＾＾