LANG SELRCT

コードを書く場所についてはこちら

2018年6月5日火曜日

スプレッドシートでフィルタをかけたときに表示されている最終行を取得したい


ちょうど良い方法が見つからなかったので
ちょっと裏技的な方法を考えてやってみました


前提知識
 フィルタした行での先頭のセルをクリックしてアクティブにして
 キーボードの ⌘ と ↓を同時に押すと
 その行でデータが入っている最終行にカーソルが移動する

↑この動作を利用する


上記の動作をスクリプトにするとどうなるかマクロに記録してみる
  1. マクロの記録を開始する
  2. フィルタをかけたい行で任意のフィルタをかける
  3. ⌘と↓を同時に押す
  4. マクロを保存する


マクロを記録して自動で作成されたコード


マクロ.gs
function myFunction() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('B1').activate();
  var criteria = SpreadsheetApp.newFilterCriteria()
  .setHiddenValues([''])
  .build();
  spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(2, criteria);
  spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();
};



spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();
が⌘と↓を同時に押したときのコードっぽい

ほしい部分だけ抜き出して
getA1Notationでセルの位置を取得してみる


マクロ.gsからほしい部分だけ抜き出して書き換えたコード


コード.gs
function get_filtered_last_row() {
  var col = "B";
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange(col + 1).activate();
  var row = range.getNextDataCell(SpreadsheetApp.Direction.DOWN);
  Logger.log(row.getA1Notation());
}


補足


フィルタした行の先頭行が空の場合は
.getNextDataCell(SpreadsheetApp.Direction.DOWN)を2回行う

このように↓
var row = range.getNextDataCell(SpreadsheetApp.Direction.DOWN)
                        .getNextDataCell(SpreadsheetApp.Direction.DOWN)


1回だと一番下に行かずに表示されている2行目(データが入っている)がアクティブになる→もう一度やると一番下に移動する

これは⌘と↓を押したときと同じ挙動


参考

getNextDataCell(direction)
https://developers.google.com/apps-script/reference/spreadsheet/range#getNextDataCell(Direction)

Enum Direction
https://developers.google.com/apps-script/reference/spreadsheet/direction