GASを使用した条件分岐によるプルダウン作成

Question

前提

スプレッドシートに入力した内容から
別シートの対照表を基にプルダウンの選択肢を表示させたい

実現したいこと

・スプレッドシート上でプルダウンを選択、その内容を基に隣の列に新たなプルダウンを作成
・作成されたプルダウンの内容は別シートの対照表を参照
・作成される列は6列(B列で項目選択、C～H列にプルダウン作成)

発生している問題・エラーメッセージ

下記、コードで
基準となる情報を”使用シート”で選択すると
その隣の1列に”設定シート”の対照表を基に”使用シート”上にプルダウンが作成されます。
※ここまでは問題なし

問題は”使用シート”で項目を選択し、隣に6列まで”設定シート”の対照表を参照して
プルダウンを作成したいのですが、上手くいきません。

＜使用シート＞
B列　　　　　　C列　　　　　　D列・・・
データ1　　　　①
(プルダウン)　　(プルダウン)

A列：空白(他データを入れるため)　　
B列：このシート上で選択
C列：GASで”設定シート”の対照表のB列を基にプルダウン作成
D列：GASで”設定シート”の対照表のC列を基にプルダウン作成

”設定シート”
A列　　　　　　B列　　　　　　C列　　　　　　D列・・・
データ1　　　　①　　　　　　　③　　　　　　 ④
データ1　　　　②　　　　　　　④　　　　　　 ④
データ2　　　　③　　　　　　　⑤　　　　　　 ⑥　
データ2　　　　③　　　　　　　⑤　　　　　　 ⑦
・
・

該当のソースコード

/**
 * ①グローバル変数の定義
 */
const USE_SHEET_NAME     = "使用シート";
const SETTING_SHEET_NAME = "設定シート";
const CATEGORY1_COL_NUM  = 2; // B列

/**
 * ②本体
 */
function onEdit(e) {
  if (!isTargetCol(e)) return;

  const category1Value = e.value;
  const changedRow     = e.range.getRow();
  const changedCol     = e.range.getColumn();
  const useSheet       = e.source.getSheetByName(USE_SHEET_NAME);

  // ③設定シート のデータ(二次元配列)
  const settingData = e.source.getSheetByName(SETTING_SHEET_NAME).getDataRange().getValues();

  // ④カテゴリ1に対応するカテゴリ2を入れておくところ
  let catgory2List = [];

  // ⑤設定シート のデータの中から、「選択されたカテゴリ1」に対応するカテゴリ2を取り出すところ
  settingData.forEach( row => {
    if (row[0] === category1Value) {
      catgory2List.push(row[1]);
    }
  });

  if(catgory2List.length === 0) return;

  // ⑥編集されたセルの右のセルにカテゴリ2のプルダウンをセットする
  const range = useSheet.getRange(changedRow, changedCol + 1);
  const rule  = SpreadsheetApp.newDataValidation().requireValueInList(catgory2List, true);
  rule.setAllowInvalid(false).build();
  range.setDataValidation(rule);
}

/**
 * ⑦プルダウン連動をさせる列かどうかの判断
 */
function isTargetCol(e) {
  // 値が削除されたときはvalueが undefになるので無視
  if (!e.range.getValue()) return false; // ※1

  // 関係ないシートのとき
  if (e.source.getSheetName() !== USE_SHEET_NAME) return false;

  // 列が違うとき
  if (e.range.getColumn() != CATEGORY1_COL_NUM) return false;

  return true;
}

補足情報（FW/ツールのバージョンなど）

やりたい事が類似したコードが下記URLにあったので
それを基に変数追加したりと色々試してみたのですが、どうしてもうまくいかず。。
https://qiita.com/sakaimo/items/689e9f3c516bb5a96c19

コードをいじりすぎてわけわからなくなったので
上記のコードはURLのものを貼り付けました。

分かる方お力添えをいただければ幸いです。
何卒宜しくお願いいたします。

Comments

[退会済みユーザー]

「”使用シート”で項目を選択し、隣に6列まで”設定シート”の対照表を参照してプルダウンを作成」するとのことなので、
”設定シート"のB列～G列までの6列分（固定）のデータをプルダウンの元データとして持ってくればよい、という理解でよろしいでしょうか？

[tkt1964]

回答遅くなり申し訳ございません。
はい、そうです。
回答も早速試してみます。
誠にありがとうございます！

[tkt1964]

gnoirさん
出来ました、誠にありがとうございました！

Answer 1

下記のような形ではいかがでしょうか。

/**
 * ①グローバル変数の定義
 */
const USE_SHEET_NAME = "使用シート";
const SETTING_SHEET_NAME = "設定シート";
const CATEGORY1_COL_NUM = 2; // B列

/**
 * ②本体
 */
function onEdit(e) {
  if (!isTargetCol(e)) return;

  const category1Value = e.value;
  const changedRow = e.range.getRow();
  const changedCol = e.range.getColumn();
  const useSheet = e.source.getSheetByName(USE_SHEET_NAME);

  // 設定シート のデータ(二次元配列)
  const settingData = e.source.getSheetByName(SETTING_SHEET_NAME).getDataRange().getValues();

  // 編集されたセルの右のセルにカテゴリ2のプルダウンをセットする
  for (let i = 1; i <= 6; i++) {
    const range = useSheet.getRange(changedRow, changedCol + i);
    const rule = getRule(category1Value, settingData, i);
    if (rule == null) continue;
    range.setDataValidation(rule);
  }
}

function getRule(category1Value, settingData, column) {
  // カテゴリ1に対応するカテゴリ2を入れておくところ
  let catgory2List = [];

  // 設定シート のデータの中から、「選択されたカテゴリ1」に対応するカテゴリ2を取り出すところ
  settingData.forEach(row => {
    if (row[0] === category1Value) {
      catgory2List.push(row[column]);
    }
  });

  const rule = SpreadsheetApp.newDataValidation().requireValueInList(catgory2List, true);
  rule.setAllowInvalid(false).build();
  return rule;
}

/**
 * ⑦プルダウン連動をさせる列かどうかの判断
 */
function isTargetCol(e) {
  // 値が削除されたときはvalueが undefになるので無視
  if (!e.range.getValue()) return false; // ※1

  // 関係ないシートのとき
  if (e.source.getSheetName() !== USE_SHEET_NAME) return false;

  // 列が違うとき
  if (e.range.getColumn() != CATEGORY1_COL_NUM) return false;

  return true;
}

説明

元のコードだと1列しか作っていないプルダウンを作る部分を、関数として切り出しています。
引数columnが、プルダウンの元データを読み込む設定シートの列（1ならばB列）となります。

function getRule(category1Value, settingData, column) {
  // カテゴリ1に対応するカテゴリ2を入れておくところ
  let catgory2List = [];

  // 設定シート のデータの中から、「選択されたカテゴリ1」に対応するカテゴリ2を取り出すところ
  settingData.forEach(row => {
    if (row[0] === category1Value) {
      catgory2List.push(row[column]);
    }
  });

  const rule = SpreadsheetApp.newDataValidation().requireValueInList(catgory2List, true);
  rule.setAllowInvalid(false).build();
  return rule;
}

呼び出し元では、ループを使って列を切りかえて、上記の getRule 関数を呼び出し、読み込んだプルダウンの情報を各列に設定するようにします。

function onEdit(e) {
（略）
  // 編集されたセルの右のセルにカテゴリ2のプルダウンをセットする
  for (let i = 1; i <= 6; i++) {
    const range = useSheet.getRange(changedRow, changedCol + i);
    const rule = getRule(category1Value, settingData, i);
    if (rule == null) continue;
    range.setDataValidation(rule);
  }
}