まとめ
- 特定列の最終行が取得したい場合など、getLastRow() や getNextDataCell() でうまくいかない場合がある。
- そういった場合でも対応できる方法として、３つの手法を紹介している。柔軟性も高く、数式やチェックボックス☑にも対応できる。
- ただし、10万行を超えるようなシートでは、処理時間の点で不利になる場合もある。
ある列の、セルにデータの記載されている最終行を取得したい！
これ、けっこうよくあると思います。方法自体はいろいろあるのですが、いずれも一長一短あり、そのため状況に応じて最適な選択肢を選ぶのがけっこう難しかったりします。
本記事では、よく用いられる手法の意外な弱点や、その弱点をカバーできる方法など、紹介していきます。
よく使われるが弱点のある方法
方法１：getLastRow()
let sheet = SpreadsheetApp.getActiveSheet();
let lastRow = sheet.getLastRow();シート最終行を取得するために一番よく用いられる sheet クラスの getLastRow() メソッドですが、
✖ これはすべての列に対して判定されますので、特定列に限定しては使えません。
✖ また、セルに数式(*1)やチェックボックス☑(*2)が入力されていても「データあり」と判定されてしまうため、行の見た目が空欄に見えても、カウントされてしまう場合も多いです。
*1 たとえ数式の計算結果が空白文字であったとしても、getLastRow() では入力ありと判定されます。
*2 チェックボックスが設置されると、たとえチェックされていなくても、FALSE の値が入力されている状態になります。チェックすると TRUE です。
方法２：getNextDataCell()
let lastRow = sheet.getRange(sheet.getMaxRows(), 2).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();次はインターネット上の記事でよく紹介されている、Rangeクラスの getNextDataCell() メソッドを使う方法です。処理も早くて便利なんですが、
✖ 実はシートにフィルターによる絞り込みがかけられていると、次のエラーが生じます（たとえ他の列であっても）。これ、案外よく起こります。。もし不具合なら、是非ともgoogleさんに早く直してほしいなぁ。。
Exception: Invalid argument✖ また、これもチェックボックス☑に反応してしまいます。
どうにかならないのか？💦
前節で紹介した方法１と２の弱点は、どうにも解消できないものです。そのためここでは、それらの弱点を補いうる、代替手法を３つ紹介します。
方法３：最大値を求める 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方法４：配列の findLastIndex() メソッドを用いる
let lastRow = sheet.getRange("B:B").getValues().findLastIndex(row => row[0] !== '') + 1これ以外と使われないんですが、見た目もできる人っぽくてスマートでｗ、柔軟性も高いです。
○ 処理を書き換えることで、チェックボックス☑や数式入力にも対応できる
方法５：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;
}
}方法４よりも、より一般的な書き方です。やってることはほぼ同じです。柔軟性も高いです。気になるとすれば、ちょっと冗長に見えるくらいで。
○ 処理を書き換えることで、チェックボックス☑や数式入力にも対応できる
特徴の比較
ここまでの内容を表にまとめると、次のような形です。方法３～５は、１～２の弱点をカバーできることがわかります。
| control formula detection? 数式検知を制御できるか？ | control checkbox detection? チェックボックス検知を制御できるか？ | other weakness 他の弱点 |
|
|---|---|---|---|
| Type1: use "getLastRow" method | ✖ | ✖ | Not 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 | ○ | ○ | － |
処理時間の比較
ではでは、方法３～５が、１～２の弱点を補いうることは分かったんですが、これらの方法に弱点らしきはないのでしょうか？？
…実はあります。ズバリ処理時間が長くなる場合があります。処理内容がちょっと複雑なためか、特にシートの行数が多い場合には不利になる傾向があります。
実際に処理時間を計測してみた結果が、次の表です。（単位はミリ秒。いずれも３回測定の平均値）
| 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" method | 136 | 105 | 71 | 135 | 74 | 103 | 100 |
| Type2: use "getNextDataCell" method | 129 | 154 | 133 | 158 | 156 | 170 | 227 |
| Type3: use "Math.max" | 7 | 20 | 145 | 177 | 797 | (impossible) | (impossible) |
| Type4: use "findLastIndex" method | 115 | 175 | 30 | 361 | 704 | 543 | 2214 |
| Type5: use "for" syntax | 5 | 19 | 25 | 184 | 695 | 410 | 2368 |
- 1万行くらいまでのシートであれば、いずれの方法でも、大して影響はないかなと思います。
- 10万行程度になってくると、最終行の位置によっては、方法３～５では700～800ミリ秒の処理時間が必要になります。これは、方法１～２に比べると、5倍以上です。
- 30万行レベルになると、この傾向はより顕著で、最終行の位置にもよりますが、最大で10倍以上になります。
まとめ
- 方法１～２で支障がないのであれば、できるだけこれらを使いましょう。処理時間の点では有利です。
- 支障がある場合、方法３～５を利用することになります。1万～数万行くらいまでのシートであれば、いずれでも問題ありません。
- 10万行を超えてくるようなシートでは、方法４～５のいずれかになります。また、できるだけ空白行を少なくするなど、処理時間短縮の工夫が必要になるかもしれません。
なかなか、想定外のところで壁にぶつかりやすいのが最終行の取得です。問題なく動いてたのに、ある日、他のユーザーがフィルタを設置していきなりクラッシュ、、とか。。T_T
しかし、本稿で紹介したような方法を用いれば、だいたいの場合は、何とか乗り切れるかなと思います。ぜひ参考にしてみてください。
※本稿のサンプルスクリプトでは、列を「B:B」のようなアルファベット表記しているところが多いです。でも処理中は、「2」といった列番号で列を扱っている場合も多いはず。そのような場合は、こちらの「列番号と列名称を変換する方法」の記事も参考にしてみてください！
JA 
EN