【GAS】データのある最終行、最終列を取得する方法（スプレッドシート）

表計算・事務系
2023.03.14

こんにちは、せこしょーです。
今日はスプレッドシートに紐付いているGoogle Apps Script（GAS）でデータのある最終行と最終列を取得する方法の説明です。

コード

最終行を取得するコードも最終列を取得するコードも指定が行か列かの違いだけで仕組みは一緒です。とりあえず急ぎでコードが欲しい人は以下からコピーください。

特定列の最終行を取得するコード

A列（1列目）の最終行を取得するコードです。B列の場合は以下の1の箇所を2に変更します。希望の列番号を指定してください。

const lastRow = sheet.getRange(sheet.getMaxRows(), 1).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();

特定行の最終列を取得するコード

1行目の最終列を取得するコードです。2行目の場合は以下の1の箇所を2に変更します。希望の行番号を指定してください。

const lastColumn = sheet.getRange(1, sheet.getMaxColumns()).getNextDataCell(SpreadsheetApp.Direction.PREVIOUS).getColumn();

実用例

以下は最終行を出力するサンプルです。

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const lastRow = sheet.getRange(sheet.getMaxRows(), 1).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  console.log(lastRow);
}

指定した列（今回の場合A列）に値がどのように入っているかで挙動が変わることがあります。

隙間なく値が入っている場合

最終行lastRowは15になります。

歯抜けになっている場合

最終行lastRowは15になります。

注意点

注意点があります。以下のようにシートの最大の行に値が入っている場合はlastRowを正しく取得できません。

最終行lastRowは1000のはずですが・・lastRowは15になってしまいます。

この挙動はコードの指定上そうなります。

const lastRow = sheet.getRange(sheet.getMaxRows(), 1).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();

コードの解説で詳細を見てみましょう。

コードの解説

ざっくりいうとこのコードは、GASがシンプルに最終行を取得することができないので、一度シートの最大の行（以下の例では1000）の位置からセルを上方向にビューっと移動してぶつかったセル、つまり値の入っているセルの行数を返しています。

const lastRow = sheet.getRange(sheet.getMaxRows(), 1).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();

順を追ってみていきます。まずgetRange関数でセル位置を指定しています。その位置の行については、はgetMaxRows関数を利用してシートの最大行（シート上で見える範囲の最大の行数）を取得しています。以下の場合、getMaxRows関数は1000と返します。

列については1列目として、「1」とコードで指定しています。

そして指定した位置（行＝1000、列＝1）のセルから、指定した方向に移動した次のセルを返すgetNextDataCell関数を利用しています。今回指定した方向は・・SpreadsheetApp.Direction.UP。指定方法はとりあえず置いておいてノリで日本語に訳すとDirection=方向は、UP=上↑ですね。

この指定はどういうことかというと、スプレッドシートもしくはExcelを利用していてあるセルを選択して「Ctrl + ↑」を入力すると選択位置がぶっ飛んで移動したりするの、分かります？

まさに、あれです。

getNextDataCell(SpreadsheetApp.Direction.UP)は「Ctrl + ↑」と一緒のことをやっています。で最後にgetRow関数でぶっ飛んだ先のセルの行数を取得している、という流れです。

const lastRow = sheet.getRange(sheet.getMaxRows(), 1).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();

先程の挙動をもう一度見てみましょう。

先程lastRowが15を返していましたが、この理由は、実際に1000行目のセルを選択して「Ctrl + ↑」を入力してみるとよく分かります。

15行目で止まるはずです。

このように上記の場合の挙動には明確な理由があります。最後に解説のまとめです。シートの最大行や列は場合によって、指定によって変わります。

  1. getRange関数でセル範囲を取得する。（行=sheetの最大行1000（getMaxRows関数を利用）、列=1列目）
  2. getNextDataCell(SpreadsheetApp.Direction.UP)で「Ctrl + ↑」した時と同じように移動先のセルを取得する
  3. getRow関数でその行数を取得する

特定行の最終列を取得するコードも一緒です

const lastColumn = sheet.getRange(1, sheet.getMaxColumns()).getNextDataCell(SpreadsheetApp.Direction.PREVIOUS).getColumn();

最終行を取得する方法と同じ仕組みです。指定が行と列で異なるだけです。

  1. getRange関数でセル範囲を取得します（行=1行目、列=シートの最大列数）。
  2. getNextDataCell(SpreadsheetApp.Direction.PREVIOUS)で「Ctrl + ←」した時と同じように移動先のセルを取得する
  3. getColumn関数でその行数を取得する

まとめ

Google Apps Script（GAS）でスプレッドシートの値の入っている最終行と最終列を取得する方法の一つを紹介しました。GASでは特定範囲内で値の入っている最終の行・列を取得するような直接的なメソッドはないので自分でコードを組む必要があります。今回紹介した方法以外にも指定範囲をループで回して値の入っている行や列を取得する方法などあります。どちらもなんだかなぁって感じですよね、他の方法を採用しているなど何かあればコメントください！

コメント