タイムズカーの予約状況をGmailからGoogleカレンダーに反映させる

タイムズカーで予約した時間をカレンダーで表示できるようにします。

今回の記事ではGASの詳しい使い方は説明しないので、こちらの記事の「GASの設定」を参照してください。

タイムズカーの特徴
システムの構成
プログラム全体

タイムズカーの特徴

時間が重なっていなければ、複数の予約をすることが可能である。
予約開始日時前であれば、予約内容を変更したり、予約を取消したりできる。
新規予約、予約内容の変更、取消、返却後にメールが届く。23/06/26時点では、それぞれ以下の件名でメールが届く。
- 「【Times CAR】予約登録完了」：予約後
- 「【Times CAR】予約確認」　　：予約開始時間の約10分前
- 「【Times CAR】予約変更完了」：予約内容の変更後
- 「【Times CAR】予約取消完了」：予約の取消後
- 「【Times CAR】返却証」　　　：使用終了後

システムの構成

前提として、Times CAR のメールがGmailに届くことが必要です。(他のメールから転送してもOKです)

タイムズカーの予約メールがGmailに届く
GASを使ってメールを解析し、情報を抽出する
カレンダーに予定を反映する
スプレッドシートに情報を反映する
時間変更・キャンセルした場合には情報を更新する
返却後には、実際に使用した時間をカレンダーに反映する

システムで対応すべき事項

重複しないようにGmailの内容を取得する(解析するのは1回だけでよい)
予約時間が変更される可能性がある
予約は取消される可能性がある
予約は同時に複数可能であり、予約変更・取消がどの予約に対応しているか判別する必要がある

重複しないようにメールを取得する

GASはメールが届いたことをトリガーにしてプログラムを実行することはできないので、代わりに時間ベースのトリガーを指定しておく。このとき既に解析済のメールかどうか判定する必要がある。これには、メールにラベルをつける、スターをつけるなどの方法もあるが、今回はメールIDをスプレッドシートに記録しておく手法を採用する。

以下のプログラムを「予約登録完了」「予約確認」「予約変更完了」「予約取消完了」「返却証」のメールで行い、すべてのメールをスプレッドシートで記録できるようにする。スプレッドシートには「Mail」という名前のシートを用意し、1行目に [ メールID, タイムスタンプ, 件名 ] と見出しを付けている。

const searchDay = "2d"

function yoyakuProcess() {
  //メールの件名と日付から解析対象のメールを取得
  const threads = GmailApp.search('subject:(【Times CAR】xxx)　AND newer_than:'+searchDay)
  threads.reverse().forEach(thread => {
    const messages = thread.getMessages();
    messages.forEach(message => {
      //メールIDを取得
      const mailId = message.getId()
      if(isNewMailId(mailId)){
　　　　//解析済のメールでない場合
        ~~(必要な情報を抽出する処理)~~
        processMail(message)
      }
    })
  })
}

//メールIDから解析済でないかどうかスプレッドシートから判定する
function isNewMailId(id){
  //シート名は「Mail」で1行目に[メールID,タイムスタンプ,件名]の見出しあり
  const sheet = sheetId("Mail")
  //1行目は見出しなので、2行目以降の1列目のデータを取得
  const mailIdList = sheet.getRange(2,1,sheet.getLastRow(),1).getValues().flat()
  return !mailIdList.includes(id)
}
//メール情報(メールID,受信日付,件名)をスプレッドシートに追加する
function processMail(message){
  sheetId("Mail").appendRow([message.getId(),message.getDate(), message.getSubject()])
}

function sheetId(sheetName){
  return SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName)
}

メールから必要な情報を抽出する

メールの内容を正規表現を使って抽出する。「予約番号」はすべてのメールから抽出し、予約登録・予約変更の場合には「ステーション」「車両」「利用開始日時」「返却予定日時」を、返却の場合には「利用開始日時」「返却日時」を抽出する。

const body = message.getPlainBody()
const yoyakuId = body.match(/■予約番号\s*(\d+)/)[1]

//以下は予約登録完了・予約変更完了の場合のみ
const station = body.match(/■ステーション\s*((?:\S|\s)*?)■/)[1]
const car = body.match(/■車両\s*((?:\S|\s)*?)■/)[1]
const startDate = body.match(/■利用開始日時\s*(\d{4}\/\d{2}\/\d{2}\s\d{2}:\d{2})(?:\S|\s)*?■/)[1]
const returnDate = body.match(/■返却予定日時\s*(\d{4}\/\d{2}\/\d{2}\s\d{2}:\d{2})(?:\S|\s)*?■/)[1]

//以下は返却証の場合のみ
const duringDate = body.match(/■利用時間\s*(\d{4}\/\d{2}\/\d{2}\s\d{2}:\d{2}) - (\d{4}\/\d{2}\/\d{2}\s\d{2}:\d{2})(?:\S|\s)*?■/)
const startDate = duringDate[1]  //利用開始日時
const returnDate = duringDate[2] //返却日時

~~(カレンダーの追加・更新)~~
~~(スプレッドシートの追加・更新)~~

カレンダーに予定を追加・更新する

カレンダーには、利用開始日時・返却予定日時(返却後は利用開始日時・返却日時)と、メールの本文を記載しておきます。予約登録の場合は予定を作成し、予約変更・返却の場合は予定を更新、予約取消の場合は予定を削除する。

またどの予約情報(に対応するイベント)を更新するか判別するために、メールに記載されている予約番号からイベントIDを取得できる関数も用意している。

カレンダーIDは以下の記事を参考にしてください。

//予定を作成
function setCalendar(startDate ,returnDate ,desc){
  return calendarId().createEvent(
    'Times CAR',          //イベントタイトル
    new Date(startDate),  //イベント開始日時
    new Date(returnDate), //イベント終了日時
    {description: desc}   //イベントの説明
  );
}

//イベントIDを指定して、予定の詳細を変更(開始終了時間、説明)
function editInfoCalendar(calcId, startDate, returnDate, desc){
  const startDateTime = new Date(startDate)
  let returnDateTime= new Date(returnDate)
  if(startDateTime > returnDateTime){
    //返却時間が予約開始時間より早い場合
    returnDateTime = startDateTime
  }
  const date = calendarId().getEventById(calcId).setTime(
    startDateTime,
    returnDateTime
  );
  const description = calendarId().getEventById(calcId).setDescription(desc);
  return {date:date, desc:description};
}

//イベントIDを指定して、予定を削除
function deleteCalendar(calcId){
  calendarId().getEventById(calcId).deleteEvent();
}

function calendarId(){
  //カレンダーIDを指定しておく
  return CalendarApp.getCalendarById("xxxxxxxxxxxx@group.calendar.google.com")
}

//予約番号→カレンダーIDを取得
function getCalId(yoyakuId){
  const row = yoyakuIdRowNum(yoyakuId);
  if(row===0) return 0;
  return sheetId("Main").getRange(row,7).getValue();
}

//予約番号に対応する行番号を返す(ない場合は0)
function yoyakuIdRowNum(yoyakuId){
  const sheet = sheetId("Main")
  const yoyakuIdList = sheet.getRange(2,2,sheet.getLastRow()-1,1).getValues().flat()
  for(var i=0;i<yoyakuIdList.length+1;i++){
    if(yoyakuIdList[i] === Number(yoyakuId)){
      return i+2;
    }
  }
  return 0;
}

シートに情報をまとめる

いつどこでどの車両を予約・使用したか一覧で見れるようにシートに情報をまとめる。スプレッドシートには「Main」という名前のシートを用意し、1行目に [ ステータス, 予約番号, ステーション, 車両, 利用時間, 返却時間, イベントID ] と見出しを付けている。

カレンダーと同様に、予約登録の場合はシートに行を追加し、予約変更・返却の場合は該当する行を更新する。

またステータスには「予約」「使用」「返却」「取消」の４つのステータスを用意し、予約した車両がどのようなステータスにあるか一目でわかるようにする。

//予約情報を追加
function yoyakuSheet(yoyakuId,station,car,startDate,returnDate,calId){
  sheetId("Main").appendRow(["予約",yoyakuId,station,car,startDate,returnDate,calId])
}

//予約情報を更新(車両・開始時間・返却時間)
function updateYoyakuSheet(yoyakuId, car, startDate, returnDate){
  const row = yoyakuIdRowNum(yoyakuId)
  if(row===0){
    //予約IDが存在しない場合はエラー
    Logger.log("Error: yoyakuId is not exist (%s)",yoyakuId)
  }else{
    //予約IDに対応する情報を更新する
    sheetId("Main").getRange(row,4).setValue(car)
    sheetId("Main").getRange(row,5).setValue(startDate)
    sheetId("Main").getRange(row,6).setValue(returnDate)
  }
}

//ステータスを更新
function updateYoyakuStatusSheet(yoyakuId, status){
  const row = yoyakuIdRowNum(yoyakuId)
  if(row===0){
    //予約IDが存在しない場合はエラー
    Logger.log("Error: yoyakuId is not exist (%s)",yoyakuId)
  }else{
    sheetId("Main").getRange(row,1).setValue(status)
  }
}

//予約番号に対応する行番号を返す(ない場合は0)
function yoyakuIdRowNum(yoyakuId){
  const sheet = sheetId("Main")
  const yoyakuIdList = sheet.getRange(2,2,sheet.getLastRow()-1,1).getValues().flat()
  for(var i=0;i<yoyakuIdList.length+1;i++){
    if(yoyakuIdList[i] === Number(yoyakuId)){
      return i+2;
    }
  }
  return 0;
}

構成まとめ

これらのプログラムはメールの種類ごとに操作が異なります。表にまとめると下のようになります。

メール件名	メール取得	情報抽出	カレンダー操作	ステータス更新
予約登録完了	◯	予約番号・ステーション・車両・利用開始日時・返却予定日時	作成	予約
予約確認	◯	予約番号		使用
予約変更完了	◯	予約番号・ステーション・車両・利用開始日時・返却予定日時	更新
予約取消完了	◯	予約番号	削除	取消
返却証	◯	予約番号・利用開始日時・返却日時	更新	返却

最後にGASの実行トリガーとして、時間ベースのトリガーを5分間隔で設定しておくことで、メールが来て5分以内にはスプレッドシートとカレンダーに情報が更新されるようになります。

プログラム全体

const searchDay = "2d"

function Main(){
  yoyakuProcess()
  yoyakuKakuninProcess()
  yoyakuChangeProcess()
  yoyakuCancelProcess()
  returnProcess()
}

function yoyakuProcess() {
  const threads = GmailApp.search('subject:(【Times CAR】予約登録完了)　AND newer_than:'+searchDay)
  threads.reverse().forEach(thread => {
    const messages = thread.getMessages();
    messages.forEach(message => {
      const mailId = message.getId()
      if(isNewMailId(mailId)){
        const body = message.getPlainBody()
        const yoyakuId = body.match(/■予約番号\s*(\d+)/)[1]
        const station = body.match(/■ステーション\s*((?:\S|\s)*?)■/)[1]
        const car = body.match(/■車両\s*((?:\S|\s)*?)■/)[1]
        const startDate = body.match(/■利用開始日時\s*(\d{4}\/\d{2}\/\d{2}\s\d{2}:\d{2})(?:\S|\s)*?■/)[1]
        const returnDate = body.match(/■返却予定日時\s*(\d{4}\/\d{2}\/\d{2}\s\d{2}:\d{2})(?:\S|\s)*?■/)[1]
        Logger.log("予約 %s",yoyakuId)
        const calId = setCalendar(startDate,returnDate,body).getId()
        yoyakuSheet(yoyakuId,station,car,startDate,returnDate, calId)
        processMail(message)
      }
    })
  })
}

function yoyakuKakuninProcess() {
  const threads = GmailApp.search('subject:(【Times CAR】予約確認)　AND newer_than:'+searchDay)
  threads.reverse().forEach(thread => {
    const messages = thread.getMessages();
    messages.forEach(message => {
      const mailId = message.getId()
      if(isNewMailId(mailId)){
        const body = message.getPlainBody()
        const yoyakuId = body.match(/■予約番号\s*(\d+)/)[1]
        Logger.log("確認 %s",yoyakuId)
        updateYoyakuStatusSheet(yoyakuId,"使用")
        processMail(message)
      }
    })
  })
}

function yoyakuChangeProcess() {
  const threads = GmailApp.search('subject:(【Times CAR】予約変更完了)　AND newer_than:'+searchDay)
  threads.reverse().forEach(thread => {
    const messages = thread.getMessages();
    messages.forEach(message => {
      const mailId = message.getId()
      if(isNewMailId(mailId)){
        const body = message.getPlainBody()
        const yoyakuId = body.match(/■予約番号\s*(\d+)/)[1]
        const station = body.match(/■ステーション\s*((?:\S|\s)*?)■/)[1]
        const car = body.match(/■車両\s*((?:\S|\s)*?)■/)[1]
        const startDate = body.match(/■利用開始日時\s*(\d{4}\/\d{2}\/\d{2}\s\d{2}:\d{2})(?:\S|\s)*?■/)[1]
        const returnDate = body.match(/■返却予定日時\s*(\d{4}\/\d{2}\/\d{2}\s\d{2}:\d{2})(?:\S|\s)*?■/)[1]
        Logger.log("変更 %s",yoyakuId)
        updateYoyakuSheet(yoyakuId,car,startDate,returnDate)
        const calId = getCalId(yoyakuId)
        if(calId!==0){
          editInfoCalendar(calId,startDate,returnDate, body)
          processMail(message)
        }else{
          const calId = setCalendar(startDate,returnDate,body).getId()
          yoyakuSheet(yoyakuId,station,car,startDate,returnDate, calId)
          processMail(message) 
        }
      }
    })
  })
}

function yoyakuCancelProcess() {
  const threads = GmailApp.search('subject:(【Times CAR】予約取消完了)　AND newer_than:'+searchDay)
  threads.reverse().forEach(thread => {
    const messages = thread.getMessages();
    messages.forEach(message => {
      const mailId = message.getId()
      if(isNewMailId(mailId)){
        const body = message.getPlainBody()
        const yoyakuId = body.match(/■予約番号\s*(\d+)/)[1]
        Logger.log("取消 %s",yoyakuId)
        updateYoyakuStatusSheet(yoyakuId,"取消")
        const calId = getCalId(yoyakuId)
        if(calId!==0){
          deleteCalendar(calId)
        }
        processMail(message)
      }
    })
  })
}

function returnProcess() {
  const threads = GmailApp.search('subject:(【Times CAR】返却証)　AND newer_than:'+searchDay)
  threads.reverse().forEach(thread => {
    const messages = thread.getMessages();
    messages.forEach(message => {
      const mailId = message.getId()
      if(isNewMailId(mailId)){
        const body = message.getPlainBody()
        const yoyakuId = body.match(/■予約番号\s*(\d+)/)[1]
        const car = body.match(/■車両\s*((?:\S|\s)*?)■/)[1]
        const duringDate = body.match(/■利用時間\s*(\d{4}\/\d{2}\/\d{2}\s\d{2}:\d{2}) - (\d{4}\/\d{2}\/\d{2}\s\d{2}:\d{2})(?:\S|\s)*?■/)
        const startDate = duringDate[1]
        const returnDate = duringDate[2]
        Logger.log("返却 %s",yoyakuId)
        updateYoyakuStatusSheet(yoyakuId,"返却")
        updateYoyakuSheet(yoyakuId,car,startDate,returnDate)
        const calId = getCalId(yoyakuId)
        if(calId!==0){
          editInfoCalendar(calId,startDate,returnDate, body)
        }
        processMail(message)
      }
    })
  })
}



////メール管理シート
//メールIDから解析済でないかどうか判定する
function isNewMailId(id){
  const sheet = sheetId("Mail")
  const mailIdList = sheet.getRange(2,1,sheet.getLastRow(),1).getValues().flat()
  return !mailIdList.includes(id)
}

//メール情報(メールID,受信日付,件名)をスプレッドシートに追加
function processMail(message){
  sheetId("Mail").appendRow([message.getId(),message.getDate(), message.getSubject()])
}


////メインシート
//予約情報を追加
function yoyakuSheet(yoyakuId,station,car,startDate,returnDate,calId){
  sheetId("Main").appendRow(["予約",yoyakuId,station,car,startDate,returnDate,calId])
}

//予約情報を更新(車両・開始時間・返却時間)
function updateYoyakuSheet(yoyakuId, car, startDate, returnDate){
  const row = yoyakuIdRowNum(yoyakuId)
  if(row===0){
    Logger.log("Error: yoyakuId is not exist (%s)",yoyakuId)
  }else{
    sheetId("Main").getRange(row,4).setValue(car)
    sheetId("Main").getRange(row,5).setValue(startDate)
    sheetId("Main").getRange(row,6).setValue(returnDate)
  }
}

//ステータスを更新
function updateYoyakuStatusSheet(yoyakuId, status){
  const row = yoyakuIdRowNum(yoyakuId)
  if(row===0){
    Logger.log("Error: yoyakuId is not exist (%s)",yoyakuId)
  }else{
    sheetId("Main").getRange(row,1).setValue(status)
  }
}

//予約番号に対応する行番号を返す(ない場合は0)
function yoyakuIdRowNum(yoyakuId){
  const sheet = sheetId("Main")
  const yoyakuIdList = sheet.getRange(2,2,sheet.getLastRow()-1,1).getValues().flat()
  for(var i=0;i<yoyakuIdList.length+1;i++){
    if(yoyakuIdList[i] === Number(yoyakuId)){
      return i+2;
    }
  }
  return 0;
}

function sheetId(sheetName){
  return SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName)
}



////カレンダー
//予定を作成
function setCalendar(startDate,returnDate,desc){
  return calendarId().createEvent(
    'Times CAR',
    new Date(startDate),
    new Date(returnDate),
    {description: desc}
  );
}

//予定の詳細を変更
function editInfoCalendar(calcId, startDate, returnDate, desc){
  const startDateTime = new Date(startDate)
  let returnDateTime= new Date(returnDate)
  if(startDateTime > returnDateTime){
    returnDateTime = startDateTime
  }
  const date = calendarId().getEventById(calcId).setTime(
    startDateTime,
    returnDateTime
  );
  const description = calendarId().getEventById(calcId).setDescription(desc);
  return {date:date, desc:description};
}

//予定を削除
function deleteCalendar(calcId){
  calendarId().getEventById(calcId).deleteEvent();
}

function calendarId(){
  return CalendarApp.getCalendarById("xxxxxxxx@group.calendar.google.com")
}

//予約番号→カレンダーIDを取得
function getCalId(yoyakuId){
  const row = yoyakuIdRowNum(yoyakuId);
  if(row===0) return 0;
  return sheetId("Main").getRange(row,7).getValue();
}