Googleカレンダー スプレッドシートに出力する方法

仕事の業務をGoogleカレンダーを利用して行なっている方は、 Googleのスプレッドシートの スクリプトエディタ を利用して、一気にスプレッドシートへ内容を出力することが可能です。

うまく利用すればGoogleカレンダーに実工数管理をしておくだけで自動的にスプレッドシートに工数管理表を作成することも。

この記事ではその為の初めの一歩。
スプレッドシートに出力するまでを解説したいと思います。

手順

1. 出力先となるスプレッドシートの用意
2. スクリプト作成
3. スプレッドシートの編集

出力先となるスプレッドシートの用意

まずはこれがないと始まりません。スプレッドシートを準備しておきます。
今回はとりあえず「タイトル」「業務開始日時」「業務終了日時」「詳細」「所要時間」の5つを出力していきます。

自分のスプレッドシートは以下のような感じに仕上がりました。

スクリプト作成

「ツール > スクリプトエディタ」を選択すると新しいタブが開き、スクリプトを書くための画面が表示されます。 スクリプトには自分の好きなタイトルをつけておいてください。自分は「calendar stuff」とつけておきました。

スクリプトは getEvent() という名前にしました。「コード.gs」に以下のように関数を準備しておきます。

function getEvents() {
    // todo...
}

Googleカレンダーにアクセスする

まずはスクリプトの中でGoogleカレンダーにアクセスする為に、自分の取得したいカレンダーの「メニュー > 設定と共有」を開きます。

「マイカレンダーの設定 > カレンダーの統合 > カレンダーID」から カレンダーID をコピーしておきます。

スクリプトに以下の様に追記します。
こうすることで CalendarApp オブジェクトを取得することができます。
公式ドキュメントからクラスのメソッドについて調べることもできます。

unction getEvents() {
    var calendar = CalendarApp.getCalendarById("xxxxx@gmail.com");
}

つづいて、カレンダーの取得したい期間のイベントを取得します。
スクリプトに以下の様に追記することで、「2019/2/1 ~ 2019/2/28」までの全てのイベントを取得することができます。

unction getEvents() {
    var calendar = CalendarApp.getCalendarById("xxxxx@gmail.com");
    var events = calendar.getEvents(new Date("2/1/2019 00:00 AM"), new Date("2/28/2019 11:59 PM"));
}

タイトルを取得してみる

例えば、各イベントのタイトルだけを取得する場合、以下の様に書くことができます。 以下では実際にログに吐き出して確認してます。

function getEvents() {
  var calendar = CalendarApp.getCalendarById("canada.shohe@gmail.com");
  var events = calendar.getEvents(new Date("2/1/2019 00:00 AM"), new Date("2/28/2019 11:59 PM"));

  for(var i=0; i<events.length; i++) {
    var title = events[i].getTitle();
    Logger.log(title);
  }
}

再生ボタンを押すとスクリプトが実行されます。

その後「表示 > ログ」でダイアログを表示します。
Googleカレンダー上にあるイベントのタイトル名が出力されています。

スプレッドシートにアクセスする

出力するためのスプレッドシートにアクセスして出力していきます。
スクリプトに以下の様に追記します。

function getEvents() {
  var spreadSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); // ← ① スプレッドシート取得

  var calendar = CalendarApp.getCalendarById("canada.shohe@gmail.com");
  var events = calendar.getEvents(new Date("2/1/2019 00:00 AM"), new Date("2/28/2019 11:59 PM"));

  for(var i=0; i<events.length; i++) {
    var title = events[i].getTitle();
    var start = events[i].getStartTime();
    var end = events[i].getEndTime();
    var description = events[i].getDescription();

    spreadSheet.getRange(2+i, 1).setValue(title); // ← ② getRange(row, column) でセルにアクセス
    spreadSheet.getRange(2+i, 2).setValue(start);
    spreadSheet.getRange(2+i, 2).setNumberFormat("mm/dd/yyyy h:mm:ss AM/PM"); // ← ③ 日付のフォーマット設定
    spreadSheet.getRange(2+i, 3).setValue(end);
    spreadSheet.getRange(2+i, 3).setNumberFormat("mm/dd/yyyy h:mm:ss AM/PM");
    spreadSheet.getRange(2+i, 4).setValue(description);
  }
}

先ほどと同じく再生ボタンで実行します。
初めてこのスクリプト（スプレッドシートにアクセスする）を実行すると、アクセス権限についてエラーが表示されます。

承認が必要です
無題のプロジェクト needs your permission to access your data on Google.

「許可を確認」を押すとログインをさせられ、以下の様な画面に飛ばされます。

小さい文字ですが「詳細 > 無題のプロジェクト（安全ではないページ）に移動」で実行されます。

スプレッドシートの編集

最後におまけで所要時間の計算式をスプレッドシート側に入力します。

=(C2-B2)*24

終わりに

以上でカレンダー情報をスプレッドシートに出力することができました。
これを改良していってとことん自動化していきたいです。
目標はカレンダーを更新する度に自動的に工数表も書き換わっている状態。
また続きをのちに書きたいと思います。