前提
スプレッドシートに入力した内容から
別シートの対照表を基にプルダウンの選択肢を表示させたい
実現したいこと
・スプレッドシート上でプルダウンを選択、その内容を基に隣の列に新たなプルダウンを作成
・作成されたプルダウンの内容は別シートの対照表を参照
・作成される列は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 ③ ⑤ ⑦
・
・
該当のソースコード
GAS
/** * ①グローバル変数の定義 */ 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のものを貼り付けました。
分かる方お力添えをいただければ幸いです。
何卒宜しくお願いいたします。
まだ回答がついていません
会員登録して回答してみよう