開発にあたって
半年前、ミニ電車（５インチゲージの体験乗車会）の運営で、文化祭等の大規模イベントの際に多くの人を捌くために、GoogleFormsとスプレッドシートを利用した整理券発行システムを作れないかと考えた。
仕様
・一定人数ごとに整理券番号を自動で付与する（例えば5人目までの予約番号を１としたとき、次の６人目の予約の際番号を繰り上げて番号２を付与する）
・GoogleFormsでメールアドレスと予約人数を指定し、送信すると自動返信メールで整理券番号を割り当てる。
開発方法
GoogleFormsの準備
まずはGoogleFormsを開き、アンケートを作成する。
作成が終わったら、回答方法の収集のためにスプレッドシートリンクさせる。
スプレッドシートの準備
新規でスプレッドシートを用意する。
１行目のセルに乗車人数・整理券番号を出力するセル・人数を合計するセル・人数を判定し指定に人数を超える場合は２を出力するセルを作成する。
また、A2のセルには次のように入力する
=arrayformula(importrange("スプレッドシート識別子","C2:C1304"))
//使用方法
=arrayformula(importrange("スプレッドシート識別子","セルの範囲"))
これは先ほど作成したGoogleFormsとリンクしているスプレッドシートから情報を抜き出すもので、この場合は乗車人数の書かれているC2～C1304までの情報を抜き出して記入してくれるありがたいものである。
スプレッドシート識別子とは、スプレッドシートのリンク"https://docs.google.com/spreadsheets/d/ *** /edit?usp=sharing"の***が該当箇所であり、それをコピーする。
整理券番号を計算で求める
整理券番号を計算する式を作る。
今回用いた仕組みは単純で、アンケートから入力された乗車人数を規定人数の８になるまで足し続けて、８以上になったときは足すのやめて、その足す人数をそのまま入力しもう一度８になるまで足し続けるものである。この動作を式に表すと、
=If(sum(C2+A3)<=8,C2+A3,A3)
この式をC3から下のセルに適用した。（C2は初期値なので、A2の値をそのまま代入させる）
このまま人数を足し続けるだけの情報だけでは、整理券番号の変わる箇所が分かりづらいので次の式をD2から下のセルに適用する。
=if(C3-C2>0,1,2)
この式は一個前のセルと比較して正の数なら１、負の数なら２を返すもので、負の数の場合は整理券番号が変わることを表す。（乗車人数を足すときの計算式を思い出して欲しい）
これで整理券番号が変わる際を検知できるようになったので、整理券番号を出力するセルに次のように記入する。
=if(A3="","", if(D2=2,B2+1,B2))
最初にA3が空欄の際の処理を入れたのは、後にマクロを記述する際に楽に記述できるようになるからであり、今は気にする必要がない。（B2のセルに整理券番号の初期値１を入れるのを忘れずに）
これで整理券番号を計算で出すことができた。
マクロの記述
整理券番号の計算で用いたスプレッドシートのマクロを作成する。
拡張機能からApps Scriptを開く。
先に記入したコードを以下に示す。
function TicketNum(e) {
var spreadsheet = SpreadsheetApp.openByUrl("スプレッドシートのURL");
console.log(spreadsheet.getName());
var sheet = spreadsheet.getSheetByName("main"); //スプレッドシート内のシートmainを取得
Utilities.sleep(3000);
const FValues = sheet.getRange('B:B').getValues(); //B列の値を全て取得
const LastRow = FValues.filter(String).length; //空白の要素を除いた長さを取得
Logger.log(LastRow); //空白を除いた整理券番号の列の最終行を表示
const mainnum =sheet.getRange(LastRow,2).getValue();
console.log(mainnum);
var URL ="formのURL";
const form = FormApp.openByUrl(URL);
let TimeStamp = e.namedValues["タイムスタンプ"][0];
let Email = e.namedValues["メールアドレス"][0];
let Subject = "【自動返信】" + "きっぷ事前予約システム「らくQ」予約完了メール";
let Body = "５インチゲージの乗車予約、誠にありがとうございます。" + "\n" +
"\n" +
"整理券番号を発行しましたので確認の上、指定された時間に会場へお越しください。" + "\n" +
"\n" +
"整理券番号："+mainnum +"\n";
"メールアドレス：" + Email + "\n" +
"フォーム送信日時：" + TimeStamp + "\n";
GmailApp.sendEmail(Email, Subject, Body);
簡単に解説すると、
var spreadsheet = SpreadsheetApp.openByUrl("スプレッドシートのURL");
console.log(spreadsheet.getName());
var sheet = spreadsheet.getSheetByName("main");
の部分で指定されたスプレッドシートを読み込む。
Utilities.sleep(3000);
const FValues = sheet.getRange('B:B').getValues(); //B列の値を全て取得
const LastRow = FValues.filter(String).length; //空白の要素を除いた長さを取得
Logger.log(LastRow); //空白を除いた整理券番号の列の最終行を表示
const mainnum =sheet.getRange(LastRow,2).getValue();
console.log(mainnum);
ここでは整理券番号が記入されてるB列の値を一旦すべて読み込んで、空白を除いて整理券番号が書いてある最後の行の数を読み取っている。これで整理券番号が書いてあるセルが特定できたので、セルの番地を指定してからその場所の値を読み取って変数mainnumに入れる。
var URL ="formのURL";
const form = FormApp.openByUrl(URL);
let TimeStamp = e.namedValues["タイムスタンプ"][0];
let Email = e.namedValues["メールアドレス"][0];
let Subject = "【自動返信】" + "きっぷ事前予約システム「らくQ」予約完了メール";
let Body = "東京高専鉄道同好会の５インチゲージの乗車予約、誠にありがとうございます。" + "\n" +
"\n" +
"整理券番号を発行しましたので確認の上、指定された時間に会場へお越しください。" + "\n" +
"\n" +
"整理券番号："+mainnum +"\n";
"メールアドレス：" + Email + "\n" +
"フォーム送信日時：" + TimeStamp + "\n";
GmailApp.sendEmail(Email, Subject, Body);
GoogleFormsと連携させ、メールを送信するコードを記入する。
最後にトリガーを設定し、完成である。
参考資料
https://tatsuya-note.com/gas-autoreplymail-forms/
実行権限について
実行したとき権限がない原因のエラーが生じた時は、jsonファイルを書き換える。
{
"timeZone": "Asia/Tokyo",
"dependencies": {},
"exceptionLogging": "STACKDRIVER",
"oauthScopes": [
"https://www.googleapis.com/auth/spreadsheets.currentonly",
"https://www.googleapis.com/auth/spreadsheets",
"https://www.googleapis.com/auth/spreadsheets.readonly",
"https://www.googleapis.com/auth/userinfo.email",
"https://www.googleapis.com/auth/forms",
"https://www.googleapis.com/auth/script.send_mail",
"https://www.googleapis.com/auth/gmail.modify"
],
"runtimeVersion": "V8",
"webapp": {
"executeAs": "USER_DEPLOYING",
"access": "MYSELF"
}
}
実行権限を許可するにはセキュリティの承認画面が出てくる。これをすべて許可することで、プログラムが動く。