GASで飛び飛びデータの最終行を取得したい

Question

下記ホームページ
https://qiita.com/3mc/items/826cd66890d7812e917e

のコードを丸々コピーし、サンプルデータも丸々コピーしましたら
動きましたけれど、ここで、データが飛び飛びの場合、期待通りの
最終行が得られません。

仮に、そのホームページのサンプルデータに以下のように『キリン』
を追加しました場合、最終行の期待値は『6』ですが、結果は『5』と
出てきます。

0　　動物
1　　パンダ
2　　うさぎ
3　　コアラ
4　　
5　　キリン

function getLastRowWithValue() {
  const sheet = SpreadsheetApp.getActiveSheet(); 
  const columnBVals = sheet.getRange('B:B').getValues(); // B列「動物」の値を配列で取得
  const LastRow = columnBVals.filter(String).length;  //空白を除き、配列の数を取得

  Logger.log(LastRow); //4.0
}

　実運用的に、A列にはナンバリングがしてありますので、
(B:D)をRangeで範囲指定をして、飛び飛びデータの最終行
を取得したいのですが、ネット検索をしましても、希望の
動きをしてくれますコードが見つかりません。

var lastRow_B = sheet1.getRange(sheet1.getMaxRows(), 2).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
var lastRow_C = sheet1.getRange(sheet1.getMaxRows(), 3).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();

　という具合に各行の最終行を個々に取得して、一番大きな数字を
最終行とするしかないのでしょうか？

Answer 1

sheet.getLastRow() とした場合は、シート全体のうちデータが入っている範囲の最後の行の行番号が返ってきますが、
たとえば範囲を指定してsheet.getRange("B:Z").getLastRow()等としてしまうと、データが入っている行を超えて指定した範囲の最大行(デフォルト最大行1000）が返ってきてしまいますね。

そのQiita記事に記載されているように、たとえばA列にシート最終行（例：1000行目）まであらかじめ付番が入力されており、
B列以降にとびとびのデータを入力するような前提で、
「A列を除いた範囲での最終行を得たい」という場合は、
質問文最後に記載のコードで、1列ずつ判定するしかないようです。

ただ、1列ずつ変数を定義して最大数を判定するのは面倒なので、下記のように関数化してみてはいかがでしょうか。

(もしかしたらもっとシンプルなやり方があるかもしれませんが）

/* 指定した範囲の最終行を返す関数。
**  sheet:シートオブジェクト
**  range：探索する列範囲　（例："B:Z"のように文字列で指定する）
*/
function getLastRowEx(sheet, range) {
  if (sheet == null){
    Logger.log("有効なシートオブジェクトが指定されていません。")
    return;
  }
  if (range == null){
    Logger.log('対象範囲が指定されていません。')
    return;
  }

  var maxrow = 1;
  var srange = sheet.getRange(range);
  var start = srange.getColumn();  // 指定した範囲の先頭列番号
  var len = srange.getNumColumns(); // 指定した範囲の列数
  for (var i = start; i <= start + len - 1; i++){
    maxrow = Math.max(sheet.getRange(sheet.getMaxRows(), i).getNextDataCell(SpreadsheetApp.Direction.UP).getRow(), maxrow);
  }
  return maxrow;
}

下記のようにして使います。
（B列～Z列の範囲でデータが入っている最終行の最大行数を得る例）

function test(){
  var SHEET_ID = '***';
  
  var sheet1 = SpreadsheetApp.openById(SHEET_ID).getSheetByName("シート名");
  
  var lastRowNum = getLastRowEx(sheet1, "B:Z"));  // 範囲を "列番号:列番号"のように指定する。

  Logger.log(lastRowNum);
}

Comments

[ghon]

ありがとうございます。
理想のプログラムです。
飛び飛びデータでしかも指定範囲内の最終行取得出来ました。