【改訂版】GAS Googleフォームで申込があったら抽選番号や申込番号を付与してメールで知らせるツール

good-sun（a03）

2021年11月23日 19:44

2021/12/06 追記

この記事の発展形となる下記の記事を公開しました。

以下、原文。

過去に書いたコードを改善していきます。

今回は「晩秋のペアプロ！」として、ノンプロ研の　@ID_HelpDesk　さんにお相手いただきました。感謝！

とっげったーリンク

過去に書いたコードの問題点

秒単位で同時にフォーム回答があった時に、番号が空白で、そのまま通知メールが送信されることがあった。

秒単位で同時にフォーム回答するというテストができないので、実地で発生した問題だった。

きっちり採番したり、通知するようなところでは、使用をおすすめできない。

過去に書いたコードとnote

フォームのコンテナバインドで書いていた。フォーム回答時をトリガーとしていた。

フォームに回答があったら、スプレッドシートの行番号を抽選番号としてセットして、それをメールで送る、という形であった。

/* 抽選番号を付与して自動メール返信する。トリガーセット。 */
function setSerialNumber() {
const spreadsheet = SpreadsheetApp.openById('YOUR SHEET ID');
const sheet = spreadsheet.getSheetByName('YOUR SHEET NAME');

/* 回答シートに連番セット */
var range = sheet.getRange(sheet.getLastRow(), 13);// 行列の指定は環境によって調整してね
if (range.isBlank() == true) {
  range.setValue("=ROW()-1");　//=ROW()-1　行数マイナス1の数式をシートに反映して、それが抽選番号になる
}
SpreadsheetApp.flush();　// シートに即時に抽選番号反映させるためのコード

/* ここからメール送信スクリプト */
const row = sheet.getLastRow();
const mail = sheet.getRange(row, 2).getValue();
const name = sheet.getRange(row, 5).getValue();
const lotteryNumber = sheet.getRange(row, 13).getValue();
const ownName = '抽選係'                // こちらの名前
const refUrl = '紹介URL' // 紹介URL
const title = "【抽選番号のお知らせ】"
//メール本文を作成
const body = `
${name} 様
この度は　抽選エントリーフォーム　にご回答いただきありがとうございます。
あなたの抽選番号は
${lotteryNumber}
です。
抽選については、こちらのURLをご覧ください: ${refUrl}
${ownName} より `
GmailApp.sendEmail(mail, title, body, {
  //  attachments: [file.getAs(MimeType.PDF)],
  //  bcc: 'メールアドレス', 
  //  cc: 'メールアドレス', 
  from: 'メールアドレス',
  name: ownName
});//メール送信
}

改善版のフォームとシートの構成

こんなフォームがあるとして、

その回答シート（原本）と、それとは別に作業用のシートを用意しておく。

改善版のコード

スプレッドシートのコンテナバインドで書いている。フォームのコンテナバインドではイベントオブジェクトがうまく取れなかったので。

後で直すメモ

===は==でないとだめ。
data[i][1]はstringで、mailはobjectだから、includeOf()がうまく行かなかった

function myFunction(e) {
 const eventObject = e;
 const array = e.values;
 const answerObject = e.namedValues;
 const spreadsheet = SpreadsheetApp.openById('YOUR SHEET ID');
 const sheet = spreadsheet.getSheetByName('YOUR SHEET NAME');
 
 /* 連番付与用シートにフォーム回答を貼り付け */
 sheet.appendRow(array);
 SpreadsheetApp.flush();
 
 /* ここからメール送信スクリプト */
 const mail = answerObject['メールアドレス'];
 const name = answerObject['お名前'];
 const data = sheet.getDataRange().getValues();
 let lotteryNumber = '';
 for (let i = 0; i < data.length; i++) {
   if (data[i][1] == mail) {
     data[i][5] = i;
     lotteryNumber = i;
   }
 }
 
 sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
 
 //メール差し込み内容
 const ownName = '抽選係' // こちらの名前
 const refUrl = 'https://紹介URL // 紹介URL
 const title = "【抽選番号のお知らせ】"
 
 //メール本文を作成
 const body = `
  ${name} 様
  この度は　抽選エントリーフォーム　にご回答いただきありがとうございます。
  あなたの抽選番号は
  ${lotteryNumber}
  です。
  抽選については、こちらのURLをご覧ください: ${refUrl}
  ${ownName} より `
  
 GmailApp.sendEmail(mail, title, body, {
   //  attachments: [file.getAs(MimeType.PDF)],
   //  bcc: 'メールアドレス', 
   //  cc: 'メールアドレスx', 
   from: 'メールアドレス',
   name: ownName
 });//メール送信
}

感想戦・ポイントなど

そうなのか～！と勉強になったポイントがたくさんあったのでメモする。

イベントオブジェクトから取得していくという発想

改良前のコードでは、スプレッドシートに反映されたフォームの回答内容と、スプレッドシートに書き込まれた連番を、メールに流す、という形を取っていた。

で、同時申込があった時に、連番が追い付かないのか、番号が空のままメールが送信される、range.setValue("=ROW()-1");　が反映されていない、というケースがあった。

改良版では、イベントオブジェクトを取得し、それを作業シートに転記し、連番を付与して、メール送信という流れ（あってる？）。イベントオブジェクトを取得するという発想が無かったですよ。

↑これの下の方に、↓こんな感じで　フォームの回答について、オブジェクトと配列でデータがある。へ～～Google先生さっすが気が利いてる～～～。

namedValues: 
  { 'メールアドレス': [ 'xxxxxx@gmail.com' ],
    'タイムスタンプ': [ '2021/11/23 17:16:53' ],
    'コメント': [ 'はろー' ],
    'お名前': [ '太郎' ],
    '希望商品': [ '賞品2' ] },
 range: { columnEnd: 5, columnStart: 1, rowEnd: 6, rowStart: 6 },
 source: {},
 triggerUid: 'xxxxxx',
 values: 
  [ '2021/11/23 17:16:53',
    'xxxx@gmail.com',
    '太郎',
    '賞品2',
    'はろー' ] }

イベントオブジェクトの定数化、test用function

テストで毎回フォームに回答するのがめんどう、ということで、こんなコードを書いておくという技。すげー。functionに入れずに、グローバルで const eするとだめでした。

function test() {
 const e = {
   namedValues:
   {
     'メールアドレス': ['xxxxxx@gmail.com'],
     'タイムスタンプ': ['2021/11/23 17:16:53'],
     'コメント': ['はろー'],
     'お名前': ['太郎'],
     '希望商品': ['賞品2']
   },
   range: { columnEnd: 5, columnStart: 1, rowEnd: 6, rowStart: 6 },
   source: {},
   triggerUid: 'xxxxxx',
   values:
     ['2021/11/23 17:16:53',
       'xxxx@gmail.com',
       '太郎',
       '賞品2',
       'はろー']
 }
 myFunction(e);
}

appendRow　で連番付与用のシートにデータを書き込んでいく

appendRowの存在をすっかり忘れていた。

オブジェクトのブラケット記法

イベントオブジェクトからブツを取得していきたいんだけど、ドット記法で書くとどうもだめ。そんな時に「ブラケット記法」という神の声が聞こえました（な～さんありがとうございます！）

ドット記法
const mail = answerObject.メールアドレス;
const name = answerObject.お名前;

ブラケット記法
const mail = answerObject['メールアドレス'];
const name = answerObject['お名前'];

配列で抽選番号を追加する

forでmailが一致するときに、配列の最後に番号を加えて、

for (let i = 0; i < data.length; i++) {
   if (data[i][1] === mail) {
     data[i][5] = i;
     lotteryNumber = i;
   }

シートに書き出す
sheet.getRange(1, 1, data.length, data[0].length).setValues(data);

またしてもflush

sheet.appendRow(array);　のあとに、SpreadsheetApp.flush();　を入れることで、変更の即時反映となる。これを入れてなくて、一瞬あれれ～となっていた。請求書に引き続き、またしてもflush、お前か～！

*****

は～、まだ理解が追い付いていないところもたくさんあるのですが、とても勉強になりました！ありがとうございました！

さて、このコードで実際に同時に回答があったときの挙動を見てみたいんだけど、何かのイベントの時にでも声掛けでお願いしようかしらん...。

とりあえず、ゆるめのイベントでリカバリできそうな体制であればこのコードでもいけそうかな？
GAS自体がサーバエラーで動かないことも稀によくあるそうなので、過信は禁物ということで。

カッチリしたシステムが必要だったら、ノンプログラマの私が片手間に書いたコードを使うんでなくて、ちゃんとした本職の人にお金払って、良いシステムを組んでもらう必要があると思われる。

は～ひとりだと、無い頭を捻っても出てこないですね～。ノンプロ研のみなさま、本当にありがとうございます！

応用・展開
フォームに回答があったら、ランダムで言葉を返すとかして、おみくじみたいなものもできるかもしれない。

疲れたので、ひとまず終わり！

いただいたサポートで、書籍代や勉強費用にしたり、美味しいもの食べたりします！