【GAS】スプレッドシートのフィルター条件を作成するnewFilterCriteriaメソッドをマスター

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

スプレッドシートを使用している方の中にはデータを作成し管理している方も多いかと思います。大量のデータを管理していくとあるデータだけを抽出したいという状況も出てきます。

手動で設定して項目を抽出する方法もありますが、スクリプトで設定をすることによって手軽にフィルター作成ができてしまうnewFilterCriteriaメソッドについて今回はご紹介します。この機能を個別に設定すれば担当者毎に別のフィルターを設定することができますので間違った所に記入してしまうというミスを防ぐことができます。


newFilterCriteria() :フィルター条件

スプレッドシートのフィルター条件を新たに作成するためのメソッドです。このメソッドに続けて設定条件、設定する内容や設定する範囲を設定することができます。

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

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

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

newFilterCriteria()メソッド(フィルタ作成)このブログ

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

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

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

クラス:SpreadsheetApp

注意:初めてコードを実行する際にアクセス権限の許可を求められますが許可をしてください。アクセス権限についての詳細は以下の記事に詳しく載っています。

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

構造

  SpreadsheetApp.newFilterCriteria().トリガー.build()

こちらがフィルター条件ビルダーを作成するための基本メソッドです。

newFilterCriteria()の後に条件となるトリガーをスクリプトで記入をし最後にbuild()でビルダーを構築させます。

スプレッドシート上ではデータ→フィルタを作成をクリックすると表示されます。

下の画面の設定今回のメソッドではスクリプトで設定することができます。

サンプルスクリプト1

今回は以下のスプレッドシートデータを使用しA列に空白のセルがある場合フィルターをかけて非表示にするスクリプトを作成します。

以下がスクリプトです。スプレッドシート内のスクリプトに記載してください。

function newFilterCriteria(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var rule = SpreadsheetApp.newFilterCriteria()
    .whenCellNotEmpty() 
    .build();
  if(sheet.getFilter() != null) {
     sheet.getFilter().remove();
  }
  sheet.getDataRange().createFilter().setColumnFilterCriteria(1, rule);
}

newFilterCriteria()以降に以下の条件をを設定しています。

  //サンプルスクリプト1 3行目〜5行目
  var rule = SpreadsheetApp.newFilterCriteria() 
    .whenCellNotEmpty() //トリガー セルが空白ではない場合表示する
    .build(); //ビルダーを構築

*フィルター条件を作成する場合、新しいスプレッドシートであっても6行目〜8行目の

  if(sheet.getFilter() != null) { 
     sheet.getFilter().remove();//シートのフィルター条件を削除する
  }

フィルター条件を取り除くスクリプト記載が必要です。このスクリプト記載がないと9行目で

setColumnFilterCriteriaがnullです。

というエラーが出てしまうので注意してください。

そして10行目の

sheet.getDataRange().createFilter().setColumnFilterCriteria(1, rule);

でフィルターを設定しています。

setColumnFilterCriteria(列,フィルター条件)なので1行目に3行目〜5行目のフィルター条件を設定するという記述となります。

スクリプトを実行すると1列目の空白以外の部分のみ表示されます。

サンプルスクリプト2

次は1列目と2列目にフィルター条件を設定してみます。

以下のスプレットシートのデータの1列目はサンプルスクリプト1と同じ空白を非表示、2列目は東京以外は非表示に設定します。

以下がスクリプトです。スプレッドシート内のスクリプトに記載してください。

function newFilterCriteria2(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var rule1 = SpreadsheetApp.newFilterCriteria()
    .whenCellNotEmpty()
    .build();
  var rule2 = SpreadsheetApp.newFilterCriteria()
    .whenTextEqualTo('東京') //条件 東京と書かれたテキストがある場合表示
    .build();
  if(sheet.getFilter() != null) {
     sheet.getFilter().remove();
  }
  sheet.getDataRange().createFilter()
    .setColumnFilterCriteria(1, rule1)
    .setColumnFilterCriteria(2, rule2);
}

今回は変数rule1とrule2に分けてフィルター条件を作成しています。

rule1はサンプルスクリプト1と同じです。

rule2ではwhenTextEqualTo(‘表示したい文字’)メソッドを使用して東京と記載がある場合に表示する記載をしています。

そして12行目〜14行目の

sheet.getDataRange().createFilter().setColumnFilterCriteria(1, rule1).setColumnFilterCriteria(2, rule2);

で2つのフィルター条件を設定しています。

setColumnFilterCriteriaは複数繋げられるので条件は何個あっても1文で作成位することができます。

スクリプトを実行すると以下の画像のように1列目空白非表示、2列目東京のみ表示となります。

この他の詳しいフィルター条件設定メソッドについては

Class newFilterCriteria(公開予定)で紹介します。

 

ソートについて

昇順、降順でソートをしたい場合なのですが、このnewFilterCriteriaでは作成することができません。

ですがcreateFilterの後にsortメソッドを記載することで簡単に設定することができます。

ソートフィルターの作成方法

sort(列番号, 昇順ならtrue 降順ならfalse)

function test3(){
  var sheet = SpreadsheetApp.getActiveSheet();
  if(sheet.getFilter() != null) {
    sheet.getFilter().remove();
  }
  sheet.getDataRange().createFilter().sort(3, false);
 }

ここでは6行目で3列目を降順にソートするスクリプトを作成しています。

ですのでサンプルスクリプト2のスプレッドシートで実行してみると以下のように3列目の名前が降順でフィルターがかけられていますね。

まとめ

今回はフィルター条件を作成できるnewFilterCriteriaメソッドを紹介しました。

フィルターを設定することで大量のデータが見やすくなりますが、この機能をスクリプトで実行することによって、

ボタンクリックで担当者専用のフィルターが作成することが出来る

スプレッドシートを起動するとフィルターが自動でかけられるといった設定をすることが出来る

などといった条件設定が可能になります。

担当者によっては不要な行を非表示にすることによって作業ミスを減らしたり、間違った箇所に入力をすることを防ぐことができますね。

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

SNSでもご購読できます。

コメントを残す

*