Googleフォームが送信されたときにGASでいろいろする
Googleフォームは標準の機能だけでもスプレッドシートへの記録から回答のコピーの送信までできるすぐれものです。
ですが、回答が送信されたときにスプレッドシートに質問項目にはないカラムをつけて記録したかったり、文面をカスタマイズしたメールを送りたいことがあると思います。
さすがにここまでの機能は標準ではありませんが、 GAS ( Google Apps Script ) を使うと実現することができます。
今回は特に、送信される時間がある時間に極端に集中するようなフォームでプログラムを作る際に気をつけたほうが良いことを書きたいと思います。
単純なメール送信プログラムの作り方に関しては他の記事でいろいろな方が説明してくださっているのでそちらをご覧ください。一応軽く説明はします。
1. つくるもの
メールアドレスと希望の時間帯の項目を作り、申し込んだ順番で時間帯ごとに番号を振ってメールを送信するプログラムを作ります。
基本的な方針としては、メールが送信されたらプログラムで内容をチェックし、番号を振ってスプレッドシートに新しい行として追加した上でメールを送信、って感じで行きます。
2. つくる
デモなのでシンプルにメールアドレスと希望の時間帯に加え、最後に自由記述欄を1つおいたフォームを作りました。
次に右上の三点メニューから「スクリプトエディタ」を開いてください。
するとこんな画面が出てくると思うので、一度コードの部分を全部消して下のコードに書き換えます。
const spreadSheet = SpreadsheetApp.openById("your_spreadsheet_id");
const sheet = spreadSheet.getSheetByName("シートの名前");
function onSubmit(e) {
const row = sheet.getLastRow() + 1;
const questions = e.response.getGradableItemResponses();
let message = "予約の受付が完了しました。\n\n\n";
for (const question of questions) {
const answer = question.getResponse();
switch(question.getItem().getTitle()) {
case "希望の時間帯":
const timePartNumber = getTimePartNumber(answer);
const order = countTimePartReservation(answer, row);
const orderNumber = ("000" + order).slice(-3);
const reservationId = timePartNumber + orderNumber;
sheet.getRange(row, 1).setValue(reservationId);
sheet.getRange(row, 2).setValue(answer);
message += `【予約ID】\n ${reservationId}\n\n`;
message += `【希望の時間帯】\n ${answer}\n\n`;
break;
case "メールアドレス":
address = answer;
sheet.getRange(row, 3).setValue(answer);
message += `【メールアドレス】\n ${answer}\n\n`;
break;
case "その他":
sheet.getRange(row, 4).setValue(answer);
message += `【その他】\n ${answer}\n\n`;
break;
}
const timestamp = e.response.getTimestamp();
sheet.getRange(row, 5).setValue(timestamp);
}
const subject = "予約の受付が完了しました";
GmailApp.sendEmail(address, subject, message);
}
function getTimePartNumber(part) {
const index = ["9時～10時", "10時～11時", "11時～12時", "12時～13時", "13時～14時", "14時～15時"].indexOf(part) + 1;
return String(index);
}
function countTimePartReservation(part, row) {
const reservationList = sheet.getRange(1, 2, row, 1).getValues();
const targetTimePartCount = reservationList.filter(v => (v[0] === part)).length + 1;
return targetTimePartCount;
}
1行目のyour_spreadsheet_idのところは別でスプレッドシートを作ってそのIDに書き換えてください。2行目もです。
保存したら左側上から3つめの時計アイコンからトリガーの画面を開き、「トリガーを追加」で「イベントの種類を選択」を「フォーム送信時」に変えて保存します。
ポップアップ画面が出てくるのでGoogleアカウントを選択します。
するとこんな警告が出てきます。場合によっては日本語かもしれませんが、「Advanced」から 「Go to (プロジェクト名)」をクリックしてください。
次の画面でメールの読み書きとか、スプレッドシートとフォームへのアクセス権限を求められます。「Allow」を押してポップアップが閉じ、元のページでトリガーが追加されていればOKです。
3. うごかす
動かします。フォームを送って正常に動作するか確認してみましょう。
メールが正しく届いていれば成功です。うまくいかない場合はスクリプトエディタの左側に「実行数」という項目があるのでそこでエラーが出ていないか確認してみてください。
ちなみに
getEditResponseUrl() とすることで該当のフォームの編集リンクを取得できます。標準のメールのコピー送信には付いている機能ですね。場合によってはメールの文面につけてあげると親切かもしれません。
詳しくは公式のリファレンスで確認してみてください。
4. いっぱいおくる
さて、ここからがこの記事の本題です。
今このプログラムは正常に動くことが確認できましたが、これがもし、より送信される時間が集中したらどうでしょうか？
頻度を上げたときにどうなるか試してみます。
これを手動で再現するのは難しいので、PythonのSeleniumを使って秒間1送信レベルの状況を再現してみました。コードは省略します
下の写真がプログラムで10件送信させたあとのスプレッドシートです。
10件送ったのに7件しかありません。
A列をよく見てみてください。連番になるようプログラムを組んでいたはずなのに、同じ番号が出来てしまったり、番号が飛んでしまったりしています。
5. なおす
この空行はプログラムでスプレッドシートの最下行を取得した時まだ前の投稿の書き込みが終わっていなかったことによって起きています。同じ列に上から書き換えちゃってるわけですね。セルを右クリックして編集履歴を確認すると分かると思います。
スプレッドシートへの書き込みは意外と遅いです。まあこのコードに関しては5項目の書き込みをすべて別々で行っているからでもあるのですが、1回にしたところで微妙なタイムラグは発生してしまいます。
これを回避するため、過去のデータをスプレッドシートから取ってくるのではなくフォームのAPIから取ってくる方法に変えます。
const spreadSheet = SpreadsheetApp.openById("your_spreadsheet_id");
const sheet = spreadSheet.getSheetByName("シートの名前");
+ const form = FormApp.openById("your_form_id");
function onSubmit(e) {
- const row = sheet.getLastRow() + 1;
const questions = e.response.getGradableItemResponses();
const timePart = questions[1].getResponse();
+ const responseId = e.response.getId();
+ const responses = form.getResponses();
+ let row = 1;
+ let order = 0;
+ for (const response of responses) {
+ row++;
+ if (response.getGradableItemResponses()[1].getResponse() === timePart) {
+ order++;
+ }
+ if (response.getId() === responseId) {
+ break;
+ }
+ }
let message = "予約の受付が完了しました。\n\n\n";
for (const question of questions) {
const answer = question.getResponse();
switch(question.getItem().getTitle()) {
case "希望の時間帯":
const timePartNumber = getTimePartNumber(answer);
- const order = countTimePartReservation(answer, row);
const orderNumber = ("000" + order).slice(-3);
const reservationId = timePartNumber + orderNumber;
sheet.getRange(row, 1).setValue(reservationId);
sheet.getRange(row, 2).setValue(answer);
message += `【予約ID】\n ${reservationId}\n\n`;
message += `【希望の時間帯】\n ${answer}\n\n`;
break;
case "メールアドレス":
address = answer;
sheet.getRange(row, 3).setValue(answer);
message += `【メールアドレス】\n ${answer}\n\n`;
break;
case "その他":
sheet.getRange(row, 4).setValue(answer);
message += `【その他】\n ${answer}\n\n`;
break;
}
const timestamp = e.response.getTimestamp();
sheet.getRange(row, 5).setValue(timestamp);
}
const subject = "予約の受付が完了しました";
GmailApp.sendEmail(address, subject, message);
}
function getTimePartNumber(part) {
const index = ["9時～10時", "10時～11時", "11時～12時", "12時～13時", "13時～14時", "14時～15時"].indexOf(part) + 1;
return String(index);
}
function countTimePartReservation(part, row) {
const reservationList = sheet.getRange(1, 2, row, 1).getValues();
const targetTimePartCount = reservationList.filter(v => (v[0] === part)).length + 1;
return targetTimePartCount;
}
getResponses 関数は対象のフォームのすべての回答を送信された順で返します。この配列を0個目から走査し、現在作業中の回答IDと一致するものがあるまで row 変数をインクリメントしています。
今回はこれに加え、送信された回答の前に同じ時間帯で申し込んだ人が何人いるかをチェックしたかったのでforで回すときに同じ回答がいくつあるかも数えています。
多少パフォーマンスは落ちますが、このように組むことで空行が発生するのを防ぐことができます。
6. おわり
とまあ、ここまでくどくど回答が集中した時に正常に動作しない場合の対策を書いてきましたが、実はそれよりも注意しなくてはいけないのがGASのメール送信数の制約です。Googleの無料アカウントでは24時間に100通までしか送ることが出来ません。そこまで多くの人が使わないフォームであれば問題ないですが、本格的な申し込みの受付でカスタムメールを送りたい場合はGASの GmailApp を使うのではなく SendGrid などのサービスを利用することも検討したほうが良いかもしれません。ちなみに組織のアカウントであれば1日1500通まで送ることができます。
Discussion