【GAS】スプレッドシートの入力規則を作成するnewDataValidationメソッドをマスター

  • このエントリーをはてなブックマークに追加
  • Pocket
  • LINEで送る

スプレッドシートを作成する際に指定された文字や数値以外を誤って入力してしまうことがあります。ダブルチェックをしたり他の人に確認してもらうことでミスを減らすことはできますが報告書など毎日作成するものや大規模なものを作成する場合マンパワーでは限界があります。

そこでスプレッドシートで使用できる機能としてデータの入力規則という設定があります。

入力規則を行うことで指定していない入力値を入力すると警告やエラー表示が出るため、作業ミスが大幅に低減されることができます。

今回はその入力規則をスクリプトから設定することが出来るnewDataValidationメソッドについてご紹介します。

newDataValidation()メソッド(入力規則)

スプレッドシートの入力規則を作成できるメソッドです。このメソッドに続けて条件などのメソッドを設定することにより入力規則を作成することができます。

スプレッドシートの作成メソッドは

newConditionalFormatRuleメソッド(条件付き書式)

newDataValidation()メソッド(入力規則)このブログ

newFilterCriteria()メソッド(フィルタ作成)

newRichTextValue() メソッド(リッチテキスト)

newTextStyle()メソッド(テキストスタイル)

があります。(各リンクに詳しい説明があるのでご覧ください。)

クラス:SpreadsheetApp

スプレッドシートでの表示・設定方法はデータタブからデータの入力規則をクリックすると以下の画像のように出てきます。

今回ご紹介するnewDataValidation()メソッドはこの画面の設定をスクリプトに記述することによって毎回手動で設定することなく起動することが可能になります。

2020-09-07 20.40.25

注意:初めてコードを実行する際にアクセス権限の許可を求められますが許可をしてください。

アクセス権限についての詳細は以下の記事に詳しく載っています。

無料アカウントでGoogleサービスへのスコープを設定する方法

構造(横にスクロールできます)

  SpreadsheetApp.newDataValidation().条件.無効なデータの場合.警告テキストの設定.build()

入力規則を作成するためのメソッドです。

newDataValidation()の後に

条件

条件に当てはまらない時の処理

当てはまらない場合にテキストを作成するか

などのメソッドを追加していき最後にbuild()でビルダーを構築させます。

サンプルスクリプト1ではGoogleリファレンスの例を元に説明します。以下がスクリプトです。

サンプルスクリプト1

function newDataValidation1(){
  var cell = SpreadsheetApp.getActive().getRange('E1');//E1の範囲を取得
  var rule = SpreadsheetApp.newDataValidation()
    .requireNumberBetween(1, 100)
    .setAllowInvalid(false)
    .setHelpText('数値は1から100までの間で入力してください。')
    .build();
  cell.setDataValidation(rule);//作成した入力規則を2行目で取得した範囲に設定
}

入力規則メソッドが作成されている箇所は3行目から7行目までの以下の部分です。

  var rule = SpreadsheetApp.newDataValidation()
    .requireNumberBetween(1, 100)//条件
    .setAllowInvalid(false)//無効なデータの場合
    .setHelpText('数値は1から100までの間で入力してください。')//警告メッセージの設定
    .build();//ビルダーの構築

今回使用されているメソッドについて解説をすると

.requireNumberBetween(1, 100):条件:数字が次の間(1〜100)
.setAllowInvalid(false):上の条件では無い場合:true なら警告のみでデータはそのまま、falseなら警告がポップアップされデータを拒否します。
.setHelpText(‘数値は1から100までの間で入力してください。’):警告メッセージを表示
.build():ビルダーを構築する際に必要

今回はE1に数字1〜100以外が入力された場合、警告を出す入力規則を作成しています。

スクリプトを起動した後スプレッドシートのA1セルに試しに1000と入力すると以下のようにエラーが出て入力が拒否されます。

2020-09-04 10.11.25

E1セルを選択してからデータタブのデータの入力規制をクリックすると入力規制が入っているのがわかりますね。

2020-09-04 10.11.14

 

サンプルスクリプト2

function newDataValidation2() {
  var cell = SpreadsheetApp.getActive().getRange('A1');//A1の範囲を取得
  var rule = SpreadsheetApp.newDataValidation()
      .requireTextContains('テスト')//入力したテキストが含まれているか
      .setAllowInvalid(true)//今回はtrueに
      .build();
  cell.setDataValidation(rule);
}

サンプルスクリプト1との変更箇所は

4行目のrequireTextContains

5行目がfalseからtrue。.setHelpTextを削除

requireTextContainsは(‘****’)がセルに含まれているかを条件にするメソッドです。

それではスプレッドシートのA1にサンプルと入力してみましょう。

以下のように警告メッセージは出てきますがスクリプト1のように入力拒否はされません。

これは.setAllowInvalidの設定をtrueにしたため警告のみとなります。2020-09-08 13.47.24

ちなみにこの.setAllowInvalid(true)はデフォルトの設定なので記入しない場合でも同じ結果になります。

また.setHelpTextも設定しない場合は自動でメッセージを作成します。

ですのでメッセージをオリジナルで作成する必要がない場合は記入する必要はありません。

条件などの入力規則で使用する様々なメソッドについては別記事(公開予定)で詳しく説明していますのでそちらをご覧ください。

 

まとめ

今回はスプレッドシートの入力規則をスクリプトで作成するためのメソッドnewDataValidationを紹介しました。

自分が作成する報告書や作業書などはもちろん他の作業者に書いてもらいたい資料に決められた文字や数字を入れて欲しい場合この入力規則をスクリプトで設定することによって作業者のミスだけでなく確認などにも役に立つかと思います。

そして今までこの作業を手動でやっていた方にとってはスクリプトに記述することによってスプレッドシートを起動するだけで毎回同じ設定の状態にすることができるので作業効率化にもなるメソッドだと思います。

こういったメソッドをたくさん使いこなして作業がどんどん楽になっていくといいですね。

ここまでご覧いただきありがとうございました。

SNSでもご購読できます。

コメントを残す

*