Google スプレッドシートで２段プルダウンをGAS(Google Apps Script)で作成

【導入】
私は事業所を選択したあとに担当者名が表示されるプルダウンを作成しようと考えました。
主にXLOOKUP関数とVLOOKUP関数で作成された記事を参考にしました。
一応形は完成しましたがいくつかの問題がありました。
本記事では、この一般的な問題に対処するための具体的なソリューションを、Google Apps Scriptを用いて紹介します。

【問題の定義】
多くのスプレッドシートユーザーが直面する問題は、セルの削除や追加によって関数が適切に動作しなくなることです。特に、XLOOKUPやVLOOKUP関数を使った場合、データの整合性が保てなくなることがしばしばです。

【具体例】
・利用者台帳シート
T列 = 事業所名
U列 = 担当者名
・事業所リスト
A列 = 事業所名
B列からJ列 = 担当者名

初期のアプローチ: 関数の使用
最初に、XLOOKUP関数を使用して、選択した事業所名に基づいて担当者名を動的に表示しようとしました。しかし、行を追加や削除すると、参照がずれてしまい、担当者名が正しく表示されなくなる問題が発生しました。

エラー事例

=XLOOKUP(A6, '事業所リスト'!$A$1:$A$100, '事業所リスト'!$B$1:$K$100, "")

【Google Apps Scriptの導入】
これらの問題を解決するために、GAS(Google Apps Script)を導入しました。具体的には、スプレッドシートのセルが編集されるたびに自動的にトリガーされるonEdit関数を用いて、担当者名のプルダウンリストを自動更新するスクリプトを作成しました。

スクリプト例

function onEdit(e) {
  if (!e || !e.range) return;
  var sheet = e.range.getSheet();
  var editedColumn = e.range.getColumn();
  if (sheet.getName() === '利用者台帳' && editedColumn === 20) {
    updateDropdowns(e.range);
  }
}

function updateDropdowns(cell) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('利用者台帳');
  var officeListSheet = ss.getSheetByName('事業所リスト');
  var officeName = cell.getValue();
  var staffList = findStaff(officeName, officeListSheet);
  setDropdownList(cell, staffList, sheet);
}

function findStaff(officeName, sheet) {
  var dataRange = sheet.getRange('A2:J' + sheet.getLastRow()).getValues();
  for (var i = 0; i < dataRange.length; i++) {
    if (dataRange[i][0] === officeName) {
      return dataRange[i].slice(1).filter(function(name) { return name !== ""; });
    }
  }
  return [];
}

function setDropdownList(cell, staffList, sheet) {
  var dropdownCell = sheet.getRange(cell.getRow(), 21); // U列
  var rule = SpreadsheetApp.newDataValidation().requireValueInList(staffList, true).build();
  dropdownCell.setDataValidation(rule);
}

【アウトプットのイメージ】

【まとめ】
このスクリプトによって、利用者台帳シートの事業所名が変更された際に、自動的に担当者名のプルダウンリストが更新されるようになりました。これにより、誰が操作してもエラーが発生しないようになり、管理が非常に楽になりました。スプレッドシートの自動化とデータ整合性の維持に関心のある方にとって、この記事が有益な情報源となれば幸いです。