Google App Scriptで楽をしてもらった話
川崎慎吾
はじめに
コロナ禍で感染拡大防止を目的とした在宅勤務が認められるようになった際、在宅勤務や出勤などの当日の勤務情報を登録する出勤届というものができました。 出勤届はGoogleスプレッドシートで図1のようなフォーマットで作成されており、プルダウンメニューで出勤や在宅などを登録します。この出勤届ファイルが各部門・センターごとに存在していて、Googleドライブでフォルダ分けされて共有されています。
図１
防災研究所には12の部門やセンターがあり、それぞれの部門・センターごとに毎月の暦に合わせた出勤届ファイルを用意する必要があります。これを担当者が毎月手作業でファイルを作っていると聞いた私は、「Google Apps Scriptで楽させてあげれそう」だと思いました。これが今回の話の動機です。
単純作業はすべてGoogle Apps Scriptが解決してくれるはず
GoogleスプレッドシートやGoogleドライブでは、Google Apps Script（以下GAS）を使ってファイル作成・操作が可能なので、GASで月末に翌月の出勤届を自動で作成すればよいと考えました。
自動化のフローチャートは下記の通りです。
↓
テンプレートをコピーして次月のファイル名に変更（例：技術室・広報室他_4月）
↓
次月の日数に合わせて列を削除、土日祝に合わせてセルの内容や色を変更
これをもとに書いたGASがこちらです。
function myFunction() {
var date = new Date();
/*作成する出勤簿の月の指定,+1でgas実行月の翌月 */
date.setMonth(date.getMonth() + 1,1);
var nextmonth = Utilities.formatDate(date, "JST", "M");
console.log(date);
/*月あたりの日数の設定 */
if(nextmonth==2){/*2月の場合 */
var k=28;
var thisyear = Utilities.formatDate(date,"JST","yyyy");
/*うるう年の処理 */
if(thisyear % 4==0 && nextmonth % 100!=0||nextmonth % 400==0) {
var k=29;
}
/*末日が30日/31日に分ける */
}else if (nextmonth==4||nextmonth==6||nextmonth==9||nextmonth==11) {
var k=30;
}else {
var k=31;
}
/*テンプレのID,ファイル名取得 */
var tmpltID ="テンプレートファイルのIDを入力";
var Tmplt = DriveApp.getFileById(tmpltID);
var tmpltname = Tmplt.getName();
/*テンプレをコピー＆ファイル名末尾に_X月を追記 */
/*コピー保存先の指定 */
var FoloderID ="保存先フォルダのIDを入力";
var savedFolder = DriveApp.getFolderById(FoloderID);
var newsheet = Tmplt.makeCopy(tmpltname +"_" + nextmonth +"月",savedFolder).getId();
var newsheet1 = SpreadsheetApp.openById(newsheet);
/*セルにデータのある最終行数を取得 */
var lastRow = newsheet1.getLastRow();
/*セルA1に月を入力 */
newsheet1.getRange("A1").setValue(nextmonth + "月");
/*不要な列(31日-k日)を削除 */
if(k==28) {
newsheet1.deleteColumns(30,3);
}else if(k==29) {
newsheet1.deleteColumns(31,2);
}else if(k==30) {
newsheet1.deleteColumns(32,1);
}
/*"出勤簿"シートの条件付き書式をクリアする*/
var sheet1 = newsheet1.getSheetByName("出勤簿");
sheet1.clearConditionalFormatRules();
/*平日と休日のプルダウンメニューの設定 */
var holidaylist =["休日","出勤","出張"];
var listrule1 = SpreadsheetApp.newDataValidation().requireValueInList(holidaylist).build();
var daylist =["在宅","出勤","出張","休暇","非出勤日"];
var listrule2 = SpreadsheetApp.newDataValidation().requireValueInList(daylist).build();
/*祝日用、google謹製の祝日カレンダー設定 */
var calendarId = "ja.japanese#holiday@group.v.calendar.google.com";
var eventclndr = CalendarApp.getCalendarById(calendarId);
/*日数分(k)の表を作成*/
for(i=2; i<=k+1; i++) {
/*Ｘ月i-1日を指定 */
date.setMonth(date.getMonth(),i-1);
/*祝日カレンダーで祝日かどうかを確認する */
var todayEvents = eventclndr.getEventsForDay(date);
var week_num1 = date.getDay();
/*console.log(week_num1);*/
if(todayEvents.length > 0){/*祝日だった場合 */
sheet1.getRange(1,i,lastRow,1).setBackground("#fff2cc");
sheet1.getRange(2,i,lastRow,1).setValue(null).setBackground("#fff2cc");
sheet1.getRange(2,i,lastRow,1).setDataValidation(listrule1).setValue("休日");
}else if(week_num1==6){/*土曜の場合 */
sheet1.getRange(1,i,lastRow,1).setBackground("#c9daf8");
sheet1.getRange(2,i,lastRow,1).setValue(null).setBackground("#c9daf8");
sheet1.getRange(2,i,lastRow,1).setDataValidation(listrule1).setValue("休日");
}else if(week_num1==0){/*日曜の場合 */
sheet1.getRange(1,i,lastRow,1).setBackground("#fff2cc");
sheet1.getRange(2,i,lastRow,1).setValue(null).setBackground("#fff2cc");
sheet1.getRange(2,i,lastRow,1).setDataValidation(listrule1).setValue("休日");
}else{/*平日の設定 */
sheet1.getRange(1,i,lastRow,1).setBackground("#ffffff");
sheet1.getRange(2,i,lastRow,1).setValue(null).setBackground("#ffffff");
sheet1.getRange(2,i,lastRow,1).setDataValidation(listrule2).setValue("");
}
}
/*名前のない行は値をクリアする */
for(i=2; i<=lastRow+1; i++){
var blank = sheet1.getRange(i,1);
if(blank.isBlank()) {
for(j=2; j<=k+1; j++)
sheet1.getRange(i,j).clearContent();
}
}
}
案１
案１の問題点と解決策
うるう年まで対応させて完璧なものができたと思った私は、さっそく担当者に連絡しました。好反応を得ることはできましたが、即採用にはいたりませんでした。その理由は
- 各部門・センターの構成員の増減が随時発生するため、増減があるたびに当月のファイルに追記または削除している。そのたびにテンプレートのメンテナンスが必要。完全自動化にならない
- 外国人構成員のセルは英語表記にする必要がある
問題点１は前月のファイルをテンプレートにすれば解決できるとの助言をもらいました。ただし、案１では列の増減を31日からの引き算で実装しているため、前月の日数と翌月の日数の差を足し引きに変更して解決しました。
問題点2は、外国人構成員名は英字表記なので、構成員名にアルファベットを含む場合に英語表記のプルダウンメニューを設ける場合分けをして解決しました。
解決策を盛り込んだフローチャートはこちらです。
指定したフォルダのGAS実行月のファイルをコピーして、次月のファイルを作成↓
コピーしたファイルから日数をカウントして、次月の日数に合わせて列を足し引きする
↓
土日祝に合わせてセルの内容や色を変更し、英字表記の構成員の行は英語表記に書き換え
完成
実際に採用されたGASはこちらです。
function setVCalendar(Tmplt,savedFolder,div_str) {
var date = new Date();
/*作成する出勤簿の月の指定,+1でgas実行月の翌月 */
date.setMonth(date.getMonth() +1,1);
var nextmonth = Utilities.formatDate(date, "JST", "M");
/*月あたりの日数の設定 */
if(nextmonth==2){/*2月の場合 */
var k=28;
var thisyear = Utilities.formatDate(date,"JST","yyyy");
/*うるう年の処理 */
if(thisyear % 4==0 && nextmonth % 100!=0||nextmonth % 400==0) {
var k=29;
}
/*末日が30日/31日に分ける */
}else if (nextmonth==4||nextmonth==6||nextmonth==9||nextmonth==11) {
var k=30;
}else {
var k=31;
}
/*テンプレのID,ファイル名取得 */
var tmpltname = Tmplt.getName();
/*テンプレをコピー＆ファイル名末尾に_X月を追記 */
/*コピー保存先の指定 */
var newsheet = Tmplt.makeCopy(div_str + "_" + nextmonth +"月",savedFolder).getId();
var newsheet1 = SpreadsheetApp.openById(newsheet);
/*セルにデータのある最終行数&列数を取得 */
var lastRow = newsheet1.getLastRow();
var lastColumn = newsheet1.getLastColumn();
/*セルA1に月を入力 */
newsheet1.getRange("A1").setValue(nextmonth + "月");
/*次月X<31日の場合、列を追加する */
if(lastColumn==31) {/*30日の場合 */
newsheet1.insertColumnsAfter(31,1);
newsheet1.getRange("AF1").setValue("31日");
}else if(lastColumn==29){/*28日の場合 */
newsheet1.insertColumnsAfter(29,3);
newsheet1.getRange("AD1").setValue("29日");
newsheet1.getRange("AE1").setValue("30日");
newsheet1.getRange("AF1").setValue("31日");
}else if(lastColumn==30){/*29日の場合 */
newsheet1.insertColumnsAfter(30,2);
newsheet1.getRange("AE1").setValue("30日");
newsheet1.getRange("AF1").setValue("31日");
}
/*不要な列(31日-k日)を削除 */
if(k==28) {
newsheet1.deleteColumns(30,3);
}else if(k==29) {
newsheet1.deleteColumns(31,2);
}else if(k==30) {
newsheet1.deleteColumns(32,1);
}
/*"出勤簿"シートの条件付き書式をクリアする*/
var sheet1 = newsheet1.getSheetByName("出勤簿");
var sheet2 = newsheet1.getSheetByName("出勤届");
sheet1.clearConditionalFormatRules();
sheet1.getRange(1, 1, sheet1.getMaxRows(), sheet1.getLastColumn()).setBackground(null);
/*平日と休日のプルダウンメニューの設定 */
var holidaylist =["休日","出勤","出張"];
var listrule1J = SpreadsheetApp.newDataValidation().requireValueInList(holidaylist).build();
var daylist =["在宅","出勤","出張","休暇","非出勤日"];
var listrule2J = SpreadsheetApp.newDataValidation().requireValueInList(daylist).build();
var setwordJ ="休日";
/*英語表記用 */
var holidaylistE =["Holiday","Office","Business Trip"];
var listrule1E = SpreadsheetApp.newDataValidation().requireValueInList(holidaylistE).build();
var daylistE =["Remote","Office","Business Trip","Leave","Off Duty"];
var listrule2E = SpreadsheetApp.newDataValidation().requireValueInList(daylistE).build();
var setwordE ="Holyday";
/*どちらの表記を使うか。初期:日本語 */
var listrule1 = listrule1J;
var listrule2 = listrule2J;
var setword =setwordJ;
/*祝日用、google謹製の祝日カレンダー設定 */
var calendarId = "ja.japanese#holiday@group.v.calendar.google.com";
var eventclndr = CalendarApp.getCalendarById(calendarId);
/*日数分(k)の表を作成*/
for(i=2; i<=k+1; i++) {
/*Ｘ月i-1日を指定 */
date.setMonth(date.getMonth(),i-1);
/*祝日カレンダーで祝日かどうかを確認する */
var todayEvents = eventclndr.getEventsForDay(date);
var week_num1 = date.getDay();
/*console.log(week_num1);*/
if(todayEvents.length > 0){/*祝日だった場合 */
sheet1.getRange(1,i,lastRow,1).setBackground("#fff2cc");
sheet1.getRange(2,i,lastRow,1).setValue(null).setBackground("#fff2cc");
sheet1.getRange(2,i,lastRow,1).setDataValidation(listrule1).setValue(setword);
}else if(week_num1==6){/*土曜の場合 */
sheet1.getRange(1,i,lastRow,1).setBackground("#c9daf8");
sheet1.getRange(2,i,lastRow,1).setValue(null).setBackground("#c9daf8");
sheet1.getRange(2,i,lastRow,1).setDataValidation(listrule1).setValue(setword);
}else if(week_num1==0){/*日曜の場合 */
sheet1.getRange(1,i,lastRow,1).setBackground("#fff2cc");
sheet1.getRange(2,i,lastRow,1).setValue(null).setBackground("#fff2cc");
sheet1.getRange(2,i,lastRow,1).setDataValidation(listrule1).setValue(setword);
}else{/*平日の設定 */
sheet1.getRange(1,i,lastRow,1).setBackground("#ffffff");
sheet1.getRange(2,i,lastRow,1).setValue(null).setBackground("#ffffff");
sheet1.getRange(2,i,lastRow,1).setDataValidation(listrule2).setValue("");
}
}
/*名前のない行は値をクリアする */
for(i=2; i<=lastRow+1; i++){
var blank = sheet1.getRange(i,1);
if(blank.isBlank()) {
for(j=2; j<=k+1; j++)
sheet1.getRange(i,j).clearContent();
}
}
/*名前欄にアルファベットが入っているかの判定 */
for(j=2; j<=lastRow+1; j++) {
var alphabet = sheet1.getRange(j,1).getValue();
/*アルファベットが入っていた場合 */
if(alphabet.search(/[a-zA-Z]/) !=-1) {
/*英語表記に変更 */
var listrule1 = listrule1E;
var listrule2 = listrule2E;
var setword = setwordE;
for(i=2; i<=k+1; i++) {
/*Ｘ月i-1日を指定 */
date.setMonth(date.getMonth(),i-1);
/*祝日カレンダーで祝日かどうかを確認する */
var todayEvents = eventclndr.getEventsForDay(date);
var week_num1 = date.getDay();
if(todayEvents.length > 0){/*祝日だった場合 */
sheet1.getRange(j,i).setDataValidation(listrule1).setValue(setword);
}else if(week_num1==6){/*土曜の場合 */
sheet1.getRange(j,i).setDataValidation(listrule1).setValue(setword);
}else if(week_num1==0){/*日曜の場合 */
sheet1.getRange(j,i).setDataValidation(listrule1).setValue(setword);
}else{/*平日の設定 */
sheet1.getRange(j,i).setDataValidation(listrule2).setValue("");
}
}
}
}
/*シート2の列を削除 */
sheet2.deleteRows(2,sheet2.getLastRow());
}
あとがき
これを読んでちょっとでも楽させてほしいと思った防災研の方はぜひ技術支援依頼を！