今までの記事一覧
Googleフォーム×GASで実現する業務自動化シリーズ全記事まとめ目次
前提
この記事は、フォーム回答を保存しているスプレッドシート側のGASを前提にしています。
トリガーは以下を設定してください。
- 実行する関数：
onFormSubmit - イベントのソース：スプレッドシートから
- イベントの種類：フォーム送信時
スプレッドシート構成
スプレッドシート「集計シート」をこの図のように設定しています。
- B1セルに残数が入る
- 4行目以下にフォームから送信された注文内容が記録される
おさらい
前回は、スマホからでもフォーム更新ができるよう、セル編集時に動くインストールトリガーを使う方法を試しました。
ここで１本の電話が。「もしもし、白黒ハチ太郎だけど、お弁当の数を変更したいんだけど」
現場は大慌て。シートを修正してフォームも変えて・・・あ、引き算間違えた！
なんてパニックにならないよう、今回は注文数が修正されたらシートもフォームも一発で修正、もちろん引き算も間違えないシステムを考えてみましょう。
やりたいこと
- 現場に注文数変更の連絡が来たら集計シートを修正
- 集計シートの残数も修正
- フォームの残数も修正
シートの処理
今までの集計シートを変更しておきます。
- 今まで「残数」だけだった集計を３行に増やし、「販売上限」「注文合計」「残数」とします。販売上限は今まで「残数」として入れてた、注文を受けられる最大値です。
- 注文合計には後述する「有効注文数」の合計値を入れる関数を入れます（図の場合は
=SUM(F6:F)）。 - 残数は
販売上限-注文合計、つまり、=B1-B2を入れます。 - 実際に注文データが入る６行目以降に列を２つ追加、それぞれ「修正数」「有効注文数」とします。
- 「有効注文数」に関数
=ARRAYFORMULA(IF(D6:D="","",IF(E6:E="",D6:D,E6:E)))を入れます。E列の「修正数」がある場合は修正数を、なければD列の「注文数」が「有効注文数」に入ります。この有効注文数の合計がB2セルに入る仕組みです。
注文変更の連絡を受けた管理者は、該当の注文のE列に変更後の数を入力すれば、あとはシート上で自動で残数が計算されます。キャンセルの場合は「0」を入力してください。
残数計算をシートの数式に任せることで、計算ロジックがGASから切り離され、保守性が向上しました。
今回のキモはコレ！
今回の最大の変更点は、残数をGASで計算しないことです。
これまではフォーム送信のたびにGASが残数を計算していましたが、今回はシート上の関数で残数を算出します。
そのため、onFormSubmitから残数計算のコードを削除できます。
// フォームの回答から「注文数」を取得（文字列になるケースがあるため念のため数値化）
// formDataの中の4番目（インデックス [3] ）を取得
const orderCount = Number(formData[3]);
//////注文上限（今まで残数を入れていた）からシート上で引き算して残数を入れるのでこのコード不要
// // 「集計シート」のB1セルから現在の残数を読み込む
const currentRemaining = sheet_summary.getRange("B1").getValue();
// // 新しい残数を計算して、B1セルに上書き
const newRemaining = Number(currentRemaining) - orderCount;
sheet_summary.getRange("B1").setValue(newRemaining);
これでonFormSubmitの仕事は
- フォームのデータを集計シートに転記
- シートから残数を取得してフォームの説明欄と選択肢に反映
だけになりました。
ここを変える
onFormSubmit syncForm syncOnEdit
- 残数が入っているセルをB1→B3に変更します。（
onFormSubmitだけではなく、syncFormやsyncOnEditなど、残数セルを操作している関数は全部直しておく）
syncOnEdit
- 変更されたセルの「行」と「列」を取得
const row = range.getRow(); //★編集された行
const col = range.getColumn(); //★編集された列
- ターゲットとなるセルを指定します。
今まではB1セルだけでしたが、今回は残数セルがB3に移動したためB1からB3に変更します。
さらに、変更を反映するため「E6セル以下」もターゲットに追加します。
そのため(row === 3 && col === 2)でB3セル、(row >= 6 && col === 5)でE列の6行目以下を指定します。
if (sheet.getName() === "集計シート"
&& ((row === 3 && col === 2) //B3セル
||(row >= 6 && col === 5))) { //E6セルから下
- 前回
range.getValue()で取得していた残数currentRemainingをB3セルから取得するように変更します。
const currentRemaining = Number(sheet.getRange("B3").getValue());
onFormSubmit
- フォームデータの取得と書き込みを２次元配列のまま扱う。
今までデータを抜き出しやすくするために１次元配列にしていましたが、データをそのままシートに貼り付けられるようになったため、２次元配列のまま扱います。
Before
//データの取得
const formData = sheet.getRange(row, 1, 1, column).getValues().flat();
//データの貼り付け
sheet_summary.getRange(summaryLastRow + 1, 1, 1, column).setValues([formData]);
After
//データの取得
const formData = sheet.getRange(row, 1, 1, column).getValues();
//データの貼り付け
sheet_summary.getRange(summaryLastRow + 1, 1, 1, column).setValues(formData);
- シートの即時反映をします。
GASのシート操作は内部的にまとめて実行されるため、書き込んだ直後にセルを読み込むと、まだ数式の再計算結果が反映されていないことがあります。
この後、シート上で計算した残数を取得するため、ここでいったんシートを即時反映させます。
SpreadsheetApp.flush(); //今書き込んだ内容をシートに反映させる
- 集計シートの実データ最終行を取得します。シート上に
arrayformula関数で最終行まで計算結果が入るようにしているため、sheet.getLastRow()だと空行を含めた最終行（1000行目など）を取得してしまいます。実データがあるA列の最終行をとるよう、以下のように最終行を取得しておき、それをシートへの書き込み時に使用します。
const summaryLastRow = sheet_summary
.getRange(sheet_summary.getMaxRows(), 1)
.getNextDataCell(SpreadsheetApp.Direction.UP)
.getRow(); //A列の最終行を取得
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();
const sheet_summary = ss.getSheetByName("集計シート");
//この下のブロックは全部削除
// フォームの回答から「注文数」を取得（文字列になるケースがあるため念のため数値化）
// formDataの中の4番目（インデックス [3] ）を取得
//const orderCount = Number(formData[3]);
//////注文上限（今まで残数を入れていた）からシート上で引き算して残数を入れるのでこのコード不要
// // 「集計シート」のB1セルから現在の残数を読み込む
// const currentRemaining = sheet_summary.getRange("B1").getValue();
// // 新しい残数を計算して、B1セルに上書き
// const newRemaining = Number(currentRemaining) - orderCount;
// sheet_summary.getRange("B1").setValue(newRemaining);
//集計シートに転記
const summaryLastRow = sheet_summary
.getRange(sheet_summary.getMaxRows(), 1)
.getNextDataCell(SpreadsheetApp.Direction.UP)
.getRow(); //A列の最終行を取得
sheet_summary.getRange(summaryLastRow + 1, 1, 1, column).setValues(formData);
SpreadsheetApp.flush(); //★★今書き込んだ内容をシートに反映させる
//紐づいているフォームを取得
const formUrl = ss.getFormUrl(); // スプレッドシートに紐づくフォームのURLを取得
const form = FormApp.openByUrl(formUrl);
const currentRemaining = Number(sheet_summary.getRange("B3").getValue());
// もし残数が0以下になったら、フォームを自動で締め切る
if (currentRemaining <= 0) {
form.setCustomClosedFormMessage("本日の注文受付は【完売】のため終了いたしました。またのご利用をお待ちしております！");
form.setAcceptingResponses(false); // 新規受付を停止
} else { //残数がまだある場合
//フォームが受付停止になっていたら再開する
if (form.isAcceptingResponses() == false) {
form.setAcceptingResponses(true);
}
//フォーム全体の「説明欄」を書き換える場合
form.setDescription("【現在の残り在庫： " + currentRemaining + " 個】");
//特定の質問（注文数）の「プルダウン選択肢」を書き換える
const items = form.getItems();
for (const item of items) {
if (item.getTitle() === "注文数を選択してください") {
// プルダウン(asListItem) に型変換
const listItem = item.asListItem();
// 1 から currentRemaining までの「文字列の配列」を作る
const choices = [];
for (let i = 1; i <= currentRemaining; i++) {
choices.push(String(i)); // "1", "2", "3"... と文字列で入れていく
}
// プルダウンの選択肢を最新在庫に合わせて書き換え
listItem.setChoiceValues(choices);
// ヘルプテキスト（説明文）を更新
listItem.setHelpText("※現在の残り在庫は「あと " + currentRemaining + " 個」です。");
break;
}
}
}
}
function syncOnEdit(e) {
// イベントオブジェクト e から編集されたセルを特定する
const range = e.range;
const sheet = range.getSheet();
const row = range.getRow(); //★編集された行
const col = range.getColumn(); //★編集された列
// ★「集計シート」の、かつ（&&）「B3セル」と「E6セル以下」が編集されたときに動く
if (sheet.getName() === "集計シート"
&& ((row === 3 && col === 2) //B3セル
||(row >= 6 && col === 5))) { //E6セルから下
const ss = SpreadsheetApp.getActiveSpreadsheet();
const formUrl = ss.getFormUrl();
const form = FormApp.openByUrl(formUrl);
// ★集計シートのB3セルに入っている最新の残数を取得
const currentRemaining = Number(sheet.getRange("B3").getValue()); //★★B1→B3に変更
// 在庫が0以下の条件分岐
if (currentRemaining <= 0) {
form.setCustomClosedFormMessage("本日の注文受付は【完売】のため終了いたしました。またのご利用をお待ちしております！");
form.setAcceptingResponses(false);
return;
}
// 在庫復活時の再開処理
if (!form.isAcceptingResponses()) {
form.setAcceptingResponses(true);
}
// フォーム全体の「説明欄」を書き換える
form.setDescription("【現在の残り在庫： " + currentRemaining + " 個】");
// 特定の質問（注文数）のプルダウン選択肢を書き換える
const items = form.getItems();
for (const item of items) {
if (item.getTitle() === "注文数を選択してください") {
const listItem = item.asListItem();
const choices = [];
for (let i = 1; i <= currentRemaining; i++) {
choices.push(String(i));
}
listItem.setChoiceValues(choices);
listItem.setHelpText("※現在の残り在庫は「あと " + currentRemaining + " 個」です。");
break;
}
}
}
}
function syncForm(e)は、上で書いたとおり、残数セルのB1をB3に変えるだけです。エディタの検索機能（Ctrl + F）を使うと便利です。
動作確認
この状態から、白黒ハチ太郎さんの注文数を3に、茶白トラ美さんの注文を2に修正してみます。
シート上で即座に自動計算されて残数が5に変わります。
修正数を入力するたびに syncOnEdit が実行されるため、
白黒ハチ太郎さんの修正時に1回、
茶白トラ美さんの修正時に1回、
合計2回フォームが更新されます。
なお、前々回で作成した「フォーム更新」ボタンを残している場合は、編集時トリガーを使わなくても同じ結果が得られます。
この状態で新しい注文をフォームから注文数「1」で送信してみましょう。
onFormSubmitをいじりましたが、フォーム送信時でも今までどおりの処理がされています。
まとめ
今回は、注文数の変更/キャンセルを管理者がシートに入力することでフォームにも反映させる処理を実装してみました。
今回の修正で、残数計算はGASではなくスプレッドシートの関数に任せるようになり、注文数の変更やキャンセルが発生しても、シートの値を書き換えるだけで残数が自動的に再計算されるようになりました。
また、
- GASは「データ転記とフォーム更新」だけ
- 集計はシート関数だけ
という形で役割を分離できたため、コード量も減り保守しやすくなっています。
成果コードは上の「GASコード」に載せています。プルダウン型以外は //特定の質問（注文数）の「プルダウン選択肢」を書き換えるから下を、前回同様に書き換えてください。
今回は「管理者がシート上で修正する」方法を実装しました。
しかし運用が大きくなると、修正依頼の電話やメールが増え、管理者が1件ずつ対応するのは大変です。
そこで次回は、利用者自身が注文内容を変更・キャンセルできる仕組みを考えてみます。
次の記事はこちら
注文数の変更・キャンセルに対応②ユーザー対応編
