Google Apps ScriptでスプレッドシートとGoogleカレンダーを連携させて予定一括登録アドオンを作った

Googleカレンダーは予定の管理で便利だが、大量の予定を登録しようと思うと、かなり作業が面倒だ。

そこで、スプレッドシートに登録したいまとめて予定を入力し、Google Apps Scriptを使ってGoogleカレンダーに一括登録できるアドオンを作ってみた。

## アドオンの概要
今回作ったアドオンについて、もう少し詳しくまとめた。

### 使用技術
アドオンの開発で使用した技術は、以下の通りだ。

|技術|目的|
|---|---|
|[Google Apps Script](https://developers.google.com/apps-script/)|スプレッドシートとGoogleカレンダーの連携|
|[Vue.js](http://vuejs.org)|アドオンのサイドバーのコーディング|
|[Material Components](https://material.io/develop/web/)|アドオンのサイドバーのデザイン|

### 機能一覧
今回作ったアドオンの機能について、もう少し詳しくまとめた。

|機能|説明|
|---|---|
|シートの自動生成|事前に設計した入力用シートの再利用|
|カレンダー更新|カレンダーを新規作成・削除時の更新|
|カレンダー選択|予定の登録先のカレンダーを予定ごとに設定|
|終日の対応|終日の予定でも登録できるように対応|
|繰り返しの対応|毎日・毎週・毎月・毎年の繰り返しに対応|

入力用シートだが、毎回作っていては面倒でミスも生まれるので、事前にテンプレートを作っておいて、それをコピーして再利用できるようにした。
<img width="1424" alt="スクリーンショット 2019-02-10 23.31.26.png" src="https://qiita-image-store.s3.amazonaws.com/0/105813/144d19f8-bf28-dd73-cda9-a191bdb3bfa7.png">

また、Googleカレンダーは「マイカレンダー」以外に自由にカレンダーを作成できるが、その作成・削除をシートに反映できるようにした。

さらに、よく使う終日の予定や繰り返しの予定の登録にも対応した。
繰り返しは本当はもっと細かく設定できるが、今回は毎日〜毎年の単純な繰り返しのみ対応した。

## 開発
ここから、実際の開発について説明する。

### Google Apps Scriptについて
まず、今回のコアになるGoogle Apps Script（以後GASと略す）について。
これはJavascriptベースの言語で、Googleの様々なサービスを操作したり、連携させたりできる。

[リファレンス](https://developers.google.com/apps-script/reference/)を見るとわかるが、GmailやGoogleカレンダー、Googleドライブなど、対応サービスは多い。

Javascriptがわかれば基本的には学習コストも低く、基本的にブラウザ上で開発が一通りできる。
ソースコードもGoogleドライブ上で管理できる。

### アドオンの実装
今回の機能の実装にアドオン化は必須ではないが、せっかくなので勉強を兼ねてやってみた。

調べてみると、最低限必要なのは`code.gs`と`sidebar.html`の2つのファイルだ。
ちなみに、ファイル名は特に決まっていない。

|ファイル|説明|
|---|---|
|code.gs|実際にGASを書き込むファイル<br>アドオンの起動もここで行う|
|sidebar.html|サイドバーを構成するHTMLファイル|

```js:code.gs
/**
 * スプレッドシートを開いたときにアドオンを追加する関数
 * アドオンのメニュー名と、対応して実行したい関数を設定する
 */
function onOpen(e) {
	Logger.log("onOpen");
	SpreadsheetApp.getUi().createAddonMenu()
		.addItem('起動', 'showSidebar') //ここでメニューを追加
		.addToUi();
}

/**
 * アドオンをインストールしたときに起動する関数
 * 基本的にはonOpen()を呼び出す
 */
function onInstall(e) {
	Logger.log("onInstall");
	onOpen(e);
}

/**
 * サイドバーを表示する関数
 * sidebar.htmlを読み込んで表示する
 */
function showSidebar() {
	Logger.log("showSidebar");
	var ui = HtmlService.createHtmlOutputFromFile('sidebar').setTitle('ScheduleGenerator'); //ここでhtmlファイルを読み込む
	SpreadsheetApp.getUi().showSidebar(ui);
}
```

### サイドバーの実装
前述したとおり、アドオンのサイドバーはhtmlで記述する。
基本的にはhtml,css,jsで記述していくが、今回[Vue.js](http://vuejs.org)で作ってみた。

また、CSSには[Material Components](https://material.io/develop/web/)を使ってみた。
[リファレンス](https://developers.google.com/gsuite/add-ons/guides/css)を見ると、本当は用意されたCSSを適応させたほうが良い。

```html
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
```

ただ、最近Google Docsが新しくマテリアルデザインに対応して合っていないので、今回はマテリアルデザインのCSSフレームワークを使った。
（アドオンを公開するときは審査で怒られるので、リファレンスに従おう）

以下に、基本的な構成を記述している。

```html:sidebar.html
<!DOCTYPE html>
<html>

<head>
	<base target="_top">
	<link rel="stylesheet" href="https://unpkg.com/material-components-web@latest/dist/material-components-web.min.css">
</head>

<body>
	<div id="app">
		<div>
			<!-- @click="fancName"でボタンクリック時に関数を実行、いわゆるonclick -->
			<button class="mdc-button mdc-button--unelevated" @click="fancName">
				<span class="mdc-button__label">ボタン</span>
			</button>
		</div>
	</div>
	<script src="https://unpkg.com/material-components-web@latest/dist/material-components-web.min.js"></script>
	<script src="https://cdn.jsdelivr.net/npm/vue/dist/vue.js"></script>
	<script>
		window.mdc.autoInit(); //Material Componentsの初期化処理

		var app = new Vue({
			el: '#app',
			methods: {
				//@click(いわゆるonclick）で実行される関数
				fancName: function () {
					// google.script.run.doSomething()でdoSomething()というcode.gs側の関数を実行可能
					google.script.run
						.withSuccessHandler(function (e) {
							//成功時の処理
						})
						.withFailureHandler(function (e) {
							//失敗時の処理
						})
						.gasFanc()
				}
			}
		})
	</script>
</body>

</html>
```

#### サイドバーからGASの関数の実行
ここで注目してほしいのが、`google.script.run.doSomething()`という部分だ。
この記述を行うと、`code.gs`で宣言した`doSomething()`という関数を実行できる。
これで、`sidebar.html`から`code.gs`の関数の実行が可能になっている。
もちろん、引数も渡すことができる。

また、`doComething()`が成功したときと失敗した時の処理を書きたい場合、以下のように`withSuccessHandler()`と`withFailureHandler()`を記述することで可能だ。
それぞれの引数には特定の関数（もしくは無名関数を直接書く）を与え、`doSomething()`の返り値は引数で受け取れる（以下でいう`e`がそれ）。

```js:code.js
google.script.run
	.withSuccessHandler(function(e) {
		//ここに成功時の処理
	})
	.withFailureHandler(function (e) {
		//ここに失敗時の処理
	})
	.doSomething();
```

リファレンスは[こちら](https://developers.google.com/apps-script/guides/html/reference/run)。

### スプレッドシートとGoogleカレンダーの処理
サイドバーを表示してGASの関数の実行が出来たので、実際にGASを使ったスプレッドシートとGoogleカレンダーの処理を書いていく。

#### シートテンプレートの利用
GASを使えば、シートの編集もできる。
しかし、細かいレイアウトをGASで記述するのは効率が悪いので、テンプレート用のシートはスプレッドシート側で作って、これをアドオンで任意のスプレッドシートにコピーして利用できるようにした。

```js:code.js
//今回テンプレート用に作成したスプレッドシートのID
var SS_ID = '1I6Joo8vVs3jcuCTdL0AtucB7edXD-PzbYja3ANLxpfU';

function addSheet() {
	var ss = SpreadsheetApp.getActiveSpreadsheet();
	//現在のスプレッドシートにテンプレートをコピー
	var sheet = SpreadsheetApp.openById(SS_Id).getSheetByName('template').copyTo(ss);
	sheet.activate();
	//カレンダーを選択できるよう、次に説明するupdateCalendarList()を実行
	updateCalendarList();
}
```

これで任意のシートを任意のスプレッドシートにコピーできる。
リファレンスは[こちら](https://developers.google.com/apps-script/reference/spreadsheet/sheet)。

#### カレンダー一覧の取得
登録先カレンダーを任意のものにできるよう、自分がGoogleカレンダー上に作成しているカレンダーの情報を取得する。

```js:code.js
//今回テンプレート用に作成したスプレッドシートのID
var SS_ID = '1I6Joo8vVs3jcuCTdL0AtucB7edXD-PzbYja3ANLxpfU';

function updateCalendarList() {
	//自分のカレンダー一覧を取得
	var calendars = CalendarApp.getAllOwnedCalendars();
	var calendarList = {};
	for (var i in calendars) {
		//あとでカレンダー名からカレンダーobjを取得しやすいように
		//{カレンダー: カレンダーオブジェクト}という形式の連想配列に整形
		calendarList[calendars[i].getName()] = calendars[i];
	}

	var ss = SpreadsheetApp.getActiveSpreadsheet();
	var sheet = ss.getActiveSheet();
	var range = sheet.getRange(2, 3, sheet.getMaxRows() - 1, 1);
	var calendarNames = Object.keys(calendarList);
	// カレンダー選択列に入力規則としてカレンダー名を指定
	var rule = SpreadsheetApp.newDataValidation().requireValueInList(calendarNames, true).setAllowInvalid(false).build();
	range.setDataValidation(rule);

	return calendarList;
}
```

ここでのポイントは「カレンダー情報の取得」と「カレンダーリストのシートへの設定」だ。
自分が管理しているカレンダーは`CalendarApp.getAllOwnedCalendars()`で取得できる。
リファレンスは[こちら](https://developers.google.com/apps-script/reference/calendar/calendar-app#getAllOwnedCalendars())。

スプレッドシートには入力規則を使ってリストからセルの値を設定できるが、GASでもこの設定が可能だ。
これは`SpreadsheetApp.newDataValidation().requireValueInList(values, showDropdown).setAllowInvalid(false).build()`で可能だ。
この`equireValueInList(values, showDropdown)`という部分でンリストの項目と、ドロップダウン表示の有効化を行っている。
また、`setAllowInvalid(false)`とすることで、これ以外の値を禁止している。
最後に`range.setDataValidation(rule)`とすることで、`range`の範囲のセルに`rule`を適応している。

リファレンスは[こちら](https://developers.google.com/apps-script/reference/spreadsheet/data-validation-builder)。

#### 予定の登録
ここからが、今回のコアとなる部分だ。

```js:code.js
function generateEvent(calendar, title, start, end, allDay, description, loop, loopEnd) {
	//タイトル・開始・終了は必須
	if (calendar == '' || title == '' || start == '' || end == '') {
		return null;
	}
	try {
		//var calendar = CalendarApp.getDefaultCalendar();
		//繰り返しの予定かどうかで分岐
		if (loop == '') {
			if (allDay == false) {
				//繰り返しなし・終日でない
				return calendar.createEvent(title, start, end, { 'description': description });
			} else if (allDay == true) {
				//繰り返しなし・終日
				return calendar.createAllDayEvent(title, start, end, { 'description': description });
			}
		} else if (loop != '') {
			//繰り返しルールの作成
			var recurrence = buildRecurrenceRule(loop, loopEnd);
			if (allDay == false) {
				//繰り返し・終日でない
				return calendar.createEventSeries(title, start, end, recurrence, { 'description': description });
			} else if (allDay == true) {
				//繰り返し・終日
				return calendar.createAllDayEventSeries(title, start, recurrence, { 'description': description });
			}
		}
	} catch (error) {
		return null;
	}
}
```

ここでは以下の2点について2*2の4パターンに分岐している。

* 終日の予定かどうか
* 繰り返しの予定かどうか

理由は、それぞれで実行するメソッドが異なるからだ。
以下が、それぞれのメソッドになる。

|メソッド|終日|繰り返し|
|---|---|---|
|createEvent()|false|false|
|createAllDayEvent()|true|false|
|createEventSeries()|false|true|
|createAllDayEventSeries()|true|true|

リファレンスは[こちら](https://developers.google.com/apps-script/reference/calendar/calendar)。

#### 繰り返しルールの設定
上のコードで`buildRecurrenceRule(loop, loopEnd)`という部分があったが、実はここで繰り返しのルールを設定している。

```js:code.js
function buildRecurrenceRule(loop, end) {
	var recurrence = CalendarApp.newRecurrence();
	switch (loop) {
		case '毎日':
			recurrence = recurrence.addDailyRule();
			break;
		case '毎週':
			recurrence = recurrence.addWeeklyRule();
			break;
		case '毎月':
			recurrence = recurrence.addMonthlyRule();
			break;
		case '毎年':
			recurrence = recurrence.addYearlyRule();
			break;
	}
	recurrence.until(end);
	return recurrence;
}
```

今回も、繰り返しの頻度で分岐している。

|メソッド|繰り返し頻度|
|---|---|
|addDailyRule()|毎日|
|addWeeklyRule()|毎週|
|addMonthlyRule()|毎月|
|addYearlyRule()|毎年|

また、繰り返しの終了日が入力されていた場合、`until(endDate)`で設定している。
今回は使っていないが、`times(times)`で繰り返し回数も設定できる。

リファレンスは[こちら](https://developers.google.com/apps-script/reference/calendar/recurrence-rule)。

#### 一括登録
最後に、一括登録を行う。

```js:code.js
var COL = {
	TITLE: 0,
	CAL: 1,
	ALLDAY: 2,
	START: 3,
	END: 4,
	LOOP: 5,
	LOOP_END: 6,
	DESCRIPTION: 7
};

function generateEvents() {
	var ss = SpreadsheetApp.getActiveSpreadsheet();
	var sheet = ss.getActiveSheet();

	var range = sheet.getRange(2, 2, sheet.getLastRow() - 1, sheet.getLastColumn() - 1);
	var values = range.getValues();

	var checkBox = sheet.getRange(2, 1, sheet.getLastRow() - 1, 1);
	var results = checkBox.getValues();

	var result = 0;
	var calendarList = updateCalendarList();
	for (var i in values) {
		if (results[i][0] == false) {
			var value = values[i];
			var event = generateEvent(
				calendarList[value[COL.CAL]],
				value[COL.TITLE],
				value[COL.START],
				value[COL.END],
				value[COL.ALLDAY],
				value[COL.DESCRIPTION],
				value[COL.LOOP],
				value[COL.LOOP_END]
			);
			if (event != null) {
				results[i][0] = true;
				result.success++;
			} else {
				results[i][0] = false;
			}
		}
	}
	checkBox.setValues(results);
	return result;
}
```

基本的には、シートの内容を取得してきて、順番に`generateEvent()`を実行しているだけだ。

あえて言うなら、登録完了した予定のチェックを`FALSE`から`TRUE`に変えることで、複数回実行した時の2重処理を防いている。

### 実行
以上で、主な処理に関しては説明した。
細かい部分に関しては、説明は省く。
実際のコードに関しては、[Githubで公開している](https://github.com/YuheiFUJITA/schedule-generator-for-qiita)。
使いたい場合は以下の手順で利用可能だ。

 1. スプレッドシートを開く
 2. **ツール＞スクリプトエディタ**からスクリプト編集画面を開く
 3. `code.js`と`sidebar.html`を作成し、内容をコピペする
 4. 保存し、スプレッドシートを再読込する
 5. **アドオ**に**ScheduleGenerator＞起動**が追加されているので、クリックする
 6. サイドバーが表示されたら**追加**をクリックし、入力用シートを作成する
 7. 一括登録したい予定をすべて入力する
 8. サイドバーの**登録**をクリックする。

## まとめ
今回スプレッドシートのアドオンを作ってみたが、それほど難しいということはなかった。
エラー処理などはほとんど行っていないので、変な値がシートに入力されていると正しく動作しない場合があるので、使うときは注意して欲しい。
（一応シートのテンプレートで入力規則は設定した）

可能であれば、ストアに公開しようと思う（審査が通れば）。
GithubとGASのリファレンスは以下にある。

* [YuheiFUJITA/schedule\-generator\-for\-qiita](https://github.com/YuheiFUJITA/schedule-generator-for-qiita)
* [Apps Script  \|  Google Developers](https://developers.google.com/apps-script/)