AppSheetでタイムレコーダーを作成する
2024年、1997年からイントラネットのオンプレ環境で提供されてきたグループウェアである「サイボウズOffice」が販売終了となり、以降はクラウドベースのサイボウズOfficeか？もしくはGaroonに移行が必要です。MSやGoogleにとっては１つのチャンスです。
ですが、Microsoft365もGoogle Workspaceも「タイムカード」な機能が備わっていません。よって、今回このタイムカード部分をAppSheetを使って構築してみたいと思います。
今回使うツール等
- AppSheetでタイムレコーダー
- 記録用のスプレッドシート - Google Spreadsheet
- 勤怠打刻スクリプト - Google Apps Script
タイムレコーダアプリのテンプレートから「Copy and Customize」をクリックすると自分の環境にコピーされて使えるようになります。
人事経験者から見た時、タイムレコーダーに求められる機能は主に以下のようなものになると思います。今回はタイムレコーダーであって、勤怠管理システムではない点に注意が必要です。
- 毎日の出勤・退勤打刻機能
- 間違った場合の修正申請機能（本人に手動で修正はやらせない）
- 自身の過去の勤怠記録の閲覧機能
- 管理者が出勤簿の集計や修正申請に基づいて直す機能
- 勤怠締め日近くになったら未入力箇所を本人に通知する機能（ただしこれは休暇申請などのデータが別途必要になる）。
- 管理者とユーザとで画面構成を切り替える機能
- 通常の9時〜17時だけじゃなく、深夜勤対応・三交代制対応・フレックス対応(コアタイム有り)といった各種勤務体制に対する対応（特に実労働時間の計算や遅刻・早退の計算基準が全て異なる為）
- 変形労働時間制採用時の残業時間の算出（1ヶ月単位や1年単位がある）
- スプシに対する祝日や休日の対応（会社によって土日祝が休みとは限らないし、年末年始なども対応が異なる）
- 勤怠締め日の違い（月末締めなのか？20日締めなのか？等）
- 有給休暇対応（午前休、午後休、時間休、振替休日など）
- 週40時間を超過した場合や深夜勤務時間、土日出勤、振替休日などを勘案した割増賃金対象の計算
- 在宅勤務か？出社したのか？といったフラグによる手当の支給管理
- スマフォやPCではなく端末を用意して、NFCやバーコードによる出退勤システムを構築する（アルバイトのように個別端末の無い者に対する勤怠記録手段の用意）
- 日付跨ぎの残業時の退勤打刻対応
- 休憩時間の厳格な計算対応
- 遅刻早退時や午前休・午後休時の昼食時間分の差し引き可否に関するロジックの装備
故にタイムレコーダを汎用で作るといっても、結構大変なのです。今回はとりあえずこの中で「一般的な企業」を対象としたものとして作成しようと思っています。上記のうち青文字で記したものが対象になります。
上記全部を実装するとなると「ほぼ勤怠管理システム」となってしまうので、AppSheetでどこまで作り込むか？は要判断となります。
ライバル製品
他サービスに移行で伴う課題点
Microsoft365もGoogle WorkspaceもサイボウズOfficeのような旧来のグループウェアとは異なりいくつか重要な機能が搭載されていないので、移行する場合には課題があります。SIer等に移行を依頼すると必ず抜けポイントのある点です。
- タイムカード：いわゆる出退勤を管理する簡易的な機能（今回のテーマ）
- 電話メモ：電話を受電して他の人へのメモとして残しておく機能
- ワークフロー：社内稟議や各種申請フォーム関係で承認ルートを必要とする
- プロジェクト：プロジェクト進行管理などを行うガントチャート的機能
- 掲示板：社内掲示板機能。お知らせやファイルのアップなど
- 報告書：営業報告や活動報告などを逐次行うフォーム機能
- 組織カレンダー：ここが抜け漏れポイント。M365やGWSでは備わっていない縦に人や組織、横に日付を取った独特なカレンダー。
電話メモなどはAppSheetで既に作成されてる方がいました。ワークフロー系はAppSheetでは少々ロジックが必要になるのでちょっとハードルが高い。プロジェクト進行や掲示板などは自分の場合過去にGASでウェブアプリ作成していたりしますので置き換え可能です。
そして今回のテーマからはハズレますが、文化の違いで一向に装備されることのない「組織カレンダー」。サイボウズ独特のカレンダーの代表例とも言える機能で、これはAppSheetでは置き換え出来ません。非常に需要が高いのに存在しないので移行の大きな壁になります。GASで作る場合はFullCalendarの有償版を購入すれば実装可能なのですが、自分の場合Vis.jsを使って作成しました。
そして、タイムカード機能。グループウェアの１つの目玉機能なのです。
問題はこれらをそれぞれ個別のサードパーティ製品で提案すると非常に嫌がられるでしょう。つまり、こういったものは移行に際して必要になるものなので、社内で予め用意しておきたいメジャーなアプリ達なのです。
電話の引き継ぎアプリ作成しました。
自分でアプリ作れるようになるなんて、すごい時代になったな。#protoout #appsheet #デジタル #初心者 pic.twitter.com/JFuwqSMt2I— くろ (@Av2Tuq) November 5, 2024
解決策としてのサードパーティ製品
これらの課題点に対する解決策はAppSheetやGASを利用して構築する事以外に、既にいくつかGoogle Workspace連携や本格的なアプリとして移行すべきというものがあります。
- rakumo : 組織カレンダー、ワークフロー、掲示板、タイムレコーダー、経費申請が備わっていて、Google Workspace連携しています。
- サテライトオフィス：組織カレンダー、タイムレコーダー、掲示板、ワークフロー、ドキュメント管理、拡張フォームだけに留まらず、他にも多数のアドオンが用意されています。Google Workspace / Microsoft365連携しています。一番オススメ。
- バクラク：本格的な社内の各種申請系ワークフロー対応で電子帳簿保存法対応もしてるソリューション。本来ワークフローは専用の製品を使ったほうが望ましい。
概ね上記のソリューションでサイボウズOfficeの機能はカバー可能です。またサテライトオフィスの製品は非常に安価なので導入しやすいと思います。もし、自分で構築しないという選択であるならば、新規導入時や移行時には一考するべきでしょう。
アプリを作成する
概要
今回のアプリは、平日昼間勤務を行い、年間の休日についても土日祝とカレンダー通りの企業を想定して作成しています。冒頭にあるように企業によって勤務形態や体制、休日の差異など多々あるため、全てに対応するように構築するというのはなかなか大変です。
しかし、自身で作る場合には自身の会社の体制にだけ合わせて構築すれば良いので、全てを網羅する必要はありません。朝9時を始業とし、夕方17時を終業、昼休み1時間として考えますが、これらの設定値はGAS側でプロパティに格納し、計算時に変動するようにします。単純に記録してオシマイというわけにはいかないのが「タイムレコーダー」なのです。
事前準備
ユーザ情報の整備
今回のアプリとスクリプトは割とシンプルなので、スプレッドシート上では「userシート」上にユーザの情報を整備します。打刻時にこれらのデータを使ってユーザ名などを自動補完しますので、ユーザは打刻時に自分自身でユーザ名等を入れる必要はなくなります。
メアド、ユーザ名、部署名、役割の4点を入力して整備しましょう。特に役割は管理者の場合は承認が出来る画面が出てきます。
図：メアドとユーザ名、部署を整備する
GAS側の準備
スタンドアローンスクリプトの用意
今回のAppSheetアプリはバックエンドにGASを利用しています。まずはAppSheetで利用するスタンドアローンGASを準備してコードを記述しておきます。
- Google Driveを開いて、左上の新規をクリック
- その他 => Google Apps Scriptをクリック
- スクリプトエディタが直接開いて警告文が出るのでそのまま作成を続行する
- ドライブにはスタンドアローンファイルが生成される
- こちらのスクリプト内容をコピペして保存する。
- ファイル名は「勤怠打刻スクリプト」とでもつけておく
見つからない場合には、Chrome Webstoreから拡張機能インストールしましょう（Google Apps Scriptで検索すると出てくる）。
図：新規から作成することが出来る
スクリプトプロパティ
GAS側は少し整備が必要です。主に以下の4点を整備します。
- コード最上部にあるssid変数に読み書き先のスプシのIDを入力します。
- スクリプトプロパティには以下の3点を整備します。
- starttimeは業務の規定開始時刻を入れます
- endtimeは業務の規定終了時刻を入れます
- lanchtimeは休憩で与える時間を入れます（通常1時間）
ただし、早退や遅刻時にこのlanchtimeを入れるか入れないか？の考慮が今回のコードには無いため、1時間で早退してしまうと、lanchtime分が差し引かれて労働時間0時間になってしまうので、ここは少しロジックが必要になります。勤怠管理システム側で考慮するならば、0:00:00で良いと思います。
図：各種既定値を整備する
Automationの中身を修正
GASの参照先を変更
前述までで、スタンドアローンスクリプトを用意しました。AppSheetのAutomationからGAS実行の項目で参照するべきファイルを指定してあげます。以下の手順で直します。
- AppSheetのアプリの開発画面を開く
- 左サイドバーのAutomationを開き、打刻の中にある「打刻」というBotを開きます。
- プロセスの中にある「GASで処理を実行」を開くと、右にApps Script Projectが出てきます。
- ファイル参照のアイコンをクリックして、自分が用意したスタンドアローンスクリプトを指定し直します。
- Function Nameをtimekunを選択し、koumokuの引数に「項目名」を指定する
- returnd valueはオンにしておき、返り値指定はStringを選択する。
- 右上のSAVEをクリックして保存する。
同様の手順で、修正申請の中の「承認却下通知」のbotに於いてもGASで処理を実行にて、以下のように設定します。
- Function Nameは「recalckintai」を指定する
- Function Parametersでは、以下のように設定する
- action = [_THISROW].[アクション]
- kintaiid = [_THISROW].[kintaiID]
- status = [_THISROW_AFTER].[ステータス]
- retime = [_THISROW].[修正時刻]
statusの引数だけthisrowのafterである点に注意が必要です。
図：GASファイルの参照先を変更
図：承認却下通知のボットの修正
打刻修正依頼通知ボットの修正
Automationの中にある「打刻修正依頼通知」には管理者宛に打刻修正のメール通知を飛ばす機能を備えています。ここで利用する時のToのアドレスを実際に使う現場では、管理者のアドレスに書き換える必要性があります。
グループアドレスでも飛ぶと思いますので承認する人事担当者などのアドレス等を入れると良いでしょう。
図：ここのアドレスを書き換えが必須です
スプシで日付時刻を扱う場合の注意点
今回のアプリの仕組みでは、労働時間、遅刻早退時間、残業時間の算出はGoogle Apps Script側に個別にGASで関数を用意して、差分を計算させているため、分単位での正確な時刻を計算出来ています。しかし、もしこれらのデータからご自分でなんらかのデータの解析を行って、時刻の差分計算をする場合、かなり注意を要するのが「スプシの数式で時刻計算をするとオカシナ数値が出る」という点。自分もはまりました。
これはスプシに問題があるのではなく、ユーザ側の作業に問題があります。サンプルにもスプシ数式問題としてシートを別個追加しています。以下のエントリーにこの問題点について別途まとめましたので、労働時間集計などをする場合には要注意の内容です。
スプレッドシート設計
アプリを実装するための土台となるデータソース部分であるスプレッドシートも今回は結構作り込みました。あえて２つに分割してるシートもあります。
kintaiシート
勤怠打刻情報を主に担当してるシートです。打刻されるとGASによって情報が細分化されて年月日個別のデータや、その際のユニークIDとしてID列に記録され、これが重要な役割を果たします。
ユニークIDは他のシートではKintaiID列として連結することになります。
図：一番メインとなる管理シートです。
kintai_subシート
前述のkintaiシートと同時に記録されるサブのシート。kintaiシートとは1:1で対応します。ここでは出勤時間と退勤時間の２つを使って、様々な時間を「分単位」で計算記録します。
労働時間：単純な出勤時間と退勤時間の差を計算
休憩時間：スクリプトプロパティに記録しておいた休憩時間を記録
実労働時間：労働時間 - 休憩時間の差分を記録
勤務フラグ：複数値選択のプルダウン。自動的に出勤時間と退勤時間から「遅刻と早退」のフラグを算出して付けます。
遅刻時間：スクリプトプロパティのstarttimeから出勤時間の差分を計算して記録
早退時間：スクリプトプロパティのendtimeから退勤時間の差分を計算して記録
深夜時間：22:00以降の残業分はここに記録（但し翌朝5時までしか最大で算出しません）
残業時間：スクリプトプロパティのendtimeから退勤時間までの差のうち、深夜時間に抵触しない時間帯の分を記録
修正可否：ここにチェックが入ると、ユーザは修正申請ができなくなります。勤怠締めをするとこのチェックが実施月の前月分までのデータについてチェックが入るようになります。
図：様々な時間を自動計算して記録します。
集計表シート
ここは管理者が利用するものでkintaiとkintai_subをQuery関数でkintaiIDを軸に連結し擬似外部結合して１つのテーブルにしています。
上部の年月のプルダウンは後述のnengetsuシートの一覧から拾っており、ここをいじると集計対象の年月が変わります。ここで１ヶ月分の勤怠データの集計をして、実際の給与計算に繋げるために利用できます。
テーブルを利用しつつQuery関数で処理をしてるため、数式的には以下のような連結と列の入れ替え、年月フィルタも実施しています。
=query({kintai[[ID]:[退勤時刻]],iferror(arrayformula(vlookup(kintai[ID],kintai_sub[[kintaiID]:[修正可否]],{3,4,5,6,7,8,9,10,11,12,13,14},false)))},"select Col1,Col2,Col9,Col10,Col3,Col4,Col5,Col6,Col7,Col8,Col11,Col12,Col13,Col14,Col15,Col16,Col17,Col18,Col19 where Col4=" & C1 & "and Col5=" & E1)
修正申請シート
勤怠打刻に於いて以下のような場合にユーザは修正申告をすることができ、その内容を記録するために存在しています。ユーザ・管理者の両方が利用します。
次項の修正可に出力されてる内容からユーザは修正を依頼することが可能になっています。出勤打刻修正や退勤打刻修正を提出し、承認されると実際に時刻が修正できる仕組みで、ユーザ側では勝手に時刻修正はできないようになっています。ただし、ステータスが差し戻しの場合は再度おなじ依頼内容で再修正をして提出が可能です。
これは打刻忘れの場合にも利用します。
図：修正依頼をだしてみた
修正可リスト
中身は基本、集計表同様にQuery関数でkintaiとkintai_subを連結している表になりますが、抽出条件は年月ではなく「修正可否」が空っぽであることが条件になっています。管理側で勤怠締めをした時点で対象のレコードは修正可否にチェックが入って、修正依頼のリストに出てこなくなるので要注意。
数式的には以下のような形になっています。
=query({kintai[[ID]:[退勤時刻]],iferror(arrayformula(vlookup(kintai[ID],kintai_sub[[kintaiID]:[修正可否]],{3,4,5,6,7,8,9,10,11,12,13,14},false)))},"select Col1,Col2,Col9,Col10,Col3,Col4,Col5,Col6,Col7,Col8,Col11,Col12,Col13,Col14,Col15,Col16,Col17,Col18,Col19 where Col19=''")
図：修正可な勤怠データのリストになります
その他
打刻シート
打刻シートは、アプリ管理用のもので、出勤と打刻の２つのボタンのようなものを叩くためのデータになりますので通常弄ることはありません。アクションにてこの更新フラグの値を変更すると、GASが走って打刻される仕組みになっています。
AppSheetは現在ボタンをクリックしてGASを直接起動ができないので、テーブルを更新させてAutomationのUpdateを利用して発火させています。打刻ボタンのようなものの画像のデータもここで扱っています。
図：アプリの打刻ボタン用
nengetsuシート
申請されたkintaiシートに於いて、年と月をUnique関数で抽出し、集計表のプルダウンで利用するための管理用シートですので通常メンテナンスする必要はありません。
図：集計表プルダウン用
アプリの実装
AppSheet側の実装ポイント
今回のアプリ、見た目はシンプルでギミックも少ないように見えて、AppSheetのいろいろなギミックを用いる必要がありました。そのため思ってる以上に実装するのはスキルが必要です。そのポイントになる部分を解説します。
結構頭悩ましましたが、それでもこれだけ高速に実装できるのは、AppSheetならでは。もうちょっとケース別の作成知見が増えてくれば、悩まずに済むかもしれない。
打刻ボタン
打刻ボタンは過去のエントリーでも利用してるギミックを使っています。ボタンから直接GASを呼び出せないので、専用のテーブルの特定列の値を意図的に更新掛けるアクションを使って、Automationを発動させるようにし、それぞれのボタンの役割をもって、GAS側で打刻発火させるようにしています。
以下のエントリーの「フォーム上のボタンを押して実行できないの？」で詳しく解説しています。
打刻修正と承認作業画面のボタン
打刻修正と承認作業のタブは同じテーブルを元にビューを作っています。そのため、ギミックを入れないでアクションボタンを作ってしまうと、両方に両方のボタンが常に表示されるオカシナ状況になってしまいます。
そこでビューの名称を元に判定し表示・非表示を行なっています。
例えば、承認作業の申請を開いたときに出る「承認却下」のアクションボタンのBehavior => Only if this condition is trueに対しては以下のような条件式を入れています。管理用_Detailの時且つステータスが申請中の時にボタンを表示という仕組みです。
if(CONTEXT("VIEW")="管理用_Detail",if([ステータス]="申請中",true,false),false)
逆に、打刻修正の修正用のEditボタンは以下のような条件式を入れています。管理用_Detailの場合は非表示にし、そうでない場合は、ステータスが差し戻しの場合は表示するという仕組みです。
if(CONTEXT("VIEW")="管理用_Detail",false,
if(IN(LOOKUP([kintaiID] ,"修正申請","kintaiID","ステータス"),list("差し戻し")),true,false))
図：ボタンの表示非表示にも気を配る必要性
打刻修正と承認作業のSAVE時イベント
通常、フォームで利用してるテーブルにAddやUpdateが掛かると、AutomationでGASなどを発動させることができます。しかし今回の承認用のテーブルは打刻修正と承認作業で同じものを利用しているため、そのままでは片方でだけ動いて欲しい時でも両方の作業で意図しないAutomationが発動してしまいます。
よって、修正申請のUserTriggerとAdminTriggerの2列を用意し、単純に修正申請に読み書きされたからといって意図せず発動しないようにロジックを組みます。
以下打刻修正フォームの場合のロジックです。
- UserTrigger更新というアクションを作成し、「Data: set the value of some column in this row」を選択し、4つの値書き込みを入れています。
- UserTriggerにトリガー発火用のUNIQUEID()を入力
- 申請後にステータスを「申請中」に
- 承認作業を空にしてるのは差し戻し時にはここが「差し戻し」の文字が入ってるためクリアする為です。
- コメント欄も同様に空にする
- １つ目は打刻修正のFormに対してBehavior => Event ActionsをUserTrigger更新のアクションを割り当てています。
- Automation側はConditionにて、以下の数式でUserTriggerが変更されたかどうかで発火を決定しています。
[_THISROW_BEFORE].[UserTrigger] <> [_THISROW_AFTER].[UserTrigger]
- 新規追加時と更新時のみに発動するようにしています。
- またセーブ後は元の一覧画面に戻るように「Finish view」を意図的に一覧画面のビューを指定して戻しています。
これで、承認却下時と同じテーブルに読み書きをしたとしても、分けて処理を発火させることが可能になります。AdminTrigger更新も似たようなアクションとして作って同じ仕組みで発火させています。
図：Trigger更新アクション
図：Automation側のロジック
対象の日付一覧ドロップダウン
打刻修正の新規作成画面に於いて、対象の日付に自分の打刻した「日付」だけを表示していますが、これは2つのロジックで実現しています。またNewなどが表示しないようにする必要性もあります。
詳細は以下のエントリーにて実装の方法をまとめていますが、実態は日付ではなく「KintaiID」を選ばせています。
- 修正申請のテーブルのKintaiIDについてはRefで修正可リストのユーザ別という自分が打刻したリストのみをスライスしたものにRefでリレーションを貼っています。
- また、修正可リストのTable SettingsはUpdateとDeleteのみとしてNewの表示を抑制しています。
- 同じくユーザ別スライスもUpdateとDeleteのみとし、Row filter conditionを「[Mail]=USEREMAIL()」で自分自身のものだけにフィルタリングしてます。
図：SliceとRefでのリレーションがポイント
承認作業タブは管理者にだけ表示
userシートに管理者の役割がある人だけアプリ表示時に「承認作業」のタブが表示するようにロジックを入れています。フォームのShow Ifに対して以下の数式を入れて判定し、表示・非表示しています。
if(LOOKUP(USEREMAIL(),"user","mail","役割")="管理者",true,false)
図：showifに判定をいれている
承認却下通知のAutomation
承認却下通知は前述のようにAdminTriggerの値の更新によって発火するようになっています。この時GAS側にパラメータを渡す必要があるのですが、Trigger列の更新後のステータスの値を渡したいのですが、既存のデータは「[_THISROW].[アクション]」でアクション列の値を渡すことが可能です。
しかし、同じことをステータス欄でやってしまうと、Triggerの値更新→読み書き完了前にGAS発動してしまい、更新前の値がstatusのparamに渡ってしまいます（処理が間に合っていない）。よって、ここだけは「[_THISROW_AFTER].[ステータス]」という形で、Afterの値を渡すように指定する必要性があります。
図：Afterの値でなければならない
修正用シートと管理用のスライス
打刻修正申請と管理用という形で修正申請のテーブルから2つのスライスを作成しています。それぞれフィルタの方法が異なっており、前者は自分自身が申請をあげたものだけとしてフィルタし、後者はフィルタはせず全てを表示しつつ、Update modeは「Update」のみとしています（新規追加や削除はできない）。
これはそれぞれで用途が異なる為という点と、打刻申請で他人の打刻データが出てしまっては問題になるため。管理用はむしろ全部出てくれないと困る。
そしてそれぞれのフォームにこれらのスライスをソースとして使うように設定しています。
図：用途に応じて2種類のスライスを用意
GAS側の実装
スプレッドシート上
勤怠管理シートのスプシ上で利用するスクリプトで、「勤怠締めして前月までの勤怠データの修正可否をチェックしてロックする」役目をもっています。悪魔でも勤怠締めした月の前月までであって、勤怠締めした日までではないので注意。
先月末の日付を取得し、修正可リストに残ってるもので勤務基準日列の値を比較し、古いものはすべてkintai_sub側のレコードにチェックが入るようになります。これでいつまでも修正申請できるような状況を防ぐことが可能です。
//勤怠締めの実行
function kintaiclose(){
//スプシを取得
let ss = SpreadsheetApp.getActiveSpreadsheet();
let ui = SpreadsheetApp.getUi();
let sheet = ss.getSheetByName("kintai_sub");
let target = ss.getSheetByName("修正可リスト");
//先月末の日付を取得
let dt = new Date();
let lastdate = new Date(dt.getFullYear(), dt.getMonth(), 0)
//スプシデータを取得する
let data = target.getRange("A2:S").getValues();
let targetdata = sheet.getRange("A2:N").getValues();
//dataを回し該当日付があるかどうか？
for(let i = 0;i<data.length;i++){
//dataから1個取り出す
let rec = data[i];
//空データだったらスルーする
if(rec[0] == "" || rec[0] == undefined){
continue;
}
//lastdate以前の日付かどうか？
if(new Date(rec[4]) <= lastdate){
//dataのkintaiIDを取得する
let kintaiid = rec[0];
//targetdataを回して該当のレコードの修正可否チェックを入れる
for(let j = 0;j<targetdata.length;j++){
//レコードを一個取り出す
let tarrec = targetdata[j];
//一致するか？
if(tarrec[1] == kintaiid){
//書き込みレコード番号
let num = j + 2;
//対象のtargetの修正可否列の値をTrueにする
sheet.getRange("N" + num).setValue(true);
}
}
}
}
//終了処理
ui.alert("勤怠締め終了しました。");
}
スタンドアローンスクリプト
こちらは、AppSheetのAutomationから呼び出される各種作業を担当するスクリプトで、スタンドアローン形式のGASになります。
打刻用のスクリプト
出勤・退勤の打刻のためのスクリプトで、kintaiとkintai_subに対して読み書きをしています。いくつかのパターンに対応するため、以下のような仕様で実装しています。
- 出勤打刻は通常通りレコードを追加し、遅刻時には自動計算します。
- 退勤打刻も通常通り既存のレコードに追記し、早退時には自動計算します。
- 出勤打刻忘れの状態で退勤打刻をすると新規にレコードを追加し、退勤時刻のみを記入します。打刻修正申請をして承認してもらうことで出勤打刻を入れることが可能です（ユーザ側で勝手に追記はできません）
- 同じ日付で出勤打刻をしても、同日に打刻ありとして打刻を拒否するようになっています。
//読み書き先スプレッドシートのID
var ssid = "ここに読み書きするスプシのIDを入れる";
//打刻の実行
function timekun(koumoku) {
//スプレッドシートを取得
let ss = SpreadsheetApp.openById(ssid);
let sheet = ss.getSheetByName("kintai");
//ユーザを取得
let user = GetUser();
//GUIDを取得する
let guid = Utilities.getUuid();
//勤務基準日を取得する
let kijunday = dateformatter(new Date(),false);
//シートデータを取得する
let kintai = sheet.getRange("A2:I").getValues();
//発見用フラグ
let matchflg = false;
//koumokuによって処理を分岐
if(koumoku == "出勤"){
//mailとkijundayが一致するデータを探索
for(let i = 0;i<kintai.length;i++){
//レコードを一個取り出す
let rec = kintai[i];
//空チェック
if(rec[0] == "" || rec[0] == undefined){
continue;
}
//mailと一致するか？
if(user == rec[1]){
//kijundayと一致するか？
if(String(kijunday) == String(dateformatter(rec[2]),false)){
//書き込みフラグをオン
console.log("一致あり")
matchflg = true;
break;
}else{
console.log("一致なし")
}
}
}
//存在した場合は既にデータがあるので打刻出来ないようエラーで返す
if(matchflg == true){
return "⚠️既に本日分の打刻レコードが存在します。"
}
//出勤打刻の場合は新規レコードを入れる
//出勤時刻を取得する
let syukkin = dateformatter(new Date(),true);
//年月日を取得する
let nengetsu = splitdate(new Date());
//配列に値を格納する
let array = [
guid,
user,
kijunday,
nengetsu[0],
nengetsu[1],
nengetsu[2],
syukkin,
"",
];
//スプレッドシートに行を追加
sheet.appendRow(array);
//サブレコードを追加する
let result = subrecordUpdate(array,true);
//終了メッセージ
return "出勤打刻が完了しました。"
}else{
//退勤打刻の場合は既存レコードを探して追記
//mailとkijundayが一致するデータを探索
let syukkin = "";
for(let i = 0;i<kintai.length;i++){
//レコードを一個取り出す
let rec = kintai[i];
//mailと一致するか？
if(user == rec[1]){
//kijundayと一致するか？
if(String(kijunday) == String(dateformatter(rec[2],false))){
//書き込み行を特定
let cnt = i + 2;
//退勤時刻を取得
let taikin = dateformatter(new Date(),true);
//出勤時刻を取得
syukkin = rec[6];
//既存のGUIDを取得する
guid = rec[0];
//退勤時刻を追記する
sheet.getRange("H" + cnt).setValue(taikin);
//書き込みフラグをオン
matchflg = true;
break;
}
}
}
//kintai_sub用のデータを用意
//退勤時刻を取得
let taikin = dateformatter(new Date(),true);
//出勤時刻を取得する
if(syukkin == ""|| syukkin == undefined){
syukkin = ""
}else{
syukkin = dateformatter(syukkin,true);
}
//年月日を取得する
let nengetsu = splitdate(new Date());
//レコード用の配列を用意
let temparr = [
guid,
user,
kijunday,
nengetsu[0],
nengetsu[1],
nengetsu[2],
syukkin,
taikin,
]
//書き込みフラグがfalseのままだった場合、出勤打刻が存在しない
if(matchflg == false){
//スプレッドシートに行を追記する
sheet.appendRow(temparr);
//サブレコードをアップデートする
let result = subrecordUpdate(temparr,false);
//終了メッセージを返す
return "出勤打刻がありません。退勤打刻はされましたが、出勤時刻修正が必要です。"
}else{
//サブレコードをアップデートする
let result = subrecordUpdate(temparr,false);
//終了メッセージ
return "退勤打刻が完了しました。"
}
}
}
kintai_subに追記する関数
前述の関数から呼びされて、kintai_subに対して各種の時間を計算し追記します。仕様としては
- 出勤と退勤が揃ってる場合、退勤打刻時に労働時間や残業時間の計算も行い、kintai_subに対して追記します。
となっています。
//勤怠サブにレコードを登録する関数
function subrecordUpdate(array,flg){
//kintai_subを取得する
let ss = SpreadsheetApp.openById(ssid);
let sheet = ss.getSheetByName("kintai_sub");
let kinsub = sheet.getRange("A2:M").getValues();
let userlist = ss.getSheetByName("user").getRange("A2:C").getValues();
//GUIDを取得する
let guid = Utilities.getUuid();
//ユーザ情報
let user = array[1];
let kijunday = array[2];
let syukkin = array[6];
let taikin = array[7];
let masterid = array[0];
//user情報を取得する
let usrinfo = [];
for(let i = 0;i<userlist.length;i++){
//レコードを一個取り出す
let rec = userlist[i];
//メールアドレスが一致するものを見つける
if(user == rec[0]){
usrinfo = rec;
break;
}
}
//出勤と退勤で処理を分ける
if(flg == true){
//出勤の場合
//遅刻状態を算出
let delayflg = "";
let diff = checktime(kijunday,syukkin,true);
if(diff < 0){
diff = diff * -1;
delayflg = "遅刻";
}else{
diff = 0;
}
//配列を構成する
let temparr = [
guid, //自身のGUID
masterid, //親レコードID
user, //メールアドレス
usrinfo[1], //ユーザ名
usrinfo[2], //所属部署
"",
"",
"",
delayflg, //勤務フラグ
diff, //遅刻時間
"",
"",
""
]
//レコードを新規追加
sheet.appendRow(temparr);
//処理終了
return 0;
}else{
//退勤の場合
//早退状態を算出
let delayflg = "";
let fastdiff = checktime(kijunday,taikin,false);
console.log(fastdiff)
if(fastdiff < 0){
fastdiff = fastdiff * -1;
delayflg = "早退";
}else{
fastdiff = 0;
}
//各種時間を計算する
let lanchtimeman = lanchtime();
//深夜労働時間を算出
let lastnight = lastnighttime(kijunday,taikin);
//残業時間を計算
let zangyo = zangyotime(kijunday,taikin);
//遅刻時間を再計算
let delaytime = checktime(kijunday,syukkin,true);
if(delaytime < 0){
delaytime = delaytime * -1;
}else{
delaytime = 0;
}
//出勤時刻の有無をチェック
let calcman = [];
if(syukkin == "" || syukkin == undefined){
//出勤時刻がないので計算出来ない
calcman = [
"",
lanchtimeman,
"",
delayflg,
fastdiff,
lastnight,
""
]
}else{
//労働時間を算出する
let date1 = new Date(syukkin); //出勤時刻
let date2 = new Date(taikin); //退勤時刻
let total = date2.getTime() - date1.getTime(); //労働時間
total = total / (60*1000);
//実労働時間を算出する（労働時間から休憩時間分を差し引く）
let worktime = total - lanchtimeman;
calcman = [
total,
lanchtimeman,
worktime,
delayflg,
fastdiff,
lastnight,
zangyo,
delaytime
]
}
//kintai_subからkintaiIDを元にレコードを探し出す
let matchflg = false;
let cnt = 0;
let editrec = [];
for(let i = 0;i<kinsub.length;i++){
//レコードを一個取り出す
let rec = kinsub[i];
//親IDと一致するものがあるか？
if(rec[1] == masterid){
cnt = i + 2;
editrec = rec;
matchflg = true
break;
}
}
//matchflgがtrueならば追記、なければ新規追加
if(matchflg == true){
//勤務フラグに遅刻がある場合はdelayflgは書き換える
let flagcheck = editrec[8];
if(flagcheck.indexOf("遅刻") == -1){
if(delayflg == ""){
calcman[3] = "";
}else{
calcman[3] = delayflg;
}
}else{
if(delayflg == ""){
calcman[3] = "遅刻";
}else{
//カンマ区切りで切り直し
delayflg = ["遅刻",delayflg];
delayflg = delayflg.join(',');
calcman[3] = delayflg;
}
}
//既存のレコードに追記する
editrec[5] = calcman[0];
editrec[6] = calcman[1];
editrec[7] = calcman[2];
editrec[8] = calcman[3];
editrec[9] = calcman[7];
editrec[10] = calcman[4];
editrec[11] = calcman[5];
editrec[12] = calcman[6];
//対象レコードを上書き
//一括書き込み
let lastColumn = editrec.length; //カラムの数を取得する
let lastRow = 1; //行の数を取得する
//指定行に一気に書き込み
sheet.getRange(cnt,1,lastRow,lastColumn).setValues([editrec])
//終了処理
return 0;
}else{
//新規にkintai_subにレコードを追加
//サブレコードのGUID生成
let guidsub = Utilities.getUuid();
//配列を構成する
let temparr = [
guid, //自身のGUID
masterid, //親レコードID
user, //メールアドレス
usrinfo[1], //ユーザ名
usrinfo[2], //所属部署
"",
"",
"",
delayflg, //勤務フラグ
"",
fastdiff, //早退時間
lastnight, //深夜時間
zangyo //残業時間
]
//レコードを新規追加
sheet.appendRow(temparr);
//終了処理
return 0;
}
}
}
承認プロセス用のスクリプト
ユーザが申請してきた打刻修正の申請に対して、管理者が承認作業を行なった場合に発動する関数です。主な仕様としては
- ステータスが「承認」の場合にだけ発動します。却下や差し戻しの場合にはreturnでそのまま終了します。
- 申請削除機能は実装していません。
- 承認するだけじゃなく、打刻修正をkintaiに対して行い、労働時間等の再計算をしてkintai_subの該当レコードに対して上書きします。
//承認後に出勤・退勤時刻に応じてkintaiおよびkintai_subの値の再計算を行う
function recalckintai(action,kintaiid,status,retime){
//スプシを取得する
let ss = SpreadsheetApp.openById(ssid);
let kintai = ss.getSheetByName("kintai");
let kinsub = ss.getSheetByName("kintai_sub");
let master = kintai.getRange("A2:H").getValues();
let sub = kinsub.getRange("A2:N").getValues();
//ステータスが承認の場合だけ処理
if(status !== "承認"){
return 0;
}
//アクションが削除であった場合の処理
if(action == "削除"){
return 0;
}
//kintaiからまずは時刻修正
let kjunrec = [];
for(let i = 0;i<master.length;i++){
//レコードを一個取り出す
let rec = master[i];
//kintaiidが一致するか？
let temptime,num;
if(rec[0] == kintaiid){
//actionに応じて処理を分岐
switch(action){
case "出勤時刻修正":
//出勤時刻の値を取得
let syukkin = dateformatter(rec[6],false);
//訂正時刻を作る
temptime = syukkin + " " + retime;
//値をセットする
num = i + 2;
kintai.getRange("G" + num).setValue(temptime);
//kijunrecに値を格納
kjunrec = [temptime,rec[7]];
break;
case "退勤時刻修正":
//退勤時刻の値を取得
let taikin = dateformatter(rec[7],false);
//訂正時刻を作る
temptime = taikin + " " + retime;
//値をセットする
num = i + 2;
kinsub.getRange("H" + num).setValue(temptime);
//kijunrecに値を格納
kjunrec = [rec[6],temptime];
break;
}
break;
}
}
//kinsubのデータを再計算して修正
for(let i = 0;i<sub.length;i++){
//レコードを一個取り出す
let rec = sub[i];
//kintaiidが一致するか？
if(rec[1] == kintaiid){
//kjunrecの値を元にF〜M列の値を再計算して格納
//基準日を計算
let kijunday = dateformatter(kjunrec[0],false);
//労働時間を算出する
let syukkin = new Date(kjunrec[0]); //出勤時刻
let taikin = new Date(kjunrec[1]); //退勤時刻
let total = taikin.getTime() - syukkin.getTime(); //労働時間
total = total / (60*1000);
//各種時間を計算する
let lanchtimeman = lanchtime();
//深夜労働時間を算出
let lastnight = lastnighttime(kijunday,taikin);
//残業時間を計算
let zangyo = zangyotime(kijunday,taikin);
//勤務フラグ用
let kinflg = [];
//遅刻時間を再計算
let delaytime = checktime(kijunday,syukkin,true);
if(delaytime < 0){
delaytime = delaytime * -1;
kinflg.push("遅刻");
}else{
delaytime = 0;
}
//早退時間を再計算
let fastdiff = checktime(kijunday,taikin,false);
if(fastdiff < 0){
fastdiff = fastdiff * -1;
kinflg.push("早退");
}else{
fastdiff = 0;
}
//勤務フラグを確定する
kinflg = String(kinflg.join( ',' ));
//実労働時間を算出する（労働時間から休憩時間分を差し引く）
let worktime = total - lanchtimeman;
//書き込み用配列を用意
let = calcman = [[
total,
lanchtimeman,
worktime,
kinflg,
delaytime,
fastdiff,
lastnight,
zangyo
]]
//書き込み行を特定
let editnum = i + 2;
//一括書き込み
let lastColumn = calcman[0].length; //カラムの数を取得する
//F列に一括で書き込み
kinsub.getRange(editnum,6,1,lastColumn).setValues(calcman)
}
}
//終了処理
return true;
}
その他の関数
上記のメインの関数から呼びされる補助的な関数として
- zangyotime：残業時間を計算します
- lastnighttime：深夜残業時間を計算します
- checktime：遅刻時間を計算します
- lanchtime：休憩時間を分換算します
- splitdate：年月日を３つに分割して配列で返します。
- dateformatter：フラグによって年月日のデータを整形し、時刻ありとなしの２つで返します。
といったような補助的なものを用意しています。
使い方
ユーザ側
打刻画面
ユーザが毎日の出勤時・退勤時の打刻をするボタンだけがある画面です。基本的には押すだけ。
- 打刻するだけでGASが発動し、kintaiとkintai_subに追記されていきます
- 退勤だけは常に上書きで打刻時刻と再計算結果が更新されます。
- 出勤は同一に出勤打刻がある場合には打刻拒否されます。
- スマフォアプリの場合、打刻結果の通知が飛ぶようになっています。
図：毎日の打刻の画面
打刻一覧
前述の打刻で記録した内容は、カレンダーに表示されます。但しこの画面から打刻修正や打刻データの削除などの作業はユーザは行うことはできないようにしてあります。
図：打刻した結果がカレンダーで表示
打刻修正
この画面は自分が打刻修正依頼をしたもの一覧が表示されたり、新規に打刻修正依頼を行う画面です。打刻修正画面の仕様は以下の通りです。
- 一覧表示は自分が打刻修正依頼したもののみが表示されます。
- 既存の申請で修正や削除ができるのは、差し戻しされた場合のみ。その際にはボタンが表示されるようになる。
- 右下の新規のボタンで新規申請を作成することが可能です。
- 作成画面の対象の日付リストは自分が打刻したレコードだけが表示されるようになっています。
- アクションを選んで修正時刻とコメントを入れてSAVEをクリックする
- SAVEをクリックすることで、修正申請のUserTriggerに値を入れるアクションが動作し、同時に管理者宛に通知メールが飛びます（GASは未使用です）。
承認ステータスが「承認もしくは却下」になったら、処理は完了になります。
図：この画面から打刻の修正依頼をする
図：打刻修正依頼を新規作成する
図：打刻修正依頼の通知メール
管理者側
管理者側としての機能はユーザ側に加えて「承認作業」が登場します。これはuserシートで対象のメアドに対して「管理者」の役割を割り当てることで出るようになります。ユーザからあがってきた打刻修正の申請を開き、承認する作業を行います。
仕様としては以下のようになっています。
- 修正申請のステータスが「申請中」のものに対してだけ、承認却下のボタンが表示されます。
- 承認結果欄で承認・却下・差し戻しのいずれかを選んでコメントを入れる
- SAVEをクリックすると、修正申請のAdminTriggerに値を入れるアクションが実行されて、同時にGASが発動して「recalckintai関数」が動いて読み書きを行います。
- また作業後にユーザに対して、承認結果の通知メールが飛びます。
これだけの簡単仕様になっています。
図：承認作業タブが表示されます
図：承認作業はここで行う
図：通知メールの内容
関連動画
関連リンク
- 「そんな電話あったこと聞いてないよ」をなくせ。問い合わせ電話を一括管理！！
- 週40時間労働制の実現 1ヵ月又は1年単位の変形労働時間制
- 【実用レベル】appsheetで出勤アプリを作ってみよう
- AppSheetで簡単アプリ開発 ～vol4. 日報アプリを作る（ビュー編）
- javascript HH:MM:SS形式を秒単位に変換する
- 「Googleスプレッドシート」のテーブル参照機能が強化
- Class Utilities - getUuid
- テンプレートで変数を使用する - AppSheet
- 深夜残業と割増賃金を解説 – 定義や計算方法
- HOUR関数の使い方！24時間以上の時間の取得方法は？
- なぜ労働時間は1分単位で計算するのか？違法になる根拠や対策方法を解説！
- 時間を時・分・秒の単位に変換
- Calendar colors - AppSheet Community
