LoginSignup
15
12

More than 5 years have passed since last update.

@Yuhei_FUJITA(Yuhei FUJITA)

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

Last updated at Posted at 2019-02-11

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

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

アドオンの概要

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

使用技術

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

技術 目的
Google Apps Script スプレッドシートとGoogleカレンダーの連携
Vue.js アドオンのサイドバーのコーディング
Material Components アドオンのサイドバーのデザイン

機能一覧

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

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

入力用シートだが、毎回作っていては面倒でミスも生まれるので、事前にテンプレートを作っておいて、それをコピーして再利用できるようにした。
スクリーンショット 2019-02-10 23.31.26.png

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

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

開発

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

Google Apps Scriptについて

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

リファレンスを見るとわかるが、GmailやGoogleカレンダー、Googleドライブなど、対応サービスは多い。

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

アドオンの実装

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

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

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

また、CSSにはMaterial Componentsを使ってみた。
リファレンスを見ると、本当は用意されたCSSを適応させたほうが良い。

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

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

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

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がそれ）。

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

リファレンスはこちら。

スプレッドシートとGoogleカレンダーの処理

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

シートテンプレートの利用

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

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();
}

これで任意のシートを任意のスプレッドシートにコピーできる。
リファレンスはこちら。

カレンダー一覧の取得

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

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()で取得できる。
リファレンスはこちら。

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

リファレンスはこちら。

予定の登録

ここからが、今回のコアとなる部分だ。

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

リファレンスはこちら。

繰り返しルールの設定

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

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)で繰り返し回数も設定できる。

リファレンスはこちら。

一括登録

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

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で公開している。
使いたい場合は以下の手順で利用可能だ。

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

まとめ

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

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

15
12
3

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
15
12