LANG SELRCT

Apps Script Reference  (Create: Create new Spreadsheet | Create new Apps Script

Tuesday, June 5, 2018

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


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


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

↑この動作を利用する


上記の動作をスクリプトにするとどうなるかマクロに記録してみる
  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

Latest post

Extracting data from Google Sheets with regular expressions

Introduction Regular expressions are a powerful tool that can be used to extract data from text.  In Google Sheets, regular expressions ca...