Last active
March 8, 2020 08:16
-
-
Save kotakato/8f2ce484a71b21323cc2b6af6418cc25 to your computer and use it in GitHub Desktop.
Googleカレンダーから翌週の空き時間を計算するGoogle Apps Script
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
var moment = Moment.moment; | |
var LOCAL_TZ_SUFFIX = '+09:00'; // タイムゾーの接尾辞 | |
var TOTAL_DAYS = 14; // 空き時間を取得する日数 | |
var MINUTES_PER_TIMESLOT = 30; // タイムスロットの分数 | |
var TIMESLOTS_PER_DAY = 60 * 24 / MINUTES_PER_TIMESLOT; // 1日あたりのタイムスロット数 | |
var JAPANESE_HOLIDAY_CALENDAR_ID = 'ja.japanese#holiday@group.v.calendar.google.com'; // 日本の祝日のカレンダーID | |
/** | |
* メインの処理。Googleカレンダーの予定をもとに、スプレッドシートに作業可能時間を出力する。 | |
*/ | |
function main() { | |
const sheet = SpreadsheetApp.getActive().getSheetByName('シート1'); | |
// カレンダーIDのリストを取得 | |
const range = sheet.getRange(3, 1, sheet.getLastRow() - 2, 1); // A3から縦に最終行まで取得 | |
const calendarIds = range.getValues() | |
.map(function(cols) { | |
return cols[0]; // 最初の列の値を取得 | |
}) | |
.filter(function(value) { | |
return value.trim(); // 中身があるセルのみをフィルター | |
}); | |
Logger.log(calendarIds); | |
// 日付のリストと休日かどうかのリストを取得 | |
const baseDate = moment().zone(LOCAL_TZ_SUFFIX).startOf('week').add(1, 'days'); // 今週の始まりの日を取得する。startOf('week')は日曜日なので、月曜始まりにする | |
Logger.log(baseDate.format()); | |
const dates = listDates(baseDate, TOTAL_DAYS); | |
const isHolidays = getIsHolidays(dates); | |
Logger.log(isHolidays); | |
// 予定を取得して空き時間を計算 | |
const availableHoursListByCalendarId = {}; | |
calendarIds.forEach(function(calendarId, i) { | |
const events = listEvents(calendarId, baseDate, TOTAL_DAYS); | |
const timeslots = buildTimeslots(events, baseDate, TOTAL_DAYS); | |
Logger.log(timeslots); | |
const availableHoursList = calculateAvailableHoursList(timeslots, dates, isHolidays); | |
Logger.log(availableHoursList); | |
availableHoursListByCalendarId[calendarId] = availableHoursList; | |
}); | |
// 表示 | |
sheet.getRange(1, 2).setValue(moment().format()); | |
dates.forEach(function(date, j) { | |
sheet.getRange(2, 4 + j).setValue(date.format('YYYY-MM-DD')); | |
}); | |
calendarIds.forEach(function(calendarId, i) { | |
for (var j = 0; j < TOTAL_DAYS; j++) { | |
sheet.getRange(3 + i, 4 + j).setValue(availableHoursListByCalendarId[calendarId][j]); | |
} | |
}); | |
} | |
/** | |
* 基準日と日数を指定して、momentオブジェクトのArrayを取得する。 | |
* | |
* @param baseDate {moment} - 基準日(開始日)を表すmomentオブジェクト | |
* @param days {number} - 日数 | |
* @return {Array} momentオブジェクトのArray | |
*/ | |
function listDates(baseDate, days) { | |
const dates = []; | |
for (var j = 0; j < days; j++) { | |
var date = baseDate.clone().add(j, 'days'); | |
dates.push(date); | |
} | |
return dates; | |
} | |
/** | |
* momentオブジェクトのArrayから、休日(土日祝)かどうかを表すboolean値のArrayを取得する。 | |
* | |
* @param dates {Array} - momentオブジェクトのArray | |
* @return {Array} 休日かどうかを表すboolean値のArray | |
*/ | |
function getIsHolidays(dates) { | |
const holidayEvents = listEvents(JAPANESE_HOLIDAY_CALENDAR_ID, dates[0], dates.length); | |
Logger.log(holidayEvents); | |
const holidays = holidayEvents.map(function(event) { return event.start.date; }); | |
return dates.map(function(date) { | |
return date.day() === 0 || date.day() === 6 || holidays.indexOf(date.format('YYYY-MM-DD')) >= 0; | |
}); | |
} | |
/** | |
* GoogleカレンダーからイベントのArrayを取得する。辞退した予定は含まない。 | |
* | |
* @param calendarId {string} - カレンダーID | |
* @param baseDate {moment} - 基準日(開始日) | |
* @param days {number} - 日数 | |
* @return {Array} イベントのArray | |
*/ | |
function listEvents(calendarId, baseDate, days) { | |
const events = Calendar.Events.list(calendarId, { | |
timeMin: baseDate.format(), | |
timeMax: baseDate.clone().add(days, 'days').format(), | |
singleEvents: true, | |
orderBy: 'startTime', | |
}); | |
return events.items.filter(function(event) { | |
if (!event.attendees) { | |
return true; | |
} | |
var attendee = event.attendees.filter(function(a) { | |
return a.email === calendarId; | |
})[0]; | |
if (!attendee) { | |
return false; // 作成者が出席者から抜けた場合はattendeesに含まれない | |
} | |
return attendee.responseStatus !== 'declined'; | |
}); | |
} | |
/** | |
* イベントのArrayをもとに、タイムスロットごとに予定があるかどうかを表すboolean値のArrayを構築する。 | |
* | |
* @param events {Array} - GoogleカレンダーのイベントのArray | |
* @param baseDate {moment} - 基準日(開始日) | |
* @param days {number} - 日数 | |
* @return {Array} タイムスロットごとに予定があるかどうかを表すboolean値のArray | |
*/ | |
function buildTimeslots(events, baseDate, days) { | |
const timeslots = []; | |
for (var i = 0; i < (TIMESLOTS_PER_DAY * days); i++) { | |
timeslots.push(false); | |
} | |
events.forEach(function(event) { | |
var startDateText; | |
var endDateText; | |
if (event.start.date) { | |
// all-day event | |
Logger.log(event.start.date + " - " + event.end.date); | |
startDateText = event.start.date + "T00:00:00" + LOCAL_TZ_SUFFIX; | |
endDateText = event.end.date + "T00:00:00" + LOCAL_TZ_SUFFIX; // 1日の予定の場合、event.end.date は翌日の日付になっている。 | |
} else { | |
Logger.log(event.start.dateTime + " - " + event.end.dateTime); | |
startDateText = event.start.dateTime; | |
endDateText = event.end.dateTime; | |
} | |
const startDate = moment(startDateText); | |
const endDate = moment(endDateText); | |
Logger.log(startDate.format() + " - " + endDate.format() + ": " + event.summary); | |
const startMinutes = startDate.diff(baseDate, 'minutes'); | |
const endMinutes = endDate.diff(baseDate, 'minutes'); | |
const startIndex = Math.floor(startMinutes / MINUTES_PER_TIMESLOT); | |
const endIndex = Math.min(Math.ceil(endMinutes / MINUTES_PER_TIMESLOT), timeslots.length); | |
Logger.log("" + startIndex + "-" + endIndex); | |
for (var i = startIndex; i < endIndex; i++) { | |
timeslots[i] = true; // タイムスロットを埋める | |
} | |
}); | |
return timeslots; | |
} | |
/** | |
* 日ごとの作業可能時間を計算する。 | |
* | |
* @param timeslots {Array} - タイムスロットごとに予定があるかどうかを表すboolean値のArray | |
* @param dates {Array} - 対象期間を表すmomentオブジェクトのArray | |
* @param isHolidays {Array} - 休日かどうかを表すboolean値のArray | |
* @return {Array} 日ごとの作業可能時間を表すArray | |
*/ | |
function calculateAvailableHoursList(timeslots, dates, isHolidays) { | |
const availableHoursList = dates.map(function(date, i) { | |
if (isHolidays[i]) { | |
return 0; // 土日祝の作業可能時間は0 | |
} | |
const timeslotsInDay = timeslots.slice(i * TIMESLOTS_PER_DAY, (i + 1) * TIMESLOTS_PER_DAY); | |
Logger.log(timeslotsInDay); | |
const numAvailableTimeslots = timeslotsInDay.filter(function(timeslot, i) { return isWorkHour(i) && !timeslot; }).length; | |
const availableHours = numAvailableTimeslots * MINUTES_PER_TIMESLOT / 60; | |
Logger.log(availableHours); | |
return availableHours; | |
}); | |
return availableHoursList; | |
} | |
/** | |
* タイムスロットが業務時間内かどうかを取得する。 | |
* | |
* @param i {number} タイムスロットのインデックス | |
* @return {boolean} 業務時間内かどうか | |
*/ | |
function isWorkHour(i) { | |
return (19 /* 9:30 */ <= i && i < 24 /* 12:00 */) || (26 /* 13:00 */ <= i && i < 36 /* 18:00 */); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment