Google Apps Script Google Workspace (G Suite) Javascript プログラミング

Googleカレンダーの検索できない問題をGASでスプレッドシートに書き出して解決?

カレンダーで検索できないならスプレッドシートで検索すればいい

以前、こんな記事を書きました。

このときの結論は、別アプリと連携してそっちで検索するというものでした。

しかし、やっぱり他のアプリを使うのはちょっと不便です。同期が遅かったりするし。

そんなワケで別の方法を考えていたら、GASで全部の予定をスプレッドシートに書き出して、そっちで検索すればいいんじゃない？と思いました。

ちょっと検索してみたら、予定をスプレッドシートに転送している人は結構いらして、情報も結構豊富なのでやってみたら，割とあっさりとできました。

スプレッドシートを開いてメニューを選べばOK

使い方は簡単です。

カレンダーIDと、さかのぼる年数の設定

まず、検索用のGoogleスプレッドシートを開きます。

B1セルに取得したいカレンダーIDを設定します。カレンダーが複数ある場合は右方向、C2以降のセルにに追加します。

カレンダーIDはGoogleカレンダーのカレンダーリストでハンバーガーメニューから「設定と共有」を選択すると分かります。

メインのカレンダーも省略形の"primary"ではなく、Googleアカウントのメールアドレスを設定します。

B2セルには本日から何年分さかのぼって取得するかを入力します。

カレンダーIDとさかのぼる年数は一度設定すれば、次回もそのまま使えます。

イベントデータを取得する

データを取得するには「イベント取得」というカスタムメニューから「開始」を選択します。

指定したカレンダーと期間の全予定がスプレッドシートに書き込まれます。

収集される情報は、開始日時、タイトル、説明、場所、それに後述するイベントURLを付け加えています。

あとはスプレッドシートの検索やフィルタといった機能を使って目的のイベントを探します。

各イベント情報の最後のURLをクリックするとGoogleカレンダーのページが開き、該当するイベントが表示されます。

スプレッドシートへの書き出しは、日常的に検索するにはちょっと面倒ですが、たまに検索する場合の手法としてはアリかなぁと思います。

日常的に検索する人は他のカレンダーサービスやアプリ連携を使いましょう。

コード

コードは以下のようなものです。Googleスプレッドシートに付属のスクリプトとして設定してください。

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

//メニューバーにコマンドを追加
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  const menu = ui.createMenu("イベント取得");
  menu.addItem("開始", "getPastEvents");
  menu.addToUi();
}

function clearSheet() {
  sheet.getRange("A5:G").clearContent();
}

function getPastEvents() {

  sheet.getRange("A4:G").clearContent();
  const lastColumn = sheet.getLastColumn();
  const myCalenderIDs = sheet.getRange(1, 2, 1, lastColumn - 1).getValues()[0];
  const yearsToCheck = sheet.getRange(2, 2).getValue();
  const myEventData = [];

  const names = ["日付", "タイトル", "説明", "場所", "イベントURL"];
  sheet.getRange("A4:E4").setValues([names]);

  for (let j = 0; j < myCalenderIDs.length; j++) {
    Logger.log(myCalenderIDs[j]);
    const calendar = CalendarApp.getCalendarById(myCalenderIDs[j]);
    let startTime = new Date();
    startTime.setFullYear(startTime.getFullYear() - yearsToCheck);
    const endTime = new Date();
    const events = calendar.getEvents(startTime, endTime);

    for (let i = 0; i < events.length; i++) {
      const splitEventId = events[i].getId().split('@');
      const eventURL = "https://www.google.com/calendar/event?eid=" + Utilities.base64Encode(splitEventId[0] + " " + myCalenderIDs[j]);

      const temp = [
        events[i].getStartTime(), //開始日時
        events[i].getTitle(), //タイトル
        events[i].getDescription(),//説明
        events[i].getLocation(),//場所
        // events[i].getEndTime(),
        // events[i].getId(),
        eventURL,//イベントURL
      ];
      myEventData.push(temp);
    }
  }

  sheet.getRange(5, 1, myEventData.length, myEventData[0].length).setValues(myEventData);//まとめてスプレッドシートに書き込み

}

Googleカレンダーで指定した期間のイベントを取得する

Googleカレンダーで指定した期間のイベントを取得するには以下のように書きます。

一回で取得する数には上限があるかもしれません。私はそんなに予定が多くないので10年分でも余裕で取得できました。

## 10年前から今日までのイベントを取得
    const calendar = CalendarApp.getCalendarById(***カレンダーID***);
    let startTime = new Date();
    startTime.setFullYear(startTime.getFullYear() - 10);
    const endTime = new Date();
    const events = calendar.getEvents(startTime, endTime);

上記のコードで取得できるeventsはイベントオブジェクトの配列になっています。

要素は直接参照せずに、以下のような取得メソッドで取り出します。

        events[i].getStartTime(), //開始日時
        events[i].getTitle(), //タイトル
        events[i].getDescription(),//説明
        events[i].getLocation(),//場所
        events[i].getEndTime(),//終了日時
        events[i].getId(),//イベントID

Google Class Calendar Event公式ドキュメント

コードは以下のブログ記事を参考にしました。感謝。

GAS：Googleカレンダーに登録している予定をスプレッドシートに出力して工数管理をする

実行時に時々、原因不明のエラーが出ますが、エラーが出たときもスプレッドシートは更新されています。うーん。まいっか。

カレンダーイベントURLを生成する

各行の最後にはそのイベントをGoogleカレンダー上で見るためのURLを付加しています。

このURLを開くとGoogleカレンダーの画面が開き、イベントが選択された状態になります。

イベントURLは以下のように生成しています。

      const splitEventId = events[i].getId().split('@');
      const eventURL = "https://www.google.com/calendar/event?eid=" + Utilities.base64Encode(splitEventId[0] + " " + myCalenderIDs[j]);

イベントURLの生成は以下の情報を元にしています。

Google Apps ScriptでGoogleカレンダーのイベントのURLを手に入れる

このイベントURLは、プライマリのカレンダーについては完璧動作しています。クリックするとちゃんと該当するイベントが表示されます。

ところが個人的に追加してあるユーザーカレンダーでは、URLをクリックするとカレンダーの該当月は開くものの、イベントは選択されず「 リクエストされた予定を開くことができませんでした」といったエラーメッセージが表示されます。

理由は分かりませんが、まぁ実用上は問題ありません。

setValues()メソッドでスプレッドシートへの書き込みを高速化する

GASはAPIコールが遅いので、できるだけブラウザ側のjavascriptで処理して、APIを呼ぶ回数を減らします。

スプレッドへの書き込みは、appendRawやsetValueメソッドでひとつずつ書き込むよりも、setValuesメソッドを使って配列から一括で書き込むほうが圧倒的に速く処理できます。

GASは実行時間の制限があるので大量の処理を行う場合には高速化が大事みたいです。

setValuesは配列のサイズにぴったり合ったrangeを設定する必要がありますが、以下のようlengthを使って簡単に設定できます。定番の書き方としてメモしておきます。

array = データの入った2次元配列
column = 開始セルのカラム
raw  = 開始セルの行
sheet.getRange(raw, column, array.length, array[0].length).setValues(array);

ちなみに setValues()に渡す配列は２次元配列である必要があります。

1行だけ書き込む場合には [array] のように配列を[ ]で囲って2次元配列に変換して渡します。

-Google Apps Script, Google Workspace (G Suite), Javascript, プログラミング