やりたいこと

下記の状態のシートが存在しているときに、「A列で、値のあるセルの最終行数」(つまり 7 )を取得したい。

パターンA

こういうときはうまくいくのだけれど、

パターンB

↓ こういうとき(存在する一番の下のセルに値がある場合) に10 がほしいのに、うまく取れないことがあったので自分メモ。

ダメな例

パターンA

のときに、sheet.getLastRow() だと、そのシート内で値のある最終行数が取得されるので、B8の8になります。

function myFunction() {
  const sheet = SpreadsheetApp.getActive().getSheetByName("sheet");
  const lastRow = sheet.getLastRow();

  console.log(lastRow); // -> 8
}

maxRow から上に上がるやり方を知る

やっていることは、「そのシートの、特定の列の一番下(値の有無に関わらず、存在するセルの一番下)から、上に上がってきて(ctrl+↑ と同じ挙動) 最初に見つかったセルの行数を取得する」

sheet.getRange() の第2引数の 1 がA列のこと。

の状態で

function myFunction2() {
  const sheet = SpreadsheetApp.getActive().getSheetByName("sheet");
  const lastRow =sheet.getRange(sheet.getMaxRows(), 1).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();

  console.log(lastRow); // -> 7 (A列の最終行。期待通り！)
}

を実行すると、A列で値のある一番下の行数である 7 が取れます。

詳しくは【GAS】1行で書ける！特定列の最終行・特定行の最終列を取得する方法｜もりさんのプログラミング手帳を参照。

やったね！これで解決だ！！！

...って思ってたよね。しばらくはこれでうまく行っていたよね。

問題発生。うまくいかないことがある。

ここでパターンB が出てきます。

この状態で上記の myFunction2() を実行すると 7 になります。

なぜならば

「そのシートの、特定の列の一番下(値の有無に関わらず、存在するセルの一番下)から、上に上がってきて(ctrl+↑ と同じ挙動) 最初に見つかったセルの行数を取得する」

からです。つまり、A10を選択して、ctrl+↑ をすると、A7 に移動するので 7 が取得できます。

でもほしいのは 10 なんです！！！

ってことで、「そのスプレッドシートの最終行に値がある場合、この方法が使えない」ことになります。。。

ということでこういう関数を作ってみたよ。

sheetと列名を渡すと、その列において値のある最終行数を返してくれる関数を作ってみました。

/**
 * 指定した列で、値の存在する最終行数を返す
 * @param {sheet} sheet 対象のsheetオブジェクト
 * @param {string} col 列を表すアルファベット
 */
function getLastRow(sheet, col) {
  // そのシート内で値のある最終行
  const lastRow = sheet.getLastRow()

  // そのシートの最終行のcol列に値があれば、それが「col列に値がある最終行」
  if (sheet.getRange(`${col}${lastRow}`).getValue()) return lastRow

  // ↑コレじゃない場合は、col列の最終行は「空欄」なので、そこからCtrl↑で取得したのが、「col列に値がある最終行」
  return sheet.getRange(`${col}${sheet.getMaxRows()}`).getNextDataCell(SpreadsheetApp.Direction.UP).getRow()
}

function myFunction3() {
  const sheet = SpreadsheetApp.getActive().getSheetByName("sheet");
  console.log(getLastRow(sheet, "A"));
  console.log(getLastRow(sheet, "B"));
}

myFunction3() を実行すると、パターンBの時でもAの最終行として10、Bの最終行として8 が出力されます。

やったね！！