はじめに
Twitterで某氏のツイートを見て、これくらいならGASで簡単にできそうだなと思って作ってみたやつです。
GASでカレンダーやスプレッドシートに出退勤付けても面白いかなと思った https://t.co/IXdlyJsk7i
— Yusuke S. (@Uske_S) February 18, 2019
できたもの
出勤ボタンを押すとA列に押した時間が記述される。
同様に、退勤ボタンを押すとB列に押した時間が記述され、C列に時間の差分を記述するというもの。
フロー
- ボタンを押した時刻を取得
- 出勤ボタンだった場合、シートの最終行の次の行のA列に時刻を(特定書式に整形して)記述
- 退勤ボタンだった場合、シートの最終行のB列に時刻を(特定書式に整形して)記述
- と同時にC列にB列-A列の差分を記述
- C列のセルの表示形式を「hh:mm:ss」にする
下準備
GoogleSheetsから新しいスプレッドを起こしてください。
1行目に「出勤」「退勤」「実働時間」などと適当に入れます。
そしたらツールメニューからスクリプトエディタを開いてください。
エディタが開いたら、もともと書かれていたコード(空っぽの関数)を消して、以下のコードを貼り付けます。
そしたらひとまず準備完了です。適当にプロジェクト名に名前をつけて保存してください。
ただ、スクリプトエディタを起動して最初のスクリプトの実行は権限の確認処理があります。
詳しくは@tanabeeさんのこのQiitaの記事の、「意図しないエラー対策」の部分を読んで適宜ご対応ください。
Google Apps Script ハンズオン資料 #GoogleAppsScript - Qiita
コード
function getTimestamp() { var myDate = new Date(); var result = myDate.getFullYear() + "/" + ("0" + (myDate.getMonth() + 1)).slice(-2) + "/" + ("0" + myDate.getDate()).slice(-2) + " " + ("0" + myDate.getHours()).slice(-2) + ":" + ("0" + myDate.getMinutes()).slice(-2) + ":" + ("0" + myDate.getSeconds()).slice(-2); return result; }; function setAttendance(contents, type) { var sheetObj = SpreadsheetApp.getActiveSheet(); var tgtRange = sheetObj.getRange(sheetObj.getLastRow()+type.suf, 1, 1, 3); var tgtValues = tgtRange.getValues(); if (type.name === "attend") { tgtValues[0][0] = contents; } else if (type.name === "left") { tgtValues[0][1] = contents; tgtValues[0][2] = "=B"+sheetObj.getLastRow()+"-A"+sheetObj.getLastRow(); var cell = tgtRange.getCell(1,3); cell.setNumberFormat("hh:mm:ss"); } tgtRange.setValues(tgtValues); }; function attend() { setAttendance(getTimestamp(), {name: "attend", suf: 1}); }; function left() { setAttendance(getTimestamp(), {name: "left", suf: 0}); };
はい、ほとんどコメントを残してなくてすみません^^;;
部分解説
getTimestamp
関数
ここはメインじゃないのでさらっと。
YYYY/MM/DD hh:mm:ss
という形で記述したかったため、Dateオブジェクトからそれぞれの値を取得して整形しています。
詳しく説明しませんが、年を取得するのはDate.getFullYear
メソッド、月を取得するのはDate.getMonth
メソッド(しかしゼロインデックスなので+1する)あたりを気をつけてください。
setAttendance
関数
このスクリプトのメインエンジンです。
引数は2つで、第一引数はセルに書き込む内容(contents)、第二引数はオブジェクト(type)で、出勤か退勤かを表すnameというプロパティと、最終行に内容を記述するのかその次の行に記述するのかを区別するためのずらし量を与えるsufというプロパティを想定しています。
getRange
メソッド
Google Sheetsでは大抵Rangeオブジェクトをあれこれします。そのRangeオブジェクトを取得するのがこのgetRange
メソッドです。引数にはいろいろなパターンがあって、使い勝手がいいのか悪いのかなんとも言えませんw
詳しくは公式ドキュメント見てください。
今回はこの中からgetRange(row, column, numRows, numColumns)
という4つの引数を与えるパターンを使ってRangeオブジェクトを取得しています。
セルの値を取得して一旦書き換えておく
Range.getValues
メソッドで、Rangeオブジェクトからすべての値を引っ張ってきます。今回は1行3列のRangeオブジェクトから値を引っ張るので、構造としては
[ [ A列のセルの内容, B列のセルの内容, C列のセルの内容 ] ]
みたいになっています。要するに二重配列です。A列のセルの内容が欲しければ、Rangeオブジェクト[0][0]
とするわけです。
出勤(attend)だった場合、A列にタイムスタンプを入れたいので[0][0]
にその内容を。退勤(left)だった場合、B列に入れたいので[0][1]
にその内容を代入しておきます。ついでに、退勤だった場合はC列に差を計算する計算式「=Bn - An」を入れたい(nは行番号の整数値)ので、[0][2]にその文字列を代入しておきます。
セルの表示形式の変更
ここでC列の表示形式を一緒に変更してしまいましょう。セルの表示形式を指定するにはRange.setNumberFormat
メソッドを利用します*1。
どういう形で記述したらどう表示されるかは、Google Sheetsのリファレンスを参照してください。
今回はhh:mm:ss方式にしました。このへんはお好みでどうぞ。
セルに値を戻す
Range.setValues
メソッドを使って、値を代入しておいた二重配列をそのまま引数として渡し、セルの値としてセットしておしまいです。
僕が普段使っている/作っているInDesign用のスクリプトでは、表組のセルに値をセットするためにfor文などを使ってひとセルずつセットしていく感じですが、GASの場合はsetValues
メソッドを使うことで一度にかつ高速に複数のセルに値をセットします。個別にセルを指定して値をセットしていく方法もありますが、処理速度が非常に遅くなりますし、セルごとに値をセットしたことがUndoに記録され、1回の⌘+Zではスクリプト実行前に戻れません。いろんな意味を含めて、getValues
とsetValues
で値の出し入れをしたほうが効率的です。
attend
関数とleft
関数
これはスプレッドシート側から「ボタンを押したときに実行する関数」として利用します。setAttendance
関数に2つの引数を渡して実行するだけです。
スプレッドシート側にボタンを用意する
最初に書いたGASの記事にも書いたので割愛します。
画像を用意したり、スプレッドシートに図形を作成したりして、それらのオブジェクトに関数を関連付けるだけです。
以上です! スクリプトさえできちゃえばあとはとっても簡単なのがGASのいいところ。
この勤怠管理もどきはあくまでもどきなので、実際には実働時間をn分単位で計算とか、休憩時間はどうするとか、まぁいろいろあるでしょうが、雛形としてはこんな感じでタイムスタンプを漏れなく間違いなく記入しつつ、スプレッドシートの関数で情報を整形するという形でいいかなと思います。
*1:Range.getCellメソッドでセルを参照していますが、Cellオブジェクトというものはなく、戻り値は引数で指定したセルのRangeオブジェクトになります。