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

  • このエントリーをはてなブックマークに追加
  • Pocket
  • LINEで送る
how_to_get_last_row_from_sheets

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

スポンサーリンク

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

コピペで使えるサンプルコードを参照したい方はこちらをクリックすればサンプルコードまでジャンプできますので、手っ取り早くコードを確認したい方はクリックしてください。

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

  1. データが入っている最終の行
  2. シートの最終行

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

例えば、スプレッドシートは見た目的に同じように見える場合にも、関数処理の結果、空欄が出力されるというような場合もあると思います。

まずは、本題に入る前に、GASで最終行を取得するにはどのような方法があるのかをご紹介します。

  1. getLastRow()
  2. getMaxRows()
  3. getRow()/getRowIndex()
  4. getDataRange().getValues().length

ざっと列挙するとこのくらいでしょうか。中には、少し工夫をしなければならないものもありますが、どれも最終行を取得することができる関数です。

最終列を取得したい場合は似たような関数が用意されていますので、必要な場合は読み替えてもらえればと思います。

  1. getLastColumn()
  2. getMaxColumns()
  3. getColumn()/getColumnIndex()
  4. getDataRange().getValues()[0].length

使い方はほとんど最終行の取得とほとんど同じなので、今回は最終行取得の説明をメインに進めていきます。

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

さて、そろそろ本題に入っていきたいと思います。
今回の記事では、下記のパターンでサンプル用シートを用意してみました。

  1. 値や関数が4行目まで入っているシート(5行目以下は何も入っていない)→シートAと呼びます
  2. C列には1行目から1000行目まで関数が入っているが、5行目以降は空欄が出力されているシート)→シートBと呼びます
  3. 1行目から1000行目まで値や関数が入っているシート)→シートCと呼びます

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

カスタムメニューには、下記の5つの選択肢が選択できるようになっています。

  1. getLastRow
  2. getMaxRows
  3. getRow
  4. length
  5. getRowUpwards

サンプルスクリプト『シートの最終行を取得するサンプルコード

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

最終行を取得する関数たち
//スプレッドシートにアクセス時にカスタムメニューを追加する関数
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('最終行を取得するあれこれ')
      .addItem('getLastRow', 'sampleCodeForGetLastRow')
      .addItem('getMaxRows', 'sampleCodeForGetMaxRows')
      .addItem('getRow', 'sampleCodeForGetRow')
      .addItem('length', 'sampleCodeForLength')
      .addToUi();
}

//ダイアログを表示させる関数
function showDialog(msg) {
  Browser.msgBox(msg);
}

//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);
}

カスタムメニューを実行するとそれぞれの関数で実行時に自分が開いているシートの最終行を取得し、ダイアログで表示させるようになっています。
自分でもシートにアクセスしてぜひ関数を実行してみて下さい。一番最初に実行するときだけスクリプトに許可を求められますので、『無料のGmailアカウントでGASに初回の実行許可を与える方法』の記事の手順で許可を付与して下さい。

上記関数を実行するとどうなるのかを私の方でも以下の表でまとめてみました。

 getLastRowgetMaxRowsgetRowlength
シートA4行1,000行4行4行
シートB1,000行1,000行4行1,000行
シートC1,000行1,000行1,000行1,000行

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

それぞれ関数の解説

① getLastRow

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

② 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セルがアクティブセルとなり、そこから下方向にデータが入っているセルにジャンプして行を取得しています。

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

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

シート最終行から上にデータが入っているセルを取得する
//下から上にデータが入っている最終行を取得する
function sampleCodeForGetMaxRows() {
  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);
}



Direction_Up

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

④ length

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

 getLastRowgetMaxRowsgetRowlength
シートA4行1,000行4行4行
シートB1,000行1,000行4行1,000行
シートC1,000行1,000行1,000行1,000行

このやり方でも最終行を取得することができます。

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

今回、色々なやり方で最終行を取得する方法を学んできましたが、キーポイントになるのは数式が入っているかどうかということになると思います。数式が大量に入っているシートでも、実際にほしいデータ(値が出力されている行)は少ししか入っていないというような処理の場合、必要のないセルのデータは取得しないほうが処理の高速化に繋がります
そのため、短絡的にgetLastRowなどで最終行を取得しない方が良い場合があるということは覚えておいて損はないと思います。
自分が基準とする列の最終行を取得するという方法がわかれば、値が出力されていない大量の数式を除いた範囲の最終行を取得することができるため、無駄な処理がなく、結果として処理の高速化に繋がります。

まとめ

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

 

SNSでもご購読できます。

商標および、登録商標について

Googleおよび、Googleロゴおよび、Googleドライブおよび、Googleドキュメントおよび、Googleフォトおよび、Googleスプレッドシートおよび、Googleサイトおよび、GoogleスライドおよびGmailおよび、Googleマップおよび、Googleタスクおよび、Google WorkspaceはGoogle LLCの登録商標です。

本サイトのコンテンツはいかなる場合においても、Googleから公式に提携及び承認されているわけではありません。

 

 

コメントを残す

*