Googleフォーム＋αでメールアドレスの自動登録システムを作ろう

はじめに

この記事では Googleフォームやその周辺技術を駆使して、メールアドレスの自動登録システムを作るための仕組みをコードベースで説明していきます。

JavaScriptとWebのURLパラメータが大丈夫ならほぼ読めると思いますが、ワンオフで書いたコードのため雑な部分があります。ご覚悟を。

始めた理由：手動でメアドを集めるのは面倒

Windows10 アプリのテスター募集をしようとした際、当初ツイッターで参加者のメールアドレスを一人ひとりコンタクトをもらって登録していました。全手動です。そうすると当然…

「ぐっへぇ、手動面倒くさ、その時間開発に割きたいわ…」

ということでメールアドレスの収集を自動化するような仕組みを

• Google フォーム
• Google スプレッドシート
• Google Apps Script

で組みました。

（以降は淡々とコード含めて進むので、気になるところだけピックアップして読むといいと思います）

要件

• Googleフォームからメアドの登録ができる
• メアドの所有者確認が必要
• 登録解除もサポートしたい

構成

登録・解除の流れ

• Google FormからSpread Sheetに申請メールアドレスを書き込む
• 申請メールアドレス宛にアクティベーションコードを送信する
• アクティベーションコードを受け取ったら、申請メールアドレスを確認済みメールアドレスとして登録
• 確認済みメールアドレスに登録完了通知＋解除コードを送信する
• 解除コードを受け取ったら、確認済みメールアドレスを削除する（削除の確認画面付き）

定期処理

• 一定期間以内にアクティベートされなかった申請メールアドレスを定期削除
• 確認済みメールアドレスをcsvファイルとして出力

Google Formでメアドを申請する

Googleフォームを新規作成して、以下のような形でメールアドレスだけを入力できるテキストボックスを設定します。

あとは、フォーム編集画面→回答タブから、回答をスプレッドシートを出力するように設定するだけでOK

ここから先では回答で送信された情報を扱う「仮登録」シート、アクティベートが完了した情報を扱う「本登録」シートとして記述しています。

アクティベーションコードを申請メアドに送信

スプレッドシートのツール→スクリプト エディタからスプレッドシート向けの処理を書いていきます。

フォームから送信されたらアクティベーションコードをメアドに送信

var gasheet = "スプレッドシートのID";
var API_URI = "https://script.google.com/macros/s/APIのID/exec";

function onFormSubmit(args)
{
  Logger.log(args);
  
  try 
  {

    var range = args.range;
    var sheet = range.getSheet();
    var row = range.getRow();
    var timeStamp = args.values[0];
    var mailAddress = args.values[1];
    
    Logger.log(mailAddress);
    
    var ss = SpreadsheetApp.openById(gasheet);
    var kari_sheet = ss.getSheetByName("仮登録");
    var hon_sheet = ss.getSheetByName("本登録");
    
    // 既に 本 登録済みかチェック
    var deactivateCode = getCodeFromMail(hon_sheet, mailAddress);
    if (deactivateCode != null)
    {
      Logger.log("既に登録済みアドレスのため、解除用リンクを含んだメールを送信して処理を完了。");
      sendDeactivateMail(mailAddress, deactivateCode);
      kari_sheet.deleteRow(range.getRowIndex());
      return;
    }
    
    // 既に 仮 登録済みかチェック
    var activationCode = getCodeFromMail(kari_sheet, mailAddress, row);
    if (activationCode == null)
    {
      Logger.log("アクティベートコードを生成");
      activationCode = randomString(24);
      // シートの３行目にアクティベートコードを書き込み
      var activateCodeRange = sheet.getRange(row, 3);
      activateCodeRange.setValue(activationCode);
    }
    else 
    {
      Logger.log("既に仮登録済み");
      kari_sheet.deleteRow(range.getRowIndex());
    }
    
    Logger.log("activate code:" + activationCode);
           
    // アクティベートコードをmailAddressに送信
    var activateUrl = API_URI + "?mode=activate&code=" + activationCode;
    var encodedUrl = encodeURI(activateUrl);
    
    Logger.log(activateUrl);
    Logger.log(encodedUrl);
    
    MailApp.sendEmail({
      to: mailAddress,
      subject: 'Hohoemaテスター登録を確認してください',
      body: '次のリンクにアクセスすることでHohoemaテスター登録が完了します ' + encodedUrl, 
      htmlBody: '<p>次のリンクにアクセスすることでHohoemaテスター登録が完了します</p><a href="'+encodedUrl+'">このメールアドレスをHohoemaテスターとしてアクティベート</a>' 
    });
    
    Logger.log("mail send to " + mailAddress);
  }
  catch (e)
  {
    Logger.log("エラー");
    Logger.log(e);
  }
}

コード用のランダム文字列生成

// アクティベーションまたは解除のためのコードを生成する
// code from http://stackoverflow.com/questions/1349404/generate-random-string-characters-in-javascript
function randomString(len, charSet) {
    charSet = charSet || 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
    var randomString = '';
    for (var i = 0; i < len; i++) {
        var randomPoz = Math.floor(Math.random() * charSet.length);
        randomString += charSet.substring(randomPoz,randomPoz+1);
    }
    return randomString;
}

アクティベーションコードと解除コードを受け取るAPI

Google Drive上でスクリプトファイルを新規作成してAPI用のコードを書いていきます。

このスクリプトファイルから公開するAPIのIDを「API_URI」に組み込んで利用しています。

スクリプトプロジェクトの「公開」から「ウェブアプリケーションの導入…」からAPIを公開して、APIのIDを取得します。

doGetの返り値としてブラウザに表示するHTMLコンテンツを返すようになっています。

APIのパラメーターとして

• mode：activate または deactivate
• code：ランダム文字列
• ensure：解除確認済みフラグ

を受け取ります。

modeとensureを元に条件分岐させつつ関数に処理を小分けにしています。

function doGet(args) {
  
  Logger.log(args);

  var mode = args.parameter.mode;
  var code =  args.parameter.code;
  var ensure = args.parameter.ensure == "1" || args.parameter.ensure == "true" || args.parameter.ensure == "on";
  
  var resultHTML = "?";
  try 
  {
    if (mode == "activate")
    {
      Logger.log("mode: activate");
      
      resultHTML = activate(code);
    } 
    else if (mode == "deactivate")
    {
      Logger.log("mode: deactivate");
      
      resultHTML = deactivate(code, ensure);
    }
    else 
    {
      throw "登録解除のモード指定が不正です" + mode;
    }
  }
  catch (ex)
  {
    Logger.log("error.");
    Logger.log(ex);
    resultHTML = HtmlService.createHtmlOutput('<p>Hohoemaテスター登録（解除）に失敗しました。</p><p>エラーメッセージ：' + ex + '</p>');
  }
  
  Logger.log("done.");
  
  return resultHTML;
}

アクティベートとデアクティベート

// アクティベート
// アクティベートコードを受け取って仮登録シートに該当コーどが存在する場合
// 該当メールアドレスを「本登録」シートに新規行として書き込み
// 本登録シートへは同時に、解除コードを生成して
// 該当メールアドレスに登録完了の旨と一緒に解除用リンクを送付する
// 最後に登録完了の旨のHTMLを返しておしまい
function activate(code)
{
  var ss = SpreadsheetApp.openById(gasheet);
  var kari_sheet = ss.getSheetByName("仮登録");
  var hon_sheet = ss.getSheetByName("本登録");
    
  var codeContainRange = getCodeContainRange(kari_sheet, code);
  if (codeContainRange == null)
  {
    throw "このアクティベートコードは無効です。 code : " + code;
  }
  
  var values = codeContainRange.getValues()[0];
  
  Logger.log("アクティベート対象を発見");
  Logger.log(values);
  
  var timeStamp = values[0];
  var mailAddress = values[1];
  var existCode = values[2];
  
  Logger.log("アクティベート対象を本登録シートへ書き込み、開始");
  var hon_sheetLastRow = hon_sheet.getLastRow();
  hon_sheet.insertRowAfter(hon_sheetLastRow);  
  var hon_sheetNewRow = hon_sheet.getRange(hon_sheetLastRow + 1, 1, 1, 3);
  var hon_sheetValues = [].concat(values);
  
  var deactivateCode = randomString(24);
  
  hon_sheetValues[2] = deactivateCode;
  Logger.log(hon_sheetValues);
  hon_sheetNewRow.setValues([hon_sheetValues]);
  
  Logger.log("アクティベート対象を本登録シートへ書き込み、完了");
  
  Logger.log("アクティベート完了をメール送信、開始");
  
  // 載せるべき内容
  // 解除用リンク
  // テスト参加者向けの説明ページ
  
  var deactivateUrl = encodeURI(API_URI + "?mode=deactivate&code=" + deactivateCode); // ■■■ TODO
  var testerNoticePageUrl = "";// ■■■ TODO
  var activatedHtmlTemplate = HtmlService.createTemplateFromFile('activated');
  activatedHtmlTemplate.Mail = mailAddress;
  activatedHtmlTemplate.DeactivateUrl = deactivateUrl;
  
  
  var activatedHtml = activatedHtmlTemplate.evaluate();
  activatedHtml.setTitle("登録完了 Hohoemaテスター登録");
  var mailHtml = activatedHtml.getContent();
  MailApp.sendEmail({
    to: mailAddress,
    subject: 'Hohoemaテスター登録完了（解除用リンク含む）',
    body: 'Hohoemaテスター登録が完了しました。\n 解除用リンク： ' +deactivateUrl + "\n テスト参加者向けの説明ページ：" + testerNoticePageUrl + "\n", 
    htmlBody: mailHtml 
  });
  
  Logger.log("アクティベート完了をメール送信、完了");
  
  Logger.log("アクティベート完了");
  
  kari_sheet.deleteRow(codeContainRange.getRowIndex());
  
  return activatedHtml;
}



// 解除
// 解除コードを受け取って本登録シートに確認
// 存在する場合、解除確認用のフォームを表示、確認フラグ付きの解除リンクへアクセス
// 解除確認フラグ付きの場合、解除完了表示とメールで登録解除完了と通知しておしまい
function deactivate(code, ensure)
{
  var ss = SpreadsheetApp.openById(gasheet);
  var hon_sheet = ss.getSheetByName("本登録");
  
  var codeContainRange = getCodeContainRange(hon_sheet, code);
  if (codeContainRange == null)
  {
    throw "この解除用コードは利用できません。code: " + code;
  }
  
  if (ensure == true)
  {
    return realDeactivate(hon_sheet, codeContainRange);
  }
  else 
  {
    return makeEnsureDeactivateForm(codeContainRange);
  }
}

// 解除確認後、該当行の削除処理と削除完了HTMLを生成
function realDeactivate(hon_sheet, codeContainRange)
{
  var values = codeContainRange.getValues()[0];

  var mail = values[1];
  
  Logger.log("解除開始 : " + mail);
  
  var rowIndex = codeContainRange.getRowIndex();
  hon_sheet.deleteRow(rowIndex);
  
  Logger.log("解除処理を完了しました。：" + mail);
  
  var deactivatedHtmlTemplate = HtmlService.createTemplateFromFile('deactivated');
  deactivatedHtmlTemplate.Mail = mail;
  
  var html = deactivatedHtmlTemplate.evaluate();
  html.setTitle("解除完了 Hohoemaテスター登録");
  return html;
}

// 解除確認用のHTMLを生成
function makeEnsureDeactivateForm(codeContainRange)
{
  var values = codeContainRange.getValues()[0];
  
  var mail = values[1];
  var code = values[2];

  Logger.log("解除確認HTMLを作成: " + mail);
  
  var deactivatedHtmlTemplate = HtmlService.createTemplateFromFile('ensureDeactivate');
  deactivatedHtmlTemplate.Mail = mail;
  deactivatedHtmlTemplate.Code = code;
  deactivatedHtmlTemplate.Url = API_URI;
  var html = deactivatedHtmlTemplate.evaluate();
  html.setTitle("解除の確認 Hohoemaテスター登録");
  return html;
}

スプレッドシートからコードを取得する

// アクティベートコードまたは解除コードを含む列を取得する
// コードは３行目にあると決め打ちしています
function getCodeContainRange(sheet, code)
{
  const CODE_COLUMN = 3;
  
  var lastRow = sheet.getLastRow();
  var range = sheet.getRange(2, CODE_COLUMN, lastRow, 1);
  var values = range.getValues();
  for (var rowIndex  = 0; rowIndex < values.length; ++rowIndex)
  {
    var value = values[rowIndex][0];
    if (value == code)
    {
      var codeContainRowIndex = rowIndex + 2;
      Logger.log(codeContainRowIndex);
      return sheet.getRange(codeContainRowIndex, 1, 1, 3);
    }
  }
  
  return null;
}

テンプレートのHTMLは以下のような形で作成します。

HTMLファイルはAPIを公開するスクリプトプロジェクトの中に作成します。（「ファイル」→「新規作成」→「HTML ファイル」）

アクティベート完了通知メールの本文HTML

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
  
    <p><?= Mail ?> のHohoemaテスター登録が完了しました。</p>
       
    <p><a href="<?= DeactivateUrl ?>">登録を解除する場合はこちらへアクセスしてください</a></p>
    
  </body>
</html>

HTML内に = Mail ?> と書くことでテンプレートに文字列をインジェクトできます。

インジェクトしたいプロパティは、HtmlService.createTemplateFromFileで作成したテンプレートオブジェクトにプロパティを追加することで参照が通るようになります。
（下の登録の解除を見るとわかりやすいかも）

登録の解除

解除コードを受け取るAPIはアクティベーションと同じコードで処理されます。
mode=deactivateのとき、コードを解除コードとして認識して解除処理を進めます。

doGetの返り値として、解除の確認用のチェックボックスと送信ボタンを持ったHTMLを設定します。

HTMLテンプレートにプロパティをインジェクトするコード例

// 解除確認用のHTMLを生成
function makeEnsureDeactivateForm(mail, code)
{
  var deactivatedHtmlTemplate = HtmlService.createTemplateFromFile('ensureDeactivate');
  deactivatedHtmlTemplate.Mail = mail;
  deactivatedHtmlTemplate.Code = code;
  deactivatedHtmlTemplate.Url = API_URI;

  var html = deactivatedHtmlTemplate.evaluate();
  html.setTitle("解除の確認 Hohoemaテスター登録")
;
  return html;
}

ensureDeactivate.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <title>登録解除の確認 Hohoemaテスター登録</title>
  </head>
  <body>
    <p><?= Mail ?> のHohoemaテスター登録を解除しても、よろしいですか？</p>
     
    <!-- チェックボックス ＋送信フォーム-->
    <form method="get" action="<?= Url ?>">
    
      <input type="hidden" name="mode" value="deactivate" />
      <input type="hidden" name="code" value="<?= Code ?>" />
      
      <label>
      <input type="checkbox"  onchange="document.getElementById('send').disabled = !this.checked;"
      name="ensure" 
      />
      Hohoemaのテスター登録を解除します
      </label>
      
      <br />
      
      <input type="submit" name="send" class="inputButton" id="send" disabled="true" width="120" />
    
    </form>
  </body>
</html>

フォームからの登録申請、アクティベート、登録解除までの流れは以上になります。

そのまま書いていくと一部HTMLファイルが不足すると思いますが、解除コードHTMLの例を元に自作してもらえばなんとかなるかと思います。

定期処理

以下のスクリプトに含まれる関数を「編集」→「現在のプロジェクトのトリガー」から時間指定実行されるように登録することで、「アクティベート済みメアドのcsvファイル出力」と「有効期限切れのアクティベート待ちメールアドレスの削除処理」を仕掛けます。

時間設定はお好みでどうぞ。

mentainance

var gasheet = "メールアドレスを登録しているシートのID";

var targetFolder = "csvファイルを出力したいドライブのフォルダID";
var fileName = "testerMails.csv";

// アクティベート済みメールアドレスをCSV出力します
function OutputRegistratedMails()
{
  var ss = SpreadsheetApp.openById(gasheet);
  var sheet = ss.getSheetByName("本登録");
  var range = sheet.getRange(2, 2, sheet.getLastRow(), 1).getValues();

  var folder = DriveApp.getFolderById(targetFolder); 
  var files = folder.getFilesByName(fileName);
  
  // すでにあるcsvファイルを削除
  if (files != null)
  {
    while(files.hasNext())
    {
      file = files.next();
      folder.removeFile(file);
    }
  }
  
  // 出力可能なデータがない場合はファイルを作成しない
  if (range.length == 0)
  {
    return;
  }

  // テーブル情報をcsvテキストに変換
  var mails = [];
  for (var i in range)
  {
    var mail = range[i][0];
    mails.push(mail);
  }
  var csvData = mails.join(",\r\n");
  
  //blobデータをcsvファイルとしてドライブに保存
  var blob = Utilities.newBlob("", "text/comma-separated-values", fileName);
  blob.setDataFromString(csvData);
  var fileid = folder.createFile(blob).getId();
}



// 有効期限が切れた仮登録のメールアドレスを削除する
var expirationTime_Hour = 3;

function DeleteOverExpiration()
{
  var ss = SpreadsheetApp.openById(gasheet);
  var kari_sheet = ss.getSheetByName("仮登録");
    
  var lastRow = kari_sheet.getLastRow();
  
  var expirationTime = 60 * 60 * 1000 * expirationTime_Hour; // ミリ単位
  // タイムスタンプの行を取得
  var now = new Date().getTime();
  var range = kari_sheet.getRange(2, 1, lastRow, 1);
  var values = range.getValues();
  
  Logger.log("now: " + now);
  
  for (var rowIndex  = 0; rowIndex < values.length; ++rowIndex)
  {
    var value = values[rowIndex][0];
    if (value == null || value == undefined || value == "")
    {
      continue;
    }
    
    
    var parsed = Date.parse(value);
    Logger.log("parsed: " + parsed);
    var rowExpirationTime = Date.parse(value) + expirationTime;
    Logger.log("time: " + rowExpirationTime);
    if (rowExpirationTime < now)
    {
      var deleteRowIndex = rowIndex + 2;
      kari_sheet.deleteRow(deleteRowIndex);
    }
  }
}

GASのファイル取得はFilesのイテレータなのでwhileで回してます。（GASのJSバージョン古いのでforeachが使えないため）

日付はDate.parseで得られる数字が基準時からの経過ミリ秒のため、Date型ではなく数値で統一して扱ってます。

さいごに

自動化楽しい、生JavaScript辛い

WebやGASの基礎知識などすっ飛ばして書いている部分は補足説明も出来ますので必要でしたらコメントください。頑張って書きます。

最後になりますが、どんな時でも使いやすいGoogleサービスに大感謝です。

いいアプリ作らなきゃ（使命感）

宣伝

Windows 10専用のニコニコ動画プレイヤーアプリ「Hohoema」を作ってます
（この記事の自動登録システムもHohoemaのテスター募集に利用しています。）

Hohoemaのアプリページ
https://www.microsoft.com/ja-jp/store/p/hohoema/9nblggh4rxt6
（Windows10端末から開くとストアアプリが起動します。ご注意を）

Windows10のストアアプリからフル機能がずっと無料で使える試供版がダウンロード出来ますので是非お試しください。

（ドネーションウェアのつもりで料金設定したんですが、わかりづらくなってしまいました汗）

以上でほんとにおしまいです。最後までお読みいただきありがとうございました。