Google Apps Script勉強会に参加しました #gasja

@atusi さん主催のGoogle Apps Script勉強会#2 に参加しました。

第一回の時とは違い、半分くらいの人が「Google Apps Scriptを触ったことがある！」という状態でビックリです。日本でそんなにGASを触っている人がいるなんて…。

発表資料など

@webos_goodiesさんがAWSのROUTE53とSpreadsheetの連携アプリの紹介を、@atusiさんがGASの概要の説明をされました。

• @webos_goodiesさん - GASでアプリ作ってみた
• @atusiさん - Google Apps Script #2

ハカソン

今回はハッカソン主体だったので、自分も何か作ろうと作業しました。お題は「会社の勤怠シートの入力を補助するツール」です。私は勤怠シートを月末にまとめて記入するずぼらなタイプで、毎月入力が面倒です。そこで、下記の機能を持ったスクリプトを作ることにしました。

• 入力する勤怠の対象月を入力する
• 日本の祝日を取得する
• 自分の予定一覧を取得する
• 土日祝以外は基本となる時刻を固定で入力済みにし、自分の予定を補足情報として対象日に表示する

結局作業時間の範囲内では表示に必要なデータの取得までしかできていなかったのですが、ログに出力した内容を表示しつつ「取得まで出来たんだぜ」とか成果発表をしました。

帰宅後に少し作業して表示部分もできたので貼っておきます。アクティブシートにがっつり上書きするので、興味があるなどで試される方は新規のシートで試してください。

	function onOpen() {
	SpreadsheetApp.getActiveSpreadsheet()
	.addMenu('gasja2', [{name: '勤怠入力補助', functionName: 'timesheet'}]);
	}
	function timesheet() {
	try {
	SpreadsheetApp.getActiveSpreadsheet().show(_createUI());
	} catch(e) {
	Logger.log(e);
	Browser.msgBox(e);
	}
	}
	/**
	* 年月入力ダイアログでokボタンがクリックされた時のハンドラ。
	* @param event {object}
	* @return {UiInstance}
	*/
	function okHandler(event) {
	Logger.log('okHandler. parameter=' + event.parameter);
	var app = UiApp.getActiveApplication();
	var yearMonth = event.parameter.yearMonth;
	if (!yearMonth) { return app; }
	var startTime = event.parameter.startTime;
	var endTime = event.parameter.endTime;
	var year = yearMonth.substring(0, yearMonth.indexOf('/'));
	var month = yearMonth.substring(yearMonth.indexOf('/')+1, yearMonth.length);
	var startDate = new Date();
	var endDate = new Date();
	startDate.setFullYear(year, month-1, 1);
	endDate.setFullYear(year, month-1, 31);
	var ss = SpreadsheetApp.getActiveSpreadsheet();
	// 指定された月の日本の祝日、ユーザの予定を取得する。
	var holidays =
	CalendarApp.getCalendarById("ja.japanese#holiday@group.v.calendar.google.com")
	.getEvents(startDate, endDate);
	var ownerEvents =
	CalendarApp.getOwnedCalendarById(ss.getOwner().getEmail())
	.getEvents(startDate, endDate);
	var sheet = ss.getActiveSheet();
	sheet.getRange('A1:D32').clear();
	sheet.setColumnWidth(1, 100).setColumnWidth(2, 350).setColumnWidth(3, 50).setColumnWidth(4, 50);
	sheet.getRange('C2:D32').setNumberFormat('H:mm');
	sheet.getRange('A1:D1').setValues([['日','予定','開始','終了']]);
	for (var day =1; day<=31; day++) {
	var today = new Date();
	today.setFullYear(year, month-1, day);
	if (today.getMonth() != month-1) { break; }
	var rowNum = day+1;
	_day(sheet, rowNum, today, startTime, endTime, holidays, ownerEvents);
	}
	app.close();
	return app;
	}
	function _day(sheet, rowNum, today, startTime, endTime, holidays, ownerEvents) {
	var range, i, event, isHoliday, title;
	range = sheet.getRange('A'+rowNum);
	var tz = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone();
	range.setValue(Utilities.formatDate(today, tz, 'M月d日(EE)'));
	// 日本の祝日を検索し、祝日だった場合はA列のコメントに設定する。
	isHoliday = false;
	for (i = 0; i < holidays.length; i++) {
	event = holidays[i];
	if (event.getStartTime().getYear() == today.getYear()
	&& event.getStartTime().getMonth() == today.getMonth()
	&& event.getStartTime().getDate() == today.getDate()) {
	isHoliday = true;
	range = sheet.getRange('A'+rowNum);
	range.setComment(event.getTitle());
	break;
	}
	}
	// 曜日ごとの処理の振り分け
	if (isHoliday === true || today.getDay() === 0/*Sunday*/) {
	range = sheet.getRange('A'+rowNum);
	range.setBackgroundColor('#ff0000');
	} else if (today.getDay() === 6/*Saturday*/) {
	range = sheet.getRange('A'+rowNum);
	range.setBackgroundColor('#0000ff');
	} else {
	range = sheet.getRange('C'+rowNum+':D'+rowNum);
	range.setValues([[startTime, endTime]]);
	}
	// ユーザの予定を取得し、予定が存在する場合はB列にその内容を記述する。
	for (i = 0; i < ownerEvents.length; i++) {
	event = ownerEvents[i];
	if (event.getStartTime().getYear() == today.getYear()
	&& event.getStartTime().getMonth() == today.getMonth()
	&& event.getStartTime().getDate() == today.getDate()) {
	range = sheet.getRange('B'+rowNum);
	title = range.getValue();
	if (title) { title+= '\n'; }
	title+= _getEventTitle(event);
	range.setValue(title);
	}
	}
	}
	function _getEventTitle(event) {
	if (event.isAllDayEvent()) {
	return '終日 ' + event.getTitle();
	}
	var tz = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone();
	var title = '';
	title += Utilities.formatDate(event.getStartTime(), tz, 'HH:mm');
	title += '-'+Utilities.formatDate(event.getEndTime(), tz, 'HH:mm');
	title += ' '+event.getTitle();
	return title;
	}
	/**
	* 年月入力ダイアログを作成する。
	* @return {UiInstance}
	*/
	function _createUI() {
	Logger.log('_createUI');
	var ui = UiApp.createApplication().setTitle('出力対象の年月を入力してください');
	var yearMonthText = ui.createTextBox().setName('yearMonth');
	ui.add(ui.createLabel('年月(yyyy/MM)')).add(yearMonthText);
	var startTimeText = ui.createTextBox().setName('startTime').setText('10:00');
	ui.add(ui.createLabel('業務開始時刻(HH:mm)')).add(startTimeText);
	var endTimeText = ui.createTextBox().setName('endTime').setText('19:00');
	ui.add(ui.createLabel('業務終了時刻(HH:mm)')).add(endTimeText);
	var okButton = ui.createButton('ok');
	var okHandler = ui.createServerClickHandler('okHandler')
	.addCallbackElement(yearMonthText)
	.addCallbackElement(startTimeText)
	.addCallbackElement(endTimeText);
	okButton.addClickHandler(okHandler);
	ui.add(okButton);
	return ui;
	}

view raw gasja2.js hosted with ❤ by GitHub

新規シートを作ってスクリプトエディタを起動し、コピペでペタっと貼ってシートを再読込すると「gasja2」というメニューが追加されます。その下の「勤怠入力補助」をクリックします。

いくつか入力項目をもったダイアログが表示されるので、2011/02のような形式で年月を入力してOKをクリックします（最初はカレンダーへのアクセスを許可を求めるダイアログが表示されるので、許可する必要があります）。

対象月の勤怠表の雛形が表示されます。土日祝以外はデフォルトの勤務時刻が入力され、土日祝は日付列の背景色が変わります。祝日情報は日付列のコメントに設定されます。また、シート所有者のカレンダー情報は2列目に表示されます。

気になった

今回も新たに勉強になったことがたくさんありましたが、その中でも特に気になった物。

• Rage#setBackgroundColor()
• Range#setNumberFormat()
• Sheet#setColumnWidth()

上記のような、スタイルの設定を行う機能が存在しているということに驚いた。確かgoogle-data-apiのSpreadsheet APIにはセルのスタイルを操作するAPIが無かった記憶があったんですよね。GASは必ずGAS-GData-(Spreadsheet等)という経路でAPIを実行すると思っていたので、実はそれ以外にGAS-(Spreadsheet等)のような経路があるのか？等と思いました。それか、GDataにスタイル操作系のAPIが追加されたか、追加される前兆か？…と気になりました。