自治会館予約システムの構築

自治会DX化の一環として、自治会館の活用を活発にするべく、自治会館の予約をインターネット上からできるようにしよう、と考えた。

自治会館の活用に対する課題と思うところは以下の通り

• 会館利用はどのような条件（費用・時間帯・用途）がよくわからない
  紙で配られるものには書いてあるけど、捨ててしまっている。
• 使用の申込みが紙のみ。
  年に１回配られる使用要綱の裏表紙に２枚程度申込用紙がついていて、それに記載して会長宅や役員の家にもっていく
  　→役員の家どこ？問題

少なくとも私はこの２つのことが障壁と感じていた。

そこで、今回自治会のWebを立ち上げるにあたってWeb空予約したい。と考え色々調べ始めた。

要件はやはり、「無料でできるシステム」。
アドバイザの方からは「りざぶろう」というサービスの紹介を受けたが、その後更に要件を考えたところ、下記の要件が出てきて合致しないため、落選。

よく考えた自治会館予約システムの要件

1. 費用が無料
2. 予約者とメールにて連絡が取れること
3. 予約されたら自治会側に通知が来ること
4. 予約する側もすでに予約が入っている時間帯を認識できること
   すでに予約されている時間帯に申し込みがあった場合はメールにて拒否通知ができること
5. 自治会側が見やすいカレンダー機能があること
6. （発展的に）自治会員以外の人が予約できないこと（規約にあるため）

りざぶろうでは、3の予約されたら自治会側に通知が来る、ということが実現できなかったため、却下とした。

これらの要件で考えていて、まずはGoogle Formsとカレンダーの連携が必要と考え、検索したところ、下記のページがヒットし参考にさせていただいた。
（Yuki_Sahashi様、ありがとうございます）
https://note.com/sahashiyuuki/n/nda4b098641b4

このページでは、

1. GoogleFormからSpreadSheetに自動連携（Google Forms標準機能）
2. SpreadSheetのGoogle Apps Scriptをキック
3. Google カレンダーにAPIでアクセス
4. 空き情報の確認
5. 予約ができたかどうかをメールで申込者に返す。

というシステムが紹介されていた。まさに今回の要件の一部がこれで網羅されている！
ということで、このシステムをカスタマイズして自治会館の予約システムに対応させた。

このフォームに入力されると、これに連動設定したスプレッドシートに内容が記載されます。
このときにGoogle Apps Scriptをキックすることでカレンダーに登録されます。

オリジナルからの変更点として、班の名前、開始、終了時刻の記載を導入。
さらに今は無効に設定しているが、こんな形で班の名前（15,13の部分）、班員の名字（B列）の組み合わせを記載したスプレッドシートを用意して、登録された名前の中にB列が含まれているかどうか、含まれていたら班名と合致するかどうかを比較し、この２つが比較したら会員と判定するようにしていたずらを防止した。

以下、コード。
フォーム返信.gs

function sendThanksForm(e) {
  console.log(e.values);
  let [timestamp, username,han, email, date, startTime, endTime, naiyou,ninzu, bikou] = e.values;
  Logger.log('sendThanksForm');
  Logger.log('timestamp '+ timestamp);
  Logger.log('username ' + username);
  Logger.log('han ' + han);
  Logger.log('email '+ email);
  Logger.log('date '+ date);
  Logger.log('startTime '+ startTime);
  Logger.log('endTime ' + endTime);
  Logger.log('naiyou '+ naiyou);

  //let [timestamp, username,email, date, time, other] = e.values;
  let subject = '【自治会館予約(2F)】仮予約を受け付けました';
  let options = {
    "cc": "CC送付先メールアドレス"
  };
  let body =

`${username}様

以下の内容でご仮予約承りました。
こちらからご連絡差し上げます。
- 班： ${han}
- 日付: ${date}
- 時間 : ${startTime}~${endTime}
- 内容 :${naiyou}
- 人数: ${ninzu}
- 備考: ${bikou}

どうぞよろしくお願いいたします。
キャンセルされる際は、必ず前日までに直接ご連絡ください。
-- 
フォーム送信時間: ${timestamp}`;

  GmailApp.sendEmail(email, subject, body, options);
}

//もしすでに会議室の予約が埋まっている場合（Google Calender）
function sendAlertMail(e) {
  let [timestamp, username,han, email, date, startTime, endTime, naiyou, bikou] = e.values;
  let formUrl = SpreadsheetApp.getActiveSpreadsheet().getFormUrl();
  let subject = '【自治会館予約(2F)】ご予約の時間はすでに予約が埋まっています。';
  let options = {
    "cc": "CC送付先メールアドレス"
  };
  let body =

`${username}様
利用申請ありがとうございます。大変恐れ入りますが、以下の時間はすでに予約が埋まっています。

- 日付: ${date}
- 時間　: ${startTime}~${endTime}
- 内容 :${naiyou}
- 備考: ${bikou}

日程を確認の上、別日程を選択ください。
下記より、再度ご予約いただきますようお願い申し上げます。

▼予約フォーム
${formUrl}
-- 
フォーム送信時間: ${timestamp}`;
  GmailApp.sendEmail(email, subject, body, options);
}

function sendNotAllowedMail(e) {
  let [timestamp, username,han, email, date, startTime, endTime, naiyou, bikou] = e.values;
  let formUrl = SpreadsheetApp.getActiveSpreadsheet().getFormUrl();
  let subject = '【自治会館予約(2F)】予約エラー';
  let options = {
    "cc": "CC送付先メールアドレス"
  };
  let body =

`${username}様
利用申請いただきましたが、ご利用申請可能なのは自治会員様のみとなっております。
もし会員にも関わらずこのメッセージが届いた場合は、予約者氏名に入力した名前と班が自治会に登録した名前と一致しているかご確認ください。
一致しているにも関わらずこのメッセージが届いた場合には、お手数ですがお問い合わせ窓口（お問い合わせフォームへのURL）から
ご連絡ください。

登録いただいた内容：
- 予約者氏名: ${username}
- 班： ${han}
- 日付: ${date}
- 時間　: ${startTime}~${endTime}
- 内容 :${naiyou}
- 備考: ${bikou}
-- 
フォーム送信時間: ${timestamp}`;
  GmailApp.sendEmail(email, subject, body, options);
}

function sendForm(e) {
  if(existuser(e)){
    if(!exist(e)){
      createEvent(e);
      sendThanksForm(e);
    }else{
      sendAlertMail(e);
    }
  }else{
    sendNotAllowedMail(e);
  }
}



function exist(e) {
  let [timestamp, username,han, email, date, startTime, endTime, naiyou, bikou] = e.values;
  //let [timestamp, email, username, date, time, others] = e.values;
  let id = '登録するカレンダーID@group.calendar.google.com';
  let calender =  CalendarApp.getCalendarById(id);

  // let m = Number(date.split('/')[0])- 1; //-1:JavaScriptの月は0から始まるので、1を引く
  // let d = Number(date.split('/')[1]);
  // let y = Number(date.split('/')[2]);


  let y = Number(date.split('/')[0]);
  let m = Number(date.split('/')[1].split('/')[0]) - 1;
  let d = Number(date.split('/')[2]);
  // let hours = Number(time.substring(0, 2));
  // let minutes = Number(time.substring(time.length - 2));
  let hours = Number(startTime.split(':')[0]);
  let minutes = Number(startTime.split(':')[1].split(":")[0]);
  let endHours = Number(endTime.split(':')[0]);
  let endminutes = Number(endTime.split(':')[1].split(":")[0]);

  let startTime2 =new Date(y,m,d,hours,minutes);
  let endTime2 =new Date(y,m,d,endHours,endminutes);
  let events = calender.getEvents(startTime2,endTime2);

  if (events.length >0){
    return true;
  }else{
    return false;
  }
}

function existuser(e){
  return true;  //一旦全部無効
  
  let [timestamp, username,han, email, date, startTime, endTime, naiyou, bikou] = e.values;
  let id = 'スプレッドシートID'; //自治会員の班名、名字が記載されたシートのID。
  var DataSpreadsheet = SpreadsheetApp.openById(id);
  var sheet = DataSpreadsheet.getActiveSheet();
  var Names = sheet.getRange('B1:B1000').getValues(); //名前一覧（ひとまず1000人分）
  var Hans = sheet.getRange('A1:A1000').getValues();   //班（ひとまず1000人分）
  Logger.log('Name = ' + Names);
  Logger.log('Hans = ' + Hans);
  for(let i = 0; i < Names.length; i++){
    if(Names[i] == ''){
      break;
    }
    Logger.log('for... Names =[' + Names[i] + '] username=[' + username + '] Han = ' + Hans[i]);
    if(username.indexOf(Names[i]) >= 0){
      //名前があったので、班をチェック
      Logger.log('name find! Names = ' + Names[i] + 'username = ' + username);
      Logger.log('hans[i] = ' + Hans[i] + ' han = ' + han);
      if(han == Hans[i]){
        Logger.log('existuser return true');
        return true;
      }
    }
  }
  Logger.log('existuser return false');

  return false;
}

カレンダー作成.gs

//はコメントアウトです。
//このfunction createEventは、指定したGoogleカレンダーに予定を書き込む関数です。
function createEvent(e) {
  let [timestamp, username, han, email, date, startTime, endTime, naiyou,ninzu, bikou] = e.values; //この順番は、スプレッドシートの順番
  let id = 'カレンダーID@group.calendar.google.com'; //★カレンダーの統合→GoogleカレンダーのIDを入れる
  let calendar = CalendarApp.getCalendarById(id);
  let title = `${username}様`;

//★ここは注意が必要です。各個人の時刻の設定によっては2024/02/24と入ることもありますし、 2/24/2024と入ることもあります。
//date:2/24/2024 などの mm/dd/yyyyの場合
  let m = Number(date.split('/')[1])- 1; //-1:JavaScriptの月は0から始まるので、1を引く
  let d = Number(date.split('/')[2]);
  let y = Number(date.split('/')[0]);

//date:2024/2/24 などの mm/dd/yyyyの場合
  //let y = Number(date.split('/')[0]);
  //let m = Number(date.split('/')[1].split('/')[0]) - 1; //-1:JavaScriptの月は0から始まるので、1を引く
  //let d = Number(date.split('/')[2]);
  
  // 最初の2文字をhoursに、最後の2文字をminutesにする
  // let hours = Number(time.substring(0, 2));
  // let minutes = Number(time.substring(time.length - 2));

  let hours = Number(startTime.split(':')[0]);
  let minutes = Number(startTime.split(':')[1]);
  let startTime2 = new Date(y, m, d, hours, minutes);
  let endTime2 = new Date(y, m, d, hours, minutes+60);

  //Loggerは、実行したときに出力される。エラーが起きたときに解決が容易
  Logger.log('createEvent ');
  Logger.log('timestamp '+ timestamp);
  Logger.log((han ) + han);
  Logger.log('email '+ email);
  Logger.log('date '+ date);
  Logger.log('hours '+ hours);
  Logger.log('minutes '+ minutes);
  Logger.log('m-1 '+ m);
  Logger.log('d '+ d);
  Logger.log('y '+ y);
  Logger.log('startTime '+ startTime);
  Logger.log('endTime ' + endTime);
  Logger.log('startTime2 '+ startTime2);
  Logger.log('endTime2 ' + endTime2);
  Logger.log('naiyou '+ naiyou);
  Logger.log('ninzu ' + ninzu);

//カレンダーに書き込む詳細
  let description = 
`▼申込内容
予約日時: ${timestamp}
Ｅメール: ${email}
お名前 : ${username}
班 : ${han}
利用日 : ${date}
利用開始時間: ${startTime2}
利用終了時間: ${endTime2}
使用内容:${naiyou}
使用人数:${ninzu}
備考: ${bikou}`;

//calendar.createEventが実際にカレンダーに書き込む
  let options = {
    description: description,
  };
  calendar.createEvent(title, startTime2, endTime2, options);
   Logger.log('calendar.createEvent Success');
}

これらのコードをスプレッドシートの「拡張機能」→「Apps Script」で表示されるところに書き込み、トリガーから下記のように設定。

セキュリティーの警告が出るので、それは無視して実行するように選択（自己判断ください）し、GoogleFormsから入力して動作確認。
うまく動作しなかったらデバッグして完了です。

私の自治会ではまだ会員名簿をクラウドに置くことに関してセキュリティー上懸念されていることから、自治会員からかどうかのチェックは無効にしているので、existuser()関数内で必ずtrueを返すようにしていますが、いずれこのreturn分は消します。

今現在はそれほど自治会館の利用数が多くないので、一旦総務の方が目視チェックする手筈になっています。

長々と書きましたが、これが私の自治会で無料で自治会館予約システムを構築した例になります。
ある程度のプログラミングスキルと、細かい行間を読む能力が必要な内容になってしまいましたが、参考になれば幸いです。

今回始めてGoogle Apps Scriptを触りましたが、もともとのスクリプトがあったおかげで、なんとなく雰囲気を掴んで改造することができました。
Googleのこのあたりの無料サービス、何か他にも活用できそうな予感を感じつつ、いまのとおkろアイディアが思い浮かびません。

自治会関連では、行事の参加、不参加のアンケートなどもガンガンFormsを活用してオンライン化したいと考えています。
今はまだオンラインに不慣れな高齢者が多いですが、このあと10年もすればスマホを触ったことのある世代が高齢者になるので、もう少し踏み込んだデジタル化ができるのではないかと期待しています。
その頃自分も50代なかば。どこまでこの辺りを推し進める気力があるかは不安なところですが、後継者が出てくることを願いつつ・・。

以上です。