はじめに

無料で、充実した予約フォームを使いたい...

イベントを企画して、参加者を募る時、予約フォームが必要になると思います。
そんな時に、どんな予約フォームを使いたいですか？
ウェブ上には無料のものから有料のものまで、さまざまな用途に応じた予約フォームが存在します。
しかし、あくまで自分調べですが、無料で使えて、機能が充実している予約フォームってそんなにない気がしました。

今回は、自主制作映画の上映会の予約フォームを作成すべく、無料のGoogleフォームで機能を拡張してみました。（使われているデータはすべてダミーです！）

機能

既存のGoogleフォーム・スプレッドシートの機能に加えて、次の機能を追加しました。

予約フォーム送信直後に内容確認メールを送信
予約情報をスプレッドシートに反映
残席情報にもとづいてフォームを更新
本番前日にリマインドメールを送信

1.予約フォーム送信直後に内容確認メールを送信

Googleフォームに回答したら以下のようなメールが送られます。

2. 予約情報をスプレッドシートに反映

これにより予約情報を視覚的に理解することができます。

3. 残席情報にもとづいてフォームを更新

もし残席が5席以下だったら、公演日の選択肢の横に「（メールでのみ取り扱い）」を追加し、もし残席が0になってたら、公演日のところを空白にします。

4. 本番前日にリマインドメールを送信

本番一日前に下のようなリマインドメールを送信。

フォームのデザイン

公演日時、チケット種類、チケット枚数はプルダウンになっている。

ここでは選択肢について、
・公演日時は「10月10日(木) 18:00〜, 10月11日(金) 13:00〜 , 10月11日(金) 18:00〜, 10月12日(土) 13:00〜, 10月12日(土) 18:00〜」
・チケット種類は、「学生, 一般」
・チケット枚数は、「1, 2, 3, 4, 5」
とする。

スプレッドシートのデザイン

上記のようなフォームを作成したら、スプレッドシートにリンクして、フォームの回答を反映させる。（回答はダミーです！）

この時、シートの名前を「response」にする。

さらに新しいシートを作成し、名前を「cost」にする。
列に公演日時を、行にチケット種類を入れ、それぞれの値段を値とする。

もう一つ新しいシートを作成し、名前を「reservation」にする。
列に公演日時を、行にチケット種類を入れる。さらに学生と一般の予約情報を足し合わせた予約合計を表示する行や、元々の席数を入力する行、予約合計/席数で表される予約率、席数-予約合計で表される残席をスプレッドシートに入力する。

そして、最終行には残席のレベルに応じて振り分けられるダミー変数を入れる。
（システム上、ダミー変数が最終行にあるという前提のもとで動くので注意！）

例えばB列の最終行には次のような関数を入力している。

=IFS(B7>5,2,B7>0,1,TRUE,0)

もし残席が5席より多ければ2、残席が空席でなく5席以下だったら1、空席だったら0を出力するダミー変数となっている。（上の画像では0となっているが、これはあえて値を変えているので気にしないで〜〜〜〜）

GASのコード

ここからはGoogle Apps Script(通称GAS)のコードを説明していきます！
・スプレッドシートの「拡張」から「Apps Script」に飛んでください。
・スクリプトエディタに書いてある既存のコードを消して、下のコードを書いていきましょう。

function getInfo(){
  return {
    "formid":"***********",
    "sheetid":"***********",
    "group":"***********",
    "title":"***********",
    "mail":"***********",
    "tel":"***********",
    "dateCount":5,
    "typeCount":2
  }
}

まずこちらは今後使う重要な値をまとめた関数となります。

Googleスプレッドシートの編集ページは以下のようなURLとなっています。
https://docs.google.com/spreadsheets/d/*********/edit?gid=0#gid=0
ここの「*********」の部分が、スプレッドシートのIDとなっています。このIDによって、他のGoogleスプレッドシートと区別されます。
プログラムではこのIDをもとにsheetを識別するので、コピペしてsheetidの部分に貼り付けておきましょう。

同様にGoogleフォームのURLも、
https://docs.google.com/forms/d/*********/edit
のようになっているため、「*********」の部分をformidに貼り付けておきましょう。

またgroupには制作・運営団体を、titleには作品名、mailには団体のメールアドレス、telには団体の電話番号を書いてください。
dateCount、typeCountはそれぞれ公演日程とチケット種類の種類数を表します。
公演日程は「10月10日(木) 18:00〜, 10月11日(金) 13:00〜 , 10月11日(金) 18:00〜, 10月12日(土) 13:00〜, 10月12日(土) 18:00〜」で5ステ、チケット種類は「学生, 一般」の2種類なのでそれぞれ5と2を入力している。

//料金情報を取得
function getCost(costSheet){
  
  const costLastRow = costSheet.getLastRow()
  const costLastCol = getInfo()["dateCount"]
  const costData = costSheet.getRange(1,1,costLastRow,costLastCol).getValues()

  const costRow = costData.map(row => row[0])
  const costCol = costData[0]

  // 辞書を作成
  const result = {};

  for (let i=0; i<costRow.length; i++) {
    for(let j=0; j<costCol.length; j++){

      if(!costRow[i]||!costCol[j]){continue}

      key = `${costRow[i]}, ${costCol[j]}`
      result[key] = Number(costData[i][j])

    }
  }
  Logger.log(result)
  return result
}

function sendReservationMail() {

const sheetid = getInfo()["sheetid"]
const spreadsheet = SpreadsheetApp.openById(sheetid);
const responseSheet = spreadsheet.getSheetByName('response')
const costSheet = spreadsheet.getSheetByName('cost')

// フォームの回答を取得
const responseLastCol = responseSheet.getLastColumn()
const responseData = responseSheet.getRange(2,1,1,responseLastCol).getValues()

const [timestamp, name, hurigana, mail, tel, date, type, count, remarks] = responseData[0]

//料金情報を取得
const cost = getCost(costSheet)[`${type}, ${date}`]

// 自動返信メール件名
var Subject
var Body
      
// 自動返信メール本文
if(!cost){
  Subject = 'チケット予約に失敗しました。'
  Body = `
  \n
  大変恐縮ですが、予約が失敗しました。
  \n
  ─────────────────────────\n
  \n
  ◆お問い合わせ\n
  団体名: ${getInfo()["group"]}\n
  タイトル: ${getInfo()["title"]}\n
  Mail: ${getInfo()["mail"]}\n
  tel: ${getInfo()["tel"]}\n
  \n
  ─────────────────────────
  ` 
} else{
  Subject = 'チケットご予約承りました。'
  Body = `
  ${name}様\n\n
  この度は映画『${getInfo()["title"]}』のチケットをご予約いただき、誠にありがとうございます。\n
  下記の内容で受付いたしました。\n\n
  ─────────────────────────\n
  【ご回答内容の確認】\n
  ご予約完了日: ${timestamp}\n
  お名前: ${name}\n
  ふりがな: ${hurigana}\n
  Mail: ${mail}\n
  Tel: ${tel}\n
  公演日時: ${date}\n'
  チケット種類: ${type}\n
  チケット枚数: ${count}\n
  料金: ${cost}円\n
  備考: ${remarks}\n
  \n
  【合計金額】\n
  ${cost*Number(count)}円\n
  \n
  ※開場は開演の30分前を予定しております。\n
  ※5分前にお越しいただけない場合、キャンセル扱いとさせていただくことがございます。お時間に余裕を持ってお越しください。\n'
  \n
  それでは当日劇場にてお待ちしております。\n
  \n
  ─────────────────────────\n
  \n
  ◆お問い合わせ\n
  団体名: ${getInfo()["group"]}\n
  タイトル: ${getInfo()["title"]}\n
  Mail: ${getInfo()["mail"]}\n
  tel: ${getInfo()["tel"]}\n
  \n
  ─────────────────────────
  `

}


//メール送信
  MailApp.sendEmail({
    to: mail,
    subject: Subject,
    body: Body
  });

}

//料金情報を取得
function getReservationCount(responseSheet){
  
  // フォームの回答を取得
  const responseLastRow = responseSheet.getLastRow()
  const responseLastCol = responseSheet.getLastColumn()
  const responseData = responseSheet.getRange(2,1,responseLastRow-1,responseLastCol).getValues()

  // 辞書を作成
  const reservationList = [];
  var reservationDict = {}

  for (let i=0; i<responseData.length; i++) {
    reservationDict = {}
    const [timestamp, name, hurigana, mail, tel, date, type, count, remarks] = responseData[i]
    key = `${date}, ${type}`
    reservationDict[key] = Number(count)
    reservationList.push(reservationDict)
  }
  

  Logger.log(reservationList);
  const result = {};

  // 同じキーを持つ辞書（オブジェクト）の値を合計して新しい辞書を作る
  reservationList.forEach(obj => {
    for (const key in obj) {
      if (result[key]) {
        result[key] += obj[key];
      }  else {
        result[key] = obj[key];
      }
    }
  });

  Logger.log(result);
  return result
}

function changeSheet(){

  const sheetid = getInfo()["sheetid"]
  const spreadsheet = SpreadsheetApp.openById(sheetid);
  const responseSheet = spreadsheet.getSheetByName('response');

  const reservationSheet = spreadsheet.getSheetByName('reservation')
  const reservationRow = getInfo()["typeCount"]
  const reservationCol = getInfo()["dateCount"]
  const reservationData = reservationSheet.getRange(1,1,reservationRow+1,reservationCol+1).getValues()
  
  const type = reservationData.map(row => row[0])
  const date = reservationData[0]

  const reservationCount = getReservationCount(responseSheet)

  for (let i=1; i<=reservationRow; i++) {
    for(let j=1; j<=reservationCol; j++){

      key = `${date[j]}, ${type[i]}`
      reservationSheet.getRange(i+1,j+1).setValue(reservationCount[key])
      reservationSheet.getRange(i+1,j+1).setValue(reservationCount[key])

    }
  }

}

function changeForm(){
  
  //ドロップダウン形式の質問を取得（最初の1問目）
  const formid = getInfo()["formid"]
  const form = FormApp.openById(formid);
  const items = form.getItems(FormApp.ItemType.LIST);
  const listItem = items[0].asListItem()

  const sheetid = getInfo()["sheetid"]
  const spreadsheet = SpreadsheetApp.openById(sheetid);
  const reservationSheet = spreadsheet.getSheetByName('reservation')
  const reservationLastRow = reservationSheet.getLastRow()
  const reservationLastCol = getInfo()["dateCount"]
  const reservationData = reservationSheet.getRange(reservationLastRow,2,1,reservationLastCol).getValues()[0]
  const reservationCol = reservationSheet.getRange(1,2,1,reservationLastCol).getValues()[0]

  //新しい選択肢を定義
  const newChoices = reservationCol

  for(i=0; i<=reservationLastCol; i++){
    if(reservationData[i] == 1){
        newChoices[i] = `${reservationCol[i]}(メールでのみ取り扱い)`
      } else if(reservationData[i] == 0){
        newChoices[i] = ''
      }
  }

  Logger.log(newChoices)

  //選択肢を更新
  listItem.setChoiceValues(newChoices);

  Logger.log("ドロップダウンの選択肢を更新しました");
}

function afterReservation(){
  //予約完了メールを送信
  sendReservationMail()

  //スプレッドシートを更新
  changeSheet()

  //フォームを更新
  changeForm()

}

afterReservation()では、sendReservationMail()、changeSheet()、changeForm()を実行する。
sendReservationMail()で予約完了メールを送信し、changeSheet()でスプレッドシートを更新し、changeForm()でフォームを更新する。

function sendMailBeforeTheDay() {

const sheetid = getInfo()["sheetid"]
const spreadsheet = SpreadsheetApp.openById(sheetid);
const responseSheet = spreadsheet.getSheetByName('response')
const costSheet = spreadsheet.getSheetByName('cost')

// フォームの回答を取得
const responseLastRow = responseSheet.getLastRow()
const responseLastCol = responseSheet.getLastColumn()
const responseData = responseSheet.getRange(2,1,responseLastRow-1,responseLastCol).getValues()
const costDict = getCost(costSheet)

for(i=0; i<responseData.length; i++){

  var [timestamp, name, hurigana, mail, tel, date, type, count, remarks] = responseData[i]

  //料金情報を取得
  const cost = costDict[`${type}, ${date}`]
  Logger.log(`${type}, ${date}`)
      
  // 自動返信メール本文
  var Body = `
  ${name}様\n\n
  この度は映画『${getInfo()["title"]}』のチケットをご予約いただき、誠にありがとうございます。\n
  ご予約の公演日程が近づいてまいりましたため、ご連絡差し上げました。\n\n
  ─────────────────────────\n
  【ご回答内容の確認】\n
  ご予約完了日: ${timestamp}\n
  お名前: ${name}\n
  ふりがな: ${hurigana}\n
  Mail: ${mail}\n
  Tel: ${tel}\n
  公演日時: ${date}\n'
  チケット種類: ${type}\n
  チケット枚数: ${count}\n
  料金: ${cost}円\n
  備考: ${remarks}\n
  \n
  【合計金額】\n
  ${cost*Number(count)}円\n
  \n
  ※開場は開演の30分前を予定しております。\n
  ※5分前にお越しいただけない場合、キャンセル扱いとさせていただくことがございます。お時間に余裕を持ってお越しください。\n'
  \n
  それでは当日劇場にてお待ちしております。\n
  \n
  ─────────────────────────\n
  \n
  ◆お問い合わせ\n
  団体名: ${getInfo()["group"]}\n
  タイトル: ${getInfo()["title"]}\n
  Mail: ${getInfo()["mail"]}\n
  tel: ${getInfo()["tel"]}\n
  \n
  ─────────────────────────
  `

  //メール送信
  MailApp.sendEmail({
    to: mail,
    subject: '予約公演日のご案内',
    body: Body
  });
  }
  Logger.log(`${mail}へ送信しました。`)

}

このsendMailBeforeTheDay()は、予約者全員に公演直前のリマインドメールを送る関数です。
実行に承認が必要な場合があるので、その際はひたすら承認していきましょう。ここでも解説しています。
https://qiita.com/KANEI/items/c4f190bb3a0861a0cc81

トリガーの設定

sendMailBeforeTheDay()は実行するのが一回なので、editer上で実行するが良いでしょう。
afterReservation()はフォーム送信ごとに実行される必要があるので、ここからトリガーを設定し、フォーム送信ごとに実行される設定にしていきましょう。

1. 左のメニューからトリガーを選択

2. トリガーを追加を選択

3. 下のように設定し、保存

これでタイマーがセットされました。

最後に

今回は上映会の予約フォームに特化しましたが、プログラムを変えて他のイベントにも対応させることも可能です！
無料のものを最大限利用してみてください〜！！

参考

・GoogleフォームとGASで超実用的な予約システムを自作する
　https://uncle-gas.com/develop-reserve-system/
・【GAS】Google Apps Script からメールを送信する
　https://note.com/su3_hokkaido/n/n6213a6dec475
・GASでGoogleフォームの値を取得する（フォームを指定）
　https://walking-elephant.blogspot.com/2021/01/gas.formapp.html