※本記事はプロモーションを含みます
目次
Google Apps Script（GAS）でスプレッドシートの最終行を取得しようとするとき、多くの人が最初に使うのがgetLastRowメソッドです。ただし、日々の業務で注文リストや日報データを自動追記したり、データが入力されている範囲だけを抜き出して集計したりする場面では、動的にデータの末尾を把握する処理が不可欠になります。なので、この最終行の取得は、スプレッドシートの見た目とプログラムが解釈する内部状態との間にズレが生じやすく、現場で最もエラーや意図しない動作を引き起こしやすいポイントになります。というのは、まずは実務で真っ先に遭遇するズレの原因を整理したうえで、基本的な書き方と確実な回避手順を解説するからです。無料枠や利用容量の表示は公式サイトでの表記が優先されます。
見た目は空行なのに下へ飛ばされるgetLastRowのズレ
スプレッドシートで業務フローを組んでいると、データは50行目までしかないはずなのに、GASを実行すると100行目や500行目が最終行として返されてしまう現象によくぶつかります。ただし、画面上では51行目以降は真っ白で何も書かれていないように見えているため、スクリプトの記述ミスを疑ってコードを見直してしまいがちです。なので、大半の場合はスプレッドシート側に原因があります。というのは、原因を正しく把握する必要があるからです。
この現象が起きる最大の原因は、過去にデータが入っていた行を消去した際に、セルの値を消して書式設定や空白文字を残してしまっていることです。ただし、手作業でデータを範囲選択してDeleteキーやBackspaceキーを押した場合、入力されていたテキストや数値は消えます。なので、セルの背景色、罫線、表示形式、テキストの配置といった書式情報はセルに残り続けます。というのは、getLastRowがシート内で値または書式が存在する最も下の行番号を返す仕様になっているからです。確認しましょう。
また、計算式が入力されていてIF関数などで結果として空文字を出力しているセルも同様です。ただし、人間の目には何も表示されていない空白に見えます。なので、スプレッドシートの内部的には計算式というデータがしっかりと格納されているため、その行が最終行として認識されます。というのは、外部システムからCSVを取り込んだ際に見えない改行コードやスペースが紛れ込んでいたり、行を削除するつもりでセルのクリアを行っていたりする場合も、getLastRowの戻り値が実際のデータ末尾よりはるかに大きな数値になってしまうからです。
このズレを放置したまま次の処理へ進むと、自動追記システムを作ったつもりが遥か下の行にポツンとデータが追記されてしまいます。ただし、空行を大量に含む配列を処理することになってスクリプトの実行時間が無駄に延びます。なので、最悪の場合は実行時間上限エラーで止まってしまいます。というのは、最終行を取得する際は、単にメソッドを呼び出すだけでなく、シートの特性や運用ルールに合わせた適切なアプローチを選ぶ必要があるからです。
シート全体の末尾を特定する基本の書き方
シート全体の中で何かしらのデータや書式が存在する一番下の行をシンプルに把握したい場合、SpreadsheetAppの組み込みメソッドであるgetLastRowを使用します。ただし、この処理は、定期的なデータ洗い替え処理の前に既存データの末尾を確認したり、新規レコードを末尾に追加するための基準位置を調べたりする場面で頻繁に使われます。なので、書き方を覚えましょう。というのは、基礎が重要になるからです。
以下が、アクティブなシートから最終行を取得してログに出力する最も基礎的なコードです。
function checkLastRow() {; const sheet = SpreadsheetApp.getActiveSheet();; const lastRow = sheet.getLastRow();; Logger.log(lastRow);;}このコードでは、まず現在開いているシートをオブジェクトとして取得し、そのシートに対してgetLastRowを実行しています。ただし、戻り値は1から始まる整数であり、データが存在しない完全に空のシートで実行した場合は0が返されます。なので、この数値を変数lastRowに保持しておくことで、後続の処理で動的なセル範囲を指定する引数として活用できるようになります。というのは、例えばA列からD列までの全データを配列として取り込みたい場合、sheet.getRange(1, 1, lastRow, 4).getValues()のように指定することで、データ量が増減してもスクリプトを書き換えることなく柔軟に対応できるからです。
また、この最終行の取得はappendRowメソッドと組み合わせた自動追記処理の検証にも役立ちます。ただし、appendRowはシートの最終行の次の行に自動で1行分の配列データを挿入してくれる便利なメソッドですが、内部的にはまさにこのgetLastRowに類似した基準で追記位置を判定しています。なので、なぜかappendRowが遠く離れた下の行に書き込んでしまうというトラブルが起きたときは、まず上記のコードを実行してLogger.logで出力される行番号を確認しましょう。というのは、確認がトラブル解消の第一歩になるからです。
ただし、前述の通りこのメソッドはシート全体を見に行きます。なので、たとえばA列のデータは20行で終わっているのに、備考欄のH列にだけメモが80行目まで書かれているようなシート構造の場合、返ってくる数値は80になります。というのは、列ごとのデータ長が不揃いなシートでA列基準の最終行を知りたい場合には、この基本コードのままでは意図した結果が得られないからです。注意しましょう。
特定の列に絞って最終行を判定したいときの書き方
実務で扱うスプレッドシートの多くは、すべての列が同じ行数で揃っているわけではありません。ただし、A列には注文番号がぎっしり入っている一方で、右端の列には管理用のメモや一時的な計算式が散らばっているケースがよくあります。なので、このようなシートで注文番号が入っている最後の行番号を知りたいときにシート全体のgetLastRowを使ってしまうと、関係のないメモの行番号を拾ってしまい、処理対象の範囲が狂ってしまいます。というのは、対象列を絞る必要があるからです。
特定の列に絞って最終行を取得したい場合、スプレッドシートのショートカットキーであるCtrlと上矢印キーを組み合わせた最下行から上方向へのジャンプと同じ挙動をスクリプトで再現する方法があります。ただし、これにはgetRangeとgetNextDataCellメソッドを組み合わせて、シートの一番下の行から上に向かってデータを探すアプローチをとります。なので、正しく設定しましょう。というのは、特定列の判定に有効だからです。
例えばA列の最終行をピンポイントで取得したい場合、まずシートの物理的な最大行数であるgetMaxRowsを取得し、A列の一番下のセルを指定します。ただし、そこから上方向に向かって連続する空白を飛び越え、最初に文字や数値が入っているセルを特定します。なので、この方法を使えば、シート全体の右側にどれだけ余計な書式や別データのメモが存在していても、A列だけの最終入力行を正確に把握することができます。というのは、余計なデータを無視できるからです。
ただし、この下から上へジャンプする方法にも落とし穴があります。なので、もしA列が完全に空っぽだった場合、上方向への探索はシートの先頭である1行目まで突き抜けてしまい、1行目にタイトル文字があれば1が返り、1行目も空なら1が返ってきます。というのは、A列の中に意図しない空行が挟まっている場合でも下端から探すため末尾は取れますが、数式が入っていて空文字を返しているセルがあると、やはりそこをデータが存在するセルとして拾ってしまうからです。確認しましょう。
書式残りや数式空白を見抜く配列スキャンの回避策
行をクリアした際の書式設定の残りや、数式が返している空文字によるズレを完全に排除したい場合、最も信頼性が高いのはシートのデータを二次元配列として一括取得し、下から順に本当に中身のある値を走査して最終行を特定する方法です。ただし、API経由でセルのプロパティを1つずつ確認するのではなく、JavaScriptのメモリ上で判定を行います。なので、処理速度を犠牲にすることなく確実な最終行を割り出せます。というのは、配列スキャンが有効だからです。
この手法では、まずgetDataRangeメソッドを使ってシート内でデータが存在し得る範囲全体を捉え、その値をgetValuesで二次元配列に変換します。ただし、この配列には各セルの生の値が格納されますが、数式が空文字を返している場合は空の文字列として格納されます。なので、書式だけが残っているセルは値としては空として扱われます。というのは、値の有無を正確に判別できるからです。
配列を取得した後は、配列の最後の要素である最下行から先頭に向かって逆順にループを回します。ただし、特定の列をチェックし、そのセルの値が空文字でもなく、空白スペースのみでもなく、明確なテキストや数値を持っている行が見つかった瞬間にループを抜けます。なので、そのインデックスに1を足した値を行番号として採用します。というのは、セルの背景色や枠線といった書式設定に惑わされることなく、人間が目視で確認しているデータが入っている最後の行を機械的かつ正確に判定できるようになるからです。
この配列スキャンによる判定は、Googleフォームから回答が送信されてくるシートや、複数の担当者が日々手作業でコピペを繰り返して行を削除した痕跡が残るシートで特に威力を発揮します。ただし、人間の手による運用が入るスプレッドシートは、どれだけルールを決めていてもDeleteキーによる値のみ削除や書式残りが必ず発生します。なので、スクリプト側で配列を用いた実データの走査を行っておきましょう。というのは、運用側の操作ミスによって発生する行番号のズレを完全に防御することができ、バグの発生しない堅牢なプログラムを作成できるからです。
大量データを処理する際の変数管理と実行速度の維持
GASでスプレッドシートを操作する際、処理速度に最も大きな影響を与えるのはスプレッドシートに対する読み書きの回数です。ただし、最終行を取得する処理そのものは軽量に見えますが、コードの書き方次第ではスクリプトの実行速度を著しく低下させる要因になります。なので、特に数千行から数万行に及ぶ大規模なシートを扱う業務では、最終行の取得方法と変数管理に気を配る必要があります。というのは、速度低下を防ぐためです。
よくあるアンチパターンとして、forループの終了条件の中に直接sheet.getLastRow()を書いてしまうケースがあります。ただし、1行目から最終行までをループ処理する際に、ループが1回回るごとにsheet.getLastRow()を呼び出してしまうと、GASはループの回数分だけスプレッドシートのサーバーと通信を行って行番号を再計算します。なので、500回のループであれば500回のAPI呼び出しが発生し、これだけで数十秒のタイムロスとなります。というのは、GASの実行時間上限である通常アカウントの6分に引っかかる原因になるからです。
このようなパフォーマンス低下を防ぐためには、冒頭で提示したコードのように、処理の開始時点で一度だけgetLastRowを実行し、その結果を変数に格納して使い回すのが基本原則です。ただし、変数を参照するだけであればJavaScriptのメモリ内で一瞬で処理が完結します。なので、無駄な通信オーバーヘッドは一切発生しません。というのは、ループ処理の中でシートに行を追加したり削除したりして行数が動的に変わる場合を除き、最終行は一度計算してローカル変数に保持しておくことが処理を高速化するための鉄則だからです。確認しましょう。
また、最終行を取得した後に続く書き込み処理についても配慮が必要です。ただし、1行ずつループの中でappendRowやgetRange().setValue()を呼び出すのではなく、取得した最終行を基準にして書き込み対象の範囲を計算し、二次元配列にまとめたデータをsetValuesで一括書き込みする設計にします。なので、最終行の判定からデータの挿入までを一連のバッチ処理として組み立てましょう。というのは、Googleスプレッドシート特有のレイテンシを極限まで減らし、大量データであってもストレスなく動作する安定した業務自動化が実現できるからです。無料で登録しただけでは、全機能が使えるとは限りません。
