データの入力規則とは
ご存知、「データ」タブにある奴です。
「りんご、みかん、ぶどう」の中からしかセルの値を選べないようにするリストの指定や、数字のみを受け付けるようにするなどのバリデーションをシート側で設定できます。
GASで値を取得してアレコレするにしても、まず入力規則で縛りを掛けておくと扱いが楽で良いですよね。
今現在スプレッドシートで提供している入力条件は
- 選択肢リスト
- 数字（大小等不等、指定範囲）
- テキスト（メールアドレス、URL）
- 日付（以前以降、期間指定、有効値判定）
- チェックボックス
そして
- カスタム数式
ですね。今日初めて使ったので大まかな使い方をまとめてみます。
あとGASの方から入力規則を扱う方法も。
カスタム数式で柔軟なバリデーション
今回
「基本的には日付、でも日付が不要な項目には”-”(ハイフン)も入れられるようにしたい」
という条件のセルバリデーションが必要になりました。
デフォルトで日付だけの設定はできるものの、これだと”-”(ハイフン)を受け付けてくれません。
「無効なデータ：入力拒否」にしてるので。
カスタム数式の指定方法
薄っすらプレースホルダーで＝ISODD(A1)と記載があります。
これはスプレッドシート関数（SUMとかのアレです）で、「A1が奇数かどうか」を真偽値で返してくれる関数です。
つまりカスタム関数は基本的にスプレッドシート関数と同じ書き方で、
今回の場合なら
「当該セルの内容が日付、または”-”の場合はTRUEを返す」
という条件で式を組み立てればOKですね。
=OR(ISDATE(A1), A1 = "-")
こんな感じで大丈夫そうです。これをA1セルのカスタム数式に入力して、後は他のセルにコピーすれば、座標は勝手にシートの方で合わせてくれます。
あまりスプレッドシート関数は使ったことが無いのですが、公式リファレンスを見て組み合わせたら割と簡単にできました。
[Google スプレッドシートの関数リスト]
(https://support.google.com/docs/table/25273?hl=ja)
データの入力規則をGASで指定する方法
普通にタブからセルに設定してそれでめでたしめでたしでも良いのですが、後々のメンテを考えると、指定範囲にGASから設定出来た方が楽なんじゃないかという気がしてきました。
複数人で使うシートだと、不慮の操作で別のセルをコピーで上書きして入力規則や条件付き書式が虫食いになっていったり、最下行に足していく作業でミスっておかしくなるということもありがちなので…
正攻法：DataValidationBuilderクラスを使う
var cell = SpreadsheetApp.getActive().getRange('A1');
var range = SpreadsheetApp.getActive().getRange('B1:B10');
var rule = SpreadsheetApp.newDataValidation().requireValueInRange(range).build();
cell.setDataValidation(rule);
公式のサンプルでは、セルA1をバリデーションを掛ける対象、B1:B10をドロップダウンリストの中身として取得してrequireValueInRangeメソッドでリストの値以外を受け付けないようにしています。
その他にもrequireValueInList(values)でリストを配列で直接指定したり
requireTextIsEmail()でメールアドレスとして有効かをチェックするなど、
シートの方から設定できる条件はGASからも設定できます。
もちろんrequireFormulaSatisfied(formula)を使ってカスタム関数を指定することも可能です。しかし…
シートの座標を指定するタイプのカスタム数式には不向き…
引数のformulaを文字列で渡すので、シート上でセルをコピーした時のような座標合わせが行われません。。
座標の部分を変数にして、for文とかでセルごとに設定していけばできなくはないんだろうけど何かもう考えるだけでめんどくさいですよね。
正規表現とか使って一定条件の文字列を含むかどうかの判定とか、座標を取得せずに判定できる条件には便利だと思いますが、この仕様だとちょっとDataValidationBuilderで今回のカスタム数式を設定するのは諦めました。
代替案：カスタム数式用のセルを用意して、入力規則だけをコピーする
コピーアンドペーストする時に右クリックで特殊貼り付けから、値だけとか条件付き書式だけとか色々選べますよね。
あれと同じノリで、実際どこか別のシートとかに直接入力規則を指定したセルを用意して、それをコピーして入力規則だけを指定範囲にペーストするという方法です。
これで今回は上手く行きました。
特殊貼り付けの指定方法
Range.copyTo(destination, copyPasteType, transposed)メソッドを使います。
destinationには入力規則を設定したいセルの範囲を
copyPasteTypeにはペーストタイプを（この場合は入力規則）
transposedには~~(多分)座標をペースト位置によって合わせるかの真偽値
を指定します。~~
→12/02追記
上記のように考えてtransposedにtrueを指定していたのですが、別のシートからシート関数をコピーする場合にtrueだとシートも含めた座標指定になってしまい、試しにfalseにしてみたら解決して、他の入力規則なども特に影響を受けずに上手くコピーできているので、falseにした方がよさそうです。
それにしても実際この引数って何を指定してるのか、英語読んでも分からん…
transposed -- Boolean
Whether the range should be pasted in its transposed orientation.
transposedは指定しないと単純にコピーアンドペーストしたっぽい挙動をしたので忘れずに指定しましょう。
copyPasteTypeの指定
この中で入力規則だけをペーストするのはPASTE_DATA_VALIDATIONですね。
よって、入力規則のGASからの設定はこれで大丈夫です。
const validateRange = thisShData.sheet.getRange(startRowNum, colNum, numRows)
dateValidCell.copyTo(validateRange, SpreadsheetApp.CopyPasteType.PASTE_DATA_VALIDATION, false)
シート上で設定してみると、一つのセルに設定できる入力規則は一つだけのようなので、元々他の規則が入っているセルに指定すると上書きされます。
以上です。