やりたいこと
GASを使って、外部のパートナーさんの稼働管理シートを楽にしたいです。
現状
- 月末くらいに、翌月分の営業日だけの日付がだーーーっと書かれたシートを用意
- 当月になったら、毎日帰社時にパートナーさんに「出社時間」と「退社時間」をスプレッドシートに記入してもらう
- 月末になったら稼働シート見て未記入の日が無いかとか確認して合計稼働時間が契約より上回ってないか(or 下回ってないか)を確認する
理想
- 月初に当月の営業日だけの日付の書かれたシートが自動で作られる
- パートナーさんは、スプレッドシートで言わずども、チャットで「出社した」「帰る」とか言ったらスプレッドシートに反映される
- 月末最終営業日に勝手に当月の稼働時間を計算する
- 上回ってたり下回ってたりしたらアラート出す
- 月中でアラート出せると吉
これができると、この地味に面倒な稼働時間シート管理から開放されます。
1. 当月の日付をスプレッドシートに記入してく
まずは日付だけ入れていきましょう。
function myFunction() {
var today = new Date();
// 月初日取得
var firstDate = new Moment.moment(new Date(today.getFullYear(), today.getMonth(), 1));
var thisMonthDates = getThisMonthDates(firstDate);
// 稼働実績シートにアクセス
var sheet = SpreadsheetApp.getActive().getSheetByName('稼働実績');
// 今月の日付分だけ、記入してく
thisMonthDates.forEach(function(val, i) {
// iは0から始まってるが、3行目から入れたいので i+3 にする
sheet.getRange(i+3, 2).setValue(val.format('YYYY年MM月DD日(ddd)'));
});
}
function getThisMonthDates(firstDate)
{
var daysInMonth = firstDate.daysInMonth();
var thisMonthDates = [];
for (var i = 0; i < daysInMonth; i++) {
thisMonthDates.push(firstDate.clone().add(i, 'days'));
}
return thisMonthDates;
}
月初日の取得や、Moment.jsの使い方、そしてスプレッドシートへのアクセス方法に関しては以下の記事を参考にしてください。
gasで月初日と月末日を取得する
Google Apps Script(GAS) 超入門① - スプレッドシートの値をとってみる
これでこんな感じでうまく入ってるのがわかります。
おまけ. もっと効率的にシートに値を入れる
今回は、foreachというループの中でsheet.getRange(i+3, 2).setValue(val.format('YYYY年MM月DD日(ddd)'));として、B列に値を入れていきました。
これはあまり良くなくて、getRangeというのは毎回スプレッドシートにxxx行目yyyy列のデータをくださいと問い合わせに行ってます。今回は30回。
これが多くなってくると、スクリプトの実行時間が遅くなったり、遅くなりすぎると、スクリプトがGoogleが設定してる時間以内に終わらなくて完了しないことが出てきます。
そこで便利なのが、getValuesとsetValuesです。
これは、イメージは、特定の範囲のデータを取得して、そこに値をいっきに入れちゃう。
という感じです。
超簡単な事例を見てみましょう。
このシートのB2 ~ B6の5行に、それぞれ名前を入れいきます。
function myFunction() {
var sheet = SpreadsheetApp.getActive().getSheetByName('fortest');
var range = sheet.getRange("B2:B6");
// setValuesを使うときは２次元配列にする
var names = [["田中"], ["山田"], ["大仁田"], ["二階堂"], ["山市"]];
range.setValues(names);
}
実行するとこんな感じで、B2 ~ B6 に良い感じに名前が入ってます。
実は、getRangeはこのようにB2:B6みたいにセルの値を直接指定する場合と、もう一つ値を入れてく方法があります。
これができると、行列が増えたり減ったりしても対応できるので、例えば2行目〜最終行までとかができるようになります。
getRange(開始行数, 開始列数, 何行追加するか, 何列追加するか])
少し幅を広げて、A列に名前、B列に性別、C列に年齢を入れてみましょう。
function myFunction() {
var sheet = SpreadsheetApp.getActive().getSheetByName('fortest');
// B2 (2行目の2列目)から
// 縦に5行分
// 横に3列分
// の範囲を取得する
var range = sheet.getRange(2, 2, 5, 3);
// setValuesを使うときは２次元配列にする
var data = [
["田中" , "男性", "44歳"],
["山田" , "男性", "32歳"],
["大仁田" , "男性", "56歳"],
["二階堂" , "女性", "21歳"],
["山市" , "男性", "18歳"]
];
range.setValues(data);
}
こんな感じです。
これをマスターしたら、高速に、大量の範囲にも好きな値をサクっと入れられるようになって便利なのでぜひ覚えておきましょう。
2. 土日を除外する
moment.jsのdayメソッドで曜日が取得出来ますが、こんな感じでかえって来ます。
| 数値 | 曜日 |
|---|---|
| 0 | 日曜 |
| 1 | 月曜 |
| 2 | 火曜 |
| 3 | 水曜 |
| 4 | 木曜 |
| 5 | 金曜 |
| 6 | 土曜 |
ので、今回は、getThisMonthDatesが今月の日付一覧を返すときに、dayメソッドが0か6を返したときは日付一覧に入れないように改造します。
function getThisMonthDates(firstDate)
{
var daysInMonth = firstDate.daysInMonth();
var thisMonthDates = [];
for (var i = 0; i < daysInMonth; i++) {
var targetDate = firstDate.clone().add(i, 'days');
// 休日だったらスキップ
if (isHoliday(targetDate)) continue;
thisMonthDates.push(targetDate);
}
return thisMonthDates;
}
// 休日だったらtrueを返す
function isHoliday(targetDate) {
// 土日なら返す
// moment.jsのdayメソッドは、0(日) ~ 6(土)の数値で返すので、土日である0か6が含まれてたらtrueを返す
if ([0, 6].indexOf(targetDate.day()) !== -1) return true;
return false;
}
こんな感じですね。
見事に土日がなくなりました！
3. 祝祭日も除外して、営業日だけシートに記入してく
さぁ、いよいよ大詰めの祝祭日もスルーして、営業日だけを入れていきましょう。
実は超簡単で、日本の祝祭日情報ってのはGoogleカレンダーで公開されてて、そのカレンダーIDがja.japanese#holiday@group.v.calendar.google.comです。
これを指定して、getEventsForDayで取得してみると祝祭日のときは祝祭日のデータが返ってきます。
それを加えたisLegalHolidayという関数を追加して見ましょう。
function getThisMonthDates(firstDate)
{
var daysInMonth = firstDate.daysInMonth();
var thisMonthDates = [];
for (var i = 0; i < daysInMonth; i++) {
var targetDate = firstDate.clone().add(i, 'days');
// 休日だったらスキップ
if (isHoliday(targetDate)) continue;
thisMonthDates.push(targetDate);
}
return thisMonthDates;
}
// 休日だったらtrueを返す
function isHoliday(targetDate) {
// 土日なら返す
// moment.jsのdayメソッドは、0(日) ~ 6(土)の数値で返すので、土日である0か6が含まれてたらtrueを返す
if ([0, 6].indexOf(targetDate.day()) !== -1) return true;
if (isLegalHoliday(targetDate)) return true;
return false;
}
// 祝祭日だったらtrueを返す
function isLegalHoliday(targetDate) {
// 日本の祝日を表すカレンダーID
var cal = CalendarApp.getCalendarById("ja.japanese#holiday@group.v.calendar.google.com");
// getEventForDayは、引数にDateオブジェクトで渡す必要がある
// この戻り値が1以上だったら祝日ということになる
return cal.getEventsForDay(new Date(targetDate)).length > 0;
}
結果はこんな感じ
６月は祝祭日が無くて分かりづらかったので、5月でやってみました。
今年は5/1~5/6までが祝祭日だったので、ちゃんと5/7から始まってます。
これで一歩前進ですね。
次回予告
さて次回は、この営業日一覧にパートナーさんが入れた稼働時間を自動で計算してくスクリプトを組んで行きましょう。
その次に合計時間を計算して、合計時間アラートを出しつつ、最後にチャットシステムと連動してみましょう！
エンジニアじゃなくてもとっつきやすいjavascriptがどんどん分かって、業務も効率化されて一石二鳥ばかりです！
皆さんもこれを機にGoogle Apps Scriptsを触ってみてはいかがでしょうか。