LoginSignup
5
1

More than 3 years have passed since last update.

@himeflute

Feelcycleの予約メールから履歴表を作ってGoogleカレンダーに登録して、キャンセルしたら消し込むとこまで自動化してみた

Posted at

Feelcycleの予約メールから履歴表を作ってGoogleカレンダーに登録して、キャンセルしたら消し込むとこまで自動化してみた

ところでfeelcycleってなに？

feelcycleは「暗闇バイクエクササイズ」というやつで、エアロバイクがたくさん並んでいる暗い部屋でクラブみたいな照明と音楽の中、インストラクターさんの指示に従ってひたすらノリノリで(？)自転車をこぎまくるジムです。
強度や曲の異なる1時間ごとのスケジュール枠を予約してレッスンを受けます。
何でこんなに楽しいのか、入会後1か月経たないうちにこれを作成したぐらい気に入って通っています。

こんなことに困っていた

  • 予約するのはいいが今まで何を受けたか覚えていられないから履歴表がほしい
  • 予定表登録しないと忘れる
  • ほかの予定が入っちゃったからキャンセルして別の受けよっと、、とかが結構あるから履歴表も予定表も管理が面倒
  • 今日予約したバイクナンバー何番だっけ、、プッシュ通知で出したいのに・・とかもやもやする

自動でできるようにしたこと

  • Gmailに届いた予約メールをリスト化(MailDataシート)、さらにGoogleカレンダーに予定登録、そのときにEventIDを取得(予定削除に必要)
  • キャンセルしたときも削除情報を記録(MailDataシート)、さらにGoogleカレンダーで該当の予定を削除
  • 記録として登録・キャンセル両方をMailDataシートに追記型で残す
  • 受講したレッスンの集計表を生成(Listシート) ※毎度上書きで生成

これができるということはさらに・・・

  • スマホのカレンダーアプリでGoogleカレンダー読ませてプッシュ通知出せますよね
  • 集計表をもとに自分でグラフ作ったりcountifで推しIRさんの受講回数カウントしたりできますよね
  • 集計表は条件付き書式で色分けするとfeel感出ますよね
  • iPhoneの「ショートカット」アプリでの自動化と組み合わせるとさらに便利になりますよね

しかし

私はプログラミングのできないエンジニアです。バッチファイルやPowerShellは書きますが。
なので、最後に記載させていただいた記事の内容ほとんどを流用させていただいたうえで機能追加をさせていただきました。

早速やってみよう

前提条件・事前準備

  • Googleアカウントを用意してください
    • Gmailではfeelcycleってラベルを用意してフィルタ機能でfrom:(feelcycle.com OR feelcycle-mail@mr.fm)を指定してラベル付けしておいてください
    • Googleカレンダーでもfeelcycleって名前のカレンダーを追加作成しておいてください
    • GoogleSpreadSheetは「feelcycle」とかの名前でGoogleドライブに作成してください。(カレンダーのIDとか入れるから他者と共有しないこと！)
  • feelcycleの登録メールアドレスがGmailであることを確認してください。(ただしプロバイダのメアドとか別の使ってるならpopでGmail上で取得すれば行けるはずだけど、取得間隔大きいのでタイムラグ多いはず)
  • GoogleSpreadSheetで「List」と「MailData」というシートを作成
  • メール検索重くないかなと心配して「feelcycle」というラベルを付けたGmailを検索させたけど、ラベル付けない場合は抽出条件のとこでlabel:feelcycleを削除すればよいですね
  • GmailやSpreadSheet、GoogleCalendarへの認証は確か初回実行時に聞かれるので認証してください。

SpreadSheet「feelcycle」のシート準備

こんな感じで1行目のタイトル行を作っておいてください。色は適当にどうぞ。
「List」シート
image.png
「MailData」シート
image.png

GoogleAppsScriptの記載と自分の情報の登録

  1. 「PCで」SpreadSheetを開き、[ツール]>[スクリプトエディタ]を開きます
  2. 次に記載するコードをコピペして「GetMailData.gs」と「AddCal.gs」をそれぞれ作成し、登録します
  3. 「じぶんのやつに置き換えてね」って書いたとこは書き換えてください※

※SpreadSheet URLとは
いまつくった「feelcycle」を開いたときのURLを見てください・・・

※カレンダーID確認方法
1. Googleカレンダーの設定画面を開く
2. 作成しておいたfeelcycleカレンダーが左に表示されていると思うのでクリック
3. 「カレンダーの統合」項目内にカレンダーIDが記載されています

GetMailData.gs Gmailから拾って登録・キャンセル記録と履歴表作成

GetMailData.gs
function mailToSheet () {

// ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
// 設定系
  // メールから取得した情報を書き出すスプレッドシートのURL
  var sheet_url = 'https://docs.google.com/spreadsheets/d/自分のやつに置き換えてね/edit';
  // 書き込むシート名
  var sheet_name = 'MailData';
  // Gmailの抽出条件 feelcycleラベル付きで、件名が予約かキャンセルのもの
  var mail_query = 'label:feelcycle Subject:ご予約登録||ご予約キャンセル' ;

// ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
// Googleスプレッドシートの該当シートを開く
  var ss = SpreadsheetApp.openByUrl(sheet_url);
  var sheet = ss.getSheetByName(sheet_name);

// ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
// 存在チェックフェーズ(既にシートに書き込み済のメールは処理しない(日付、件名の一致で判定))
  var existence_check = true;
  var existence_keys = fetchExistenceKeys();
  var mail_data = fetchMailData();
  for (var i = 0; item = mail_data[i]; i++) {
    // 既存データと合致したら重複登録せずにループから抜ける
    if (existence_check && existsData(item)) {
      continue;
    }
    // 重複なければ最終行に値を追加
    sheet.appendRow(itemToRow(item));
  }
  // 重複チェック用
  function fetchExistenceKeys () {
    var existence_keys = {};
    var sheet_data = sheet.getDataRange().getValues();
    for (var i = 0; row = sheet_data[i]; i++) {
      existence_keys[generateKey(rowToItem(row))] = true;
    }
    return existence_keys;
  }
  function existsData (item) {
    if (existence_keys[generateKey(item)]) {
      return true;
    }
    return false;
  }
  function generateKey (item) {
    var key = item['MailReceived'] + '_' + item['Type'];
    // Logger.log(key);
    return key;
  }

// ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝    
// 私には理解できないフェーズ
  function rowToItem (row) {
    var item = {};
    item['MailReceived'] = row[0];
    item['Type'] = row[1];
    item['Studio'] = row[2];
    item['PGM'] = row[3];
    item['IR'] = row[4];
    item['Bike'] = row[5];
    item['Start'] = row[6];
    item['End'] = row[7];
    return item;
  }

  function itemToRow (item) {
    var row = [];
    row[0] = item['MailReceived'];
    row[1] = item['Type'];
    row[2] = item['Studio'];
    row[3] = item['PGM'];
    row[4] = item['IR'];
    row[5] = item['Bike'];
    row[6] = item['Start'];
    row[7] = item['End'];
    return row;
  }

// ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
// メールから内容持ってきて中身抜き出して成形フェーズ
  function fetchMailData () {
    var result = [];
    var threads = GmailApp.search(mail_query);

    for (var i = 0; it = threads[i]; i++) {
      var messages = it.getMessages();
      for (var j = 0; message = messages[j]; j++) {
        var item = {};
        // メール受信日時と件名の取得 件名は登録かキャンセルのタイプとして利用
        item['MailReceived'] = message.getDate();
        item['Type'] = message.getSubject().replace('FEELCYCLE ご予約',''); 

        // メール本文の取得
        var body = message.getPlainBody();
          //  Logger.log(body)

        // 本文から内容が書いてある部分(日時からバイクナンバーの部分)だけ正規表現で抜粋
        var reg = new RegExp('日時：' + '[\\s\\S]*?' + 'バイクナンバー：'+'.*?' + '\r');
        var contents = body.match(reg)[0].replace('\r', '');
        //Logger.log(contents)

        // contentsサンプル
        //
        // 日時： 2019年12月14日(土)　16:00～16:45　
        // 店舗： 池袋（IKB）　
        // レッスン： BB2 Comp1　
        // インストラクター： XXX
        // バイクナンバー：23

        // 必要な行から余計な文字列を消去
        var bodys= contents.replace('日時： ','').replace('店舗： ','').replace('レッスン： ','').replace('インストラクター： ','').replace('バイクナンバー：','').replace('　','');
        // 改行を使って配列化する
        var ary = bodys.split("\n");

        // 置換
        item['Studio'] = ary[1].substring(0).replace('\r','').replace('<br>','');
        item['PGM'] = ary[2].substring(0).replace('\r','').replace('<br>','');
        item['IR'] = ary[3].substring(0).replace('\r','').replace('<br>','');
        item['Bike'] = ary[4].substring(0).replace('\r','').replace('<br>','');

        // 予約時間の行を取得し日付と時間に分けて配列として格納
        var reservation = ary[0].substring(0).replace('\r','').replace('<br>','').replace('　','').split("～");
        item['Start'] = convertDateFormat(reservation[0].slice(0,reservation[0].indexOf("\("))+reservation[0].slice(-5));
        item['End'] = convertDateFormat(reservation[0].slice(0,reservation[0].indexOf("\("))+reservation[1].slice(0,5));

        result.push(item);
      }
    }
    return result;
  }

// ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝  
// 年月日を書式変換
  function convertDateFormat (date) {
    date = date.replace("年","/").replace("月","/").replace("日"," ");
    var convertedDate = date;
    return convertedDate;
  }
// ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
// レッスン日順に並べ替え(新しいものが下、予約日が新しいものが下)
  sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).activate();
  ss.getActiveRange().offset(1, 0, ss.getActiveRange().getNumRows() - 1).sort([{column: 7, ascending: true}, {column: 1, ascending: true}]);

// ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
// キャンセルしたものはグレーアウトしてキャンセル済みを記載
  for(i = 1; i <= sheet.getLastRow(); i++) {
    // Logger.log(sheet.getRange(i, 2).getValue())
     if(sheet.getRange(i, 2).getValue() == "キャンセル") {
          sheet.getRange(i-1,9,2,2).setValue("キャンセル");
          sheet.getRange(i-1,1,2,11).setBackground("silver");
      }
   }
// ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝  
// 成形した履歴表シートの作成(キャンセルとか省いて予約済み&受けたやつリストを生成)
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A1').activate();

  //MailDataシートで実際に受けたもの(と予約済みのもの)のみにフィルター
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('MailData'), true);
  var criteria = SpreadsheetApp.newFilterCriteria()
  .setHiddenValues(['', 'キャンセル', '削除済み'])
  .build();
  spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(9, criteria);

  //必要なとこだけコピーしてListシートに貼り付け
  spreadsheet.getRange('C:G').activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('List'), true);
  spreadsheet.getRange('MailData!C:G').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('A1').activate();

  //MailDataシートにかけたフィルター解除
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('MailData'), true);
  spreadsheet.getActiveSheet().getFilter().removeColumnFilterCriteria(9);
  spreadsheet.getRange('A1').activate();

};

AddCal.gs 登録・キャンセル記録をもとにGoogleカレンダーで予定登録・削除

AddCal.gs
function createEventFromSheet() {
  // ----- ここから書き換え -----
  // カレンダーIDの指定
  var calendarId = "自分のやつに置き換えてね@group.calendar.google.com";
  // メールから取得した情報をリスト化したスプレッドシートのURL
  var sheet_url = 'https://docs.google.com/spreadsheets/d/自分のやつに置き換えてね/edit';
  // 参照・書き込むシート名
  var sheet_name = 'MailData';
  // ----- ここまで書き換え -----

  var sheet, i, Studio, PGM, IR, Bike, myevent, mystart, myend, added, cancel, dscr;
  // Googleスプレッドシートの該当シートを開く
  var ss = SpreadsheetApp.openByUrl(sheet_url);
  var sheet = ss.getSheetByName(sheet_name);


  for(i = 1; i <= sheet.getLastRow(); i++) {
    // 内容の取得
    Studio = sheet.getRange(i, 3).getValue();
    PGM = sheet.getRange(i, 4).getValue(); 
    IR = sheet.getRange(i, 5).getValue();
    Bike = sheet.getRange(i, 6).getValue();

    // カレンダー登録用に内容を成型
    myevent = PGM;
    mystart = sheet.getRange(i, 7).getValue();
    myend = sheet.getRange(i, 8).getValue();
    added = sheet.getRange(i, 9).getValue();
    cancel = sheet.getRange(i, 10).getValue();
    dscr = "+++Reservation+++\nStudio: "+Studio+"\nPGM: "+PGM+"\nIR: "+IR+"\nBike: "+Bike

    //ログで内容確認用 (デバッグ用)
    //Logger.log(dscr);

    // カレンダー登録 追加済み/削除済み/無断キャンセルではなくキャンセル/無断キャンセルではない予定を追加
    if(added == "" && cancel=="") {
      var thisevent = CalendarApp.getCalendarById(calendarId).createEvent(myevent, new Date(mystart), new Date(myend),{location:Studio+"No."+Bike,description:dscr});
      var eventid = thisevent.getId();
      sheet.getRange(i, 9).setValue("追加済み");
     sheet.getRange(i, 11).setValue(eventid);
    }

    //キャンセルしたらカレンダーからも削除 削除したら削除済みと記載
    if(added =="追加済み" && cancel !==""){
       var delid = sheet.getRange(i, 11).getValue();
       // Logger.log(delid)
       CalendarApp.getCalendarById(calendarId).getEventById(delid).deleteEvent();
       sheet.getRange(i, 9).setValue("削除済み");
    }
}
}

マクロ部分とか絶対もっときれいに書けるはずなのでだれか書き直してください・・・

タスク実行の登録

やり方は書かないけどスクリプトはテスト実行してみてくださいね。その際は適宜カレンダー登録で面倒なことにならないよういろいろ絞ってミニマムで実験することをお勧めします。
それが終わってちゃんと動くことが確認できたらタスク実行の登録をします。
これをやればクラウド上で定期実行されるのでストレスフリーな生活が送れます。

GetMailData.gsに記載した関数用
image.png
AddCal.gsに記載した関数用
image.png

注意事項

  • feelcycleの予約システム側のメール文言が変わったときはデータが成形できなくなります
  • SpecialLessonはイレギュラーなので対応不可ですが、直接MailDataシートの最終行にでも追記すればよいです
  • 無断キャンセルにも対応不可ですが、直接MailDataシートのCancel列に「無断キャンセル」など文字を入れればキャンセル扱いで集計から省きカレンダーから削除してくれます
  • 予約したあとにほかのバイクに変更する時はキャンセルメールが飛ばないでもう一度予約メールが飛ぶので、直接MailDataシートのCancel列に「バイク変更」とか書けば集計とカレンダーからは消してくれます
  • つまり、シートのデータ全消しして再実行したとして、メールさえ残っていれば通常予約と通常キャンセルだけはリスト化可能です。ただしカレンダー登録後に削除しないで再実行すると予定のduplicateは発生するだろうね・・・
  • やり直しとかが必要でカレンダー登録したくないって場合はCalendar列に「追加済み」って書けば新規カレンダー登録はしません。自動実行外してテストしてください。

完成イメージ

ということで載せたいんですけど表のほうはいろいろマスキングしたいのでお待ちください・・・

予定表のデータ
image.png

参考にしたスクリプト

仕組みは下記のサイトの内容をほぼそのまま利用させていただき、キャンセル対応などの機能追加をしました。本当にありがとうございます。
Anycaの予約リクエストをGoogleカレンダーに自動登録する

5
1
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
5
1