【コピペで使える】GASでスプレッドシートのフィルタ条件を作成する

2021 6/16

2020年11月2日 2021年6月16日

URLをコピーする

URLをコピーしました！

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

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

newFilterCriteria()　：フィルター条件

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

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

newConditionalFormatRuleメソッド（条件付き書式）

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

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

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

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

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

クラス：SpreadsheetApp

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

無料のGmailアカウントでGASに初回の実行許可を与える方法

構造

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

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

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

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

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

サンプルスクリプト１

今回は以下のスプレッドシートデータを使用し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()以降に以下の条件をを設定しています。

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

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

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

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

setColumnFilterCriteriaがnullです。

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

そして１０行目の

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

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

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

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

サンプルスクリプト２

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

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

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

function newFilterCriteria２(){
  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はサンプルスクリプト１と同じです。

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

そして１２行目〜１４行目の

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

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

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

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

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

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);
 }

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

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