現在は、Cedars-Sinai Medical Center (LA/USA)に所属しているYuki Sahashiです。

今日は、ニッチな話をします。働き方改革の話です。
所属していた組織で、実装した業務効率化の話を何人かにしたところ、やり方を教えて欲しいという声を頂いたので共有しようと思います。

題名には、「GoogleFormからGoogle Calenderに予定を自動入力する。」とありますが、これは何の話かと言うと、「アポイントメントなど外部組織からの予約システムの自動効率化を無料で実現する」ということです。

とある働き方改革の一例

私がいた組織では、
1. 製薬会社や医療機器メーカーの担当者（外部組織）からのアポイントメントメールを秘書が受けて、
2. スケジュールが空いていることを確認して、
3. 担当者にマニュアルで返信して、予定表に書き込む　
ということを行っていました。
１日に１件とかならいいですが、これ20-30社からとなるとそれだけで仕事がパンクしてしまいます。

働き方改革の一環として、これらを自動化できないかなと考えた次第です。

そういうのは外部システムを使っている　とお考えの方もいらっしゃるかもしれませんが、これは完全に無料で実装できるので、もしかしたら経費の削減もできるかもしれません。

このシステムの利点は、
・無料
・特別な知識はあまり必要ではない
・予約する側も予約される側も、仕事の手間が省ける
・予約可否のメールが自動通知されるため、混乱が生じない。

今回の作業では、Google Apps Scriptというプログラミング言語は必要ですが、コードと手順を全て記載します。

Google Formを作成する。

まずGoogle Formを作成します。非常に簡単です

ここでは、外部からの予約を想定して、
・予約者のメールアドレス
・会社名
・担当者名（予約者）
・希望日
・時間
・内容
・備考
を入力します。
（この記事ではこの順番にしています。）
後ほどコードでこの順番で入るので、アレンジする際には、コードもアレンジしてください。

このフォームに入力されると、
上部の回答タブから開くことができるスプレッドシートに内容が記載されます。

Google Form編はこれだけ、とても簡単です。

Google SpreadsheetからGoogle Apps Scriptを連携する。

Google Spreadsheetの上部の拡張機能から、Apps Scriptを開きましょう。

開くとこんな感じの画面になります。

ファイルからスクリプトを追加して、２つここではカレンダー作成.gs　と　フォーム返信.gs　を作成しましょう

カレンダー作成.gsにはこれを入れる。
ここでは、Googleカレンダーに予定を書き込む関数createEventを作成します。
注意点などは下記の通り

・let [timestamp, email, company, username, date, time, drug_name, bikou] = e.values;　この順番は、先程のスプレッドシートの列順番になっている。
・let idには後述する入力したいGoogle Calenderの特異IDを入力する
・Logger.log('endTime '+ endTime);　などは、エラーが起こっているときなどに、なにで起こっているのか明確にするために記載している
・管理人の使用している日付の設定基準によってyyyy/mm/dd もしくはmm/dd/yyyyなのかは異なる。

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

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

//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(time.split(':')[0]);
  //let minutes = Number(time.split(':')[1]);
  let startTime = new Date(y, m, d, hours, minutes);
  let endTime = new Date(y, m, d, hours, minutes+60);

  //Loggerは、実行したときに出力される。エラーが起きたときに解決が容易
  Logger.log('createEvent ');
  Logger.log('timestamp '+ timestamp);
  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('time '+ time);
  Logger.log('drug_name '+ drug_name);
  Logger.log('title '+ title);
  Logger.log('startTime '+ startTime);
  Logger.log('endTime '+ endTime);

//カレンダーに書き込む詳細
  let description = 
`▼申込内容
予約日時: ${timestamp}
Ｅメール: ${email}
会社名：${company}
お名前　: ${username}
見学日　: ${date}
見学時間: ${time}
製品_講演名:${drug_name}
備考: ${bikou}`;

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

フォーム返信.gsは下記を書き込む。これは
・Formに打ち込まれた日程を参照し、
・すでに予約が入っているか確認して、
・予約可否をメールで返信する。
・予約可能な場合は、Google Calenderに書き込む
を行います。

function sendThanksForm(e) {
  console.log(e.values);
  let [timestamp, email, company, username,  date, time, drug_name, bikou] = e.values;
  Logger.log('sendThanksForm');
  Logger.log('timestamp '+ timestamp);
  Logger.log('email '+ email);
  Logger.log('date '+ date);
  Logger.log('time '+ time);
  Logger.log('drug_name '+ drug_name);

  //let [timestamp, username,email, date, time, other] = e.values;
  let subject = '会議室の予約を受け付けました';
  let body =

`${company} ${username}様

会議室の予約を予約を受け付けました。以下の内容でご予約承りました。
下記の時間でお越しください。

- 日付: ${date}
- 時間　: ${time}~
- 製品_講演名 :${drug_name}
- 備考: ${bikou}

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

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

//もしすでに会議室の予約が埋まっている場合（Google Calender）
function sendAlertMail(e) {
  let [timestamp, email, company, username,  date, time, drug_name, bikou] = e.values;
  let formUrl = SpreadsheetApp.getActiveSpreadsheet().getFormUrl();
  let subject = 'ご予約の時間はすでに予約が埋まっています。';
  let body =

`${company} ${username}様
誠にありがとうございました。大変恐れ入りますが、以下の時間の前後1時間はすでに予約が埋まっています。

- 日付: ${date}
- 時間　: ${time}~
- 製品_講演名 :${drug_name}
- 備考: ${bikou}

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

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

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


function exist(e) {
  let [timestamp, email, company, username,  date, time, drug_name, bikou] = e.values;
  //let [timestamp, email, username, date, time, others] = e.values;
  let id = '929dbe6d0839ae48463c85839b761cd50db90910014b6a53d192ab16e5b35107@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(time.split(':')[0]);
  //let minutes = Number(time.split(':')[1].split(":")[0]);

  let startTime =new Date(y,m,d,hours-1,minutes);
  let endTime =new Date(y,m,d,hours,minutes+60);
  let events = calender.getEvents(startTime,endTime);

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

Google CalenderのID

Google Calender開いて左のマイカレンダーから設定と共有を開きます。

トリガーの設定

このコードを起動させる設定を行いましょう。

この設定にすれば、”フォームが送られたとき”に、”スプレッドシートから”、sendForm（２つ目のコード内にある）を実行してくれます。

実際に、運用する前にうまくいくか、
・Google Formで確認してみましょう
・うまく行かない場合は、左の実行数から実行を開いてLogを確認したりして、どこでエラーが起こっているか確認しましょう

実行してみよう。

実際にFormを打ち込んでみると、スプレッドシートには自動記載され、

Calenderを見ると、自動記載されます。
さらに予約が被っている場合は、受付できない旨のメールが返信されます。

以上！

自分が実装してからは、秘書や予約する会社の方から、連絡の手間がなくなってとても楽になった　とお話を頂きました。ありがとうございます。

会社や病院（前職）は、結構このようにアイデアと少しのやる気で、無料実装できるTipsが多いと思っています。ぜひ皆様の会社や組織で実装したり、アレンジして使用してみてください。

読むのよくわからないから、最初から実装手伝って！とあれば、相談頂ければ、お声かけてください。少し時間はかかるので、寿司おごってください（）