スプレッドシートのプルダウンリスト連動をGASで実現する

背景

Googleスプレッドシートで「プルダウンリスト連動」をする方法を調べると、いくつもHITします。

私がみた記事はスプレッドシートの関数(VLOOKUPとか)で実現している記事だったのですが、GASの知識が無くても扱える点では有利なのですが、 入力欄が増えると、それに合わせて式のコピーや入力規則のコピーが必要になる みたいなのです。

入力行を増やすたびにコピーミスや誤って式を消してしまったりするのが嫌だなーって思ったのです。

これは何の記事？

「プルダウン連動」をGASで実現したらどうなるかな、をやってみました。
もっとうまいやり方があったらコメント欄によろしくおねがいします！

完成図

「カテゴリ1」を選ぶと、それに連動する「カテゴリ2」の選択肢だけが表示されるようにしたい。

↑これが 使う側のシート とします。

↑これが 設定シート にカテゴリ1、カテゴリ2が定義されています。

スプレッドシートへの仕込み

「カテゴリ1」については通常の「データの入力規則」で、「設定シート」の「A2:A」を指定して設定しておく。
「使う側のシート」のB2に設定して、B3以降はこれをコピーしちゃう。

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;
}

requireValueInList(values, showDropdown) については 公式リファレンス 参照。

※1 注意点

if (!e.range.getValue()) return false; // ※1

↑ここですが

if (!e.value) return false;

ってやりたくなるでしょ！？
でもこれだと、「ペースト」されたときに undefined になるんです。
(「使う側のシート」のカテゴリ1の列に「木管楽器」という文字列をコピペしても、 e.value は undefined なのです)

...と言っておきながら、下記のデメリットのように「コピペ」に対応できていないのですけどね。

メリット

• おそらく「入力規則」を使える人は多いので、カテゴリ1の入力規則だけ設定できればあとは行データが下に増えても「行のコピー」で増やせる。
• 「設定シート」も難しくないので誰でも増やせる。行データを下に増やしても、途中に行を挿入してもOK。
• GASが分かる人にとっては、スプレッドシート関数を駆使してやるよりわかりやすい。(ここは私の主観ですけど)

デメリット

• GASを書けないとメンテナンスできない。
• 動作が遅い。(onEidtで動作させているため、動作完了までに時間がかかる)
• 「使う側のシート」の「カテゴリ1」の列に「コピペ」したときに、カテゴリ2が出てこない。(★1)
• 同じように、「B列の複数行をコピペ」してもカテゴリ2が出てこない。(★2)

改善ポイント

• ★1 はペーストされたことの検知がきっとできる、、、のかな。(ちと今は力尽きたのであとで調べる。どなたか知ってたら教えて！)
• ★2 は「編集された複数セルをループさせてカテゴリ2を出す処理を入れる」ことで改善できそうですね。うん、きっとできる。
• カテゴリ2に連動するカテゴリ3も、、、できそうですね！
• カテゴリ1を設定 → カテゴリ2を設定 → カテゴリ1を変更 したときに、カテゴリ2が残りっぱなしになる。(まぁスプレッドシートの機能で「警告」が出るから「なにかおかしいぞ」は気付けるはず。気づいてほしい)

↓ 警告