目次
複数人で1つのスプレッドシートを更新していると、ステータス欄や区分欄に表記のゆらぎが必ず発生します。「未着」と書く人もいれば「未着手」「なし」「-」と打ち込む人もおり、完了の表記も「完了」「完了！」「処理済み」などバラバラになりがちです。このような状態では、COUNTIF関数やQUERY関数、ピボットテーブルを使った集計が正しく機能せず、集計ロジック側で大量の条件分岐を書くハメになります。
画面のUI操作で入力規則を設定しても、他のセルから値だけではなくセルごとコピー＆ペーストされた際に入力規則が上書きされて消え去ってしまうケースが頻発します。そこで、スプレッドシートの入力欄に決めた値だけのプルダウンを付ける処理をGoogle Apps Script（GAS）でプログラム側から明示的にセットし、表記のゆらぎを根本から遮断する仕組みを作ります。
表記揺れが引き起こす集計不全とプルダウン化後の変化
やる前の状態では、入力者の主観やその場の気分によってデータが作られます。例えば進捗状況を記録する列において、ある人は「進捗」と入れ、別の人は「進行中」「対応中」と入力します。人間が見ればすべて「現在作業を行っている状態」であると理解できますが、プログラムや数式はこれらを全く別のデータとして判定します。その結果、全体の進捗率を計算する数式が狂い、ダッシュボードの数字が実態と乖離するという問題が起こります。
また、自由入力を許可したままだと、全角と半角の混在や、末尾に不要なスペースが入るといった目視では気づきにくいミスも蓄積されます。こうした不純物の混入は、後工程でPythonやVBA、あるいは別のGASにデータを連携して自動処理を走らせる際に、予期せぬ実行エラーを引き起こす主要な原因となります。
やったあとの状態では、選択できる値が完全に固定され、リストに存在しない文字列の入力を強制的に弾くことが可能になります。入力欄にはあらかじめ定義された選択肢しか入らないため、集計用の数式は単純な完全一致で記述できるようになり、データのクリーニング作業や集計エラーの調査にかかっていた時間を大幅に削減できます。手動操作に頼らずGASで入力規則をセットすることで、シート上のルールが常に一定に保たれます。
手入力を許す運用からGASによる動的制御への切り替え
手動で「スプレッドシート プルダウンを付ける」設定を行った場合、その場の設定としては正しく機能します。しかし、実務で運用を続けると必ず崩壊のタイミングが訪れます。最も多いのが、過去の行や外部のExcelからデータをまとめてコピー＆ペーストしてきた時です。ペースト操作によって既存の入力規則が削除され、知らぬ間に再び自由入力が可能な無防備なセルに戻ってしまいます。
この問題に対処するためには、GASを用いて定期実行トリガーやフォーム送信時トリガーのタイミングで入力規則を再適用するか、行が新規追加された際にスクリプト経由で入力規則を流し込む設計に変更するのが効果的です。コードとして入力規則の定義を保持しておけば、万が一誰かがセルを上書きして規則を破壊したとしても、スクリプトを一回動かすだけで最初から決められた状態へと即座に復旧できます。
設定を人の記憶や手動操作に依存させるのではなく、コードとしてリポジトリやプロジェクト内に保持することにより、シートの再作成や別部署への水平展開も一瞬で完了するようになります。運用中のシート保守コストを下げる上でも、スクリプトによる入力規則の付与は非常に強い威力を発揮します。
指定した文字列のみを許可する入力規則のスクリプト適用
このコードは、入力列に対して指定したリスト以外の入力を一切認めない強力なプルダウンを設定したい時に使います。新規シートを作成した直後や、既存のシートの入力ルールをリセットして再定義するタイミングで実行するのが効果的です。
const rule = SpreadsheetApp.newDataValidation().requireValueInList(["未着","進捗","完了"], true).build();
sheet.getRange("D2:D").setDataValidation(rule);コード内で使われている `requireValueInList` は、配列として渡した文字列のリスト（この例では「未着」「進捗」「完了」）だけを選択肢として提示するメソッドです。自由入力を防ぐための重要なポイントは、第2引数に `true` を渡している点です。この第2引数は「無効なデータが入力された際に拒否するかどうか」を制御しています。
ここがずれる点としてよくあるのが、第2引数を省略したり `false` に設定してしまっているケースです。`false` に設定すると、プルダウン自体は表示されるものの、ユーザーがキーボードから直接「進捗中」といった別の文字列を打ち込んだ時に、セルに赤い警告マークが付くだけで値の入力自体は成功してしまいます。これでは表記のゆらぎを止めるという本来の目的を果たせません。
この回避策として、常に第2引数へ `true` を明示的に指定します。これにより、規定のリストにない値を打ち込もうとしたユーザーに対してダイアログが表示され、入力自体がキャンセルされます。入力欄に対して「この値以外は一切受け付けない」という強い制約を課すことで、表記ゆれの発生源を完全に塞ぐことができます。
また、`getRange(“D2:D”)` のように列全体に対して一括適用する場合、データが存在しない遥か下の空白行にまで大量のプルダウンが生成されることになります。行数が数万行に及ぶ大きなシートでは、不要なセルへの入力規則の設定がシートの描画速度やGASの処理パフォーマンスを低下させる原因になります。これを回避するには、実データが存在する最終行（`getLastRow()`）を動的に取得して範囲を絞り込むか、あるいは運用上必要な範囲のみに限定して適用する工夫が実務では求められます。
すでに汚れたデータが存在するシートへ適用する際の注意
やる前の状態として、すでに数百行のデータが入力されており、その中に「未着手」「対応中」「完了（本日）」といった表記ゆれが充満しているシートが存在するとします。この状態のまま上記のスクリプトを実行して入力規則を上書き設定した場合、既存のデータが自動的に「未着」「進捗」「完了」のいずれかに置換されるわけではありません。
既存のセルに入っている不適切な値はそのまま残り、セルの隅に無効なデータであることを示す警告が表示されるだけの状態になります。一見するとプルダウンが適用されたように見えますが、データベースとしては不整合な文字列が残ったままであり、集計結果も狂ったまま放置されてしまいます。
やったあとの状態として完全に整った環境を作るためには、スクリプトで入力規則を流し込む前に、既存のデータを置換処理（クレンジング）するステップを挟む必要があります。例えば、GAS側でシート全体のデータを二次元配列として取得し、「未着手」や「なし」という文字列を「未着」に変換し、「対応中」「作業中」を「進捗」へと置換した上でシートに書き戻し、その直後に入力規則を流し込むという順番で処理を組み立てます。
データ自体のクレンジングと、これ以降の汚染を防ぐための入力規則付与をセットで行うことで、初めて過去データと今後の入力データの両方で一貫性が担保された堅牢なシートが完成します。
マスターシート連携と組み合わせたメンテナンス性の向上
ソースコード内に `[“未着”,”進捗”,”完了”]` のように選択肢を直接ハードコードする方法は、選択肢が将来にわたって絶対に変わらない場合には手軽で有効です。しかし、実務では「保留」というステータスを新たに追加したい、「完了」を「完了（検証済）」に変更したいといった変更要求が頻繁に発生します。
コード内に配列を直書きしていると、選択肢が増減するたびにGASのエディタを開き、コードを修正してデプロイし直す必要が出てきます。これは非エンジニアが混ざる現場では運用のボトルネックとなり、修正漏れや誤ったコード改変のリスクを生み出します。
この問題を回避し、安全かつ長期的に運用し続けるためには、プルダウンの選択肢自体を別タブ（マスターシート）のセル範囲から動的に読み込む設計に書き換えるのが定石です。GASには `requireValueInRange` というメソッドも用意されており、マスター用のセル範囲を指定して入力規則を作成できます。
このように設定の保持場所とロジックの実行場所を分離しておくことで、現場の運用担当者はマスターシートの文字を書き換えるだけでプルダウンの選択肢を自由に変更できるようになります。スクリプト側はその変更された範囲を読み込んで各入力欄へ規則を再適用するだけの役割に専念できるため、コードの保守性を高めつつ、現場の変更要求にも柔軟に対応できる強固なシステムを構築できます。
