【コピペで使える】GASでスプレッドシートから最終行を取得する

今回はGoogle Apps Scriptを使って最終行を取得する方法をいくつかお伝えします。最終行を取得する方法は複数ありますが、使用する状況によって適したメソッドが異なりますので、どのような場合に使うべきなのかということを含めてご紹介しようと思います。

スプレッドシートから最終行を取得する

今回の記事では、まず最終行というものを定義するところから始めたいと思います。皆さんは最終行と聞くと、どちらをイメージされるでしょうか？

Google Apps Scriptでは、この上記2つの最終行には明確な違いがあり、場合によっては処理結果が思った通りの結果にならなかったり、非効率な処理になってしまう場合があります。
そのため、複数存在する最終行取得の処理の特性やスプレッドシートの特性を十分に理解することが必要になります。

結論としては、上記のどちらも最終行になり得ると私は考えます。

例えば、見た目は同じように見える2つのスプレッドシートファイルを用意します。

• 何もデータがなく空欄、
• A列に1000行目まで関数が入っているけれども関数の出力結果が空欄

どちらもスプレッドシートの見た目は何も出力されていない空のシートです。
ただし、データが入っている最終行という風に質問すれば、２つのシートで答えは異なります。
この認識の違いが、プログラムにも影響を与えるのです。

GASで最終行を取得するメソッド

まずは、本題に入る前に、GASで最終行を取得するメソッドをご紹介します。

どちらも使い方はほど変わりませんので、今回の記事では最終行をメインとして説明をしたいと思います。
最終列のことを知りたい場合は必要に応じて文中の最終行を最終列と読み替えていただければ良いかと思います。

最終行取得の関数の出力結果を比べてみる

前置きが長くなりましたが、今回の本題に入っていきたいと思います。
本記事では、下記の３パターンに分けてご説明をします。

この3つのシートで、最終行取得の関数の実行結果を確認してみようと思います。スプレッドシートの上部には、『最終行を取得するあれこれ』というカスタムメニューを用意しました。

最終行を取得するサンプルコード

//getLastRow関数で最終行を取得するコード
function sampleCodeForGetLastRow() {
  let sheet = SpreadsheetApp.getActiveSheet();
  let lastRow = sheet.getLastRow();
  let msg = "getLastRowで取得した最終行：" + lastRow + " 行";
  showDialog(msg);
}

//getMaxRows関数で最終行を取得するコード
function sampleCodeForGetMaxRows() {
  let sheet = SpreadsheetApp.getActiveSheet();
  let lastRow = sheet.getMaxRows();
  let msg = "getMaxRowsで取得した最終行：" + lastRow + " 行";
  showDialog(msg);
}

//getRow関数でA列の最終行を取得するコード
function sampleCodeForGetRow() {
  let sheet = SpreadsheetApp.getActiveSheet();
  let range = sheet.getRange(1, 1).getNextDataCell(SpreadsheetApp.Direction.DOWN);
  let lastRow = range.getRow();
  let msg = "getRowで取得した最終行：" + lastRow + " 行";
  showDialog(msg);
}

//シートの値をすべて二次元配列で取得し、length関数で最終行を取得するコード
function sampleCodeForLength() {
  let sheet = SpreadsheetApp.getActiveSheet();
  let data = sheet.getDataRange().getValues()
  let lastRow = data.length;
  let msg = "getLastRowで取得した最終行：" + lastRow + " 行";
  showDialog(msg);
}

カスタムメニューを実行すると開いているシート（シートA/シートB/シートC）の最終行をそれぞれの処理方法で取得し、ダイアログで表示させるようになっています。

自分でもシートにアクセスしてぜひ関数を実行してみて下さい。

一番最初に実行するときだけスクリプトに許可を求められますので、下記記事の手順に従ってGASに実行許可の付与をお願いします。

あわせて読みたい

無料のアカウントでGASに初回の実行許可を与える方法【超入門】 今回と次回の投稿では、Google Apps Scriptでその他のG Suiteサービスの操作を行う際に必要なアクセス許可について説明します。Googleアカウントには無料のGoogleアカウ…

スクリプトの実行結果

シートに入っているデータによってそれぞれの関数の出力結果が変わっているのがわかります。
それぞれの関数の特徴を表していますね。

それぞれ関数の解説

① getLastRow

こちらの関数は、シートの中でデータ（値や数式）が入っている最終行を取得するような挙動をします。
そのため、4行目までしかデータが入っていなければ4行目が最終行として取得されます。
注目したい特徴としては、数式の場合は出力が空欄で出力されていたとしてもデータが入っているとみなされてしまうことです。
こちらの関数でよくやってしまう事として、数式が大量に入っているスプレッドシートで最終行までループで処理をしようとして、関数の出力結果で何もデータが出力されていない行に対しても処理を繰り返してしまうことが少なくありません。
無駄な処理が多くなりますので、動作が遅くなってしまうため、気をつけたい点です。
ARRAYFORMULA関数を使った場合も、最大出力行までがデータが入っているものとして処理されますので、『=ARRAYFOMULA(A2:A10000+B2:B10000)』なんていう関数があった場合には何もデータがなくても無駄に10,000行目までの処理をしてしまいますので、無駄な処理をしないように注意しましょう。

ARRAYFORMULA関数とは相性が悪い

② getMaxRows

こちらの関数はセルにデータが入っているかどうかに関わらず、常にシートに表示されている一番下の行数が取得されます。今回のサンプルファイルでは、すべてのシートが1,000行のありますので、どのシートでも1,000行目が出力されます。

『データの有無に関わらず、シートの最終行を常に取得したい！』というときはこの関数を使いましょう。

③ getRow/getRowIndex

この関数は取得したセルの行数を返すという関数になります。
そのため事前にgetRangeを使ってセルを取得する必要があります。
シートの最終行を取得するためにはgetMaxRowsを使えば事足りますから、この関数では、関数が大量に入っているシートで特定の列の最終行を取得する事に応用できます。

getRange関数、getNextDataCell関数、SpreadsheetApp.Direction.Down関数と併用して最終行を取得できる

getNextDataCell関数は、利用したことがない方もいるとは思いますが、簡単に説明すると選択中のセルから上下左右指定した方向のセルにデータが入っているセルまで移動するような関数です。
Ctrl+矢印キーを押したときの挙動と同じとイメージしてもらえれば良いと思います。
例えば、サンプルファイルの『4行目までデータあり』シートのA1にアクティブセルがある場合に、Ctrl+↓キーを押すと、4行目にアクティブセルが移動します。もう一度Ctrl+↓キーを押すと、今度は1,000行目までアクティブセルが移動します。
このようにgetNextDataCell関数はこれと同じ動きをプログラム的に実行するイメージです。
サンプルスプレッドシートのgetRowをクリックすると、まずはプログラムでA1セルがアクティブセルとなり、そこから下方向にデータが入っているセルにジャンプして行を取得しています。

C列に1000行目まで数式が入っていてもA列のデータが4行目までしかなければ4行目が最終行になるという形になります。
ちなみに、A1セルを含めてA列には何もデータが入っていない場合は、1000行目が最終行ととして取得されます。
この挙動もCtrl + ↓キーを押下したときと同じですね。

もし、A列のデータに途中の行で空欄セルが含まれている場合は最終行までうまくジャンプすることができませんので、その場合はgetMaxRowsと組み合わせて下から上にデータが入っているセルを取得したら良いかと思います。

//下から上にデータが入っている最終行を取得する
function sampleCodeForGetRowUpwards() {
  let sheet = SpreadsheetApp.getActiveSheet();
  let maxRow = sheet.getMaxRows();
  let lastRow = sheet.getRange(maxRow, 1).getNextDataCell(SpreadsheetApp.Direction.UP).getRowIndex();
  let msg = "getRowUpwardsで取得した最終行：" + lastRow + " 行";
  showDialog(msg);
}

このように、自分が取得したいセルに移動する方法を覚えておくとプログラムを組む際の引き出しが増えてくると思います。

④ length

これは、getDataRange関数とgetValues関数と併用することで、入力されているデータを二次元配列で取り出して配列の長さを取得することで最終行を取得する方法です。
getDataRangeでは、データ（値と数式）が入っているセルを全部取得することができます。この状態で、getValues関数を使うことで、範囲内のすべての値が二次元配列として取得できます。この配列の長さ = 最終行ということになります。
数式が入っていれば出力結果が空っぽだとしても取得されますので、動作としてはgetLastRow関数を使った場合と近いイメージだと思います。なので、先程のまとめ表でも結果が同じになっていますね。

最終行を取得するポイント

今回、色々なやり方で最終行を取得する方法を学んできましたが、キーポイントは

シートに数式が入っているかどうか

数式が大量に入っているシートでも、実際にほしいデータ（値が出力されている行）は少ししか入っていないというような処理の場合、必要のないセルのデータは取得しないほうが処理の高速化に繋がります。

短絡的にgetLastRowなどで最終行を取得しない方が良い場合がある

ということは覚えておいて損はないと思います。

まとめ

Google Apps Scriptを使っている人には、動的にシートの最終行を取得したいという場面がよく出てくると思いますが、マニュアル運用しているようなスプレッドシートとGASを共存させようとすると今回の記事のように関数が入っているために『最終行が思ったとおり取得できない！』というようなことがあると思います。
その際にもGASの特性やスプレッドシートの特性を理解し、自分の引き出しを増やしておくことで上手に処理をすることができることも少なくありません。
今回ご紹介した方法はGASでできることのすべてではありませんので、その他の方法もあるかとは思いますが、例としていくつかの方法をご紹介しました。