【GAS】Googleフォーム×在庫管理！複数商品対応② フォーム自動更新編

今までの記事一覧
Googleフォーム×GASで実現する業務自動化シリーズ全記事まとめ目次

前提

この記事は、フォーム回答を保存しているスプレッドシート側のGASを前提にしています。

トリガーは以下を設定してください。

• 実行する関数：onFormSubmit
• イベントのソース：スプレッドシートから
• イベントの種類：フォーム送信時

スプレッドシート構成

スプレッドシート「集計シート」をこの図のように設定しています。

• A1:D6 セル：商品マスタ（商品コードや現在の残数が入っている）
• 11行目以下：フォームから送信された注文の受付内容が記録される
• 図の赤字の内容の数式が入っている
  ※以前作ったこの記事の数式をそのまま生かしています。ただし、変更フォームによって在庫不足による注文数の修正が発生したときのために、有効注文数の数式をほんの少しだけパワーアップさせ =IF(ISNUMBER(修正数のセル), 修正数のセル, 注文数のセル) としておきます。こうすることで、『修正数』の欄に修正後の確定数を書き込むだけで、有効注文数も、商品マスタの残数も、シート側で全自動で連動して計算されるようになります。

おさらい

前回は、お弁当注文フォームシリーズの複数商品対応第1弾として、

• フォームとシートの整備
• シート上に商品マスタ作成
• 商品コード設定
• 2次元配列で取得した商品マスタのデータと、連想配列にしたフォーム送信データを商品コードでマッピング
• 「受け付けた数量」と「残数不足で受け付けられなかった数量」を商品コードごとに分けて管理

ここまでをやりました。

今回は次のステップとして、注文後の残数をフォームの説明欄や選択肢に反映させる処理を作っていきたいと思います。

今回のミッション

前回に加え

• 注文フォームの「フォーム説明欄」に、以下のようにそれぞれの商品の残数を表示

【現在の残数状況】
ハンバーグ弁当:3
唐揚げ弁当:3
焼き魚弁当:5

• フォームの各質問にも 残りあと3個です などの説明を入れる
• フォームの各質問の選択肢を残数以内に変える
• すべての商品の残数が0になったら、フォームの受付を停止する

今回のキモはコレ！

サブ関数にする

メイン関数 function onFormSubmit(e) の中に書いてもいいのですが、長くなって管理が大変になるのと、フォーム送信時だけでなく、別の処理からも呼び出しやすいように、サブ関数 updateFormOptions を作り、その中に処理を書いていきます。メイン関数からこのサブ関数を呼び出して処理をさせます。

使うデータは商品マスタ

下図のように関数（計算式）が入っており、10行目以下に送信データが入ると自動で商品マスタの 「注文合計」と「残数」 が計算されます。

フォーム送信後、集計シートには注文データが書き込まれますが、この時点ではシート内の関数（注文合計・残数）がまだ再計算されていない場合があります。そこでメイン関数内で SpreadsheetApp.flush() を実行してシートの数式を最新の状態まで反映させ、その後、商品マスタの2次元配列データ itemMaster をもう一度 getValues() して最新の残数を取り込みます。
itemMaster が新しくなったところで、今回のサブ関数の引数に itemMaster を入れて updateFormOptions(itemMaster) を呼び出します。

商品ごとにループ処理

商品マスタを2次元配列で取り込んだら、B列から右へ1列ずつループし、各商品の「商品名」「質問ID」「残数」を取得します。

  for (let col = 1; col < summaryLastCol; col++) {
    const productName = itemMaster[0][col]; // 1行目：商品名
    const questionId = itemMaster[2][col]; // 3行目：フォームの質問ID
    const stockNum = Number(itemMaster[5][col]); // 6行目：最新の残数

同時に、フォームの一番上に表示する説明文のため、ループ外で定義したconst formDescription = ["【現在の残数状況】"] に「ハンバーグ弁当:3」「唐揚げ弁当:3」「焼き魚弁当:5」などと追加していきます。

const formDescription = ["【現在の残数状況】"];

for (let col = 1; col < summaryLastCol; col++) {
//中略
  formDescription.push(productName + ":" + stockNum);
//中略
}
const formDescriptionText = formDescription.join("\n");
form.setDescription(formDescriptionText);   //注文フォーム

さらに、ループ外で let sumStockNum = 0 を定義し、ループごとに残数を足し算していきます。足し算した結果が0であればフォーム受付を停止する設計です。

 let sumStockNum = 0;

 for (let col = 1; col < summaryLastCol; col++) {
 //中略
   sumStockNum += stockNum;
 //中略
 }
 if (sumStockNum === 0) {    //もし商品全部の残数（sumStockNum）が0だったらフォーム受付停止
   if (form.isAcceptingResponses() === true) {  // フォームがまだ開いているときだけ！
      form.setCustomClosedFormMessage("本日の注文受付は【完売】のため終了いたしました。またのご利用をお待ちしております！");
      form.setAcceptingResponses(false); // 新規受付を停止
    }
 }

サブ関数のコード

以下はサブ関数のコードです。

注文フォームだけでなく、これから整備する予定の変更フォームも同じように残数がフォーム上部の説明欄に入るように設定しました。変更フォームはプルダウンではなく自由入力のため、選択肢の更新はしていません。
※自由入力にしている理由はこちらを参照ください。
Googleフォーム×在庫管理！注文数の変更・キャンセルに対応②ユーザー対応編

function updateFormOptions(itemMaster) {

  // 1. 注文フォームをID指定で開く
  const form = FormApp.openById("1##########"); // ここに注文フォームの管理用IDを入れてください
  const corForm = FormApp.openById("1**********"); // ここに変更フォームの管理用IDを入れてください


  // マスタの列数（B列〜最終列）を取得
  const summaryLastCol = itemMaster[0].length;
  const formDescription = ["【現在の残数状況】"];
  let sumStockNum = 0;    //商品全部の残数を足したもの

  // 2. 商品ごとにループ処理（B列=インデックス1 から開始）
  for (let col = 1; col < summaryLastCol; col++) {
    const productName = itemMaster[0][col]; // 1行目：商品名
    const questionId = itemMaster[2][col]; // 3行目：フォームの質問ID
    const stockNum = Number(itemMaster[5][col]); // 6行目：最新の残数

    // 質問IDが空欄の場合はスキップ（エラー防止）
    if (!questionId) continue;

    try {
      sumStockNum += stockNum;  //商品全部の残数を足していく
      // 3. 質問IDを元に、フォームから対象のプルダウン（ListItem）を取得
      const listItem = form.getItemById(questionId).asListItem();

      // 4. 残数に応じた選択肢（choices）を動的に組み立てる
      const choices = [];  //フォームの選択肢を入れる配列

      if (stockNum <= 0) {
        // 在庫がない場合は「0」にする（万が一マイナスになっても表示は0）
        choices.push("0");
      } else {
        // 在庫がある場合は 1 〜 残数 までの数字を選択肢にする
        // （0個も選択肢に入れたい場合は i = 0 からスタート）
        for (let i = 1; i <= stockNum; i++) {
          choices.push(String(i));
        }
      }

      // 5. フォームのプルダウン選択肢を更新 
      listItem.setChoiceValues(choices);

      // 質問のタイトルやヘルプテキスト（説明欄）も更新したい場合はここで変更可能
      listItem.setHelpText(`残りあと ${stockNum} 個です`);

      // 6. フォームタイトル下の説明欄を更新（配列へ追加）
      formDescription.push(productName + ":" + stockNum);


    } catch (e) {
      Logger.log(`質問ID [${questionId}] の更新中にエラーが発生しました: ${e.message}`);
    }
  }
  // フォームタイトル下の説明欄を書き換え
  const formDescriptionText = formDescription.join("\n");
  form.setDescription(formDescriptionText);   //注文フォーム
  corForm.setDescription(formDescriptionText + "\n ※元々のご自身の注文数にこの数を足したものが、注文可能数です。");  //変更フォーム

  if (sumStockNum === 0) {    //もし商品全部の残数（sumStockNum）が0だったらフォーム受付停止
    if (form.isAcceptingResponses() === true) {  // フォームがまだ開いているときだけ
      form.setCustomClosedFormMessage("本日の注文受付は【完売】のため終了いたしました。またのご利用をお待ちしております！");
      form.setAcceptingResponses(false); // 新規受付を停止
    }
  } else {
    //フォームが受付停止になっていたら再開
    if (form.isAcceptingResponses() === false) {
      form.setAcceptingResponses(true);
    }
  }
}

動作確認

注文後も残数がある場合

次のような残数/注文数でフォーム送信してみます。

お弁当の種類	残数	注文数	更新後の残数
ハンバーグ弁当	3個	3個	0個
唐揚げ弁当	3個	2個	1個
焼き魚弁当	5個	記入なし	5個

送信後、シートはこのように更新され

注文フォームのタイトル下の説明文も更新

各質問もこのように変更されました。（フォーム管理画面のスクショです）

変更フォームも、タイトル下の説明文が同じように更新されています。

全商品が売り切れの場合

さらにここから、更新後の残数が全部0になるように、次のような注文を入れてみます。

お弁当の種類	残数	注文数	更新後の残数
ハンバーグ弁当	0個	記入なし	0個
唐揚げ弁当	1個	1個	0個
焼き魚弁当	5個	5個	0個

シートの残数は3商品とも0になりました。

そして注文フォームは自動で受付停止されました。

管理画面を開くとこのように、説明文や選択肢がすべて0に更新されています。

ちなみに変更フォームのほうは、注文数を減らしたりキャンセルしたりする用途があるので受付停止はしません。いちばん上の説明欄だけ更新しています。

まとめ

今回は、複数商品対応第2弾として、注文フォーム受付後の残数をフォームの説明欄や選択肢に反映させる処理を実装しました。
これにより、利用者は常に最新の在庫状況を確認しながら注文できるようになり、在庫切れ商品の注文を未然に防げるようになります。

さて、今回は「フォーム送信時に自動実行」という形にしていますが、少し手を加えるだけで「シート上の実行ボタン」や「スマホ」からも実行できるようになります。

商品が1種類だったときには、以下のように「シート上の実行ボタン」と「編集時トリガー」でフォームを更新できるようにしました。

▶ 在庫変更をワンクリックでフォームに反映する
▶ 編集時トリガーで残数変更を自動反映する

次回はこれらを複数商品対応版としてアップグレードしていく予定です。

今回の成果

メイン関数　

8.と9.の★★部分が今回の変更部分です。

function onFormSubmit(e) {
  // 1. LockServiceで重複防止の鍵をかける
  const lock = LockService.getScriptLock();

  let mail = "";
  let user = "";

  try {
    lock.waitLock(30000); // 最大30秒待機

    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet_summary = ss.getSheetByName("集計シート");
    const activeSheet = e.range.getSheet(); // 回答が書き込まれたシート
    const sheetName = activeSheet.getName();

    // フォーム送信データから基本情報を取得
    mail = e.values[1]; // メールアドレス
    user = e.values[2]; // 氏名

    // 2. 集計シートの「商品マスタ（1〜6行目）」を一括取得
    const summaryLastCol = sheet_summary
      .getRange(1, sheet_summary.getMaxColumns())
      .getNextDataCell(SpreadsheetApp.Direction.PREVIOUS)
      .getColumn(); // 1行目の最終列を取得

    // A列（見出し）も含めてデータを取得
    let itemMaster = sheet_summary.getRange(1, 1, 6, summaryLastCol).getValues();

    // 3. フォームから届いた注文数を商品コードベースでマッピングする
    const orderMap = {};

    for (let col = 1; col < summaryLastCol; col++) {
      const productCode = itemMaster[1][col]; //商品コード（[HBG]など）
      let orderedNum = 0;

      for (const questionTitle in e.namedValues) {  //フォーム回答eからすべての質問文を抜き出す
        if (questionTitle.includes(productCode)) {  //質問文に該当の商品コードが出てきたら
          const responseValue = e.namedValues[questionTitle][0];  //その商品の注文数を格納
          orderedNum = Number(responseValue) || 0;  //注文数をorderedNumに格納、responseValueがなければ0を格納
          break;
        }
      }
      // 注文マップに「商品コード」をキーにして数量を保存
      orderMap[productCode] = orderedNum;
    }
    // 4. 在庫の仕分け（引き当て）ロジック
    const allocatedMap = {};
    const rejectedMap = {};

    for (let col = 1; col < summaryLastCol; col++) {  //マスタデータから商品コードを横に１つずつずらしながら
      const productCode = itemMaster[1][col]; //商品マスタの商品コード
      const orderedNum = orderMap[productCode]; //注文データの商品数
      const stockNum = Number(itemMaster[5][col]); //商品マスタの残数

      if (orderedNum === 0) continue; //注文がない、または空欄（0個）の場合は引き当て不要なので次の商品へ

      if (stockNum >= orderedNum) { //残数が注文数より多ければ、変数allocatedMapに "商品コード":注文数 を格納
        allocatedMap[productCode] = orderedNum; // 商品コードで確保
      } else {  //残数が注文数より少なければ
        if (stockNum > 0) { //残数が1以上なら、残数分を注文受付し、残りはrejectedMapに入れる
          allocatedMap[productCode] = stockNum; //変数allocatedMapに "商品コード":残数 を格納
        }
        rejectedMap[productCode] = orderedNum - stockNum; //変数rejectedMap "商品コード":注文数-残数 を格納
      }
    }

    // 5. 集計シートへ書き込む「1行分のデータ（配列）」を組み立てる
    const formattedTime = Utilities.formatDate(new Date(), "JST", "yyyy/MM/dd HH:mm:ss");
    const rowData = [formattedTime, mail, user];

    for (let col = 1; col < summaryLastCol; col++) {
      const productCode = itemMaster[1][col];
      const allocatedNum = allocatedMap[productCode] || 0;

      if (sheetName === "フォームの回答1") {    //注文フォームからの送信の場合
        rowData.push(allocatedNum); // 1列目：注文数
        rowData.push("");           // 2列目：修正数
        rowData.push("");           // 3列目：有効注文数
      } else if (sheetName === "フォームの回答2") { //変更フォームからの送信の場合
        // 変更処理用　次回以降にやります
      }
    }

    // 6. 集計シートの最終行に書き込み
    const summaryLastRow = sheet_summary
      .getRange(sheet_summary.getMaxRows(), 1)
      .getNextDataCell(SpreadsheetApp.Direction.UP)
      .getRow();
    const editRow = summaryLastRow + 1;

    // 組み立てたデータを書き込み　
    sheet_summary.getRange(editRow, 1, 1, rowData.length).setValues([rowData]);

    //検証用にログ出力
    Logger.log("注文受付内容：" + JSON.stringify(allocatedMap));
    Logger.log("受付できなかった内容：" + JSON.stringify(rejectedMap));


    //ここから先は次回以降にやります
    /*
        // 7. 【サブ関数】変更フォーム用のプレ入力URLの組み立て（ここも受付できた数 allocatedMap を元に作成）　
    
        // URLをシートの特定の列（例：最終列の後ろなど）に記録
        sheet_summary.getRange(editRow, summaryLastCol + 1).setValue(prefilledUrl);
     */

    // 8. ★★スプレッドシートの関数（注文合計や残数）を強制再計算させる
    SpreadsheetApp.flush();
    //★★残数を書き直した状態のマスターデータを再度取得
    itemMaster = sheet_summary.getRange(1, 1, 6, summaryLastCol).getValues();


    // 9.★★【サブ関数】フォームの選択肢＆説明欄を最新化　
    updateFormOptions(itemMaster);
    /*
        // 10.【サブ関数】動的メールの送信　次回以降
        sendDynamicMail(mail, user, allocatedMap, rejectedMap, prefilledUrl);
    */
  } catch (err) {
    Logger.log(`エラー発生\n mail：${mail}\n user：${user}\n 内容：${err.stack}`);
  } finally {
    if (lock.hasLock()) lock.releaseLock();
  }
}

サブ関数

/////////////////////////////////////////
//【サブ関数】フォームの選択肢＆説明欄を最新化　
////////////////////////////////////////
function updateFormOptions(itemMaster) {

  // 1. 注文フォームをID指定で開く
  const form = FormApp.openById("1##########"); // ここに注文フォームの管理用IDを入れてください
  const corForm = FormApp.openById("1**********"); // ここに変更フォームの管理用IDを入れてください


  // マスタの列数（B列〜最終列）を取得
  const summaryLastCol = itemMaster[0].length;
  const formDescription = ["【現在の残数状況】"];
  let sumStockNum = 0;    //商品全部の残数を足したもの

  // 2. 商品ごとにループ処理（B列=インデックス1 から開始）
  for (let col = 1; col < summaryLastCol; col++) {
    const productName = itemMaster[0][col]; // 1行目：商品名
    const questionId = itemMaster[2][col]; // 3行目：フォームの質問ID
    const stockNum = Number(itemMaster[5][col]); // 6行目：最新の残数

    // 質問IDが空欄の場合はスキップ（エラー防止）
    if (!questionId) continue;

    try {
      sumStockNum += stockNum;  //商品全部の残数を足していく
      // 3. 質問IDを元に、フォームから対象のプルダウン（ListItem）を取得
      const listItem = form.getItemById(questionId).asListItem();

      // 4. 残数に応じた選択肢（choices）を動的に組み立てる
      const choices = [];  //フォームの選択肢を入れる配列

      if (stockNum <= 0) {
        // 在庫がない場合は「0」にする（万が一マイナスになっても表示は0）
        choices.push("0");
      } else {
        // 在庫がある場合は 1 〜 残数 までの数字を選択肢にする
        // （0個も選択肢に入れたい場合は i = 0 からスタート）
        for (let i = 1; i <= stockNum; i++) {
          choices.push(String(i));
        }
      }

      // 5. フォームのプルダウン選択肢を更新 
      listItem.setChoiceValues(choices);

      // 質問のタイトルやヘルプテキスト（説明欄）も更新したい場合はここで変更可能
      listItem.setHelpText(`残りあと ${stockNum} 個です`);

      // 6. フォームタイトル下の説明欄を更新（配列へ追加）
      formDescription.push(productName + ":" + stockNum);


    } catch (e) {
      Logger.log(`質問ID [${questionId}] の更新中にエラーが発生しました: ${e.message}`);
    }
  }
  // フォームタイトル下の説明欄を書き換え
  const formDescriptionText = formDescription.join("\n");
  form.setDescription(formDescriptionText);   //注文フォーム
  corForm.setDescription(formDescriptionText + "\n ※元々のご自身の注文数にこの数を足したものが、注文可能数です。");  //変更フォーム

  if (sumStockNum === 0) {    //もし商品全部の残数（sumStockNum）が0だったらフォーム受付停止
    if (form.isAcceptingResponses() === true) {  // ★フォームがまだ開いているときだけ！
      form.setCustomClosedFormMessage("本日の注文受付は【完売】のため終了いたしました。またのご利用をお待ちしております！");
      form.setAcceptingResponses(false); // 新規受付を停止
    }
  } else {
    //フォームが受付停止になっていたら再開
    if (form.isAcceptingResponses() === false) {
      form.setAcceptingResponses(true);
    }
  }
}