GASで複数人のGoogleカレンダーの予定を調整する

会議室の空き状況を見つつ複数人で予定を調整することが増えてきたのですが、複数人の空き予定を探して提案するのが面倒になってきました。それをGASにやらせるようにした話です。

作るもの

[e-mail], [e-mail]など複数のGoogleカレンダーの予定を突き合わせて空いている日程を表示するようにします。

今回は実用を想定して始業時間や終業時間、そして確保したい時間も入力に与えて出力を絞り込んでいます。

入出力例を与えます。

入力

項目	サンプル値
始業時間（時）	10
終業時間（時）	20
確保したい時間（分）	30
検索範囲（日間）	2
アドレスの数	2
出力空き日程数	5
Gmail	[e-mail], [e-mail]

出力

日付	時間
8/25	11:00-12:00
8/25	13:00-14:00
8/25	15:30-19:00
8/25	19:30-20:00
8/26	18:30-20:30

Google Apps Script(GAS)

スプレッドシートやGoogleカレンダーなどGoogleアプリケーションファミリー開発用の言語です。JavaScriptがベースになってます。ES6未対応です。

最初このプログラムを全てES6で書いて letの宣言部で毎回「ステートメントの前に ; がありません」あ、、、となりました。お気をつけください。

継続的に開発するプロジェクトならWebpack + Babelあたりでトランスパイルしてもいいかもしれません。

スプレッドシートを扱う

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); // sheetオブジェクトを取得

var value = sheet.getRange("B2").getValue(); // セルの値を取得
var values = sheet.getRange(8,2,2,1).getValues(); // 複数セルに渡って値を取得

sheet.getRange(1,4).setValue(value); // セルに値を出力
sheet.getRange(2,4,2,1).setValues(values); // 複数セルに渡って値を出力

複数セルの値を操作するときは二次元配列を扱うことになります。

Sheetオブジェクトの詳細は公式ドキュメントで

Googleカレンダーの予定を取得

var mailAddress = '[e-mail]'; // Gmailアドレス
var calendar = CalendarApp.getCalendarById(mailAddress);

var today = new Date();
var events = calendar.getEventsForDay(date); // 一日の予定を取得

Calendarオブジェクトの詳細は公式ドキュメントで

アルゴリズム

この図を見て適当に想像してください。

複数の予定の空きをどう見つけるか

複数人で予定を突き合わせるとだいたい予定の時刻が重なり合います。その場合の空き時間を見つけるアルゴリズムについてです。

まず対象期間の予定を順序を何も考えずにリストLに放り込んだとします。そしてそのうちの予定Aの開始時刻までの空き時間がある状況について考えます。予定Aが8/26 18:00からだとしたら8/26 16:30〜18:00のような空き時間がある状況です。

このときリストLにある予定のうち、予定Aの開始時間より開始時間が早い予定は全て予定Aの開始時間よりも早くに終わっている必要があることがわかります。予定Aの開始時間より開始時間が早い予定が10個あって予定Aの開始時間の段階で9個しか終わってないのだとしたらまだ一個の予定が継続中ですよね。

そこで、対象の期間の予定の開始時刻を全てリストSに、終了時刻を全てリストEに入れます。対象の期間内に、期間外に跨る予定がなければ、当たり前ですがこれら二つのリストの長さは等しくなります。

続いてそれぞれを時刻順にソートします。リストの長さをn以上とした時、リストSのn番目の予定開始時刻までが空き時間であるためにはリストEのn-1番目の時刻がリストSのn番目の予定開始時刻より過去でなければなりません。

始業時間と終業時間を定めたい

空き時間として夜中の0:00から朝7:00みたいなものが取得されてしまうのはイヤですね。このように時間で絞り込みたい場合は始業時間まで、そして終業時間後にずっと予定があるようにリストSとリストEを書き換えればいいですね。

リストSに対象期間よりも過去の時間を、リストEに始業時間を追加して始業時間まで予定があるフリをします。またリストSに終業時間を、リストEに未来の時間を追加して終業時間から予定があるフリをします。

その他処理

タイムスタンプから日付表示

var dateTime = new Date();
var month = dateTime.getMonth()+1;
var day = dateTime.getDate();

Logger.log(Utilities.formatString("%d/%d", month, day));
// 08/25

var hours = dateTime.getHours();
var minutes = dateTime.getMinutes();
Logger.log(Utilities.formatString("%d:%s", hours, minutes));
// 21:50

ES6のテンプレートリテラル使いたい...

ゼロパディング

function padding(num) {
  return ('00' + num).slice(-2)
}

時刻の分は 01 02 と二桁で表記したいですね。

コーディング

以上踏まえて以下のようになります。

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

var openingHour = sheet.getRange("B2").getValue(); // 始業時間（時）
var closingHour = sheet.getRange("B3").getValue(); // 終業時間（時）
var minAssuringMinutes = sheet.getRange("B4").getValue(); // 確保したい時間（分）
var period = sheet.getRange("B5").getValue(); // 検索範囲（日間）
var addressCount = sheet.getRange("B6").getValue(); // アドレスの数
var outputCount = sheet.getRange("B7").getValue(); // 出力空き日程数
var gmailAccounts = sheet.getRange(8,2,addressCount,1).getValues(); // Gmail


function convert2String(date, withTime) {
    if (typeof withTime === "undefined") withTime = true;

    var dateTime = new Date(date);

    if (!withTime) {

		var month = dateTime.getMonth()+1;
		var day = dateTime.getDate();

		return Utilities.formatString("%d/%d", month, day);

    } else {

		var hours = dateTime.getHours();
		var minutes = padding(dateTime.getMinutes());

		return Utilities.formatString("%d:%s", hours, minutes);

    }
}

function crackSchedules(accounts) {
	var calendars = accounts.map(function(account) {
		return CalendarApp.getCalendarById(account[0]);
	});

	var today = new Date();

	var availableStack = [];
	
	for (var d=0;d<period;d++) {
		var date = new Date();
		date.setDate(today.getDate() + d);

		var startTimes = [];
		var endTimes = [];

		var continueFlag = false;
		for (var c=0;c<calendars.length;c++) {
			var events = calendars[c].getEventsForDay(date);

			for (var e=0;e<events.length;e++) {
				if (events[e].isAllDayEvent()) {
					continueFlag = true;
					break;
				}

				var startTime = events[e].getStartTime();
				var endTime = events[e].getEndTime();

				startTimes.push(startTime.getTime());
				endTimes.push(endTime.getTime());
			}
		}
		if (continueFlag) continue;

		var openingDateTime = date.setHours(openingHour, 0, 0, 0);
		var closingDateTime = date.setHours(closingHour, 0, 0, 0);

		startTimes.unshift(0);
		startTimes.push(closingDateTime);

		endTimes.unshift(openingDateTime);
		endTimes.push(date.setHours(23, 59, 59, 999)); // end of day

		startTimes.sort();
		endTimes.sort();

		var dateString = convert2String(date.getTime(), false);

		for (var i=0;i<startTimes.length-1;i++) {
			if (startTimes[i+1] - endTimes[i] >= minAssuringMinutes * 60 * 1000) {
				var startTimeString = convert2String(startTimes[i+1]);
				var endTimeString = convert2String(endTimes[i]);

				availableStack.push([dateString, Utilities.formatString("%s-%s", endTimeString, startTimeString)]);
			}
		}
	}

	return availableStack;
}

function padding(num) {
  return ('00' + num).slice(-2)
}

function main() {
	var schedules = crackSchedules(gmailAccounts);
	schedules.splice(outputCount)
	sheet.getRange(2,4,outputCount,2).setValues(schedules); // 出力
}

作ってみて

この予定の前には30分余裕がいるとかこの予定やっぱどうでもいいとかあるので、結局自分の予定だけは確認してます。