GASでオートフィルタのフィルタリングを操作する
この記事は
スプレッドシートのオートフィルタのフィルタをGASで操作する、
という内容についての作業メモです。
2~3行のスクリプトでサクッと出来るかと思いましたが、少し面倒だったので記事にしたいと思います。
フィルタルール
今回は、日付形式の値が入った列に対し、列の値が空白の行だけ表示するようにフィルタします。
手順
スプレッドシートのフィルタリングをGASで操作するには、基本は下記の流れでプログラムを記述します。
- フィルタルールを作成する
- フィルタルールを適用する
今回は、日付形式の列をフィルタに使用するので下記のようにします。
- フィルタする列の値を配列に取得する
- 日付のフォーマットを変換する
- フィルタルールを作成する
- フィルタルールを適用する
1.「特定の列が空欄のセルのみを表示」という条件でフィルタする
まずは、答えのコードを示します。
GoogleDriveを開き、GASファイルを新規作成して下記のように記述しました。
//15列目の対応日列が、空欄の行だけ表示するようにフィルタする。
//スプシには既にオートフィルタが適用されている。
function IncompleteFilter() {
var spreadsheet = SpreadsheetApp.openById('key');
var st = spreadsheet.getSheetByName("st_name")
//コンテナバインドスクリプトにする場合↓
//var spreadsheet = SpreadsheetApp.getActive();
//var st = spreadsheet.getActiveSheet();
var maxrow = st.getLastRow();
var data = st.getRange(2, 15, maxrow,1).getValues(); //①
for(var i = 0; i < data.length; i++) {
//② 日付の形式を変換する
if(data[i]!= ""){
data[i] = Utilities.formatDate( data[i][0], "JST" ,"yyyy/MM/dd" );
}else if(data[i]== ""){
data[i] = null;
}
}
var criteria = SpreadsheetApp.newFilterCriteria()
//③フィルタルール作成
.setHiddenValues(data)
.build();
//③フィルタルール適用
spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(15, criteria);
};
解説
要点だけ説明していきます。
フィルタルールは表示しない値を設定する
GASでフィルタする場合、表示しない値を設定するようです。
マクロの記録により.setHiddenValues(data)というメソッドがあることがわかったので、.setVisibleValuesもあるだろうと思ったのですが、使ってみたところ、「今は使えないのでsetHiddenに表示しない値を渡せ」というような内容のmsgが表示されました。
そこで、一旦列の値をすべて配列に入れて、その配列から表示する値を抜き取った配列を作り、その配列を.setHiddenValuesに渡しました。
日付データが入った配列を、フォーマットを変換する
フィルタに使用する列が日付形式の場合は、
セルには"2020/01/01"という形で文字列で入っている値でも、配列に入れるとDate形式になりました。
例：Mon Apr 27 19:33:33 GMT+09:00 2020
例えば、上記のデータをこのまま.setHiddenValues()の引数に渡しても、「"2020/04/27"」の行はフィルタされません。
お互いの形式が違うのでマッチしない為です。そのためUtilities.formatDateを使って日付形式を変換する必要がありました。