🗳️

GASで投票システム(個別内容を自動返信)に挑戦してみた

2022/09/17に公開

Google スプレッドシート

前提

Google Workspaceを利用
非エンジニア・プログラマなので、もっといい書き方や冗長ではない別な方法があると思います
あくまでも疑似的な匿名投票になります

スキルレベル的な

GAS未経験。大昔に大学の授業でVBAとか触ったり、極稀にArduinoとかでのLチカをしたり、簡単な電子工作をしたり、ProcessingやAppleScriptで遊んだりLINE Botに挑戦したり。
あとWPとかHTML, CSS触ったことある(一からのコーディングはあまり)レベルの初心者。

なぜ作成したか

Google Formの回答者に自動でのメール配信に関しては沢山事例が見つかったが、回答者に個別の内容を指定してメールを配信するタイプのものが見当たらなかったため
他アドオン・拡張機能なども検討はしたが、割り当てコードの条件を指定できなかったり、コードのログがこちらには残らずそのコードを検証に利用できない
1日の実行数に制限があり利用には適さなかった
件数や投票期間の長さなどを踏まえると手動/人力での対応は無謀
フォームが2段階になるため、なるべく離脱しないよう即座にコードを発行したかった
などなど

全体の構成（ざっくり）

事前に配布するコードを準備しておく
Google Form (その1)で、本人確認用に情報を入力してもらい、ユニークなコードを回答者に発行・メール送信
Google Form (その2)で、上記コードを入力しての投票

使用したもの

Python : ユニークな英数字のコードを生成（重複確認済）
Google Colaboratory : 上記PythonはColaboratory上で実行
Google Form : 2つのフォームを利用
Google Spreadsheet : 上記フォームの回答と発行するコードの保管用(計3つ)
Gmail : 検証とメール送信元のアカウント

最終的に作成したもの

目的	ファイル種類	ファイル名前
一意なコードの生成	Python, Colaboration上に	UniqueCodeGen
生成したコードの保管	Spreadsheet	YYYYMMDD_UniqueCode
(その1) コード発行用フォーム	Google Form	コード発行フォーム
コード発行用フォームの回答	Spreadsheet	コード発行フォーム(回答)＋Script
(その2) 投票用フォーム	Google Form	投票フォーム
投票用フォームの回答	Spreadsheet	投票フォーム(回答)

作り方

1. ユニークなコードの生成

ここは初っ端から写経。Googleフォームを使った簡易電子投票システム
但し、桁数を10→15桁に。生成するコード数も1000→2000に変更しました

UniqueCodeGen

# 英大文字だけからなる15文字の長さのコードを2000個作る

import secrets # randomよりも安全 
import string

codes = []
for i in range(2000):  # 2000個のコードを作る
  while True:
    c = ''.join(secrets.choice(string.ascii_uppercase) for x in range(15)) # 数字だけならstring.digitsにする。range(15)が文字数。
    if c not in codes: # 念のため重複チェック
      print(c)
      codes.append(c)
      break

上記をColaboratoryの［ファイル＞ノートブックを新規作成］にコピペし、実行。
ターミナル部分に表示、生成された2000個のコードを新規Spreadsheetに貼り付ける
※このSpreadsheetは後で使うので、ファイル名を設定し、開いたままにしておきます
※今回はこのSpreadsheetのファイル名を 「日付_UniqueCode」 としました

2. Scriptをどこに書くか、検討と下準備

(その1)コード発行フォームに回答があった時に自動的にメールを配信したい
アドオンや拡張機能は、Formの方に追加するタイプが多くあったが、回答のデータが保管されるのは作成したスプレッドシートなので、今回はスプレッドシートにScriptを作成することに。
※一応、Form本体にもScriptを組んではみたものの、トリガー関連なのか、そもそもフォームは質問をする場であり回答を保持する場ではないためか、上手くいかなかったです

この後、Scriptを動かすために、適切な質問と回答を確認します

設問	理由
メールアドレス	このメールアドレス宛に送付するため
氏名	会員情報との照合用に
会員番号	会員情報との照合用に

必要最低限だとこんな感じでしょうか

3. 各フォームとScript

3.1. (その1)コード発行フォームの作成

3.1.1. 前項目で確認した設問を含むフォームを新規作成します。
3.1.2. フォームの設定として、画面中央の［設定タブ＞回答］の「メールアドレスを収集する」以外をOFFにします

メールアドレスを収集する　ON
回答のコピーを回答者に送信 OFF
回答の編集を許可する OFF
回答を1回に制限する OFF

3.1.3. 次に、［設定タブ＞プレゼンテーション］も確認します。こちらも基本的に全てOFF

別の回答を送信するためのリンクを表示　OFF
特に上記がちゃんとOFFになっているのを確認しておきます

3.1.4. 画面中央の［回答＞Spreadsheet作成アイコン］をクリックして、回答用のSpreadsheetを作成します。
ここにScriptを書くので、念のため新規で作成しました 「コード発行フォーム(回答)」

3.2. (その2)投票フォームの作成

3.2.1. その1の回答者に送付するメール内に、この投票フォームの回答URLを入れ込みたいので、事前にフォームの準備をしておきます。「投票フォーム」「投票フォーム(回答)」
※回答のSpreadsheetは必須ではないですが、一応作成しています
3.2.2. こちらはシンプルに設問は2つ。発行されたコード入力部分と投票(ラジオボタン)です
3.2.3. コードの検証部分が条件にあっているかを正規表現で確認しておきます

3.2.4. 次に、英大文字15文字の発行コードをコピペや手入力してもらうのは面倒＋間違い頻発しそうなので画面右上部のメニューから ［事前入力したURLを取得］ を選択

3.2.5. コード部分に生成したコードを入力して、下部にある「リンクを取得」をクリック
※サンプルなのでコードは適当に入れています

すると更にポップアップが表示されるので、リンクをコピーします

3.3. 通知用のURLを作成

3.3.1. 最初に1. で作成した 「YYYYMMDD_UniqueCode」 のSpreadsheetを開きます
3.3.2. 先程3.2.5. でコピーしておいた、(その2)「投票フォーム」 の事前入力したURLを生成コードの隣の列にペーストします

※Codeはダミーです
3.3.3. https～続くURLの中から、フォームのURL・ID・コード部分とを探します

forms/d/e/ の後がこのフォームのURL部分
entry. の後が設問指定部分
=の後が事前に入力したコード部分

URLを貼り付けたセル内を次のように変更します

冒頭に=CONCAT("を追加、=の後のコード部分(ABCDEFGHIJKLMNO)を削除し", A2)でコードのセルをA2に指定し閉じる。

=CONCAT("https://docs.google.com/forms/d/e/ フォームのID(そのまま) /viewform?entry. 設問番号(そのまま) =", A2)

URLが入力されたB2のセル右下のポイントをクリック+ドラッグして、左隣のセル内容を含む事前のURLを全件分作成しておきます

3.4. 自動返信Scriptの作成

3.4.1. 3.2. で作成した (その2)「投票フォーム(回答)」 のSpreadsheetを開きます
3.4.2. 画面上部の［拡張機能＞Apps Script］を選択

3.4.3. 自動返信のScriptを書いて、プロジェクトを保存。

sendCode

function sendCode(e){
   console.log(e.values)
   const answeredEmail = e.values[1] // フォームの回答のメールアドレスセルを指定 2列目
   const answerdName = e.values[3] // フォーム回答の名前を指定 4列目
   const title = "コードを発行しました" // 自動配信メールの件名を指定

   // フォーム回答先のスプレッドシートをIDで指定
   const ss = SpreadsheetApp.openById(' Form回答のSpreadsheet ID　')
   let sheet = ss.getSheets()[0];

   let lastrow = sheet.getLastRow();
   console.log(lastrow);
   let lastrowNum = Number(lastrow);
   
   // 配布するUniqueCode一覧のSpreadsheetをIDで指定
   const sscode = SpreadsheetApp.openById('　UniqueCodeのSpreadsheetのID　')
   let sheet2 = sscode.getSheets()[0];

   // 取得するセルの位置をフォーム回答に併せてずらしていく
   for(var i = 2; i <= lastrowNum; i++){
       var randomCode = sheet2.getRange(i, 1).getValue()
       var randomCodeUrl = sheet2.getRange(i, 2).getValue()

       // メールのオプションを指定する（配信元メールアドレスと名前）
       var options = {
         name : '事務局',
         from : ' 配信元のメールアドレス　'
       };

       // メール本文を下記`内に直接記載
       var body = `
       ${answerdName} 様
       
       このメールは自動送信メールです。
       あなたの投票コードは「 ${randomCode} 」です。
       下記投票用URLから、投票をお願いいたします。
       投票期間は○月☓☓日～○月☓☓日までです
       
       ※URLには投票コードが含まれています。他の方へのシェアはしないようお願いします。
       ${randomCodeUrl} 
       
       事務局
       `
   }
   GmailApp.sendEmail(answeredEmail, title, body, options)
}

多分、上記のように何度もSpreadsheetの内容を取得しなくても大丈夫なような気がします。
あとちゃんとValueを取得すれば文字列Stringを数値に変換しなくてもよいかもしれません
試行錯誤した結果、途中で取得できていなかったので、色々直しながら編集した結果こうなりました。
また、メール本文を別ドキュメントで指定する方法もあったのですが、本文もそこまで長くもないこと。管理するファイルが多くなるのも煩雑だったこと。一度利用した値を再利用することができなかった(多分書き間違いですが)ため、念のためScript内に直接記載としました

forループ内の抜け方や、constとvar、letの使い分けなど、力技感が満載なので、もっと適した書き方はあると思います！絶対あるはず
あとインデントとかはその時の参考にしたものをベースにしています
とりあえず、素人ががんばった記録として

3.5. トリガーの設定

Apps Script画面左側にある「目覚まし時計のようなアイコン」よりトリガーの設定をします

右下にある青い「+トリガーを追加」ボタンをクリック
実行する関数を選択(先程書いたコードの冒頭function sendCode(e)の関数名)
実行するデプロイを選択：Head そのまま
イベントのソースを選択：スプレッドシートから そのまま
イベントの種類を選択：起動時▶フォーム送信時 に変更

設定したら保存します

以上、実行・デバッグを繰り返しながら作業してみたのですが
その際面倒だったのが、フォームにいちいち回答を入力し直さないと検証できないことが大変でした
アドオンだと同じメールアドレスに対して、送信されないこともあると見たので、検証用のメールアドレスはシークレットウィンドウでコード発行フォームを開いておき、Googleの「メールアドレス+番号＠gmail.com」をして検証に利用しています

参考にした記事・URL

https://www.log-stocker.com/gas/729/

最後に

力技で冗長ではありつつも、なんとか動くScriptが組めたので記録として書き留めておきます
集計や検証部分はまた今度記録用に書けたらいいなとは思っています