GASを使用した条件分岐によるデータ貼り付けしたあとプルダウン作成を追加したい

Question

前提

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

実現したいこと

・スプレッドシート上で既に設定したプルダウンを選択、その内容を基に別シートの対照表の最初に該当するデータを貼り付け
・貼り付けたセルにプルダウンを作成
・作成されたプルダウンの内容は別シートの対照表を参照
・貼り付ける/プルダウンを作成される列は6列(B列で項目選択、C～H列にプルダウン作成)

発生している問題

以前ここで回答いただいた下記、コードで
基準となる情報を”使用シート”で選択すると
その隣の列へ”設定シート”の対照表を基に”使用シート”上にプルダウンが作成されます。

今回したいのは、プルダウントと貼り付けになります。

＜使用シート＞
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();

  // 編集されたセルの右のセルにカテゴリ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]);
    }
  });
  if (catgory2List.length === 0) return;

  // 編集されたセルの右以降のセルにカテゴリ2のデータを貼り付ける
  const rule = SpreadsheetApp.newDataValidation().requireValueInList(catgory2List, true);
  rule.editsheet.appendRow(catgory2List);
  return rule;
}
//追加箇所

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

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

  // 編集されたセルの右以降のセルにカテゴリ2のプルダウンをセットする
  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;
}

試したこと

プルダウンをセットする前に、貼り付けするコードを入れてみましたが
エラーが出てしまい、こちらに再度ご質問させていただきました。

追加箇所に二次元配列のデータを貼り付けるコードを入れてみましたが
上手く作動しません。
入力してもデータ貼り付けなし、プルダウン作成もしなくなりました。

お手数ですが、ご尽力いただければ幸いです。
何卒宜しくお願いいたします。

Comments

[k.a_teratail]

> エラーが出てしまい
どのようなエラーが表示されたのでしょうか？

[tkt1964]

ご質問ありがとうございます。
あれから、自分で編集し直してみまして
//~プルダウンをセットする　内に
setValuesを入れて、エラー自体は発生しなくなったのですが
動作自体しなくなってしまいました。

[k.a_teratail]

> エラー自体は発生しなくなったのですが
> 動作自体しなくなってしまいました。

console.log()などを入れて、どこで終了しているか確認するのはどうでしょうか？

[k.a_teratail]

追加で、「getRule」の関数が二つあります。
これのせいで、期待している結果にならないとかでは無いでしょうか？

同じ関数名がある場合、後に定義された関数で実行されます。
----------------------------
function myFunction1() { aaa() }

function aaa() { console.log("先") }
function aaa() { console.log("後") }
----------------------------
実行ログ
19:32:45 情報 後
----------------------------

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, initialValue] = getRuleAndInitialValue(category1Value, settingData, i);

    // 入力規則を設定
    range.setDataValidation(rule);

    // 初期値を設定
    range.setValue(initialValue);
  }
}

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

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

  // プルダウンのデータを設定する
  const rule = SpreadsheetApp.newDataValidation().requireValueInList(catgory2List, true);
  rule.setAllowInvalid(false).build();
  return [rule, catgory2List[0]];
}

/**
 * ⑦プルダウン連動をさせる列かどうかの判断
 */
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;
}

　

説明

　
前のコードから変更したところ：
① getRuleAndInitialValue 関数：入力規則（プルダウンの設定項目）とともに、初期値（該当するデータの最初の値）も返すようにしています。
（catgory2List[0] が初期値（＝条件に当てはまるデータの最初の値）です）

  return [rule, catgory2List[0]];

　
② onEdit 関数の中で getRuleAndInitialValue 関数から値を受け取るところでは、入力規則とともに初期値を受け取り、
入力規則を設定した後に、そのセルに初期値を上書きしています。

    // 設定シートから入力規則と初期値を受け取る。
    const [rule, initialValue] = getRuleAndInitialValue(category1Value, settingData, i);

    // 入力規則を設定
    range.setDataValidation(rule);

    // 初期値を設定
    range.setValue(initialValue);

　
③ 使用シートのプルダウンで選んだデータが設定シートの中にない場合は、空白文字を設定するように変えています。

  if (catgory2List.length === 0) {
    catgory2List.push('');
  }

Comments

[tkt1964]

誠にありがとうございます。
お答え頂戴した日から確認していたのですが
どうしても、”使用シート”のプルダウンをデータ1を関数にすると反応しなくなるのですが
ここはどの様に解消すればよろしいでしょうか？
ご教授いただければ幸いです。

何卒宜しくお願いいたします。

[退会済みユーザー]

> 「どうしても、”使用シート”のプルダウンをデータ1を関数にすると反応しなくなる」

使用シートのB1セルに数式を入れているということでしょうか。それとも別のやり方でしょうか。