広告 プログラミング

【GAS入門】GmailとGoogleカレンダーの連携!パターンにあわせて予定を自動登録する

※本ページには、プロモーション(広告)が含まれています。

【GAS入門】GmailとGoogleカレンダーの連携!パターンにあわせて予定を自動登録する
悩んでいる人

Gmailに届くメールに対し、日付や内容に基づいてGoogleカレンダーに予定を自動登録したい。

極力プログラミングせずに利用できる仕組みを教えて欲しい。

こんなお悩みを解決します。

以前、Gmailに送られてくるキャンペーン情報をフィルタリング・情報抽出した上で、Googleカレンダーに登録するところまでを自動的に実施する方法について紹介しました。

あわせて読みたい

こちらの仕組み自体は良かったのですが、Googleカレンダーに予定を登録する際の設定が不足しており、使い勝手が悪い仕組みとなっていました。

今回は仕組み自体を見直し、Googleカレンダーに登録する情報を(極力)ユーザ側で自由に設定できるように改善しております。

前回同様、設定ファイルはGoogleスプレッドシートを利用しているため、プログラミング知識がない人でもカスタマイズしやすい構成になっています。

また、自動化のためにGoogle Apps Script(以下、GAS)を利用しますが、Googleスプレッドシートに埋め込んで利用する方針に変更したため、サンプルを流用すれば実装内容を気にせずに使えるようになっています。

効率良く技術習得したい方へ

短期間でプログラミング技術を習得したい場合は、経験者からフォローしてもらえる環境下で勉強することをおすすめします。

詳細は、以下の記事をご覧ください。

【比較】プログラミングスクールおすすめランキング6選【初心者向け】

続きを見る

やりたいこと

仕組み自体は前回と同様で、Gmailから条件に該当するメールを取り出した上で、指定した情報を抽出し、カレンダーに反映する部分までを自動化します。

やりたいことの全体像

仕組みが適用できるユースケースの例

今回は、「会議案内のメールや旅行先の予約メールに記載された日時の情報をもとに、Googleカレンダーに予定を自動登録する」というユースケースを考えます。

ここでは、ホテル予約と会議予約の2つをユースケースとして取り上げ、解説したいと思います。

ユースケース①:ホテル予約

例えば、以下のようなホテルの予約メールが届いたとします。

ユースケース①:Aホテル予約情報【22/1/31】

ここに記載されている情報のうち、下記の赤枠で囲まれた情報を利用すれば、Googleカレンダーに予定を登録することができますよね。

ユースケース①:Aホテル予約情報(説明付き)【22/1/31】

実際に、今回紹介する仕組みを用いることで、以下のような予定を自動登録できるようになります。

ユースケース①:自動登録結果

ユースケース②:会議予約

例えば、以下のような会議予約メールが届いたとします。

ユースケース②:会議予約【22/2/1 15:30-16:00】

ここに記載されている情報のうち、下記の赤枠で囲まれた情報を利用すれば、Googleカレンダーに予定を登録することができますよね。

ユースケース②:会議予約(説明付き)【22/2/1 15:30-16:00】

実際に、今回紹介する仕組みを用いることで、以下のような予定を自動登録できるようになります。

ユースケース②:自動登録結果

これらのユースケースは、被リンク元のサイトで紹介されていた情報をもとに作成しました。

【コピペで動く!】GASによる自動化

以降では、GASで自動化する際の考え方と実現する際の実装例を紹介します。

GASで自動化する際の考え方

まず、Googleカレンダーに予定を登録する際の手続きを次の3ステップに分けて捉えます。

以降では、それぞれの手順の具体的な実現方法を紹介します。

該当するメールの抽出

今回は、Gmailが提供する検索機能が受け付ける入力と同じものをユーザが与える方針とします。

例えば、未読メールを検索する場合はis:unreadとすればよいため、このような入力をユーザが指定できる構成とします。

メールの検索方法

設定対象の定義と抜き出すルールの指定方法

通常、Googleカレンダーに予定を追加する場合、以下のような画面が表示されます。

Googleカレンダーで予定を追加する際に表示される画面

上記の画面において、一般的に人手で設定する部分を抜き出すと以下のようになります。

予定追加時に人手で設定する部分

今回は、上記の8点を下記のように対応付けることで動的に設定できる方針としました。

図中の該当箇所設定時の名称該当列
①タイトルタイトル(title)D列
②開始日開始日(startDate)D列
③開始時刻開始時刻(startTime)D列
④終了時刻終了時刻(endTime)D列
⑤終了日終了日(endDate)D列
⑥場所場所(location)D列
⑦Googleカレンダー上で表示される色オプション[option]C列
⑧説明説明(description)D列
対応関係

※詳細は、Googleスプレッドシートのサンプルの以下の部分を参照してください。

サンプルのGoogleスプレッドシート上での対応関係

また、抜き出し方は前回同様、正規表現を活用します。

正規表現を用いた該当箇所の抽出

予定の登録

後は、これまでの方針を踏まえてGASで実装するだけとなります。

詳細は、以降で解説します。

実装①:Googleスプレッドシートの設定

先行して紹介したGoogleスプレッドシートの構成を改めて解説しておきます。

今回は、以下の構成で設定情報を管理します。

項目名プログラム上での名称入力形式内容備考
新規追加-チェックボックスチェックの有無により以下を識別するために利用する。
・チェックあり:ルールの新規追加
・チェックなし:既存ルールの継続
チェックを付けた場合、同じ行に対して検索クエリとオプションを必ず設定すること
検索クエリquery自由入力メールを検索する際のフィルタリングルールとして利用する。指定するルールはGmailのフィルタリングルールに従う
オプションoptionプルダウンリストGoogleカレンダー上に表示する際の色を設定する。Googleカレンダーで利用できる色をあらかじめ設定済み
予定追加時の設定情報calendarConfigプルダウンリストGoogleカレンダー上の要素に対し、紐づけ対象を設定する。以降で解説するGASのプログラムで参照するため、プルダウンリストの内容は変更しないこと
取得パターンcollectionPattern正規表現該当するメールの本文に対し、情報を取得するパターンを設定する。正規表現のみ受け付ける
ユーザ用のメモ欄-自由入力ユーザ用のコメントを記載する。プログラムで参照しない箇所
Googleスプレッドシートの構成

下記のサンプルもあわせてご確認ください。

https://docs.google.com/spreadsheets/d/1icUkEbbWfEGePqvuUMRby0gqKkptw02MFgKpUiL5gAo/edit?usp=drive_link

実装②:GASでのコーディング

以降では、先ほど示した処理内容をGASで実現していきます。

Googleスプレッドシートから設定情報を取得する処理

前回の記事とほとんど同じ内容のため、割愛します。

/**
 * @brief Google SpreadSheetから設定情報を取得
 * @param[in] spreadsheet Google SpreadSheetのインスタンス
 * @param[in] sheetName 設定情報が記載されたシート名
 * 
 * @return    results 設定情報一覧
 */
const getConfigFromSpreadSheet = (spreadsheet, sheetName) => {
  // スプレッドシートから該当するシートを取得
  const sheet = spreadsheet.getSheetByName(sheetName);
  // データ取得
  const results = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).getValues();

  return results;
};

Googleスプレッドシートから取得したデータを整形する処理

今回は1行ずつ処理するのではなく、ユーザが定義したルールごとに処理していきます。

まず、「B列からE列までが空欄」の場合、必要な情報が記載されていないと判断し、その行を削除します。(該当箇所:15行目)

その後、ルール単位でまとめます。

const _zip = (...arrays) => {
  const length = Math.min(...(arrays.map(arr => arr.length)));
  return new Array(length).fill().map((_, i) => arrays.map(arr => arr[i]));
};
const _genArray = (len, offset=0) => [...Array(len).keys()].map(i => i + offset);

/**
 * @brief 予約情報の整形
 * @param[in] configs 設定情報一覧
 * 
 * @return    reservations 整形結果
 */
const rebuildCells = (configs) => {
    // 先頭列と末尾列を除く行のうち、空行に該当する行を削除
    const cells = configs.filter((arr) => arr.slice(1, -1).filter((val) => val !== '').length > 0);
    // 新規項目となるインデックスを取得
    const len = cells.length;
    const indices = _zip(cells, _genArray(len)).filter(([arr, _]) => arr[0]).map(([_, idx]) => idx).concat([len]);
    const startEndPairs = _zip(indices.slice(0, -1), indices.slice(1));
    const reservations = [];

    for (const [startIdx, endIdx] of startEndPairs) {
      const targets = cells.slice(startIdx, endIdx);
      // query, optionを取得(query:B列、option:C列)
      const [query, option] = targets.filter((arr) => arr[0])[0].slice(1, 3);
      // 設定情報と取得パターンのペアを取得(設定情報:D列、取得パターン:E列)
      const _pairs = targets.map((arr) => arr.slice(3, 5));
      reservations.push({
        query: query,
        option: (option) ? option : 'PALE_BLUE', // 指定しない場合は「PALE_BLUE」とする
        patterns: _pairs,
      });
    }

    return reservations;
};

ルール単位でまとめる際は、GoogleスプレッドシートのA列で指定したチェックボックスの設定状態を参照します。

今回は、下記のように色を付けた範囲をひとまとまりのルールとして扱いたいため、チェックボックスでチェックしたセル位置に基づいて抜き出す範囲を算出します。

今回のルールの扱い方

これを実現する際は、下記のようにチェックボックスの設定状態とインデックスの情報をセットで扱うことで実現できますよね。

Googleスプレッドシートの行位置チェックボックスの設定状態A行を配列とみなしたときの添え字備考
2行目true01つ目のルール
3行目false1
4行目false2
5行目false3
6行目false4
7行目false5
8行目true62つ目のルール
9行目false7
10行目false8
11行目false9
12行目false10
13行目false11

後は、trueとなっている箇所を取り出し、該当する添え字(3列目)の情報を抜き出すことで、取り出す箇所を特定できます。

これを実現しているのが、以下の処理になります。

/* 該当箇所の取り出し */
_zip(cells, _genArray(len))      // 上記の表の左から2列目と左から3列目を持つ2次元配列を作る
  .filter(([arr, _]) => arr[0])  // 表の左から2列目のうち、trueとなっている箇所をフィルタリング
  .map(([_, idx]) => idx)        // フィルタリング後の配列に対し、表の左から3列目の情報を取得
  .concat([len]);                // 最後のルールを取り出すために、cellsの最大要素数を設定

後は、ルール単位で設定情報をまとめていきます。(22行目~33行目)

検索クエリに合致するメール一覧を取得する処理

これは前回と同様のため、解説は省略します。

const _logFormatter = (logs, prefix='', name='', value='') => logs.push([prefix, name, value]);

/**
 * @brief 検索クエリに合致するメール一覧を取得
 * @param[in] query 検索クエリ
 * @param[in] logs 実行ログ
 * @param[in] debug デバッグモード(true: オン、false: オフ)
 * 
 * @return    results 該当メッセージ一覧(mapオブジェクト)
 */
const getGmailMessages = (query, logs, debug=true) => {
  // スレッドの取得
  const threads = GmailApp.search(query);
  // スレッドからメッセージの中身を取得
  const messagesForThreads = GmailApp.getMessagesForThreads(threads);
  // メッセージ一覧
  const results = {};
  const callback = debug ? ((msg) => _logFormatter(logs, '', msg.getSubject())) : ((msg) => null);

  if (debug) {
    _logFormatter(logs, query, '取得したメッセージ(件名)');
  }

  for (const thread of messagesForThreads) {
    for (const message of thread) {
      // メッセージを取得
      results[message.getId()] = message;
      callback(message);
    }
  }

  return results;
};

注意点として、_logFormatterは以降でも利用します。

パターンに基づいて探索を行う処理

今回は前回と異なり、対象のメールごとに取得パターンを適用していくため、引数にメール本体を渡しています。

また、Googleスプレッドシートで指定された要素に対してプログラム上で紐づけを行うため、「予定追加時の設定情報」を利用しています。(30行目)

先ほど「プルダウンリストの内容は変更しないこと」と述べた理由は、30行目の処理のためです。

/**
 * @brief パターンに基づく探索
 * @param[in] message 対象メッセージ
 * @param[in] config 探索対象の設定情報
 * @param[in] logs 実行ログ
 * @param[in] debug デバッグモード(true: オン、false: オフ)
 * 
 * @return    result 探索結果
 */
const searchMessage = (message, config, logs, debug=false) => {
  const collected = {
    title: [],       // タイトル
    location: [],    // 場所
    startTime: [],   // 開始時刻
    endTime: [],     // 終了時刻
    startDate: [],   // 開始日
    endDate: [],     // 終了日
    description: [], // 説明
  };
  const body = message.getPlainBody();
  const subject = message.getSubject();
  const validKeys = Object.keys(collected);
  if (debug) {
    _logFormatter(logs, 'subject', subject);
  }

  // パターンに基づく探索
  for (const [calendarConfig, collectionPattern] of config.patterns) {
    const re = new RegExp(collectionPattern);
    const name = calendarConfig.match(/\(([a-zA-Z]+)\)/)[1];
    // 本文からパターンに合致する情報を抽出
    const matched = body.match(re, 'g');
    // 該当するデータの有無をチェック
    if (matched && validKeys.includes(name)) {
      const output = matched.slice(1);
      collected[name].push(...output);

      if (debug) {
        const out = output.join(',');
        _logFormatter(logs, '', 'name', name);
        _logFormatter(logs, '', 'matched', out);
      }
    }
  }
  if (debug) {
    for (const key of validKeys) {
      const out = collected[key].join(',');
      _logFormatter(logs, '', key, out);
    }
  }

  // 後処理
  const _warpper = (arr, altVal='') => (arr[0] ? arr[0] : altVal); // 複数登録の情報が登録された場合、最初の情報を採用する
  const result = {
    title: _warpper(collected.title, altVal=subject), // タイトルの指定がない場合は「件名」をセットする
    location: _warpper(collected.location),
    startTime: _warpper(collected.startTime),
    endTime: _warpper(collected.endTime),
    startDate: _warpper(collected.startDate),
    endDate: _warpper(collected.endDate),
    description: collected.description.join('\n'), // descriptionが複数ある場合、改行で連結した上でデータをセットする
    option: config.option,
    message: message, // 該当メッセージを紐づける
  };

  return result;
};

他の部分はこれまで通り、正規表現を用いてメールの本文から該当する情報を抽出しています。

ここで、今回は簡単のため、複数の抽出条件が指定された場合の処理として以下のように対応しています。

対象プログラム上での名称処理内容
タイトルtitle・最初に登録した要素を採用する
・指定されない場合はメールの件名を設定する
場所location・最初に登録した要素を採用する
・指定されない場合は、空文字を設定する
開始時刻startTime・最初に登録した要素を採用する
・指定されない場合は、空文字を設定する
終了時刻endTime・最初に登録した要素を採用する
・指定されない場合は、空文字を設定する
開始日startDate・最初に登録した要素を採用する
・指定されない場合は、空文字を設定する
終了日endDate・最初に登録した要素を採用する
・指定されない場合は、空文字を設定する
概要description・設定された要素すべてを改行で連結する
・指定されない場合は、空文字を設定する
複数の抽出条件が指定された時の振る舞い

ルール単位の処理(上記処理の組み合わせ)

これまでの処理を組み合わせて、Googleスプレッドシートで与えられたルールごとに処理を行います。

/**
 * @brief ルールごとの処理
 * @param[in] configs 設定情報一覧
 * @param[in] logs 実行ログ
 * @param[in] debug デバッグモード(true: オン、false: オフ)
 * 
 * @return    results 集計結果
 */
const process = (configs, logs, debug=false) => {
  const collections = [];
  // 予約情報ごとに2次元配列を生成
  const reservations = rebuildCells(configs);

  // 要素ごとに処理
  for (const target of reservations) {
    // メッセージ一覧を取得
    const messages = getGmailMessages(target.query, logs, debug);
    // メッセージごとに探索
    for (const key of Object.keys(messages)) {
      const collected = searchMessage(messages[key], target, logs, debug);
      collections.push(collected);
    }
  }
  // 後処理:開始日かつ終了日が未指定→無効データとして処理(該当する要素を削除)
  const callback = (item) => ((item.startDate !== '') || (item.endDate !== ''));
  const results = collections.filter(callback);
  collections.filter((item) => !callback(item)).map((item) => {
    _logFormatter(logs, '[Invalid Rule]', `Title:${item.title}`, `Option:${item.option}`);
  });

  return results;
};

ここで、開始日と終了日の両方が指定されていない場合、Googleカレンダーに予定を追加することができません。

このため、開始日かつ終了日が未指定の場合は無効データとして処理しています。(25行目)

メイン処理(Googleカレンダーへの追加を含む)

今回、メイン処理として呼び出す関数はexecutionWrapperという名称にしました。

const _judgeProcessTime = (baseDate, threshold=300) => {
  const current = new Date();
  const diff = Math.floor((current.getTime() - baseDate.getTime()) / 1000.0 / 60.0 / 60.0);
  const ret = (diff >= threshold);

  return ret;
};

function executionWrapper() {
  const summarySheetName = 'サマリ';
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  // 該当シートの取得
  const sheet = spreadsheet.getSheetByName(summarySheetName);
  const values = sheet.getRange('B2:B5').getValues().flat();
  const configSheetName = values[0];
  const markType = values[1];
  const debug = values[2];
  const startRowIdx = Number(values[3]);
  // UIの設定
  const ui = SpreadsheetApp.getUi();
  const result = ui.alert(
    '確認ダイアログ',
    [
      '下記を対象に処理を実行します。', 
      `【対象シート】${configSheetName}`, 
      `【既読/未読の切り替え】${markType ? '既読' : '未読'}`, 
      `【デバッグモード】${debug}`, 
      'よろしいですか?'
    ].join('\n'),
    ui.ButtonSet.YES_NO,
  );
  // ユーザの判断結果に基づく処理
  if (result === ui.Button.YES) {
    // 古い出力結果の削除
    const lastRow = sheet.getLastRow();
    if (startRowIdx <= lastRow) {
      sheet.getRange(startRowIdx, 1, lastRow - startRowIdx + 1, sheet.getLastColumn()).clearContent();
    }
    // =============
    // メインルーチン
    // =============
    const logs = searchMessageAndAddCalendar(spreadsheet, configSheetName, markType, debug);
    // ログを出力
    if (logs && logs[0]) {
      sheet.getRange(startRowIdx, 1, logs.length, logs[0].length).setValues(logs);
    }
  }
}

/**
 * @brief GmailとGoogleカレンダーの連携
 * @param[in] spreadsheet Google SpreadSheetのインスタンス
 * @param[in] sheetName 設定情報が記載されたシート名
 * @param[in] markType 既読/未読の切り替え(true:既読、false:未読)
 * @param[in] debug デバッグモード(true: オン、false: オフ)
 * 
 * @return    logs 実行ログ
 */
const searchMessageAndAddCalendar = (spreadsheet, sheetName, markType, debug) => {
  // 計測開始
  const currentTime = new Date();
  // ログ情報
  const logs = [];

  // 設定情報一覧を取得
  const configs = getConfigFromSpreadSheet(spreadsheet, sheetName);
  // メール検索処理を実行
  const outputs = process(configs, logs, debug);
  // 処理時間の確認
  if (_judgeProcessTime(currentTime, 330)) {
    _logFormatter(logs, '[Warning]', 'Processing may be interrupted due to too many applicable messages.');
  }

  // =====================
  // Google Calendarに追加
  // =====================
  calendar = CalendarApp.getDefaultCalendar();
  // 日付の情報を付与
  const _setupDate = (date, time) => {
    const targetDate = date.replace(/[^0-9]+/g, '/').replace(/(.+)\/$/, '$1');
    const targetTime = time.replace(/(\d+)[^0-9]+(\d+)[^0-9]*/g, '$1:$2');
    const ret = (targetTime) ? new Date(`${targetDate} ${targetTime}`) : new Date(targetDate);

    return ret;
  };

  for (const item of outputs) {
    const color = CalendarApp.EventColor[item.option];
    const startDate = (item.startDate) ? _setupDate(item.startDate, item.startTime) : _setupDate(item.endDate, item.endTime);
    const endDate   = (item.endDate)   ? _setupDate(item.endDate, item.endTime)     : _setupDate(item.startDate, item.startTime);
    const diffMinutes = Math.floor((endDate.getTime() - startDate.getTime()) / 1000.0 / 60.0);
    const hrfStartDate = Utilities.formatDate(startDate, 'JST', 'yyyy/MM/dd HH:mm'); // human Readable Format of startDate
    const hrfEndDate   = Utilities.formatDate(endDate, 'JST', 'yyyy/MM/dd HH:mm');   // human Readable Format of endDate

    if (debug) {
      _logFormatter(logs, 'title', item.title);
      _logFormatter(logs, '', 'color', `${item.option}(${color})`);
      _logFormatter(logs, '', 'location', item.location);
      _logFormatter(logs, '', 'startDate', hrfStartDate);
      _logFormatter(logs, '', 'endDate', hrfEndDate);
      _logFormatter(logs, '', 'diffMinutes', diffMinutes);
    }
    else {
      try {
        // 登録済みのイベントの有無チェック
        const registeredEvents = CalendarApp.getEvents(startDate, endDate);
        let exists = false;

        if (registeredEvents) {
          for (const event of registeredEvents) {
            const targetTitlte = event.getTitle();
            // 同じタイトルのイベントが登録済みの場合
            if (targetTitlte === item.title) {
              exists = true;
              break;
            }
          }
        }
        if (!exists) {
          // 分単位で差分がある場合
          if (diffMinutes > 0) {
            // 開始日時~終了日時に跨る個別イベントとして登録
            const event = calendar.createEvent(item.title, startDate, endDate, {
              description: item.description,
              location: item.location,
            });
            event.setColor(color);
            _logFormatter(logs, '[Success]', `Register event "${item.title}" from ${hrfStartDate} to ${hrfEndDate}`);
          }
          // 分単位で差分がないもしくは、終了日時が開始日時よりも前に設定されている場合
          else {
            // 開始日における終日イベントとして登録
            const event = calendar.createAllDayEvent(item.title, startDate, {
              description: item.description,
              location: item.location,
            });
            event.setColor(color);
            _logFormatter(logs, '[Success]', `Register all day event "${item.title}" on ${hrfStartDate}`);
          }
        }
        else {
          _logFormatter(logs, '[Info]', `Skip registration because same title("${item.title}") exists.`);
        }
      }
      catch (err) {
        _logFormatter(logs, '[Error]', err);
      }
      // 既読と未読の切り替え
      if (markType) {
        item.message.markRead();
      }
      else {
        item.message.markUnread();
      }
    }
    // 処理時間の確認
    if (_judgeProcessTime(currentTime)) {
      _logFormatter(logs, '[Warning]', 'Processing is interrupted because the processing limit is approaching.');
      break;
    }
  }

  return logs;
};

さらに、開始日時と終了日時の差分を考慮して、イベントの登録方法(終日のイベント or 個別イベント)を振り分けています。

また、前回の変化点として、処理時間の条件を設けるようにしています。

これは、GAS上で実行できる最長時間が6分までと決まっているためです。

プログラム一式

これまでに紹介したGASのプログラムをまとめると以下のようになります。(一部、関数の順番を入れ替えています。)

function executionWrapper() {
  const summarySheetName = 'サマリ';
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  // 該当シートの取得
  const sheet = spreadsheet.getSheetByName(summarySheetName);
  const values = sheet.getRange('B2:B5').getValues().flat();
  const configSheetName = values[0];
  const markType = values[1];
  const debug = values[2];
  const startRowIdx = Number(values[3]);
  // UIの設定
  const ui = SpreadsheetApp.getUi();
  const result = ui.alert(
    '確認ダイアログ',
    [
      '下記を対象に処理を実行します。', 
      `【対象シート】${configSheetName}`, 
      `【既読/未読の切り替え】${markType ? '既読' : '未読'}`, 
      `【デバッグモード】${debug}`, 
      'よろしいですか?'
    ].join('\n'),
    ui.ButtonSet.YES_NO,
  );
  // ユーザの判断結果に基づく処理
  if (result === ui.Button.YES) {
    // 古い出力結果の削除
    const lastRow = sheet.getLastRow();
    if (startRowIdx <= lastRow) {
      sheet.getRange(startRowIdx, 1, lastRow - startRowIdx + 1, sheet.getLastColumn()).clearContent();
    }
    // =============
    // メインルーチン
    // =============
    const logs = searchMessageAndAddCalendar(spreadsheet, configSheetName, markType, debug);
    // ログを出力
    if (logs && logs[0]) {
      sheet.getRange(startRowIdx, 1, logs.length, logs[0].length).setValues(logs);
    }
  }
}

/**
 * @brief GmailとGoogleカレンダーの連携
 * @param[in] spreadsheet Google SpreadSheetのインスタンス
 * @param[in] sheetName 設定情報が記載されたシート名
 * @param[in] markType 既読/未読の切り替え(true:既読、false:未読)
 * @param[in] debug デバッグモード(true: オン、false: オフ)
 * 
 * @return    logs 実行ログ
 */
const searchMessageAndAddCalendar = (spreadsheet, sheetName, markType, debug) => {
  // 計測開始
  const currentTime = new Date();
  // ログ情報
  const logs = [];

  // 設定情報一覧を取得
  const configs = getConfigFromSpreadSheet(spreadsheet, sheetName);
  // メール検索処理を実行
  const outputs = process(configs, logs, debug);
  // 処理時間の確認
  if (_judgeProcessTime(currentTime, 330)) {
    _logFormatter(logs, '[Warning]', 'Processing may be interrupted due to too many applicable messages.');
  }

  // =====================
  // Google Calendarに追加
  // =====================
  calendar = CalendarApp.getDefaultCalendar();
  // 日付の情報を付与
  const _setupDate = (date, time) => {
    const targetDate = date.replace(/[^0-9]+/g, '/').replace(/(.+)\/$/, '$1');
    const targetTime = time.replace(/(\d+)[^0-9]+(\d+)[^0-9]*/g, '$1:$2');
    const ret = (targetTime) ? new Date(`${targetDate} ${targetTime}`) : new Date(targetDate);

    return ret;
  };

  for (const item of outputs) {
    const color = CalendarApp.EventColor[item.option];
    const startDate = (item.startDate) ? _setupDate(item.startDate, item.startTime) : _setupDate(item.endDate, item.endTime);
    const endDate   = (item.endDate)   ? _setupDate(item.endDate, item.endTime)     : _setupDate(item.startDate, item.startTime);
    const diffMinutes = Math.floor((endDate.getTime() - startDate.getTime()) / 1000.0 / 60.0);
    const hrfStartDate = Utilities.formatDate(startDate, 'JST', 'yyyy/MM/dd HH:mm'); // human Readable Format of startDate
    const hrfEndDate   = Utilities.formatDate(endDate, 'JST', 'yyyy/MM/dd HH:mm');   // human Readable Format of endDate

    if (debug) {
      _logFormatter(logs, 'title', item.title);
      _logFormatter(logs, '', 'color', `${item.option}(${color})`);
      _logFormatter(logs, '', 'location', item.location);
      _logFormatter(logs, '', 'startDate', hrfStartDate);
      _logFormatter(logs, '', 'endDate', hrfEndDate);
      _logFormatter(logs, '', 'diffMinutes', diffMinutes);
    }
    else {
      try {
        // 登録済みのイベントの有無チェック
        const registeredEvents = CalendarApp.getEvents(startDate, endDate);
        let exists = false;

        if (registeredEvents) {
          for (const event of registeredEvents) {
            const targetTitlte = event.getTitle();
            // 同じタイトルのイベントが登録済みの場合
            if (targetTitlte === item.title) {
              exists = true;
              break;
            }
          }
        }
        if (!exists) {
          // 分単位で差分がある場合
          if (diffMinutes > 0) {
            // 開始日時~終了日時に跨る個別イベントとして登録
            const event = calendar.createEvent(item.title, startDate, endDate, {
              description: item.description,
              location: item.location,
            });
            event.setColor(color);
            _logFormatter(logs, '[Success]', `Register event "${item.title}" from ${hrfStartDate} to ${hrfEndDate}`);
          }
          // 分単位で差分がないもしくは、終了日時が開始日時よりも前に設定されている場合
          else {
            // 開始日における終日イベントとして登録
            const event = calendar.createAllDayEvent(item.title, startDate, {
              description: item.description,
              location: item.location,
            });
            event.setColor(color);
            _logFormatter(logs, '[Success]', `Register all day event "${item.title}" on ${hrfStartDate}`);
          }
        }
        else {
          _logFormatter(logs, '[Info]', `Skip registration because same title("${item.title}") exists.`);
        }
      }
      catch (err) {
        _logFormatter(logs, '[Error]', err);
      }
      // 既読と未読の切り替え
      if (markType) {
        item.message.markRead();
      }
      else {
        item.message.markUnread();
      }
    }
    // 処理時間の確認
    if (_judgeProcessTime(currentTime)) {
      _logFormatter(logs, '[Warning]', 'Processing is interrupted because the processing limit is approaching.');
      break;
    }
  }

  return logs;
};



// utils
const _judgeProcessTime = (baseDate, threshold=300) => {
  const current = new Date();
  const diff = Math.floor((current.getTime() - baseDate.getTime()) / 1000.0 / 60.0 / 60.0);
  const ret = (diff >= threshold);

  return ret;
};
const _zip = (...arrays) => {
  const length = Math.min(...(arrays.map(arr => arr.length)));
  return new Array(length).fill().map((_, i) => arrays.map(arr => arr[i]));
};
const _genArray = (len, offset=0) => [...Array(len).keys()].map(i => i + offset);
const _logFormatter = (logs, prefix='', name='', value='') => logs.push([prefix, name, value]);

/**
 * @brief Google SpreadSheetから設定情報を取得
 * @param[in] spreadsheet Google SpreadSheetのインスタンス
 * @param[in] sheetName 設定情報が記載されたシート名
 * 
 * @return    results 設定情報一覧
 */
const getConfigFromSpreadSheet = (spreadsheet, sheetName) => {
  // スプレッドシートから該当するシートを取得
  const sheet = spreadsheet.getSheetByName(sheetName);
  // データ取得
  const results = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).getValues();

  return results;
};

/**
 * @brief ルールごとの処理
 * @param[in] configs 設定情報一覧
 * @param[in] logs 実行ログ
 * @param[in] debug デバッグモード(true: オン、false: オフ)
 * 
 * @return    results 集計結果
 */
const process = (configs, logs, debug=false) => {
  const collections = [];
  // 予約情報ごとに2次元配列を生成
  const reservations = rebuildCells(configs);

  // 要素ごとに処理
  for (const target of reservations) {
    // メッセージ一覧を取得
    const messages = getGmailMessages(target.query, logs, debug);
    // メッセージごとに探索
    for (const key of Object.keys(messages)) {
      const collected = searchMessage(messages[key], target, logs, debug);
      collections.push(collected);
    }
  }
  // 後処理:開始日かつ終了日が未指定→無効データとして処理(該当する要素を削除)
  const callback = (item) => ((item.startDate !== '') || (item.endDate !== ''));
  const results = collections.filter(callback);
  collections.filter((item) => !callback(item)).map((item) => {
    _logFormatter(logs, '[Invalid Rule]', `Title:${item.title}`, `Option:${item.option}`);
  });

  return results;
};

/**
 * @brief 予約情報の整形
 * @param[in] configs 設定情報一覧
 * 
 * @return    reservations 整形結果
 */
const rebuildCells = (configs) => {
    // 先頭列と末尾列を除く行のうち、空行に該当する行を削除
    const cells = configs.filter((arr) => arr.slice(1, -1).filter((val) => val !== '').length > 0);
    // 新規項目となるインデックスを取得
    const len = cells.length;
    const indices = _zip(cells, _genArray(len)).filter(([arr, _]) => arr[0]).map(([_, idx]) => idx).concat([len]);
    const startEndPairs = _zip(indices.slice(0, -1), indices.slice(1));
    const reservations = [];

    for (const [startIdx, endIdx] of startEndPairs) {
      const targets = cells.slice(startIdx, endIdx);
      // query, optionを取得(query:B列、option:C列)
      const [query, option] = targets.filter((arr) => arr[0])[0].slice(1, 3);
      // 設定情報と取得パターンのペアを取得(設定情報:D列、取得パターン:E列)
      const _pairs = targets.map((arr) => arr.slice(3, 5));
      reservations.push({
        query: query,
        option: (option) ? option : 'PALE_BLUE', // 指定しない場合は「PALE_BLUE」とする
        patterns: _pairs,
      });
    }

    return reservations;
};

/**
 * @brief 検索クエリに合致するメール一覧を取得
 * @param[in] query 検索クエリ
 * @param[in] logs 実行ログ
 * @param[in] debug デバッグモード(true: オン、false: オフ)
 * 
 * @return    results 該当メッセージ一覧(mapオブジェクト)
 */
const getGmailMessages = (query, logs, debug=true) => {
  // スレッドの取得
  const threads = GmailApp.search(query);
  // スレッドからメッセージの中身を取得
  const messagesForThreads = GmailApp.getMessagesForThreads(threads);
  // メッセージ一覧
  const results = {};
  const callback = debug ? ((msg) => _logFormatter(logs, '', msg.getSubject())) : ((msg) => null);

  if (debug) {
    _logFormatter(logs, query, '取得したメッセージ(件名)');
  }

  for (const thread of messagesForThreads) {
    for (const message of thread) {
      // メッセージを取得
      results[message.getId()] = message;
      callback(message);
    }
  }

  return results;
};

/**
 * @brief パターンに基づく探索
 * @param[in] message 対象メッセージ
 * @param[in] config 探索対象の設定情報
 * @param[in] logs 実行ログ
 * @param[in] debug デバッグモード(true: オン、false: オフ)
 * 
 * @return    result 探索結果
 */
const searchMessage = (message, config, logs, debug=false) => {
  const collected = {
    title: [],       // タイトル
    location: [],    // 場所
    startTime: [],   // 開始時刻
    endTime: [],     // 終了時刻
    startDate: [],   // 開始日
    endDate: [],     // 終了日
    description: [], // 説明
  };
  const body = message.getPlainBody();
  const subject = message.getSubject();
  const validKeys = Object.keys(collected);
  if (debug) {
    _logFormatter(logs, 'subject', subject);
  }

  // パターンに基づく探索
  for (const [calendarConfig, collectionPattern] of config.patterns) {
    const re = new RegExp(collectionPattern);
    const name = calendarConfig.match(/\(([a-zA-Z]+)\)/)[1];
    // 本文からパターンに合致する情報を抽出
    const matched = body.match(re, 'g');
    // 該当するデータの有無をチェック
    if (matched && validKeys.includes(name)) {
      const output = matched.slice(1);
      collected[name].push(...output);

      if (debug) {
        const out = output.join(',');
        _logFormatter(logs, '', 'name', name);
        _logFormatter(logs, '', 'matched', out);
      }
    }
  }
  if (debug) {
    for (const key of validKeys) {
      const out = collected[key].join(',');
      _logFormatter(logs, '', key, out);
    }
  }

  // 後処理
  const _warpper = (arr, altVal='') => (arr[0] ? arr[0] : altVal); // 複数登録の情報が登録された場合、最初の情報を採用する
  const result = {
    title: _warpper(collected.title, altVal=subject), // タイトルの指定がない場合は「件名」をセットする
    location: _warpper(collected.location),
    startTime: _warpper(collected.startTime),
    endTime: _warpper(collected.endTime),
    startDate: _warpper(collected.startDate),
    endDate: _warpper(collected.endDate),
    description: collected.description.join('\n'), // descriptionが複数ある場合、改行で連結した上でデータをセットする
    option: config.option,
    message: message, // 該当メッセージを紐づける
  };

  return result;
};

具体的な使い方の例

最後に、具体例を2つ用意したので、それぞれ簡単に解説します。

準備

手間を省くために、Googleスプレッドシートのサンプルを用意しました。

https://docs.google.com/spreadsheets/d/1icUkEbbWfEGePqvuUMRby0gqKkptw02MFgKpUiL5gAo/edit?usp=drive_link

Googleスプレッドシートのコピー

以下の手順でGoogleスプレッドシートをコピーしてください。

Googleスプレッドシートのコピー①
Googleスプレッドシートのコピー②

権限の付与

次にGoogleスプレッドシートでGASを動かすために、以下の手順でアクセス権を付与します。


  • step.1:実行ボタンの押下

    「サマリ」シートの右上にある「実行」を押下してください。

    GAS:アクセス権の付与


  • step.2:権限の確認&アカウント選択

    以下のような画面が表示されるため、「OK」を押下します。

    権限の付与②

    その後、アカウント選択画面が表示されるため、権限を与えたいアカウントを選んでください。

    権限の付与③


  • step.3:アクセス権の付与

    警告画面が表示されたら、左下の「詳細」を押下した上で、「無題のプロジェクト(安全ではないページ)に移動」を押下します。

    権の付与④

    権の付与⑤

    最後に表示される画面にて、Gmail、Googleスプレッドシート、Googleカレンダーが選択肢に挙がっていることを確認します。

    その上で、右下の「許可」を押下してください。

    権限の付与⑥


以上で準備は完了です。

【参考】実行時の挙動

実行時は、「サマリ」シートで「実行」を押下すればOKです。

誤動作を避けるため、下記のメッセージが表示されるようになっています。

内容を確認した上で、問題なければ「OK」を押下してください。

実行時の確認ダイアログ

事例①:ホテル予約をGoogleカレンダーに反映

現状、次の3つのメールを受信しているとします。

■件名
【サンプル①】Aホテル 予約確認

■本文
-----------------------------------------------------------------------
※本メールは、自動的に配信しています。
こちらのメールは送信専用のため、直接ご返信いただいてもお問い合わせには
お答えできませんので、あらかじめご了承ください。
-----------------------------------------------------------------------

予約受付日時:2022年1月23日10:17

予約番号:0123ABCD
宿泊代表者氏名:事例 示し太郎 様
宿名:Aホテル東京
電話番号:00-0000-0000
所在地:〒100-2100 東京都小笠原村沖ノ鳥島1番地
チェックイン日時:2022年1月31日18:00
宿泊日数:1泊
部屋タイプ:【禁煙】おまかせタイプ
部屋数:1室

▼宿泊時のトラブルについて
宿泊時に発生したトラブルは宿泊施設へお申し出ください。
■件名
【サンプル②】Bホテル 予約確認

■本文
-----------------------------------------------------------------------
※本メールは、自動的に配信しています。
こちらのメールは送信専用のため、直接ご返信いただいてもお問い合わせには
お答えできませんので、あらかじめご了承ください。
-----------------------------------------------------------------------

予約受付日時:2022年1月24日11:11

予約番号:1234ABCD
宿泊代表者氏名:事例 示し太郎 様
宿名:Bホテル東京
電話番号:00-0000-0000
所在地:〒100-2100 東京都小笠原村沖ノ鳥島2番地
チェックイン日時:2022年2月2日(水)18:00
チェックアウト日:2022年2月4日(金)
部屋タイプ:【禁煙】適当タイプ
部屋数:1室

▼宿泊時のトラブルについて
宿泊時に発生したトラブルは宿泊施設へお申し出ください。
■件名
【サンプル③】Aホテル 予約確認

■本文
-----------------------------------------------------------------------
※本メールは、自動的に配信しています。
こちらのメールは送信専用のため、直接ご返信いただいてもお問い合わせには
お答えできませんので、あらかじめご了承ください。
-----------------------------------------------------------------------

予約受付日時:2022年1月25日17:02

予約番号:0134BCDE
宿泊代表者氏名:事例 示し太郎 様
宿名:Aホテル東京
電話番号:00-0000-0000
所在地:〒100-2100 東京都小笠原村沖ノ鳥島1番地
チェックイン日時:2022年2月9日18:00
宿泊日数:2泊
部屋タイプ:【禁煙】でまかせタイプ
部屋数:1室

▼宿泊時のトラブルについて
宿泊時に発生したトラブルは宿泊施設へお申し出ください

これらに対して、次の2つのルールを設定することにします。

設定するルール2つ
対象該当するメールルールの内容備考
Aホテルサンプル①

・予定の色は「PALE_BLUE」とする。

・下記を本文から抽出する。

 【タイトル】宿名

 【場所】所在地

 【開始時刻、開始日】チェックイン日時

・宿泊日数と部屋タイプを抽出し、予定の「説明」に設定する。

-
サンプル③2泊になっているが、終日の予定として登録する。
Bホテルサンプル②

・予定の色は「PALE_GREEN」とする。

・下記を本文から抽出する。

 【タイトル】宿名

 【場所】所在地

 【開始時刻、開始日】チェックイン日時

 【終了日】チェックアウト日

・部屋タイプを抽出し、予定の「説明」に設定する。

-

以降では、それぞれのルールの設定方法について説明します。

Aホテルに対するルール定義

まず、検索クエリとオプションは以下を設定すればよいですよね。

対象設定内容
検索クエリis:unread subject:"Aホテル"
オプションPALE_BLUE

続いて正規表現ですが、メールの各行を調べていき決まった文字列が表示された以降に対して抽出処理をすれば良いことが分かります。

例えば「タイトル」であれば「宿名:***」となっている「***」部分を取り出せば良いですよね。

このようなケースでは、正規表現の「肯定後読み」を利用しましょう!

【肯定後読み】(?<=パターン)の形式で表される、正規表現の表現方法の一つ

今回のケースでは、以下のような設定を行えばOKです。

予定追加時の設定情報取得パターン
タイトル(title)(?<=宿名:)(.*?)\r\n
場所(location)(?<=所在地:)(.*?)\r\n
開始日(startDate)(?<=チェックイン日時:)\s*(\d+年\d+月\d+日).*?\r\n
開始時刻(startTime)(?<=チェックイン日時:).*?(\d+:\d+)\r\n
説明(description)(宿泊日数:.*?)\r\n
説明(description)(?<=部屋タイプ:)(.*?)\r\n
【Aホテル】正規表現の設定例

Bホテルに対するルール定義

こちらもAホテルと同様に設定していきます。

まず、検索クエリとオプションは以下を設定すればよいですよね。

対象設定内容
検索クエリis:unread subject:"Bホテル"
オプションPALE_GREEN

次に正規表現も「終了日(endDate)」が増えるだけになるため、以下のような設定を行えばOKです。

予定追加時の設定情報取得パターン
タイトル(title)(?<=宿名:)(.*?)\r\n
場所(location)(?<=所在地:)(.*?)\r\n
開始日(startDate)(?<=チェックイン日時:)\s*(\d+年\d+月\d+日).*?\r\n
開始時刻(startTime)(?<=チェックイン日時:).*?(\d+:\d+)\r\n
終了日(endDate)(?<=チェックアウト日:)\s*(\d+年\d+月\d+日).*?\r\n
説明(description)(?<=部屋タイプ:)(.*?)\r\n
【Bホテル】正規表現の設定例

GASの実行結果

実際にGASを実行すると次のような結果が得られます。

適用事例:ホテルの予約情報

事例②:会議予約をGoogleカレンダーに反映

続いて、次の2つのメールを受信しているとします。

■件名
【サンプル④】XXX会議 予約詳細

■本文
-----------------------------------------------------------------------
※本メールは、システムにより自動的に配信しています。
-----------------------------------------------------------------------

下記の日程で打ち合わせを実施します。
お忙しいところ恐縮ですが、参加の程よろしくお願いいたします。

会議名:ホテル予約システムの更新
日程:2022年2月1日
時間:13:00~15:00

以上です。よろしくお願いいたします。
■件名
【サンプル⑤】決済システムの更新

■本文
-----------------------------------------------------------------------
※本メールは、システムにより自動的に配信しています。
-----------------------------------------------------------------------

下記の日程で打ち合わせを実施します。
お忙しいところ恐縮ですが、参加の程よろしくお願いいたします。

日時:2022年2月1日(火)15:30~16:00

以上です。よろしくお願いいたします。

これらに対して、次の2つのルールを設定することにします。

対象該当するメールルールの内容備考
XXX会議サンプル④・予定の色は「MAUVE」とする。
・下記を本文から抽出する。
 【タイトル】会議名
 【開始日、終了日】日程
 【開始時刻、終了時刻】時間
-
決済システムの更新サンプル⑤・予定の色は「MAUVE」とする。
・タイトルはメールの件名を採用する、
・下記を本文から抽出する。
 【開始日、開始時刻、終了日、終了時刻】日時
-

以降では、それぞれのルールの設定方法について説明します。

XXX会議に対するルール定義

まず、検索クエリとオプションは以下を設定すればよいですよね。

対象設定内容
検索クエリis:unread subject:"XXX会議"
オプションMAUVE

続いて、正規表現もホテルの時と同様に、以下のような設定を行えばOKです。

予定追加時の設定情報取得パターン
タイトル(title)(?<=会議名:)(.*?)\r\n
開始日(startDate)(?<=日程:)\s*(\d+年\d+月\d+日).*?\r\n
開始時刻(startTime)(?<=時間:)\s*(\d+:\d+)~.*?\r\n
終了日(endDate)(?<=日程:)\s*(\d+年\d+月\d+日).*?\r\n
終了時刻(endTime)(?<=時間:).*?~(\d+:\d+).*?\r\n
【XXX会議】正規表現の設定例

決済システムの更新に対するルール定義

こちらもXXX会議と同様に設定します。

まず、検索クエリとオプションは以下を設定すればよいですよね。

対象設定内容
検索クエリis:unread subject:"決済システムの更新"
オプションMAUVE

次に正規表現ですが、タイトルはメールの件名を採用するため指定しません

それ以外は、XXX会議と同様でよいため、以下のような設定を行えばOKです。

予定追加時の設定情報取得パターン
開始日(startDate)(?<=日時:)\s*(\d+年\d+月\d+日).*?\r\n
開始時刻(startTime)(?<=日時:).*?(\d+:\d+)~.*?\r\n
終了日(endDate)(?<=日時:)\s*(\d+年\d+月\d+日).*?\r\n
終了時刻(endTime)(?<=日時:).*?~(\d+:\d+).*?\r\n
【決済システムの更新】正規表現の設定例

GASの実行結果

実際にGASを実行すると次のような結果が得られます。(連続で実行したため、ホテルの予定が残っています)

適用事例:会議の予約情報

まとめ

今回は、Gmail、Googleスプレッドシート、Googleカレンダーを組み合わせて、「特定のメールに対し、Googleカレンダーの予定を設定する際の情報を抽出して、予定を登録する」という処理を自動化する方法について紹介しました。

正規表現の書き方に依存しますが、Googleカレンダーの予定を設定する上で、ある程度汎用的なものが出来上がったと考えています。

また、細かな振る舞いや入力パターンは、サンプルのGoogleスプレッドシートの別シートに記載しているので、あわせてご覧ください。

詳細情報

https://docs.google.com/spreadsheets/d/1icUkEbbWfEGePqvuUMRby0gqKkptw02MFgKpUiL5gAo/edit?usp=drive_link

シート:【解説】シートの概要説明、【参考】入力パターンの洗い出し

スポンサードリンク

-プログラミング
-, ,