今までの記事一覧
Googleフォーム×GASで実現する業務自動化シリーズ全記事まとめ目次
前提
この記事は、フォーム回答を保存しているスプレッドシート側のGASを前提にしています。
トリガーは以下を設定してください。
- 実行する関数：
onFormSubmit - イベントのソース：スプレッドシートから
- イベントの種類：フォーム送信時
スプレッドシート構成
スプレッドシート「集計シート」をこの図のように設定しています。
- B1セル：販売上限数
- B2セル：「有効注文数」の合計（=SUM(F6:F)）
- B3セル：残数（=B1-B2）
- 6行目以下のA～D列：フォームから送信された回答
- 6行目以下のE列：注文内容を変更・キャンセルした場合に、管理者が修正後の注文数を入力する列
- 6行目以下のF列：在庫計算に使用する「有効注文数」（以下の関数を設定）
- E列が空欄なら、フォームから送信された注文数（D列）を使用
- E列に修正数が入力されていれば、その値を使用
=ARRAYFORMULA(IF(D6:D="","",IF(E6:E="",D6:D,E6:E)))
おさらい
前回は、注文の変更の連絡を受けた管理者が、シートに記入することでフォームの残数表示も更新する処理を実装しました。
ところが、変更の連絡が多いと管理者の負担が大きいとの指摘が。
というわけで、今回は変更やキャンセルをユーザーがセルフサービスでできないか考えてみます。
やりたいこと
- 注文する人がフォームにて修正した数を送信するとフォームの残数表示が更新
これを実装するための方法をいろいろ考えたのですが、どれも一長一短･･･ということで、今回は、変更用フォームを別に用意する方法にしようと思います。
変更フォームの内容は、今までの注文フォームとほぼ同じ。
ただ、注文数は、今までは残数以下しか入れられませんでしたが、以下の理由により「0以上注文上限数以下」とします。
理由：
上限10で注文7を入れた場合、3以下しか注文数が入れられない
→ 8に増やしたい or 6に減らしたい、ができない。
このため、現在の残数を超える注文数を入力できてしまうリスクがありますが、
- 受信時に残数がマイナスになる場合は、注文を受け付けず、お断りメールを送信する扱いとします。
今回のキモはコレ！
スプレッドシートには、複数のフォームを紐づけることができる
GASでの呼び出し方は以下のとおり。
//紐づいているフォームを取得
const form = FormApp.openById("1#############"); //注文フォームの管理用ID
const corForm = FormApp.openById("1*******************"); //変更フォームの管理用ID
今まではこのコードでフォームを呼び出していました。
const form = FormApp.openByUrl(formUrl);
これは紐づいているフォームの中で最も先に紐づけ処理されたフォームを呼び出します。
もし今後、フォームのリンクを解除したり繋ぎなおしたりなどをした場合、意図しない挙動になる可能性があるため、複数フォームを扱う今回から、どちらもフォームIDで呼び出す方式にします。
どちらのフォームから送信されたかを判定する方法
ここで、フォーム回答が書き込まれたシート名を取得します。
const sheet = e.range.getSheet(); //フォーム回答が入力されたシート
const sheetName = sheet.getName(); //シートの名前を取得
注文フォームだったらsheetNameは "フォームの回答1"
変更フォームだったらsheetNameは "フォームの回答2" になります。
これを使って条件分岐するといいでしょう。
※シート名は変更できるので、それぞれ「注文フォームの回答」「変更フォームの回答」などと変えておくとわかりやすいかもしれません。
メールアドレスで過去の注文データと突合する
集計シートのメールアドレスを配列で取得し、今回送信されたメールアドレスがその中にあるかどうかを lastIndexOf() を使って検索します。
同じ利用者が複数回注文フォームを送信すると、シート上には同じメールアドレスの行が複数記録されます。そのため先頭を返す indexOf() ではなく、最新の行を取得できる lastIndexOf() を使用します。
戻り値として取得した要素番号から、前回の注文データが入った行を特定できます。
const mailDataList = sheet_summary.getRange(5, 2, summaryLastRow - 4, 1).getValues().flat();
const lastHitIndex = mailDataList.lastIndexOf(mail);
もし、集計シートの中に該当するメールアドレスがない場合は、サブ関数 rejectionCorrectionMail を呼び出して、「注文データがありません」とメールを返すようにしておきましょう。
function rejectionCorrectionMail(mail, user) {
const title = "【重要】お弁当のご注文を承れませんでした"
//注文フォームの回答入力用url("1FA"で始まる)
const formUrl = "https://docs.google.com/forms/d/e/1FA######/viewform";
let body = `
${user} 様
ご注文ありがとうございます。
変更フォームより送信いただきましたが、お客様の「事前の注文データ」が見つかりませんでした。
恐れ入りますが、ご注文がお済みでない場合は、
以下の「注文専用フォーム」から新規のご注文をお願いいたします。
▼ 注文専用フォームはこちら
${formUrl}
※すでに注文済みで、メールアドレスを変更された場合などは、お手数ですが管理者まで直接ご連絡ください。
`;
GmailApp.sendEmail(mail, title, body);
}
送信された注文数によって残数がマイナスになる場合は、注文を受け付けず、注文者にメール送信
サブ関数rejectionMailで、メールを作成/送信します。
マイナスになった場合はシートへの書き込みはしません。
if (currentRemaining - num < 0) { //残数がマイナスになった場合はお断りメール
rejectionMail(mail, user, currentRemaining); //function rejectionMailを呼び出す
return;
}
メール送信も、タッチの差で売り切れてNGなのか、注文数を減らせばOKなのかで分岐しています。
function rejectionMail(mail, user, num) {
const title = "【重要】お弁当のご注文を承れませんでした";
//変更フォームの回答入力用url("1FA"で始まる)
const formUrl = "https://docs.google.com/forms/d/e/1FA******/viewform";
let body;
if (num <= 0) { //残数(利用できる数)が0になった場合
body = `
${user} 様
ご注文ありがとうございます。
大変申し訳ございませんが、先に他のお客様のご注文が確定し、
残数が０になったため、${user}様の注文を承ることができませんでした。
またのご利用をお待ちしております。
`;
} else { //注文を減らせば受付できる場合
const time = Utilities.formatDate(new Date(), "JST", "yyyy/MM/dd HH:mm");
body = `
${user} 様
ご注文ありがとうございます。
大変申し訳ございませんが、先に他のお客様のご注文が確定したため
${user}様の注文を承ることができませんでした。
恐れ入りますが再度以下のリンクから注文をお願いします。
${formUrl}
なお、${time} 現在の注文可能数は【 ${num} 個】となっております。
`;
}
GmailApp.sendEmail(mail, title, body);
}
動作確認
では動作確認してみましょう。
残数以内の変更
まずは、以下の状態から、白黒ハチ太郎さんの注文を、変更フォームを使って「2→4」に変更してみます。
注文数が「4」に変更され、残数も変わっています。
残数がマイナスになる変更
この状態で茶白トラ美さんが注文数を「7」に変更します。残数がマイナスになってしまうため、注文は受付されず、茶白さんにはお断りメールが行きます。シートの変更はありません。
変更前の注文数preNumをいったん在庫に差し戻してから新しい注文数を適用する計算になるため、「現在の残数 − 新しい注文数 + 以前の注文数」の【 6個 】が注文可能数となります。
残数が0になる変更
では茶白さん、6個に変更して送信。
無事受け付けされ、残数「0」になりました。フォームも受付停止になっています。
注文数を減らす変更
ここで白黒ハチ太郎さんが「4→3」に注文数を減らしました。変更は受付され、残数が「1」になっています。
フォームの受付も復活しています。
集計シートに登録されていないメールアドレスからの変更
最後に、集計シートに載っていないメールアドレス「test3@mail.com」から、変更フォームを送信してみます。
注文は受け付けされず、お断りメールで注文フォームを使うよう依頼しています。
まとめ
今回は、ユーザー自身が変更フォームから注文数を修正できる仕組みを作りました。
単純に注文データを上書きするだけではなく、
- 過去の注文データとの突合
- 変更前注文数を考慮した残数チェック
- 在庫不足時のお断りメール送信
を組み合わせることで、ユーザー自身による変更でも販売上限を超える注文が登録されないようにしています。
管理者がシートに入力して更新ボタンやonEditでフォームを更新する仕組みも残しています。
成果コード
///////////////////////
// メイン（フォームが送信されたら実行）
//////////////////////
function onFormSubmit(e) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = e.range.getSheet(); //フォーム回答が入力されたシート
const row = e.range.getRow(); //フォーム回答が入力された行
const column = e.values.length; // フォーム回答シートに書き込まれた列
const formData = sheet.getRange(row, 1, 1, column).getValues();
const mail = e.values[1]; //フォーム送信データからメールアドレスを取得（今回は2つめの回答＝０から始まるので[1]）
const user = e.values[2]; //フォーム送信データからユーザー名を取得
const num = Number(e.values[3]); //フォーム送信データから注文数を取得
const sheet_summary = ss.getSheetByName("集計シート");
//紐づいているフォームを取得
const form = FormApp.openById("1################s"); // 注文フォームのID
const corForm = FormApp.openById("1********************"); //変更フォームのID
//集計シートに転記
const summaryLastRow = sheet_summary
.getRange(sheet_summary.getMaxRows(), 1)
.getNextDataCell(SpreadsheetApp.Direction.UP)
.getRow(); //A列の最終行を取得
const mailDataList = sheet_summary.getRange(5, 2, summaryLastRow - 4, 1).getValues().flat();
let currentRemaining = Number(sheet_summary.getRange("B3").getValue());
const sheetName = sheet.getName();
let editRow;
const lastHitIndex = mailDataList.lastIndexOf(mail);
if (sheetName === "フォームの回答1") { // 注文フォーム（新規）
if (currentRemaining - num < 0) { // 在庫がマイナスになった場合はお断りメール
rejectionMail(mail, user, currentRemaining); // function rejectionMail
return;
} else {
editRow = summaryLastRow + 1;
}
} else if (sheetName === "フォームの回答2") { // 変更フォーム（修正）
// 安全対策：もし過去の注文（メールアドレス）がシート内に見つからなければお断りメール（２）
if (lastHitIndex == -1) {
rejectionCorrectionMail(mail, user); //function rejectionCorrectionMail
return;
}
// 先にアドレスがある行を特定する
editRow = lastHitIndex + 5;
// 特定した行から、前回の注文数（preNum）を安全に取得（F列=6列目の場合）
const preNum = Number(sheet_summary.getRange(editRow, 6).getValue());
// preNumが確定したので、ここで在庫の計算を行う
if (currentRemaining - num + preNum < 0) { // 在庫がマイナスになった場合はお断りメール
rejectionMail(mail, user, currentRemaining + preNum); // function rejectionMail
return;
}
}
sheet_summary.getRange(editRow, 1, 1, column).setValues(formData);
sheet_summary.getRange(editRow, 5).clearContent(); //管理者が入力したデータをクリアする
SpreadsheetApp.flush(); //今書き込んだ内容をシートに反映させる
// 最新の在庫数をシートから再取得
currentRemaining = Number(sheet_summary.getRange("B3").getValue());
// もし残数が0以下になったら、フォームを自動で締め切る
if (currentRemaining == 0) {
form.setCustomClosedFormMessage("本日の注文受付は【完売】のため終了いたしました。またのご利用をお待ちしております！");
form.setAcceptingResponses(false); // 新規受付を停止
//変更フォームにだけ、残数を表示
corForm.setDescription("【現在の残数： " + currentRemaining + " 個】"
+ "\n" + "元々のご自身の注文数にこの数を足したものが、注文可能数です。");
} else { //残数がまだある場合
//★★フォームが受付停止になっていたら再開
if (form.isAcceptingResponses() == false) {
form.setAcceptingResponses(true);
}
//フォーム全体の「説明欄」を書き換える場合
form.setDescription("【現在の残数： " + currentRemaining + " 個】");
corForm.setDescription("【現在の残数： " + currentRemaining + " 個】"
+ "\n" + "元々のご自身の注文数にこの数を足したものが、注文可能数です。");
//特定の質問（注文数）の「プルダウン選択肢」を書き換える
const items = form.getItems();
for (const item of items) {
if (item.getTitle() === "注文数を選択してください") {
// プルダウン(asListItem) に型変換
const listItem = item.asListItem();
// 1 から currentRemaining までの「文字列の配列」を作る
//★★キャンセルの場合は0を入れてもらうため、選択肢を0からに変更
const choices = [];
for (let i = 0; i <= currentRemaining; i++) {
choices.push(String(i)); // "1", "2", "3"... と文字列で入れていく
}
// プルダウンの選択肢を最新在庫に合わせて書き換え
listItem.setChoiceValues(choices);
// ヘルプテキスト（説明文）を更新
listItem.setHelpText("※現在の残数は「あと " + currentRemaining + " 個」です。");
break;
}
}
}
}
///////////////////////////
//お断りメール（1）残数がマイナスになる
//////////////////////////
function rejectionMail(mail, user, num) {
const title = "【重要】お弁当のご注文を承れませんでした"
//変更フォームの回答入力用url("1FA"で始まる)
const formUrl = "https://docs.google.com/forms/d/e/1FA************/viewform"
let body;
if (num <= 0) {
body = `
${user} 様
ご注文ありがとうございます。
大変申し訳ございませんが、先に他のお客様のご注文が確定し、
残数が0になったため、${user}様の注文を承ることができませんでした。
またのご利用をお待ちしております。
`;
} else {
const time = Utilities.formatDate(new Date(), "JST", "yyyy/MM/dd HH:mm");
body = `
${user} 様
ご注文ありがとうございます。
大変申し訳ございませんが、先に他のお客様のご注文が確定したため
${user}様の注文を承ることができませんでした。
恐れ入りますが再度以下のリンクから注文をお願いします。
${formUrl}
なお、${time} 現在の注文可能数は【 ${num} 個】となっております。
`;
}
GmailApp.sendEmail(mail, title, body);
}
///////////////////////////
//お断りメール（2） 新規なのに変更フォームで送った場合
//////////////////////////
function rejectionCorrectionMail(mail, user) {
const title = "【重要】お弁当のご注文を承れませんでした"
//注文フォームの回答入力用url("1FA"で始まる)
const formUrl = "https://docs.google.com/forms/d/e/1FA###############/viewform"
let body = `
${user} 様
ご注文ありがとうございます。
変更フォームより送信いただきましたが、お客様の「事前の注文データ」が見つかりませんでした。
恐れ入りますが、ご注文がお済みでない場合は、
以下の「注文専用フォーム」から新規のご注文をお願いいたします。
▼ 注文専用フォームはこちら
${formUrl}
※すでに注文済みで、メールアドレスを変更された場合などは、お手数ですが管理者まで直接ご連絡ください。
`;
GmailApp.sendEmail(mail, title, body);
}
さて、このお弁当シリーズもそろそろ折り返し地点、このあと、以前お話しした LockService を使った排他処理、そして、お弁当の種類を増やしたパターンへと続いていく予定です。
が、その前に、閑話休題、ボツネタ編に行きたいと思います。
次の記事は「ボツネタ編：なぜ編集用URLを使わなかったか」を書いていきます。
実は今回のシステム、最初は「注文フォーム1つだけでキャンセルも修正も完結」を目指していました。そのカギとなるのが、Googleフォーム標準の「編集用URL getEditResponseUrl() 」です。ところが作っていくうちに大きな落とし穴があることに気付きました……
次回はこちら
Googleフォーム×在庫管理！注文数の変更・キャンセルに対応③ボツネタ編（編集用URL編）
