【GAS】送信された内容によって自身、他のフォームの質問の選択肢を増やすことができるフォーム。及び在庫管理システム

このシステムを作るに至った背景（読み飛ばし可）

ある日、製品を処理した結果の処理前と処理後の製品の在庫の増減を記録する、GASとgoogleフォーム（以下"フォーム"）、googleスプレッドシート（以下"スプレッドシート"）を用いたシステムを作りました。データとして製品それぞれの名前、納品先会社名、処理前と処理後の在庫数がスプレッドシートに記入してあり、フォームに入力者の名前、処理した数、NGの数、会社、製品名等を入力して送信するとその内容がデータに反映され、その結果によってスプレッドシートの該当製品の各在庫数が変動するというものです。このシステムにおいて、フォームの選択肢である製品の種類は取引先の企業が増えるたび、製品が増えるたびに増えていきます。その製品と会社名をその都度フォームに手動で追加するのは手間なので、新しい製品をフォームで追加すると自動でフォームの選択肢にその製品が追加されるスクリプトを書きました。
※なお、現在は他の方法（フォームをその都度フォームの設計図となる情報から作り直す）を採用したのでこの仕組みは採用していません。
※この記事に関しては相談、了承済み、被監視

本編

サンプル

以下のフォルダからコピーしてdefine.gsの最初の3行に適切なIDを入れることで動作を確認できます
IDはシートとフォームのURLの/で挟まれている「10fvROVXs1GsnU0a3lVRtdHKAC0KybKqJVJCQQP-vHU0」くらいの長さの部分です。
https://drive.google.com/drive/folders/1MEUIn5REGHaKHWZeQf6xnE66Kz_rg4yB?usp=sharing

このプログラムでできること

ある１つのフォームに送信された内容でそのフォームを含めた２つのフォームの選択肢を増やす。
在庫管理を行う。
（説明の簡略化のために２つのフォームを編集することとしますが増やすことが可能です。）

サンプル会社の状況

ある会社で製品の処理をする業務を行っていて、その処理を行うと製品の個数が増える。増える個数は製品それぞれについて異なる。複数の企業とその製品を入荷、納品する取引をしている。それぞれの製品の処理前、処理後の在庫を監視したい。新しい取引先や新製品が増えたり、処理したときに増える個数が変化したりする。

システムの構造

• 新製品登録、情報変更（フォーム）：新製品（既存or新しい取引先）の登録、登録済みの製品の情報書き換え
• 作業報告（フォーム）：製品の入荷、処理、出荷を報告するフォーム。報告の際に作業した個数を記入する
• データベース(スプレッドシート)：各種情報、フォームの解答のシートとスクリプトが書き込まれている
  • 製品登録：フォームの解答を受け入れるシート
  • 製品の処理：フォームの解答を受け入れるシート
  • 製品データ：製品それぞれのデータが１行ずつ記入されているシート
  • 会社名リスト：会社が存在するかどうかを判定するために用いられるシート（スクリプトの書き方によっては不要）
  • URLリスト：整理用。不要

フォームとスプレッドシートの要素

フォームの会社選択の質問の選択肢は解凍後にそれぞれその回答に対応するセクションに進むように設定されている。

新製品登録フォーム

機能

• 新しい会社名、新しい製品名で登録する
• 既存の会社名、新しい製品名で登録する。既存の会社名は選択可能
• 既存の会社名、既存の製品名で登録されている内容を編集する（フォーム下部の選択欄を使用）
  更新した際の変化
• 新しい会社名が入力された場合、既存の会社名に新しい会社名を追加
• 新しい製品名、会社名が入力された場合、製品名を選択する質問に製品名を追加
  
  

製品の処理フォーム

機能

• 製品の入荷数を入力する
• 製品の処理数を入力する
• 製品の出荷数を入力する
  更新した際の変化
• 新しい製品名、会社名が入力された場合、製品名を選択する質問に製品名を追加

データベーススプレッドシート

フォームの入力を受けるシート

新しい会社名が入力されていくたびに「～～の製品」の列が右に追加されていく

製品データ

製品の情報が一製品につき一行に記載されている。このシートの情報をもとに在庫の計算などを行う。新しい製品が入力された際にはこのシートの最終行に要素が追加される。このシートの左端の列で製品が存在するか否かを判定している。行の順序を変えてもシステムは問題なく稼働する。

会社名リスト

会社名が増えるたびに末尾に会社名が追加されていくシート。このシートの会社名と新しく入力された会社名が合致するかの判定と、既存の会社名を選択する質問の選択肢に持ちいる。製品データの会社名の列を利用すれば不要となるがシンプルにするために設置。

AppsScript内で設定するトリガー

フォームが送信されたときに、「送信されたフォームによって異なる関数を実行する関数」が実行されるように設定する。

コード

実行イメージ

コードはGASエディタ内で以下の4つのファイルに分けられている。

• define.gs　→操作するシートとフォームの定義、製品名と会社名のリストを作成する。
• whichFtoGo.gs →送信されたフォームによって以下の二つの関数のうちどちらを実行するかを選択し実行する関数。
  この関数がこのシステムのトリガーであり、フォームが送信されたときにこの関数が実行されるように設定しておく。
• NewProduct.gs →新商品の登録、製品情報の編集を行う。
  まず入力された製品名が既存の製品名のリストに含まれているかどうかを判定
  ├ 既存の製品名の場合 →入力された内容に製品データを更新、更新の記録を入力。
  └ 新しい製品名の場合 →既存の会社名に含まれているかを判定
  　├ 既存の会社名の場合 →新しい製品名を質問の選択肢に足し、製品データに新しい行を追加
  　└ 新しい会社名の場合 →新しい会社名、製品名を質問選択肢に足し、製品データに新しい行を追加
• Process.gs →製品と作業の種類によってそれぞれ異なる計算を行って製品データに入力されている製品在庫の増減を記入する。
  まず入力されたフォームの作業内容の質問の回答が「入荷、処理、出荷」のいずれであるかを判定
  ├ 入荷の場合は製品データの該当製品の処理前在庫を増加
  ├ 処理の場合は製品データの該当製品の処理前在庫を減少させ処理後在庫を増加率に応じて増加
  └ 出荷の場合は製品データの該当製品の処理後在庫を減少

define.gs

フォームとシートのIDからそれぞれを取得して使用可能な形にする。
また製品のリストと会社名のリストを判定などでよく用いるためそれぞれ一次配列として保存する。

define.gs

const FormNewProduct = FormApp.openById('新製品登録フォームのID'); //新製品登録フォームのIDを元にフォームを取得
const FormProcess = FormApp.openById('製品処理フォームのID'); //製品の処理フォームのIDを元にフォームを取得
const sheetiD = 'データベースシートのID';//sheetのID　
const SheetNewProduct = SpreadsheetApp.openById(sheetiD).getSheetByName('新製品登録、情報変更'); //シートを取得
const SheetProcess = SpreadsheetApp.openById(sheetiD).getSheetByName('作業報告'); //シートを取得
const SheetProductData = SpreadsheetApp.openById(sheetiD).getSheetByName('製品データ'); //シートを取得
const SheetCompanyList = SpreadsheetApp.openById(sheetiD).getSheetByName('会社名リスト'); //シートを取得

const ProductList = SheetProductData.getRange(2,1,SheetProductData.getLastRow(),1).getValues().flat();//製品データの左端の二行目から最終行までを取得。getValuesすると二次配列なのでflatで一次配列にする。
console.log("製品名リスト　"+ProductList);//製品名のリスト。製品が存在するかどうかの判定等に用いる。
const CompanyList = SheetCompanyList.getRange(1,1,SheetCompanyList.getLastRow(),1).getValues().flat();//会社名リストの一行目から最終行までを取得。getValuesすると二次配列なのでflatで一次配列にする。
console.log("会社名リスト　"+CompanyList);//会社名のリスト。会社が存在するかどうかの判定等に用いる。

whichFtoGo.gs

フォームが送信されたときに実行され、送信されたフォームに応じて異なる関数を実行する。eを用いることで更新のあったシートを特定する。

whichFtoGo.gs

function whichFtoGo(e) {
  //更新のあったシートのシート名を取得
  var sheetName = e.range.getSheet().getName();

  //更新のあったシート名の種類によって実行する関数を変更
  if (sheetName === '製品登録') {
    console.log('製品登録');
    NewProduct();
  }
  else if(sheetName=='製品の処理'){
    console.log('製品の処理');
    Process(); 
  }
}

NewProduct.gs

新製品情報フォームが送信されたときに実行され、製品の情報の編集もしくは、新しい製品名と会社名の選択肢への追加と製品データへの登録を行う。

NewProduct.gs

function NewProduct() {
  //情報を格納
  var infoRange =  SheetNewProduct.getRange(SheetNewProduct.getLastRow(),1,1,SheetNewProduct.getLastColumn());//新製品登録シートの最終行の左から右までの範囲
  var Info = infoRange.getValues().flat(); //最終列の左端から右端までを配列に格納
  console.log(Info);//[タイムスタンプ	入力者	既存の会社名	新しい会社名	新しい製品名	処理時の増加倍率	会社名	シルフカンパニーの製品	デボンコーポレーションの製品	エーテル財団の製品]
  var WhenEnter = Info[0];//タイムスタンプ
  var WhoEnter = Info[1];//入力者
  var KnownCompanyName = Info[2];//既存の会社名
  var NewCompanyName = Info[3];//新しい会社名。という名前だが既存なのを忘れてこちらに記入する可能性もあるので、既存の会社名と統一する
  if(NewCompanyName == null){ //新しい会社名の欄が空欄の場合は既存の会社名から代入する。
    NewCompanyName = KnownCompanyName;
  }
  var ProcessedProductName = Info[4];//新しい製品名
  var ProcessRate = Info[5];//処理時の増加倍率
  var ChosenCompanyName = Info[6];//会社名
  var ChosenProduct = Info.slice(7).filter(v => v)[0];  //sliceで最初の6要素を切り落とし、会社名の行よりも右のセル（Info配列における7）の中で記入されているものをフィルタリングして代入
  if(ChosenProduct != undefined){ //もし既存の部品が選択されていた場合、ProcessedProductNameに代入する。
    console.log("編集先製品"+ChosenProduct);
    ProcessedProductName = ChosenProduct;
  }


  //会社と製品のリストで存在するかどうかを判定
  var ProductIfExist = ProductList.indexOf(ProcessedProductName);  //-1なら存在しない
  console.log(ProductIfExist);
  var ComapyIfExist = CompanyList.indexOf(NewCompanyName);  //-1なら存在しない
  console.log(ComapyIfExist);


  //それぞれ存在するかで処理を分岐
  if(ProductIfExist != -1){  //すでに製品が存在する場合
    console.log("すでに存在する製品名が新製品登録に入力されました。")
    SheetProductData.getRange(ProductIfExist+2,6).setValue(ProcessRate)
    SheetProductData.getRange(ProductIfExist+2,3,1,3).setValues([[WhenEnter,"情報更新",WhoEnter]]); //更新の反映

  }else{  //新しい製品の場合
    if(ComapyIfExist != -1){  //すでに会社が存在する場合
      ProductAdd(FormNewProduct,3,ProcessedProductName,ComapyIfExist);     //◆◆◆◆◆◆◆◆製品登録の処理
      ProductAdd(FormProcess,2,ProcessedProductName,ComapyIfExist);     //◆◆◆◆◆◆◆◆製品処理の処理
    }else{  //新しい会社の場合
      SheetCompanyList.appendRow([NewCompanyName]);
      CompanyList.push(NewCompanyName);
      CompanyAdd(FormNewProduct,3,ProcessedProductName,NewCompanyName);    //◆◆◆◆◆◆◆◆製品登録の処理
      CompanyAdd(FormProcess,2,ProcessedProductName,NewCompanyName);    //◆◆◆◆◆◆◆◆製品処理の処理
      //新製品登録の二番目の既存の会社名の入力の質問に会社名を追加する
      var companyChoice = FormNewProduct.getItems(FormApp.ItemType.LIST)[1].asListItem();
      var choiceA = [];
      CompanyList.forEach(companyName =>{
        choiceA.push(companyChoice.createChoice(companyName));      //選択肢とセクション区切りを合わせた形で配列choiceAに格納
      })
      companyChoice.setChoices(choiceA); //質問項目の選択肢を更新
    }
    SheetProductData.appendRow([ProcessedProductName,NewCompanyName,WhenEnter,"製品登録",WhoEnter,ProcessRate]);
  }
}
 
//◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆
//会社選択フォームの該当会社に品名の選択肢を追加。の関数
function ProductAdd(FormX,x,NewProductName,ComapyIfExist){     //引数（変更するフォーム、上からいくつめが会社選択のプルダウンか、hantei、品名）
  var listItem = FormX.getItems(FormApp.ItemType.LIST)[ComapyIfExist+x].asListItem();
  var choice = listItem.getChoices();//項目を取得
  console.log(listItem.getTitle())
  choice.push(listItem.createChoice(NewProductName,FormApp.PageNavigationType.SUBMIT));
  listItem.setChoices(choice); //指定した質問項目の選択肢を更新
}
//◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆
//会社選択フォームにその会社に飛ぶ選択肢を足し、フォームの最後に新しいセクションと質問を足す。の関数
function CompanyAdd(FormX,x,NewProductName,NewCompanyName){   //引数（変更するフォーム、上からいくつ目の会社選択のプルダウンか、品名、会社名）
  FormX.addPageBreakItem().setTitle(NewCompanyName+'の品名');//　質問を追加してタイトルを設定
    var newQ = FormX.addListItem();  //プルダウンの質問を追加。newQには質問が格納されている
    newQ.setTitle(NewCompanyName+'の品名を選んでください');//質問のタイトルを追加
    newQ.setChoices([newQ.createChoice(NewProductName,FormApp.PageNavigationType.SUBMIT)]);    //新しい会社名での選択肢を追加
  //会社名を選ぶ質問に選択肢を加える
  var Sitem = FormX.getItems(FormApp.ItemType.LIST)[x-1].asListItem(); //作業日報のx目の質問（会社名）を取得[
  console.log(Sitem.getTitle())
  var choiceA =[]; //選択肢をセクションと合わせて最終的に格納しformに反映する配列
  for(var i = 0;i<CompanyList.length;i++){   //会社の数だけループ
    j = FormX.getItems(FormApp.ItemType.PAGE_BREAK)[i].asPageBreakItem();       //セクションを格納する配列にそのIDで取得したセクション区切りアイテムを取得
    choiceA.push(Sitem.createChoice(CompanyList[i],j));      //選択肢とセクション区切りを合わせた形で配列choiceAに格納
  }
  Sitem.setChoices(choiceA);　//会社を選ぶ選択肢としてchoiceA配列を適用し選択肢を更新
}

Process.gs

作業日報フォームが送信されたときに実行される。製品の入荷、処理、出荷の入力に対してそれらの数値を計算し製品データに反映する。

Process.gs

function Process(){
  var infoRange =  SheetProcess.getRange(SheetProcess.getLastRow(),1,1,SheetProcess.getLastColumn());//製品の処理シートの最終行の左から右までの範囲
  var Info = infoRange.getValues().flat(); //最終列の左端から右端までを配列に格納
  console.log(Info);//[タイムスタンプ	入力者	作業の種類	入荷or処理or出荷した個数	会社名	シルフカンパニーの製品	デボンコーポレーションの製品	エーテル財団の製品]
  var WhenEnter = Info[0];//タイムスタンプ
  var WhoEnter = Info[1];//入力者
  var WhatProcess = Info[2];//作業の種類
  var ProcessAmount = Info[3];//入荷or処理or出荷した個数
  var CompanyName = Info[4];//会社名
  var Product = Info.slice(5).filter(v => v)[0];  //sliceで最初の5要素を切り落とし、会社名の行よりも右のセル（Info配列における5）の中で記入されているものをフィルタリングして代入
  console.log(Product);

  //indexOfでProductをProductList内から検索する。含まれない場合は-1,含む場合は１番目を０として出力される。
  var whereProduct = ProductList.indexOf(Product);
  console.log(whereProduct);
  if(whereProduct == -1){
    console.error("製品データに該当製品名が存在しません。");
    return;
  }

  //製品データ内の該当製品のデータを取得して配列に格納
  var ProductData = SheetProductData.getRange(whereProduct+2,6,1,3).getValues().flat(); 
  console.log(ProductData);
  var ProcessRate = ProductData[0]; //処理時の増加倍率
  var StockBefore = ProductData[1]; //処理前の在庫
  var StockAfter = ProductData[2]; //処理後の在庫

  //作業の内容によって処理を分岐
  switch(WhatProcess){
    case "製品の入荷":
      console.log("入荷")
      StockBefore = StockBefore + ProcessAmount;  //処理前の製品の数に入荷数を加算
      break;
    case "製品の処理":
      console.log("処理")
      StockBefore = StockBefore - ProcessAmount;  //処理前の製品の数から処理数を減算
      StockAfter = StockAfter + ProcessAmount * ProcessRate;  //処理後の製品の数に処理数*処理時の増加倍率を加算
      break;
    case "製品の出荷":
      console.log("出荷")
      StockAfter = StockAfter - ProcessAmount;  //処理後の製品の数から出荷数を減算
      break;
  }  

  //結果の製品データへの反映
  SheetProductData.getRange(whereProduct+2,3,1,3).setValues([[WhenEnter,WhatProcess,WhoEnter]]); //更新の反映
  SheetProductData.getRange(whereProduct+2,7,1,2).setValues([[StockBefore,StockAfter]]); //在庫数の反映

}