Googleフォーム回答後、送られてくるメールの承認ボタンを押すと、カレンダーに反映

Question

出張の承認フローを作っているのですが、申請フォームに回答したら、管理者に承認か否認かを選ばせるメールが届くところまで完成しました。
もう一つ手間を加えて、承認をおせばGoogleカレンダーに反映されるようにしたいです。
フォームを送信すれば承認否認関係なくカレンダーに反映されるものはできたのですが・・・

お力添えいただきたく存じます。

メールに承認・否認ボタンを作成するのに参考にしたサイトは以下です。
https://tonari-it.com/gas-workflow-url-parameter/

実際のコードは下記です。

function doGet(e) {
  const row = e.parameter.row;
  const sheet = SpreadsheetApp.getActiveSheet();
  const values = sheet.getRange(row, 1, 1, ～).getValues()[0];
  const bodies = generateBodies(values);
  const answer = e.parameter.answer;
  const 出発 = e.parameter.start_date;
  const 帰着 = e.parameter.end_date;
  const 理由 = e.parameter.reason;

  const result = {
    ok: '承認',
    ng: '否認'
  };

  sheet.getRange(row, ～).setValue(result[answer]);

 // answerが「ok」、すなわち承認のリンクがクリックされた場合は、Googleカレンダーにスケジュールを登録する。
 if (answer === 'ok') {
  registerSchedule(textReason, timestampStart, timestampEnd);
 }

  const recipient = bodies.email;
  const subject = `出張申請${result[answer]}のお知らせ`;
  let body = '';
  body += `出張申請が${result[answer]} されました。\n\n`;
  body += bodies.plain;

  let html = '';
  html += `<h1>出張申請${result[answer]}のお知らせ</h1>`;
  html += `<p>以下の出張申請が${result[answer]}されました。</p>`;
  html += bodies.html;

  GmailApp.sendEmail(recipient, subject, body, { htmlBody: html });
  html = '';
  html += `<h1>出張申請の${result[answer]}</h1>`;
  html += `<p>あなたは以下の出張申請を${result[answer]}しました</p>`;
  html += bodies.html;
  return HtmlService.createHtmlOutput(html);
}

function generateBodies(values){
  const [～] = values;
 
  let plain = '';
  plain += `～\n`;
  plain += `～\n\n`;  
  let html = '<ul>';
  html += `<li>～</li>`;    
  html += '</ul>';
  return {
    email: email,
    plain: plain,
    html: html
  };
}

function sendMessage(e) {

  const row = e.range.getRow();
  const sheet = e.range.getSheet();
  sheet.getRange(row, ～).setValue('確認中');
  const bodies = generateBodies(e.values);
  let url = 'https://script.google.com/a/macros/～/s/～';

 // 出張期間の開始日
 const timestampStart = e.values[～].getTime();
 // 出張期間の終了日
 const timestampEnd = e.values[～].getTime();
 // 出張用件
 const textReason = e.values[～];

 url += `?start_date=${timestampStart}&end_date=${timestampEnd}&reason=${textReason}&row=${row}&answer=`;

  const recipient = '管理者のメールアドレス';
  const subject = '出張申請のお知らせ';
  let body = '';
  body += '以下の申請があります。\n\n';
  body += bodies.plain;
  body += '承認する場合は、以下URLをクリックしてください\n';
  body += url + 'ok';
  body += '否認する場合は、以下URLをクリックしてください\n';
  body += url + 'ng';

  let html = '';
  html += '<h1>出張申請のお知らせ</h1>';
  html += '<p>以下の申請があります。</p>';
  html += bodies.html;
  html += `<p>承認する場合は、<a href="${url}ok">[承認]</a>をクリックしてください</p>`;
  html += `<p>否認する場合は、<a href="${url}ng">[否認]</a>をクリックしてください</p>`;

  GmailApp.sendEmail(recipient, subject, body, { htmlBody: html });

}

function registerSchedule(textReason, timestampStart, timestampEnd) {
  // Googleフォームの回答を取得する
  // カレンダーに予定を追加する

  var CALID = "管理者のカレンダーID";
  var cal = CalendarApp.getCalendarById(CALID);
  cal.createEvent(textReason, new Date(timestampStart), new Date(timestampEnd));
}

どうぞよろしくお願いいたします。

Answer 1

【前提】
・フォームの質問項目として、GASでフォーム回答時に取得されるデータのうち、2番目に出張開始日が、3番目に出張終了日が、4番目に出張用件が位置していると仮定します。
・スプレッドシートおよびカレンダーの時刻設定やGASによるカレンダーへの読み込み・書き込みは動作意図通り正しく設定されているものとします。
・回答を収集しているシートの書式は動作の意図通り正しく設定されているものとします。（例：B列・C列は日付書式）
・GASのWebアプリとしてのデプロイが正常に更新されており、アクセス権限も問題なく動作意図通りに設定されているものとします。
（フォームの全容 [特に、フォームにどのような質問項目があるか] や、スプレッドシートの全体詳細、動作の詳細が、質問文には一切提示されておらず、ソースコードも一部省略されているため、一部想像により補完しています。
これら不明部分について、もともとあるバグや考え方の違いにより、下記を追加して動作しなかったとしても、こちらは一切関知しません。前提と異なる部分がある場合は自分で修正・カスタマイズしてください）

---

下記のコードは、変更前後の部分が色分け表示されるように、修正行の先頭に「+」「-」記号を付けています。+は追加、-は削除する行です。
実際にコードをコピーして貼り付ける場合は、先頭の「+」「-」記号は消してください。

---

① sendMessage 関数の中で、送信するメールに記載するURLに、出張開始日と出張終了日のタイムスタンプおよび出張用件を、パラメータとして設定するようにします。

function sendMessage(e) {

  const row = e.range.getRow();
  const sheet = e.range.getSheet();
  sheet.getRange(row, 13).setValue('確認中');
  const bodies = generateBodies(e.values);
  let url = '～';

+ // 出張期間の開始日
+ const timestampStart = new Date(e.values[1].getTime());
+ // 出張期間の終了日
+ const timestampEnd = new Date(e.values[2].getTime());
+ // 出張用件
+ const textReason = e.values[3];

- url += `?row=${row}&answer=`;  // 下のように修正
+ url += `?start_date=${timestampStart}&end_date=${timestampEnd}&reason=${textReason}&row=${row}&answer=`;

  const recipient = '管理者メールアドレス';
  const subject = '出張申請のお知らせ';
  let body = '';
  body += '以下の申請があります。\n\n';
  body += bodies.plain;
  body += '承認する場合は、以下URLをクリックしてください\n';
  body += url + 'ok';
  body += '否認する場合は、以下URLをクリックしてください\n';
  body += url + 'ng';

  let html = '';
  html += '<h1>出張申請のお知らせ</h1>';
  html += '<p>以下の申請があります。</p>';
  html += bodies.html;
  html += `<p>承認する場合は、<a href="${url}ok">[承認]</a>をクリックしてください</p>`;
  html += `<p>否認する場合は、<a href="${url}ng">[否認]</a>をクリックしてください</p>`;

  GmailApp.sendEmail(recipient, subject, body, { htmlBody: html });

}

② doGet 関数内で、メールの承認リンクをクリックされて送信されてきたリクエストから、出張開始日・出張終了日のタイムスタンプおよび出張用件のパラメータを取得します。
取得した出張用件および出張開始日・出張終了日のタイムスタンプを、registerSchedule 関数に引数として渡します。

function doGet(e) {
  const row = e.parameter.row;
  const sheet = SpreadsheetApp.getActiveSheet();
  const values = sheet.getRange(row, 1, 1, 13).getValues()[0];
  const bodies = generateBodies(values);
  const answer = e.parameter.answer;
+ const timestampStart = e.parameter.start_date;
+ const timestampEnd = e.parameter.end_date;
+ const textReason = e.parameter.reason;

  const result = {
    ok: '承認',
    ng: '否認'
  };

  sheet.getRange(row, 13).setValue(result[answer]);

+ // answerが「ok」、すなわち承認のリンクがクリックされた場合は、Googleカレンダーにスケジュールを登録する。
+ if (answer === 'ok') {
+   registerSchedule(textReason, timestampStart, timestampEnd);
+ }

  const recipient = bodies.email;
  const subject = `出張申請${result[answer]}のお知らせ`;
  let body = '';
  body += `出張申請が${result[answer]} されました。\n\n`;
  body += bodies.plain;

  let html = '';
  html += `<h1>出張申請${result[answer]}のお知らせ</h1>`;
  html += `<p>以下の出張申請が${result[answer]}されました。</p>`;
  html += bodies.html;

  GmailApp.sendEmail(recipient, subject, body, { htmlBody: html });
  html = '';
  html += `<h1>出張申請の${result[answer]}</h1>`;
  html += `<p>あなたは以下の出張申請を${result[answer]}しました</p>`;
  html += bodies.html;
  return HtmlService.createHtmlOutput(html);
}

③ カレンダーに追加するための関数 registerScheduleの中身は下記のような形です（元のコードで省略されている部分はそのまま省略しています）

function registerSchedule(textReason, timestampStart, timestampEnd) {
  // Googleフォームの回答を取得する
  // カレンダーに予定を追加する

  var CALID = "～";
  var cal = CalendarApp.getCalendarById(CALID);
  cal.createEvent(textReason, new Date(timestampStart), new Date(timestampEnd));
}

---

追記

sendMessage関数の出張期間の開始日と終了日を
下記のように変えてみた場合はどうでしょうか（new Date()で囲む）

 // 出張期間の開始日
 const timestampStart = new Date(e.values[1].getTime());
 // 出張期間の終了日
 const timestampEnd = new Date(e.values[2].getTime());

Comments

[oxd37527]

ご回答ありがとうございます。お渡しする情報が少なく、ご回答までに時間を割いてしまい大変失礼いたしました。初歩的な質問で恐縮ですが、①②③と順序だててご説明いただいておりますが、私が載せているコードの順番で、該当の個所を修正でよろしいのでしょうか？①②③の順番にする必要があるということなのでしょうか。また、実行する関数はdoGetでいいのでしょうか？トリガーの関数はsendMessageにしております。

コードの順番は変えず該当の個所を修正し、③を一番最後に入れました。フォームを試しに送ったところ、メールが届かないのが現状です。

なお、記載したコードに関して極力個人ID以外記入した状態のものに直しました。よければご確認ください。

[退会済みユーザー]

まず、ご質問の「①②③と順序だててご説明いただいておりますが、私が載せているコードの順番で、該当の個所を修正でよろしいのでしょうか？」「コードの順番は変えず該当の個所を修正し、③を一番最後に入れました。フォームを試しに送ったところ、メールが届かないのが現状です。」
について：
確認なのですが、現状質問文に記載されているのは、修正前のコードですよね？
修正した後のコードではなく、修正前のコードだけ記載して「修正したけどうまく動きません」と言われても、私は超能力者ではないので、「修正後のコード」のどこに問題があるかは分かりかねます。

お手数ですが、修正した後の現状のコードを、個人ID以外極力省略せず、全部記載してください。（①②③に分けて記載する必要はなく全部一気に書いていいです）

また質問文を見てのお分かりの通り、引用符等が省略されてしまっているため、こちらでコピペしても動きません
ソースコードは、質問の編集画面で
```
と
```
の間に書いてください。

次に「①②③の順番にする必要があるということなのでしょうか。」についてですが、順番は関係ありません。説明の便宜上数字を付けただけであり、実際にコードを書くときは、上記の各関数は順不動で構わないです。

「実行する関数はdoGetでいいのでしょうか？」についてですが、doGet関数は、GASのエディタ上で実行するものではありません。(実行してもうまく動かないはずです）
このプログラムでは、doGet関数は、（デプロイが正常になされていれば、という前提で）送られてきたメール内のリンクをクリックしたときに自動的に呼ばれます。

[oxd37527]

仰る通りqnoir様ご指摘コード挿入前の、個人ID以外を省かず入れただけのコードでした。ご指摘前のコードによってアプローチが変わるのかもしれないと思い、あえてそうしておりました。説明足りず申し訳ございません。

順番等ご回答いただきありがとうございました。勉強になりました。
ご回答いただいた①②③に留意し、質問文の該当コードを直しました。

ご確認の程、よろしくお願いいたします。

[oxd37527]

new Date・getTimeを削除することで解決しました。この度は作成にご協力いただき、ありがとうございました。