Googleカレンダー

タイムズカーで予約した時間をカレンダーで表示できるようにします。 今回の記事ではGASの詳しい使い方は説明しないので、こちらの記事の「GASの設定」を参照してください。 https://tech.minagu.work/archives/13 タイムズカーの特徴 時間が重なっていなければ、複数の予約をすることが可能である。 予約開始日時前であれば、予約内容を変更したり、予約を取消したりできる。 新規予約、予約内容の変更、取消、返却後にメールが届く。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 = &quot;2d&quot; function yoyakuProcess() { //メールの件名と日付から解析対象のメールを取得 const threads = GmailApp.search('subject:(【Times CAR】xxx) AND newer_than:'+searchDay) threads.reverse().forEach(thread =&gt; { const messages = thread.getMessages(); messages.forEach(message =&gt; { //メールIDを取得 const mailId = message.getId() if(isNewMailId(mailId)){ //解析済のメールでない場合 ~~(必要な情報を抽出する処理)~~ processMail(message) } }) }) } //メールIDから解析済でないかどうかスプレッドシートから判定する function isNewMailId(id){ //シート名は「Mail」で1行目に[メールID,タイムスタンプ,件名]の見出しあり const sheet = sheetId(&quot;Mail&quot;) //1行目は見出しなので、2行目以降の1列目のデータを取得 const mailIdList = sheet.getRange(2,1,sheet.getLastRow(),1).getValues().flat() return !mailIdList.includes(id) } //メール情報(メールID,受信日付,件名)をスプレッドシートに追加する function processMail(message){ sheetId(&quot;Mail&quot;).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は以下の記事を参考にしてください。 https://blog-and-destroy.com/41932 //予定を作成 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 &gt; 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(&quot;xxxxxxxxxxxx@group.calendar.google.com&quot;) } //予約番号→カレンダーIDを取得 function getCalId(yoyakuId){ const row = yoyakuIdRowNum(yoyakuId); if(row===0) return 0; return sheetId(&quot;Main&quot;).getRange(row,7).getValue(); } //予約番号に対応する行番号を返す(ない場合は0) function yoyakuIdRowNum(yoyakuId){ const sheet = sheetId(&quot;Main&quot;) const yoyakuIdList = sheet.getRange(2,2,sheet.getLastRow()-1,1).getValues().flat() for(var i=0;i&lt;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(&quot;Main&quot;).appendRow([&quot;予約&quot;,yoyakuId,station,car,startDate,returnDate,calId]) } //予約情報を更新(車両・開始時間・返却時間) function updateYoyakuSheet(yoyakuId, car, startDate, returnDate){ const row = yoyakuIdRowNum(yoyakuId) if(row===0){ //予約IDが存在しない場合はエラー Logger.log(&quot;Error: yoyakuId is not exist (%s)&quot;,yoyakuId) }else{ //予約IDに対応する情報を更新する sheetId(&quot;Main&quot;).getRange(row,4).setValue(car) sheetId(&quot;Main&quot;).getRange(row,5).setValue(startDate) sheetId(&quot;Main&quot;).getRange(row,6).setValue(returnDate) } } //ステータスを更新 function updateYoyakuStatusSheet(yoyakuId, status){ const row = yoyakuIdRowNum(yoyakuId) if(row===0){ //予約IDが存在しない場合はエラー Logger.log(&quot;Error: yoyakuId is not exist (%s)&quot;,yoyakuId) }else{ sheetId(&quot;Main&quot;).getRange(row,1).setValue(status) } } //予約番号に対応する行番号を返す(ない場合は0) function yoyakuIdRowNum(yoyakuId){ const sheet = sheetId(&quot;Main&quot;) const yoyakuIdList = sheet.getRange(2,2,sheet.getLastRow()-1,1).getValues().flat() for(var i=0;i&lt;yoyakuIdList.length+1;i++){ if(yoyakuIdList[i] === Number(yoyakuId)){ return i+2; } } return 0; } 構成まとめ これらのプログラムはメールの種類ごとに操作が異なります。表にまとめると下のようになります。 メール件名メール取得情報抽出カレンダー操作ステータス更新予約登録完了◯予約番号・ステーション・車両・利用開始日時・返却予定日時作成予約予約確認◯予約番号使用予約変更完了◯予約番号・ステーション・車両・利用開始日時・返却予定日時更新予約取消完了◯予約番号削除取消返却証◯予約番号・利用開始日時・返却日時更新返却 最後にGASの実行トリガーとして、時間ベースのトリガーを5分間隔で設定しておくことで、メールが来て5分以内にはスプレッドシートとカレンダーに情報が更新されるようになります。 プログラム全体 const searchDay = &quot;2d&quot; function Main(){ yoyakuProcess() yoyakuKakuninProcess() yoyakuChangeProcess() yoyakuCancelProcess() returnProcess() } function yoyakuProcess() { const threads = GmailApp.search('subject:(【Times CAR】予約登録完了) AND newer_than:'+searchDay) threads.reverse().forEach(thread =&gt; { const messages = thread.getMessages(); messages.forEach(message =&gt; { 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(&quot;予約 %s&quot;,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 =&gt; { const messages = thread.getMessages(); messages.forEach(message =&gt; { const mailId = message.getId() if(isNewMailId(mailId)){ const body = message.getPlainBody() const yoyakuId = body.match(/■予約番号\s*(\d+)/)[1] Logger.log(&quot;確認 %s&quot;,yoyakuId) updateYoyakuStatusSheet(yoyakuId,&quot;使用&quot;) processMail(message) } }) }) } function yoyakuChangeProcess() { const threads = GmailApp.search('subject:(【Times CAR】予約変更完了) AND newer_than:'+searchDay) threads.reverse().forEach(thread =&gt; { const messages = thread.getMessages(); messages.forEach(message =&gt; { 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(&quot;変更 %s&quot;,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 =&gt; { const messages = thread.getMessages(); messages.forEach(message =&gt; { const mailId = message.getId() if(isNewMailId(mailId)){ const body = message.getPlainBody() const yoyakuId = body.match(/■予約番号\s*(\d+)/)[1] Logger.log(&quot;取消 %s&quot;,yoyakuId) updateYoyakuStatusSheet(yoyakuId,&quot;取消&quot;) 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 =&gt; { const messages = thread.getMessages(); messages.forEach(message =&gt; { 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(&quot;返却 %s&quot;,yoyakuId) updateYoyakuStatusSheet(yoyakuId,&quot;返却&quot;) 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(&quot;Mail&quot;) const mailIdList = sheet.getRange(2,1,sheet.getLastRow(),1).getValues().flat() return !mailIdList.includes(id) } //メール情報(メールID,受信日付,件名)をスプレッドシートに追加 function processMail(message){ sheetId(&quot;Mail&quot;).appendRow([message.getId(),message.getDate(), message.getSubject()]) } ////メインシート //予約情報を追加 function yoyakuSheet(yoyakuId,station,car,startDate,returnDate,calId){ sheetId(&quot;Main&quot;).appendRow([&quot;予約&quot;,yoyakuId,station,car,startDate,returnDate,calId]) } //予約情報を更新(車両・開始時間・返却時間) function updateYoyakuSheet(yoyakuId, car, startDate, returnDate){ const row = yoyakuIdRowNum(yoyakuId) if(row===0){ Logger.log(&quot;Error: yoyakuId is not exist (%s)&quot;,yoyakuId) }else{ sheetId(&quot;Main&quot;).getRange(row,4).setValue(car) sheetId(&quot;Main&quot;).getRange(row,5).setValue(startDate) sheetId(&quot;Main&quot;).getRange(row,6).setValue(returnDate) } } //ステータスを更新 function updateYoyakuStatusSheet(yoyakuId, status){ const row = yoyakuIdRowNum(yoyakuId) if(row===0){ Logger.log(&quot;Error: yoyakuId is not exist (%s)&quot;,yoyakuId) }else{ sheetId(&quot;Main&quot;).getRange(row,1).setValue(status) } } //予約番号に対応する行番号を返す(ない場合は0) function yoyakuIdRowNum(yoyakuId){ const sheet = sheetId(&quot;Main&quot;) const yoyakuIdList = sheet.getRange(2,2,sheet.getLastRow()-1,1).getValues().flat() for(var i=0;i&lt;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 &gt; 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(&quot;xxxxxxxx@group.calendar.google.com&quot;) } //予約番号→カレンダーIDを取得 function getCalId(yoyakuId){ const row = yoyakuIdRowNum(yoyakuId); if(row===0) return 0; return sheetId(&quot;Main&quot;).getRange(row,7).getValue(); }

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