今までの記事一覧
Googleフォーム×GASで実現する業務自動化シリーズ全記事まとめ目次
前提
この記事は、フォーム回答を保存しているスプレッドシート側のGASを前提にしています。
トリガーは以下を設定してください。
- 実行する関数：
onFormSubmit - イベントのソース：スプレッドシートから
- イベントの種類：フォーム送信時
スプレッドシート構成
スプレッドシート「集計シート」をこの図のように設定しています。
- B1セルに残数が入る
- 4行目以下にフォームから送信された注文内容が記録される
おさらい
前回は、フォームから注文を受ける→残数を自動でカウントダウンし、残数が0になったら自動でフォームの受付を停止するシステムを作りました。
しかし今いくつ残っているのか分かりづらいとフォーム利用者から声があがりました。
そこで今回はフォーム上で動的に 「残り〇個」 と表示する仕組みを作りましょう。
やりたいこと
お弁当の注文フォームを題材に作成します。
- フォームで注文を送信すると、集計シート上の残数がカウントダウンする
- 残数が０になったら、自動でフォームの受付を終了（クローズ）する
- 注文データは、集計用の「集計シート」に自動で転記していく
- フォーム上に「残り〇個」と表示する（今回のテーマ）
使うのは前回と同じこのフォーム。
前回のコードはこちらでした。
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().flat();
const sheet_summary = ss.getSheetByName("集計シート");
// フォームの回答から「注文数」を取得（文字列になるケースがあるため念のため数値化）
// formDataの中の4番目（インデックス[3]）を取得
const orderCount = Number(formData[3]);
// 「集計シート」のB1セルから現在の残数を読み込む
const currentRemaining = Number(sheet_summary.getRange("B1").getValue());
// 新しい残数を計算して、B1セルに上書き
const newRemaining = Number(currentRemaining) - orderCount;
sheet_summary.getRange("B1").setValue(newRemaining);
//まとめシートに転記
sheet_summary.getRange(sheet_summary.getLastRow() + 1, 1, 1, column).setValues([formData]);
// もし残数が0以下になったら、フォームを自動で締め切る
if (newRemaining <= 0) {
const formUrl = ss.getFormUrl(); // スプレッドシートに紐づくフォームのURLを取得
const form = FormApp.openByUrl(formUrl);
form.setAcceptingResponses(false); // 新規受付を停止
}
}
GASコード
今回使うメソッドはコレ！
-
form.setDescription(text)
フォームの最上部、タイトルの下にある説明欄を書き換えます。 -
item.setHelpText(text)
フォームの各質問欄の質問の下の説明欄（各質問欄の右下にある三点メニュー（︙）から「説明」を選ぶと表示される欄）を書き換えます。
ここを変える
①紐づいているフォームを取得する変数の場所を移動
if (newRemaining <= 0)の中に入っていた
const formUrl = ss.getFormUrl(); // スプレッドシートに紐づくフォームのURLを取得
const form = FormApp.openByUrl(formUrl);
これらを、ifの外に出します。
//★紐づいているフォームを取得 ＜if の外へ移動
const formUrl = ss.getFormUrl(); // スプレッドシートに紐づくフォームのURLを取得
const form = FormApp.openByUrl(formUrl);
// もし残数が0以下になったら、フォームを自動で締め切る
if (newRemaining <= 0) {
今まではifの中でしか使わなかった定数ですが、変更によりifの外（下で説明するelse）でも使う必要があるためです。
②質問欄を変更するメソッドを追加
if (newRemaining <= 0)に対して、else(残数がまだある場合)を作り、その中にform.setDescriptionやitem.asTextItem().setHelpTextといった、説明欄を更新するコードを入れます。
//★残数がまだある場合 （追加）
} else {
//パターンA：フォーム全体の「説明欄」を書き換える場合
form.setDescription("【現在の残り在庫： " + newRemaining + " 個】");
//パターンB：特定の質問（注文数）の「説明欄」を書き換える場合
const items = form.getItems();
for (const item of items) {
// 質問のタイトルが「注文数を入力してください」の項目を探す
if (item.getTitle() === "注文数を入力してください") {
// TextItem（記述式）に型変換して、説明欄を更新
item.asTextItem().setHelpText("※現在の残り在庫は「あと " + newRemaining + " 個」です。");
break;
}
}
}
残りが0になった場合はこの処理は実行されないため「残り〇個」のままになります。
ただし、残りがなくなった時点でフォームの受付が停止するため、今回はこのままとしています。
実際の完成コード
上記のメソッドのどちらか、または両方を、前回のGASに組み込みます。
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().flat();
const sheet_summary = ss.getSheetByName("集計シート");
// フォームの回答から「注文数」を取得（文字列になるケースがあるため念のため数値化）
// formDataの中の4番目（インデックス [3] ）を取得
const orderCount = Number(formData[3]);
// 「集計シート」のB1セルから現在の残数を読み込む
const currentRemaining = Number(sheet_summary.getRange("B1").getValue());
// 新しい残数を計算して、B1セルに上書き
const newRemaining = Number(currentRemaining) - orderCount;
sheet_summary.getRange("B1").setValue(newRemaining);
//まとめシートに転記
sheet_summary.getRange(sheet_summary.getLastRow() + 1, 1, 1, column).setValues([formData]);
//★紐づいているフォームを取得 ＜if (newRemaining <= 0)の外に出す
const formUrl = ss.getFormUrl(); // スプレッドシートに紐づくフォームのURLを取得
const form = FormApp.openByUrl(formUrl);
// もし残数が0以下になったら、フォームを自動で締め切る
if (newRemaining <= 0) {
form.setCustomClosedFormMessage("本日の注文受付は【完売】のため終了いたしました。またのご利用をお待ちしております！");
form.setAcceptingResponses(false); // 新規受付を停止
//★残数がまだある場合 ＜これを追加
} else {
//パターンA：フォーム全体の「説明欄」を書き換える場合
form.setDescription("【現在の残り在庫： " + newRemaining + " 個】");
//パターンB：特定の質問（注文数）の「説明欄」を書き換える場合
const items = form.getItems();
for (const item of items) {
// 質問のタイトルが「注文数を入力してください」の項目を探す
if (item.getTitle() === "注文数を入力してください") {
// TextItem（記述式）に型変換して、説明欄を更新
item.asTextItem().setHelpText("※現在の残り在庫は「あと " + newRemaining + " 個」です。");
break;
}
}
}
}
今回は質問形式が「記述式（短文）」なので asTextItem() を使っています。
別の質問形式の場合は、プルダウンなら asListItem()、ラジオボタンなら asMultipleChoiceItem()、チェックボックスなら asCheckboxItem() など、質問形式に合わせてasTextItem()の部分を変更してください。
動作確認
ではフォームから送信してみます。
残数を10にして、フォームで6個注文してみると・・・
シートの残数が更新され
フォーム側の表示も更新され、「残り4個」と表示されました。
form.setDescriptionを使った場合
item.setHelpTextを使った場合
まとめ
今回はフォーム送信時に残り在庫をカウントし、フォーム上に「残り〇個」を表示するコードを作りました。
だがしかし！「残数表示」と「受付停止」は実装していますが、入力値チェックまでは行っていません。
つまり、画面に「残り3個」と表示されているのに、ユーザーが「5個」と入力してもそのまま送信できてしまうのです。
これでは残数がマイナスになってしまって現場が大パニックに！
そこで次回は、フォームの 「回答の検証」機能 をGASで動的にハックし、強制的に 「現在の残数以下」 しか入力できないように制限する仕組みに迫ってみたいと思います。
次回はこちら
Googleフォーム×在庫管理！回答の検証を動的変更して残数超過を防ぐ
