Googleスプレッドシートでの勤怠管理の解説
勤怠管理スプレッドシートの特徴
一般的な勤怠管理スプレッドシートでは、日付が縦に並んでいるものが多いですが、縦に長くなると管理が難しくなります。また、横に並べるとスクロールが面倒になったり、関数計算に問題が生じることがあります。
そこで、日付を横に並べた勤怠表を作成し、簡単に複製・更新できるようにしました。本記事では、その作成方法を詳しく解説します。
日付と曜日の表示と土日色付けの解説
1. 日付と曜日の表示（B4セルから横にオートフィル）
=SUBSTITUTE(SUBSTITUTE(TEXT(LEFT($A3,1)&"/"&COLUMN(A3),"m/d(aaa)"),LEFT($A3,1)&"/",""),"(",CHAR(10)&"(")
関数の解説
TEXT(LEFT($A3,1)&"/"&COLUMN(A3),"m/d(aaa)")：日付を「月/日(曜日)」の形式で取得。SUBSTITUTE(..., "(", CHAR(10)&"(")：曜日を改行して表示。
2. 土日色付け（4行目の条件付き書式）
=OR(WEEKDAY(VALUE(TEXT(LEFT($A$1,4)&"/"&LEFT($A3,1)&"/"&COLUMN(A3),"yyyy/m/d")))=1,WEEKDAY(VALUE(TEXT(LEFT($A$1,4)&"/"&LEFT($A3,1)&"/"&COLUMN(A3),"yyyy/m/d")))=7)
関数の解説
WEEKDAY(...)：曜日を取得し、1（日曜）または7（土曜）なら色付け。
祝日取得と祝日判定、色付けの解説
1. 祝日取得のApps Script
const SHEET_NAME = "祝日一覧";
const CALENDAR_ID = "ja.japanese#[email protected]";
/**
* 祝日一覧をGoogleカレンダーの「日本の祝日」から取得し、スプレッドシートを更新
*/
function holiday_main() {
const sheet = getHolidaySheet();
// 今年の1月1日〜翌年の12月31日を取得
const startDate = new Date();
startDate.setMonth(0, 1); // 1月1日
startDate.setHours(0, 0, 0, 0);
const endDate = new Date();
endDate.setFullYear(endDate.getFullYear() + 1, 11, 31); // 翌年12月31日
endDate.setHours(23, 59, 59, 999);
const holidays = getHoliday(startDate, endDate);
// シートをクリアしてから祝日データを更新
sheet.clear();
sheet.getRange(1, 1, holidays.length, holidays[0].length).setValues(holidays);
sheet.getRange(1, 1, holidays.length, 1).setNumberFormat("yyyy/m/d");
}
/**
* スプレッドシート内の "祝日一覧" シートを取得または作成する
*/
function getHolidaySheet() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
let sheet = ss.getSheetByName(SHEET_NAME);
if (!sheet) {
sheet = ss.insertSheet(SHEET_NAME);
}
return sheet;
}
/**
* 指定した期間の祝日をGoogleカレンダーの「日本の祝日」から取得
*/
function getHoliday(startDate, endDate) {
const cal = CalendarApp.getCalendarById(CALENDAR_ID);
const holidays = cal.getEvents(startDate, endDate);
const values = [];
for (let i = 0; i < holidays.length; i++) {
const holidayDate = holidays[i].getStartTime();
const holidayTitle = holidays[i].getTitle();
// 祝日ではないイベントを除外
if (!["雛祭り", "節分", "母の日", "七五三", "七夕", "クリスマス"].includes(holidayTitle)) {
values.push([holidayDate, holidayTitle]);
}
}
return values;
}
/**
* スプレッドシートのメニューに「祝日を更新」ボタンを追加
*/
function onOpen() {
const ui = SpreadsheetApp.getUi();
ui.createMenu("祝日管理")
.addItem("祝日を更新", "holiday_main")
.addToUi();
}
コード解説
holiday_main()：祝日データを取得し、スプレッドシートの"祝日一覧"シートを更新。getHolidaySheet()：シートがない場合は作成し、取得。getHoliday(startDate, endDate)：Googleカレンダーの「日本の祝日」から指定期間の祝日を取得。onOpen()：スプレッドシートのメニューに「祝日を更新」ボタンを追加。
2. 祝日判定（B5セルから横にオートフィル）
=IFERROR(IF(ISNUMBER(MATCH(VALUE(TEXT(LEFT($A$1,4)&"/"&LEFT($A3,1)&"/"&COLUMN(A3),"yyyy/m/d")), '祝日一覧'!$A$1:$A, 0)),"〇",""), "ERROR")
3. 祝日色付け（4行目の条件付き書式）
=EXACT("〇",INDIRECT(ADDRESS(ROW()+1,COLUMN())))
工数計算の解説
1. 工数計算（B10セルから横にオートフィル）
=IF(0>=B7-B6-B8,"",IF(MOD(VALUE(TEXT(HOUR(B7-B6-B8)+MINUTE(B7-B6-B8)/60,"0.00"))*60,3)=0,VALUE(TEXT(HOUR(B7-B6-B8)+MINUTE(B7-B6-B8)/60,"0.00")),FALSE))
まとめ
このスプレッドシートを活用すれば、日付・曜日・祝日が自動反映され、勤怠管理が効率的になります。また、Apps Scriptを活用することで、手動更新の手間を省くことができます。