Zenn
🗓️

Google Apps Scriptで予約フォームに申込・変更・取消機能と予約数上限をつけてみた

2021/11/18に公開
Google Apps Script
Google Sheets
Google Forms
tech

Google FormとGoogle SpreadsheetにGoogle Apps Scriptで手を入れて便利仕様にできたので、経緯他もろもろを文章にまとめることにした。
一人開発のため便利なものの存在を知らなかったり、「よそ様はどういうふうにやっているのだろう」という興味もあって公開してみた。

前提

社内研修の日程調整にGoogle FormとGoogle SpreadSheetを使っていた。
各参加者が個人で都合のあった日程を選び、参加者の情報と日程をGoogle Formで回答するとGoogle SpreadSheetに回答が蓄積されていく。
研修担当者はその回答をGoogle Spreadsheetで集計し、それを見て研修の準備を行っていた。

しばらく運用して分かったこと

  • 全体の一割程度の参加者が予約の変更・削除を行う。
  • １回の研修で対応可能な人数以上が同一日程に参加を希望した場合には本人まで連絡して予定変更をお願いする必要がある。こまめに確認が必要。

出てきた要望

参加者サイド

  • 予約の変更を自動で受け付けてほしい
  • 予約の取消を自動で受け付けてほしい
  • 誤って複数回予約してしまったので、削除したい

研修担当者サイド

  • 一度に参加できる人数を制限して、定員いっぱいになったら受け付けないようにしてほしい
  • 研修直前の申し込みを受け付けないようにしてほしい

要望への対応方針

スクリプト動作時間・作業量からワークシート関数＋Google Apps Scriptの組合せで解決可能と考えた。FormとSpreadSheetを比較すると送信され蓄積されたデータと関係が深くなりそうなのでGoogle Spreadsheetに付属するコードとして書いていく。

参加者サイド

予約の変更・取消の連絡を受けたらGoogle Spreadsheetの内容を手で変更・消込していたが、他業務で忙しいタイミングだと手で消し込むのに負担感があった。
予約変更や取消のタイミング・頻度を履歴として残したかったので、回答の編集機能を使わずGoogle Form(以下フォーム)の回答から現在の申し込み状況を生成することにした。

予約の取り扱い方

予約作成

フォームからの申込があった場合、かつ、それまでに残っている予約がない場合には新規予約として扱う。

変更

同じ人から２以上の申し込みがあるとき、最新の方を残して古いものを破棄。

取消

その人のその時点での最新の申込日程を削除する。

やったこと

  1. Google Formが申込・変更・取消用の操作フォームとして機能するよう、日程一覧（ラジオボタン）の選択肢の一番上に回答「予約の取消」を追加。
  2. Google Spreadsheet（以下スプレッドシート）に蓄積されたGoogle Formの回答を入力として受け取り、Google Apps Script(以下GAS)で変更・取消などの状況整理を行い[1]その結果をスプレッドシートに出力させる関数を用意。回答送信のタイミングで動かす。
  1. ２の出力結果をワークシート関数で加工し、現在の予約名簿の右に「変更・取消」リンクを設ける[2]。これをクリックするとリンク先のGoogle Formは現在の予約状況が記入された状態から変更・取消作業を行うことができる。

Google Formで事前記入されたURLを生成する

Google Form右上から「事前入力したURLを取得」を選択し、各質問に対して任意の回答を行ってからURLをクリップボードにコピーすると質問IDと回答の組が入っているので、それを用いて回答付URLを生成してリンクにする。

// https://docs.google.com/forms/d/e/ほげほげ/viewform?usp=pp_url&entry.なんかの数字=英数字記号の並ぶ文字列&entry.なんかの数字=英数字記号の並ぶ文字列
//　↑というURLは以下のように分解できる。
// フォームURL：　https://docs.google.com/forms/d/e/ほげほげ/viewform?usp=pp_url
// 質問と回答のペア：　&entry.質問ID1=回答1のエンコード済文字列
// 質問と回答のペア：　&entry.質問ID2=回答2のエンコード済文字列

// エンコード済み文字列の内容を見ながら質問を思い出す場合は、
// ブラウザの開発者ツールを開いてコンソールを開く。(Google ChromeであればF12キーを押す。)
//　次のように入力してエンターを押すと中身が読める。
decodeURI("英数字記号の並ぶ文字列")

// または以下でもよい。
decodeURIComponent("英数字記号の並ぶ文字列")

参考:
decodeURI() - JavaScript | MDN
decodeURIComponent() - JavaScript | MDN

これを切り分けしてスプレッドシートのencodeURL関数とhyperlink関数を使うと、次のような関数でリンクを貼ることができる。

// 1セルでいいなら
=hyperlink(
  concatenate(
    "https://docs.google.com/forms/d/e/ほげほげ/viewform?usp=pp_url", 
    "&entry.質問ID1=", encodeURL(回答1の内容が入ったセル), 
    "&entry.質問ID2=", encodeURL(回答2の内容が入ったセル)
  ), "変更・取消"
)

// *見やすさのため改行した

ただし上の式をそのまま使うと、行数が増えるごとに式をコピペする必要があってちょっと面倒くさい。

  • 各行に対してそれぞれ計算値を求めたい
  • 運用が進むと行数が増えていくことが分かっている

・・・ので、Arrayformula関数を用いて自動対応にした方が使い勝手が良い[3]。

// Arrayformulaを用いて、項目名の列に数式を入力する。
=Arrayformula(
    hyperlink(
      "https://docs.google.com/forms/d/e/ほげほげ/viewform?usp=pp_url" 
      & "&entry.質問ID1=" & encodeURL(回答1の内容が入った範囲)
      & "&entry.質問ID2=" & encodeURL(回答2の内容が入った範囲)
    , "変更・取消")
)
// *見やすさのため改行した

// 範囲についてはA2:Aのように範囲末尾を指定すると列の増減を自動で補完してくれる。
// Arrayformula関数を使うとConcatenate関数は動かないので、&演算子で文字列をつなぐ。
// なおConcat関数であればArrayformula関数内でも動く。

回答が蓄積されていくシートでフィルタをかけたりソートする場合は、上記に加えて１行目の項目名のところに数式を置けると自由にオートフィルタ・ソートが使える。よって、次の書き方を組み合わせることでフィルタ＆ソートに対応させる。
参考: Google スプレッドシートで配列を使用する - ドキュメント エディタ ヘルプ

={"列１", "列２", "列３";
  1, 2, 3;
  2, 4, 6}

// セミコロンで改行、
// カンマでセルを区切る

上のように書くと、セル表示が下のようになる

列１ 列２ 列３
1 2 3
2 4 6

以上を組み合わせて、自分はよく次のような感じで書く。

// Arrayformulaを用いて、項目名の列に数式を入力する。
={"項目名"; 
  Arrayformula(
    hyperlink(
      "https://docs.google.com/forms/d/e/ほげほげ/viewform?usp=pp_url" 
      & "&entry.質問ID1=" & encodeURL(回答1の内容が入った範囲)
      & "&entry.質問ID2=" & encodeURL(回答2の内容が入った範囲)
    , "変更・取消")
)}
// *見やすさのため改行した

// 範囲についてはA2:Aのように範囲末尾を指定すると列の増減を自動で補完してくれる。

研修担当者サイド

研修担当者からの要望は「１回で対応できる人数には限界があり、予備の資料の用意も考えると一気に参加者が増えた場合の対応がとても難しい。予約受付に締切を設けて、決まった人数しか予約を受けられないようにしたい」ということだった。
参加者を早めに確定したい、とのことだったが会場のサイズ等から定員が一定でないことが予見された。
研修担当者にはGASの知識がないため、スプレッドシートに研修日程の一覧とGASの動作を制御できるような値を書いてもらうことにした。

やったこと

  1. スプレッドシートに研修の日程・場所・定員情報を書き込むシートを用意。研修担当者が日程情報を追加する。締切時刻と同時に、同時申し込みによって人数上限を超える事態も考えうるので、上限何人＋その手前に設けるマージンの人数も書いておいてもらうことにした。
  2. フォームの選択肢になる文字列はワークシート関数で生成する。日程別の現在の参加者の人数・残り人数もワークシート関数で算出。参加者の人数を求める計算式の入力となるデータは予約申し込みを受けた後のフォーム送信時イベントに連動して更新されるので、特に対応不要。
  3. 研修担当者による入力値や計算式で求めた値を入力に用いて、フォームから選択できる日程はGASをm分に1回定期実行することで更新する[4]。

用意したスプレッドシートの内容は次のようになる。

A B C D E F G H
1 申込締切条件 前日の締切時刻 残り人数(人未満)
2 *ユーザ入力値 *ユーザ入力値
3
4 日付 開始時刻 終了時刻 場所 定員 申込人数 残り人数 Google Form選択肢
5 2021/11/10 11:00 12:00 会場A 20 10 10 (選択肢1)
6 2021/11/11 12:00 13:00 会場A 20 18 2 (選択肢2)
備考 *ユーザ入力値 *ユーザ入力値 *ユーザ入力値 *ユーザ入力値 *ユーザ入力値 *自動計算 *自動計算 *自動計算

内容は1-2列目がGAS用の動作指定。
4列目以降は研修の日程を入力してもらう。Ｈ列で算出している選択肢の内容がそのままGoogle Formの選択肢になる。

上記シートの値を使って定期実行されるGASの内容は次の通り。

function update_form(){
  const sheet = SpreadsheetApp.openByUrl("URL").getSheetByName("シート名")
  const tmp = sheet.getRange("A4:H").getValues()
  const option = tmp.slice(1)
    .filter(function(row){return row[0] != ""})
    .map(function(values){
           var result = Object()
           return values.reduce((obj, c, i) => {
                                  obj[tmp[0][i]] = c; 
                                  return obj
                                }, result)
    })
  
  const tmp_user_preference = sheet.getRange("A:C2").getValues()
  const user_preference = tmp_user_preference[1]
                           .reduce(function(obj, v, i){
                                     obj[tmp_user_preference[0][i]] = v; 
                                     return obj
                           }, {})
  
  const todays_deadline = new Date()
  todays_deadline.setHours(
    user_preference["前日の締切時刻"].getHours(), 
    user_preference["前日の締切時刻"].getMinutes(),
    user_preference["前日の締切時刻"].getSeconds(),
    0)

  const now = new Date()
  let applicable_limit
  if(todays_deadline > now){
    applicable_limit = new Date(now.getTime() + 24*60*60*1000)
  }else{
    applicable_limit = new Date(now.getTime() + 2*24*60*60*1000)
  }
  applicable_limit.setHours(0, 0, 0, 0)


  const available_option = ["予約の取消"]
  for(let opt of option){
    if((applicable_limit <= opt["日付"])
        && (opt["残り人数"] >= user_preference["残り人数(人未満)"])){
      available_option.push(opt["Google Form選択肢"])
    }
  }
  const form = FormApp.openById("フォームのURL")
  const item = form.getItemById("質問のID")
  item.asMultipleChoiceItem().setChoiceValues(available_option)
}

FormのURLは編集用の画面を表示している時のURL。
目的の質問項目のIDの取得方法は、formを宣言後に
Logger.log(form.getItems().map(item => item.getId()))
で、デバッガのBreakPointを設定して実行。質問IDを入手したあとで実行中断した[5]。

他、参照したのは次の個所:
Date - JavaScript | MDN
Class Form  |  Apps Script  |  Google Developers
Interface Item  |  Apps Script  |  Google Developers
Class MultipleChoiceItem  |  Apps Script  |  Google Developers

結果

  • 参加者側の予約変更対応に手を取られていたのが対応不要になった。この状態で数年ほど運用されているので好感触。
  • 定員オーバーな研修日程の発生(10回に1回程度)を気にしないで済みそうな見込み。

・・・というわけで、快適に過ごせるようになった。コーディング万歳。

ほか、似たようなことをしている方の記事

Googleフォームで回答数により選択肢を変更する | Yuka Lif
Googleフォームで定員のある選択肢を作る - Qiita

脚注

  1. GAS=ほぼJavascriptとのことだったので、Mapping的なデータ型を用いて{キー(申し込み者): 値(参加日程と場所)}という形にして作成・上書き・削除を行う。自分はObject型を用いた（Map型については未検証）。コードはあまりにも汚いので省略する。 ↩︎

  2. 参加者を識別するための情報を記入してもらう際の転記ミス・表記ゆれを減らすため。 ↩︎

  3. Excelでいう配列数式。ExcelだとCtrl + Shift + Enterで確定するが、Googleスプレッドシートでは代わりにArrayformula関数を使う。If関数などと組み合わせるときにAND条件やOR条件を書こうとすると書き方に注意が必要。ここでは省略する。 ↩︎

  4. 時間経過や空き状況に応じて選択肢として回答に入れるか入れないかは今回GAS内で判断したが、ワークシート関数でも算出可能。 ↩︎

  5. どうやら回答を事前入力したＵＲＬで設定されている質問IDと異なる値が必要なようだ。 ↩︎

Discussion