広告

Google apps script でfilterをかける方法｜Sheet APIと連携したら上手くいった

2018年1月18日

Google apps scriptでfilterを設置するメソッド

Google apps scriptでfilterを扱うには setBasicFilter を使います。

使い方としては

  1. フィルターをかける範囲を指定
  2. フィルターをかける基準を指定
  3. requestメソッドでAPI連携（setBasicFilter）
  4. updateメソッドでスプレッドシート更新

と大まかにこんな感じで、javascriptの配列を使って設置していきます。では実際に。

対象のシートを定義する

先に対象シートを変数に定義しておきます。

/*変数定義*/
var ss,sht,filterSet,columnIndex,request
ss = SpreadsheetApp.getActiveSpreadsheet();
sht = ss.getSheetByName("シート1");//対象のシートを変数へ格納
filterSet = {};//FilterSetで{}の中を操作
  • filterset（配列）でフィルター範囲の操作
  • columnIndexはフィルターで絞る対象列
  • requestはAPI接続

といった手順で設定ですね。

フィルターを設置する範囲を指定する

フィルターを設置する範囲（range）を指定します。

/*フィルターをかけるシートのSheetIdを取得*/
filterSet.range = {
sheetId:sht.getSheetId()
};

Sheet APIでフィルター更新する際にsheetIdが必要となるため、 sht.getsheetId で "シート1" のIdを取得します。

フィルターをかける基準を決める

フィルターをかける基準（criteria）を決めます。

/*フィルターをかける列*/
filterSet.criteria = {};
columnIndex = 2;//３列目の値選択

今回は「血液型」の列にフィルターをかけたいので、アクティブな位置から3列目 columnIndex = 2 を指定します。

フィルターで非表示にする項目を指定する

フィルターをかける列で非表示にする値を指定（3列目を基準にhidden.valueの値を非表示）します。

/*フィルターで非表示にする項目*/
filterSet['criteria'][columnIndex]={
'hiddenValues':["A型","O型","AB型"]
};

'hiddenValues':["A型","O型","AB型"] で3列目のA型とO型とAB型を非表示に指定しました。

hidden（ヒドゥン）
javascriptで非表示・表示を操作するオブジェクト。

hiddenオブジェクト

requestメソッドでSheet APIと連携

requestメソッドを使ってSheet APIと連携➜ setBasicfilterメソッド を動かしてフィルターを設置します。

/*requestメソッドでAPI接続してフィルターをかける*/
request = {
"setBasicFilter": {
"filter": filterSet
}
};

あひる
あひる
ここでSheetAPIと連携するんだね
ううこ
ううこ
requestでAPIを呼び出してsetBasicfilterを設置するのだ

SetBasicFilterRequest（）
シートに関連付けられた基本フィルタを設定します。

SetBasicFilter

FILTER
ソース範囲をフィルタ処理して、指定した条件を満たす行または列のみを返します。

構文
FILTER(範囲, 条件1, [条件2, ...])

FILTER

batchupdateメソッドでシート更新

最後に spreadsheets.batchUpdateメソッド を使って指定するシートを更新します。

//スプレッドシートをbatchUpdateで更新
Sheets.Spreadsheets.batchUpdate({'requests': [request]}, ss.getId());

メソッド：spreadsheets.batchUpdate

スプレッドシートに1つ以上の更新を適用します。

spreadsheets.batchUpdate

POINT！sht.batchUpdateではエラー、Sheets.Spreadsheets.batchUpdateでは動きました。

多分こんな風に動かすことが出来るんじゃないかなと・・

カレンダー作成Google apps scriptでカレンダーに登録しよう！-終日編-

まとめ

Google apps scriptからスプレッドシートへfilterを設置する方法でした。上手く出来ましたか？

要約すると

  1. スクリプトエディタでSheet APIと連携
  2. フィルターをかける範囲・基準・非表示を決める
  3. シートを更新してフィルターをかける

という感じですね。

Excelマクロでフィルター操作をする際はFilter関数を使いますが、Google apps scriptでは外部APIと連携しなければBasicFilterメソッドが扱えないところが最大のポイントではないでしょうか。

OYAKUN
OYAKUN
Excelマクロのようにサクッとフィルター設置できると思ってたら、全く歯が立ちませんでした（泣）

関連記事： Google apps scriptでsortする方法｜昇順・降順・列ごとに並び替えする方法
関連記事： 5分で出来る｜Google apps scriptからカレンダーへイベントの場所・説明を登録する3つの手順
関連記事： Google apps scriptでsortする方法｜昇順・降順・列ごとに並び替え
関連記事： Google apps scriptでfor loop｜繰返し処理を３ステップで

< 1 2>