【GAS】Googleフォーム×在庫管理！複数商品対応③ 手動更新・スマホ対応編

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

前提

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

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

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

スプレッドシート構成

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

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

おさらい

前回は、複数商品対応第2弾として、商品の残数を動的にフォームの説明欄や選択肢に反映させる処理を作りました。これらはフォーム送信をトリガーにして動きます。
今回は、上記の処理をシート上の更新ボタンや、スマホからチェックボックスを操作するだけで実行できるようにアップデートしていきます。

今回のミッション

今までメイン関数から呼び出されて動いていた updateFormOptions を、
シート上の更新ボタンやスマホから直接呼び出してフォーム内容を更新させる仕組みを実装します。

今回のキモはコレ！

itemMaster の有無で条件分岐

メイン関数から呼び出される場合は、updateFormOptions(itemMaster) のように、引数に商品マスタ itemMaster を入れていたため updateFormOptions 内でも、メイン関数から持ってきた itemMaster を使うことができました。しかし、直接呼び出す場合は itemMaster はありません。 updateFormOptions 内で設定する必要があります。
このように、もし itemMaster がない場合はシートから商品マスタを取得するコードを、updateFormOptions の最初に入れておきます。

  if (!itemMaster) {
    const sheet_summary = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("集計シート");

    //シートから手動で更新した場合（スマホからも含む）は、念のためスプレッドシートを強制再計算させる
    SpreadsheetApp.flush();

    // B1から右方向のデータ終端（列数取得用）
    const summaryLastCol = sheet_summary.getRange("B1").getNextDataCell(SpreadsheetApp.Direction.NEXT).getColumn();

    // 上部で宣言された引数の変数（itemMaster）にそのままデータを代入する
    itemMaster = sheet_summary.getRange(1, 1, 6, summaryLastCol).getValues();
  }

これによって、

• メイン関数から呼び出された場合（itemMasterをすでに持っている）：
  この if に入らずに、メイン関数からの itemMaster を使う。
• 単独で実行された場合（itemMasterを持っていない）：
  この if 内の処理が実行され、シートの商品マスタを読み込んで itemMaster を作成

1つの関数で2つの役割を持たせることができました。

更新ボタンのクリックでGASを動かす場合は、シート上に図形描画で実行ボタンを作成し、「スクリプトの割り当て」で「updateFormOptions」を入れてください。
詳しい方法はこちらをご覧ください。
更新ボタン作成（関数名は今回の"updateFormOptions"に修正してください）

編集時トリガー（インストールトリガー）

スマホのアプリでは実行ボタンが押せません。
以前の、単独商品バージョンのときは残数セルに書き込みがされたら実行というロジックでした。
今回は複数セルであり、それぞれの商品のセルが書き換わるたびにGASが実行されると、処理が重なって実行されるリスクがあるため、シート上（今回は「集計シート」のC7セル）に更新ボタンの代わりのチェックボックスを配置し、チェックがされたらGASが動く、というロジックにしてみましょう。

以下の関数を作り、たった今編集されたセルが更新のチェックボックスのセルであり、かつ、「true（チェックされている）」であれば updateFormOptions を呼び出してフォーム更新処理を走らせる、とさせます。

function onEditTrigger(e) {
  const range = e.range;
  const sheet = range.getSheet();

  // 「集計シート」の「C7セル」が更新されたかチェック
  if (sheet.getName() === "集計シート" && range.getA1Notation() === "C7") {

    // チェックボックスが TRUE（チェックされた）の場合のみ実行
    if (range.getValue() === true) {

      // サブ関数を呼び出し（引数なし＝手動更新モード）
      updateFormOptions();

      // チェックを自動で FALSE（オフ）に戻す
      range.uncheck();  //range.setValue(false);　でもOK      
    }
  }
}

処理が終わったらチェックボックスのチェックを自動で外す処理も盛り込んでいます。

チェックボックスを外す処理 range.uncheck(); は、range.setValue(false); でもOKです。
チェックボックスは内部的に
　- ON　→　true
　- OFF　→　false
という値を持っているため
setValue で値を false に書き換える　と　uncheck()でチェックを外す
は同じ挙動になります。

コードを作ったら、必ずトリガーの設定をしましょう。
設定方法はこの記事に書かれています。
トリガー設定方法（実行する関数は"onEditTrigger"にします）

動作確認

このように、残数がすべて0になっている状態から、スマホのスプレッドシートアプリから販売上限を12に増やして「フォーム更新」チェックを入れてみます。
残数がすべて0なので、フォームの受付も停止されている状態です。

スマホからこのようにC7セルのチェックボックスにチェックするだけで...

フォームの残数が更新されました。選択肢にも反映しています。

処理が終わると自動でチェックボックスのチェックが外れます。

まとめ

今回は、前回作成したフォーム内容更新関数 updateFormOptions を、メイン関数を通さずにシート上から更新ボタンやチェックボックスを使って動かせるようにアップデートしました。
管理者が販売上限を変更したり、特定商品の残数を0にして受付停止にしたり、注文した内容の修正依頼を受けた管理者が手動で数量変更したり、などの内容を、ワンクリックでフォームへ反映できるようになりました。

※チェックボックスで動かす場合は、更新ボタンはないほうが使いやすいかもしれません。お好みでどちらか/両方を選択してくださいね。

今回の成果コード

※掲載しているのは、今回の変更内容を反映した完成版です。今回の説明対象ではないサブ関数は、これまでの記事で作成したものをそのまま利用しています。

フォームの選択肢＆説明欄を更新

function updateFormOptions(itemMaster) {

  //手動更新する場合はitemMasterがないのでここで作成
  if (!itemMaster) {
    const sheet_summary = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("集計シート");

    //シートから手動で更新した場合（スマホからも含む）は、念のためスプレッドシートを強制再計算させる
    SpreadsheetApp.flush();

    // B1から右方向のデータ終端（列数取得用）
    const summaryLastCol = sheet_summary.getRange("B1").getNextDataCell(SpreadsheetApp.Direction.NEXT).getColumn();

    // 上部で宣言された引数の変数（itemMaster）にそのままデータを代入する
    itemMaster = sheet_summary.getRange(1, 1, 6, summaryLastCol).getValues();
  }

  // 1. 注文フォームをID指定で開く
  const form = FormApp.openById("1oIjcSPisT0ly4V7CdjyveV49RFyoZado1yvNbWGuYgc"); // ここに注文フォームの管理用IDを入れてください
  const corForm = FormApp.openById("17tu34ObuNs9M-DYrhYqX5es99uO-p25xj2m6A0AwUio"); // ここに変更フォームの管理用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だったらフォーム受付停止
    form.setCustomClosedFormMessage("本日の注文受付は【完売】のため終了いたしました。またのご利用をお待ちしております！");
    form.setAcceptingResponses(false); // 新規受付を停止
  } else {
    //フォームが受付停止になっていたら再開
    if (form.isAcceptingResponses() === false) {
      form.setAcceptingResponses(true);
    }
  }
}

チェックボックスから更新

function onEditTrigger(e) {
  const range = e.range;
  const sheet = range.getSheet();

  // 「集計シート」の「C7セル」が更新されたかチェック
  if (sheet.getName() === "集計シート" && range.getA1Notation() === "C7") {

    // チェックボックスが TRUE（チェックされた）の場合のみ実行
    if (range.getValue() === true) {

      // サブ関数を呼び出し（引数なし＝手動更新モード）
      updateFormOptions();

      // チェックを自動で FALSE（オフ）に戻す
      range.uncheck();  //range.setValue(false);　でもOK
      
    }
  }
}