GASでスプレッドシートのフィルタ条件を複数設定する（OR条件）

これだ！という正解の記事が見当たらなかったので備忘として纏めておきます。

困ったこと

スプレッドシートのフィルタをGASで自動作成し「前月の末尾より後の日付」である行のみ表示したいので次のようなコードを書いた。

function addFilterToSheet() {

  //前月の末日を取得
  const today = new Date();
  const firstDayOfCurrentMonth  = new Date(today.getFullYear(),today.getMonth(),1);
  const lastDayOfPreviousMonth  = new Date(firstDayOfCurrentMonth - 1);

  const sheet = SpreadsheetApp.getActiveSheet();

  //フィルタ条件を設定
  const criteria = SpreadsheetApp.newFilterCriteria()
    .whenDateAfter(lastDayOfPreviousMonth)
    .build(); 

  //既存のフィルタを削除
  if(sheet.getFilter() != null) {
    sheet.getFilter().remove();
  }

  //フィルタを作成
  sheet.getDataRange().createFilter().setColumnFilterCriteria(1, criteria);

  return false;
}

すると、フィルタ結果として空白セルまで表示されなくなったのでOR条件を追加したい。

実現したいこと

1. スプレッドシートのフィルタをGASで作成（更新）する
2. 同じ列に複数のOR条件を設定して「前月の末尾より後」または「セルの値が空白」であるフィルタを作成する

結論

条件を数式で作成する

修正後

条件

A列の日付を「前月の末尾より後」または「セルの値が空白」でフィルタする

コード

function addFilterToSheet() {

  //前月の末日を取得
  const today = new Date();
  const firstDayOfCurrentMonth  = new Date(today.getFullYear(),today.getMonth(),1);
  const lastDayOfPreviousMonth  = new Date(firstDayOfCurrentMonth - 1);

  const sheet = SpreadsheetApp.getActiveSheet();

  //条件となる数式を文字列で作成
  const formula = '=OR(A2>DATE(' + Utilities.formatDate(lastDayOfPreviousMonth,'JST','yyyy,MM,dd') + '),A2="")';

  //数式をフィルタ条件として設定
  const criteria = SpreadsheetApp.newFilterCriteria()
    .whenFormulaSatisfied(formula)
    .build(); 

  //既存のフィルタを削除
  if(sheet.getFilter() != null) {
    sheet.getFilter().remove();
  }

  //フィルタを作成
  sheet.getDataRange().createFilter().setColumnFilterCriteria(1, criteria);

  return false;

}

実行結果

ちゃんと空白セルも表示されました

フィルタにはカスタム数式がセットされています

おまけ

私が実現したかったこのコードでは「GASをトリガーしたタイミングでフィルターを更新する」前提なので、毎月実行しないとフィルタ条件が古いままです。

一回数式をセットしたらずっと「前月の末尾より後」または「セルの値が空白」でフィルタしたいのであれば、数式の部分に日付をテキストで埋め込むのではなく、スプレッドシートの関数をセットしてやればよいです。

数式の書き換え

const formula = '=OR(A2>DATE(' + Utilities.formatDate(lastDayOfPreviousMonth,'JST','yyyy,MM,dd') + '),A2="")'

任意のタイミングでフィルタしたいので、数式には日付を固定で設定していますが、EOMONTH関数を書いてやればスプレッドシートを開いたタイミングですぐにフィルタされます。

const formula = '=OR(A2>EOMONTH(TODAY(), -1),A2="")';

このやり方では「月が変わった直後」にスプレッドシートを開くと前月以前がすべてフィルタされてしまうので、私の場合は使っていません。