GAS | スプレッドシート最終行の取得方法

まとめ

  • 特定列の最終行が取得したい場合など、getLastRow() や getNextDataCell() でうまくいかない場合がある。
  •  そういった場合でも対応できる方法として、3つの手法を紹介している。柔軟性も高く、数式やチェックボックス☑にも対応できる。
  • ただし、10万行を超えるようなシートでは、処理時間の点で不利になる場合もある。

ある列の、セルにデータの記載されている最終行を取得したい!

 

これ、けっこうよくあると思います。方法自体はいろいろあるのですが、いずれも一長一短あり、そのため状況に応じて最適な選択肢を選ぶのがけっこう難しかったりします。

 

本記事では、よく用いられる手法の意外な弱点や、その弱点をカバーできる方法など、紹介していきます。

よく使われるが弱点のある方法

方法1:getLastRow()

let sheet = SpreadsheetApp.getActiveSheet();
let lastRow = sheet.getLastRow();

シート最終行を取得するために一番よく用いられる sheet クラスの getLastRow() メソッドですが、

✖ これはすべての列に対して判定されますので、特定列に限定しては使えません。

✖ また、セルに数式(*1)やチェックボックス☑(*2)が入力されていても「データあり」と判定されてしまうため、行の見た目が空欄に見えても、カウントされてしまう場合も多いです。

*1  たとえ数式の計算結果が空白文字であったとしても、getLastRow() では入力ありと判定されます。

*2  チェックボックスが設置されると、たとえチェックされていなくても、FALSE の値が入力されている状態になります。チェックすると TRUE です。

方法2:getNextDataCell()

let lastRow = sheet.getRange(sheet.getMaxRows(), 2).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();

次はインターネット上の記事でよく紹介されている、Rangeクラスの getNextDataCell() メソッドを使う方法です。処理も早くて便利なんですが、

✖ 実はシートにフィルターによる絞り込みがかけられていると、次のエラーが生じます(たとえ他の列であっても)。これ、案外よく起こります。。もし不具合なら、是非ともgoogleさんに早く直してほしいなぁ。。

Exception: Invalid argument

✖ また、これもチェックボックス☑に反応してしまいます。

どうにかならないのか?💦

前節で紹介した方法1と2の弱点は、どうにも解消できないものです。そのためここでは、それらの弱点を補いうる、代替手法を3つ紹介します。

方法3:最大値を求める Math.max.apply() を用いる

let lastRow = Math.max.apply(null, sheet.getRange("B:B").getValues().map((row, idx) => row[0] !== '' ? idx + 1 : 0));

//チェックボックス設定されている設定されているセルでは、TRUEのみ検知したい場合
let lastRow = Math.max.apply(null, sheet.getRange("B:B").getValues().map((row, idx) => row[0] !== '' && row[0] !== false ? idx + 1 : 0));

//数式入力を検知したい検知したい場合
let lastRow = Math.max.apply(null, sheet.getRange("B:B").getFormulas().map((row, idx) => row[0] !== '' ? idx + 1 : 0));

これもよく紹介されてます。map() メソッド内のコールバック関数を書き換えたりすることで、チェックボックス☑にも対応でき、処理もぼちぼち早いんですが、ほんの少し弱点があります。

○ 処理を書き換えることで、チェックボックス☑や数式入力にも対応できる

△ 対応行数に上限があり、10万行くらいまでしか使えません。上限を超えると次のエラーが出ます。

RangeError: Maximum call stack size exceeded

方法4:配列の findLastIndex() メソッドを用いる

let lastRow = sheet.getRange("B:B").getValues().findLastIndex(row => row[0] !== '') + 1

これ以外と使われないんですが、見た目もできる人っぽくてスマートでw、柔軟性も高いです。

○ 処理を書き換えることで、チェックボックス☑や数式入力にも対応できる

方法5:for構文を用いる

let rows = sheet.getRange("B:B").getValues();
let lastRow = 0;
for (let i = rows.length - 1; i >= 0; i--) {
  if (rows[i][0] !== '') {
    lastRow = i + 1;
    break;
  }
}

方法4よりも、より一般的な書き方です。やってることはほぼ同じです。柔軟性も高いです。気になるとすれば、ちょっと冗長に見えるくらいで。

○ 処理を書き換えることで、チェックボックス☑や数式入力にも対応できる

特徴の比較

ここまでの内容を表にまとめると、次のような形です。方法3~5は、1~2の弱点をカバーできることがわかります。

control formula detection?
数式検知を制御できるか?
control checkbox detection?
チェックボックス検知を制御できるか?
other weakness
他の弱点
Type1: use "getLastRow" methodNot available per column
列単位では使用不可
Type2: use "getNextDataCell" method
An error occurs due to narrowing down the filter
フィルター絞り込みによりエラー発生
Type3: use "Math.max"An error occurs when the number of rows is large
行数が大きいとエラーが発生
Type4: use "findLastIndex" method
Type5: use "for" syntax

処理時間の比較

ではでは、方法3~5が、1~2の弱点を補いうることは分かったんですが、これらの方法に弱点らしきはないのでしょうか??

…実はあります。ズバリ処理時間が長くなる場合があります。処理内容がちょっと複雑なためか、特にシートの行数が多い場合には不利になる傾向があります。

 

実際に処理時間を計測してみた結果が、次の表です。(単位はミリ秒。いずれも3回測定の平均値)

max rows
(lastRow position)
1,000 rows
(lastRow = 900)
10,000 rows
(lastRow = 9000)
10,000 rows
(lastRow = 1000)
100,000 rows
(lastRow = 99000)
100,000 rows
(lastRow = 1000)
300,000 rows
(lastRow = 299000)
300,000 rows
(lastRow = 1000)
Type1: use "getLastRow" method1361057113574103100
Type2: use "getNextDataCell" method129154133158156170227
Type3: use "Math.max"720145177797(impossible)(impossible)
Type4: use "findLastIndex" method115175303617045432214
Type5: use "for" syntax519251846954102368
  • 1万行くらいまでのシートであれば、いずれの方法でも、大して影響はないかなと思います。
  • 10万行程度になってくると、最終行の位置によっては、方法3~5では700~800ミリ秒の処理時間が必要になります。これは、方法1~2に比べると、5倍以上です。
  • 30万行レベルになると、この傾向はより顕著で、最終行の位置にもよりますが、最大で10倍以上になります。

まとめ

  • 方法1~2で支障がないのであれば、できるだけこれらを使いましょう。処理時間の点では有利です。
  • 支障がある場合、方法3~5を利用することになります。1万~数万行くらいまでのシートであれば、いずれでも問題ありません。
  • 10万行を超えてくるようなシートでは、方法4~5のいずれかになります。また、できるだけ空白行を少なくするなど、処理時間短縮の工夫が必要になるかもしれません。

なかなか、想定外のところで壁にぶつかりやすいのが最終行の取得です。問題なく動いてたのに、ある日、他のユーザーがフィルタを設置していきなりクラッシュ、、とか。。T_T

 

しかし、本稿で紹介したような方法を用いれば、だいたいの場合は、何とか乗り切れるかなと思います。ぜひ参考にしてみてください。

 

※本稿のサンプルスクリプトでは、列を「B:B」のようなアルファベット表記しているところが多いです。でも処理中は、「2」といった列番号で列を扱っている場合も多いはず。そのような場合は、こちらの「列番号と列名称を変換する方法」の記事も参考にしてみてください!

Leave a Reply

Your email address will not be published. Required fields are marked *