プロローグ

イベントでもワクチンの接種でもなんでもいいですが、事前に登録・予約したお客さんが当日その予約番号が書かれた紙やスマホの画面を見せて、スタッフが手元にある名簿と照合する、という光景を見たことがあります。何人といる名簿の中から予約番号を検索してチェックをつける、という作業を人力でやるって結構めんどくさいです。自分がとあるイベントの運営に携わっていたときは、Google Apps Script(GAS)を使って本人確認がサクッとできたので、その紹介です。

用意するもの

(A)...名簿のGoogleスプレッドシート
(B)...参加者が送る本人確認のGoogleフォーム
(C)...(B)の回答が集められたGoogleスプレッドシート
(D)...(C)に拡張機能として追加するApps Script

流れとしては、

(A)に事前の予約情報が記載されている
参加者が(B)に必要事項を入力し、送信する
送信された情報が(C)に記載される
(D)が起動し、(B)で送信された情報と(A)とを照らし合わせる
(A)と(B)のユニークな情報（予約番号など）が一致するなら本人確認成功、そうでないなら失敗のメールを、参加者に送る
スタッフは参加者のメールを見て確認終了

ちなみにこれらのファイル・スクリプトは全て同じGoogleアカウントで作成すべきです。また、当然と言えば当然ですがメールの送信はフォーム並びにスクリプトを作成しているGoogleアカウントから行われるので、アカウントの選択には注意してください。以下、各ファイル・スクリプトについて説明していきます。

ファイルの作成

(A)名簿

名簿についても、事前予約のフォームを送ったら勝手に登録されるみたいなシステムを作るのは可能だと思いますが、今回は名簿の作り方については省略し、形式だけ説明します。

といっても、照合に使えるユニークな情報が入っていればそれ以外は何でも良いと思います。以下は一例です。

予約番号	氏名	年齢	電話番号	予約日	予約時間
A100001	東京太郎	40	090-XXXX-XXXX	10/01	10:00
A100002	大阪次郎	30	080-YYYY-YYYY	10/02	11:00

当然ですが、個人情報を含む名簿の公開範囲については充分注意しましょう。

(B)フォーム

照合に用いる情報を確実に入力してくれるような仕様にする必要があります（回答の検証->正規表現など）。また、本人確認結果を送る先のメールアドレスも入力してもらいましょう。あとは、感染症対策の一環として体調に関する情報が欲しければ、体温などを入力してもらうなど、カスタマイズしてください。なお、「質問」の文章はなるべく簡潔にし、詳しい説明は「説明」に書いた方がいいです。理由は後ほど説明します。画像はその一例です。

(C)回答結果

送信された(B)の回答結果をスプレッドシートにまとめることができます（方法は調べてすぐわかると思うので省略します）。このとき、「質問」の文字列がそのまま１行目のセルに出力されます。このスプレッドシートはフォームが送信されるたびに行が自動で増えていくので、特にいじる必要はありません。

(D)スクリプトの作成

コード作成

まずは(C)の編集画面から、「拡張機能 -> Apps Script」でエディタを起動します。すると空の myFunction があると思うのでこれを実装していきましょう。流れとしては、以下のような感じです。

(B)フォームの情報を取得
(A)名簿の予約番号一覧を取得
1.と2.で取得してきた予約番号が一致するか確認
メールの文面を作成
(B)で回答されたメールアドレスにメールを送信

フォームの情報を取得

function myFunciton(form){
    var userReserve = form.namedValues['予約番号'][0];
    var userMailaddress = form.namedValues['メールアドレス'][0];
    var userName = form.namedValues['氏名'][0];
}

まず、 myFunction() の括弧の中に引数を入れてください、名前はなんでもいいです。トリガーをフォーム送信時にすることで（トリガーについては後ほど説明します）、第一引数にフォームで送信された情報が入るので、 .namedValues['keyname'][0] でそれを取得できます。フォームの「質問」を簡潔にしたほうがいいのは、ここでキー名を書くときに見やすいためです。

予約番号一覧を取得

// myFunction内（以下のコードブロックでも同様）
    var sheetId = '(名簿のスプレッドシートのID)';
    var file = SpreadsheetApp.openById(sheetId);
    var reserveRawData = file.getSheetByName('シート1').getRange('A2:A100').getValues();
    var reserveData = [];
    for(var i = 0; i < reserveRawData.length; i++){
        reserveData.push(reserveRawData[i][0]);
    }

なんとなく何をやっているのかわかるのではないでしょうか。該当するIDのGoogleスプレッドシートを開き、シートの名前とセルの範囲を指定してデータを得ます。 getValues() で得たデータは [[A100001], [A100002]]のような2次元配列になってしまっているので整形しています。

実際に使う場合は文字列のところを変更してください。まず、スプレッドシートのIDですが、これはURLから取得できます。URLは https:/docs.google.com/spreadsheets/d/XXXXXXX/editYYYY となっているはずなので、その XXXXXXX の部分をコピペしてください。続いてシート名ですが、(A)のシート名を何もいじっていなければ'シート1'となっているはずですが、変えた場合は確認してください。最後にセルの範囲は、予約番号が記載されているところを指定しましょう。今回は一番左でヘッダ行があるのでA2からということにしています。

予約番号が一致するか確認

    var userIndex = reserveData.indexOf(userReserve);
    var entryFlag = userIndex === -1 ? false : true;

javascriptに馴染みがあればわかると思いますが、フォームの予約番号が名簿の予約番号の配列に存在するかどうかを探しています。名簿に何千万人と登録されているなら二分探索を使った方がいいと思いますが、そうでないなら充分高速に動くと思います。入場を許可する entryFlag は他にも、予約日とフォーム送信日が一致していなかったら false にする、体温が37.5より高かったら false にする、など(A)や(B)の情報を用いて条件をカスタマイズできます。例えば予約日が(A)のE列に登録されているなら、以下のように取得できます。

    var reserveDay = file.getSheetByName('シート1').getRange(`E${userIndex+1}`).getValue()

メールの文面を作成

    var mailSubject = '件名をここに';
    var mailBody = `<div>${userName}様</div>`;
    if(entryFlag){
        mailBody += `<h1 style="color: green">照合完了</h1>` // 照合成功時の文面
    }
    else{
        mailBody += `<h1 style="color: red">照合失敗</h1>`// 照合失敗時の文面
    }

あとは、自由にカスタマイズしましょう。なお、HTMLメールを送信することも可能なので、本人確認ができたときと失敗した時で文面の色を変えるとスタッフが確認しやすいでしょう。

メール送信

残念ながら、GASのメール送信の実行回数には１日あたりの制限があります。上限を超えてしまった場合は下書きに文面を作成しておいて、スタッフが1人下書きボックスに張り付いて手動で送信する、という手法を取る必要があります（それでもボタンを２回ほどタップするだけなので、人力での照合よりは楽でした）。

    if(MailApp.getRemainingDailyQuota() >= 1){
        MailApp.sendEmail({
            to: userMailaddress,
            subject: mailSubject,
            body: mailBody,
            htmlBody: mailBody
        });
    }
    else{
        GmailApp.createDraft(
            userMailaddress, mailSubject, mailBody, {
                htmlBody: mailBody
            }
        );
    }

関数の意味は何となくお分かりかと思います。残りの送信件数が1以上残っていれば、スクリプトにメールを送信してもらい、そうでなければ下書きに保存してもらいます。

トリガー追加

コードは完成しましたが、これだけでは動きません。スクリプトが実行されるタイミングを指定するトリガーを設定する必要があります。画面左の時計マークをクリックし、

右下の「トリガーを追加」をクリックし、イベントの種類を「フォーム送信時」に変更します。

そうすると、権限を許可してくださいみたいな画面が出てくるので、OKを押します。

トリガーが追加されれば完了です！お疲れ様でした。さっそくフォームを送信して試してみましょう（メールアドレスは必ず自分のでテストしましょう）。

失敗する場合

権限を手動で追加する必要があるかもしれません。Scriptの画面左の歯車マークをクリックし、『「appscript.json」マニフェストファイルをエディタで表示する』にチェックを入れてください。その後エディタに戻ると、appsscript.jsonが表示されていると思うので、それを開き、以下を追加してください。

"oauthScopes": ["https://www.googleapis.com/auth/script.send_mail",
                  "https://www.googleapis.com/auth/spreadsheets",
                  "https://www.googleapis.com/auth/gmail.modify",
                  "https://www.googleapis.com/auth/gmail.send",
                  "https://www.googleapis.com/auth/gmail.compose",
                  "https://mail.google.com/",
                  "https://www.googleapis.com/auth/gmail.addons.current.action.compose",
                  "https://www.googleapis.com/auth/drive.readonly",
                  "https://www.googleapis.com/auth/drive"
  ],

コード.gsに戻り、１回実行します。そうすると、権限の確認がまたされるのでそれを承認して、再度フォームを送ってみてください。

エピローグ

いかがだったでしょうか。送信元のアカウントや送信数の上限などいくつか制約はありますが、短時間でそれなりのものを作るにあたっては機能が充実していると思いました。この記事が自動化の第一歩のヒントになれば嬉しい限りです。ここまでお読みいただきありがとうございました！

参考