GASでシートの最終行を取得する方法【列指定の方法も解説】 | GASおじさんのブログ
GASのTips
スプレッドシートGoogleフォーム

GASでシートの最終行を取得する方法【列指定の方法も解説】

シートの最終行取得 GASのTips

みなさんこんにちは！GASおじさんです。

今回はスプレッドシートの最終行を取得する方法について解説します。

実際の業務であり得るシーンを想定して、ケース別に詳しく解説していきます。

Youtubeでも解説しています。

最終行を取得するgetLastRow()

まずは以下のような、シートの内容が単純なケースを見ていきましょう。

A1:D6の範囲にデータがあります。この場合、最終行は「6」ですね。

このようにデータがシートの一部に綺麗にまとまっている場合は、最終行の取得は簡単です。

sheetオブジェクトに対してgetLastRow()メソッドを使うだけです。

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('シート1');
  const lastRow = sheet.getLastRow();
  console.log(lastRow);
}

こちらを実行すると、最終行である「6」という数値を取得できました。

getLastRow()で最終行が取得できない…

上記のgetLastRow()メソッドで、最終行を意図した通りに取得できないときがあります。

シートに複数のデータ群があるとき

たとえば以下のような場合。

F列とG列に、年代別の集計表が作られています。

このシートでgetLastRow()を使っても、「9」という数字が返ってきてしまいます。

これは、F, G列のほうの最終行が引っかかってしまうからですよね。

しかし、このシートで一般的に意図する最終行は、A列からD列のデータ群の最終行、つまり「6」ですよね。

このように、シートの中に複数のデータ群があるときは意図した最終行を取得できないことがあります。

空白文字が隠れているとき

また、以下のような場合も正しい最終行を取得できません。

一見問題なさそうに見えますが、よく見るとD列に関数が埋め込まれており、7行目から一番下の1000行目まで空白文字""が隠れています。

その結果、getLastRow()を使っても「1000」という数値が返ってきてしまいます。

ArrayFormula関数を使っているとき

ArrayFormula関数を使っている時も要注意です。

一見、7行目以降には関数も埋め込まれていないし、何もないように見えますが…

よく見ると2行目でArrayFormula関数が使われているため、内部的には7行目以降にも空白文字が含まれていることになります。

この場合もやはりgetLastRow()の結果は「1000」を返すことになります。

列を指定して最終行を取得する方法

getLastRow()で意図した通りに最終行を取得できないときは、列を指定して最終行を取得しましょう。

今回はA列の最終行を取得する方法を考えていきます。

一番上のセルから下方向に探す

A1セルにいる状態で、Ctrl + ↓ （Macの場合は ⌘ + ↓ ）をすると、データ群の一番下までアクティブセルをジャンプさせることができますよね。

この動きを、GASで実装することができます。

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('シート1');
  const lastRow = sheet.getRange(1, 1).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
  console.log(lastRow);
}

注目は4行目。

  • sheet.getRange(1, 1)で、1行1列目（つまりA1セル）を取得
  • そのA1セルに対してgetNextDataCell()メソッドを使う
  • その際SpreadsheetApp.Direction.DOWNを引数に指定して下方向にジャンプする
  • 最後にgetRow()メソッドを使って、行番号を取得する

こちらを実行すると、無事、最終行の「6」を取得できました。

これでA列の最終行を取得することができます。

ただし、この方法はあくまで2行目以降にデータがあるときのみ有効です。

2行目以降にデータがない場合は、シートの一番下の1000行目までジャンプしてしまいます。

このように、一番上のセルから下方向に探す方法は動作が不安定でちょっと危険です。

よって、推奨されるのは次に紹介する方法です。

一番下のセルから上方向に探す(推奨)

上記とは反対に、シートの一番下のセルから上方向に探すという動きを実装します。

つまりA1000セルにいる状態で、Ctrl + ↑ （Macの場合は ⌘ + ↑ ）をするという動きです。

この動きをGASで実装すると次のようになります。

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('シート1');
  const lastRow = sheet.getRange(1000, 1).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  console.log(lastRow);
}
  • sheet.getRange(1000, 1)で、1000行1列目（つまりA1000セル）を取得
  • そのA1000セルに対してgetNextDataCell()メソッドを使う
  • その際SpreadsheetApp.Direction.UPを引数に指定して上方向にジャンプする
  • 最後にgetRow()メソッドを使って、行番号を取得する

このとき、シートの底の行数である「1000」という数値は動的に変化する可能性があるので、これも変数にしてあげましょう。

sheetオブジェクトに対してgetMaxRows()メソッドを使うと、シートの底の行数を取得することができます。

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('シート1');
  const bottomRow = sheet.getMaxRows();
  const lastRow = sheet.getRange(bottomRow, 1).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  console.log(lastRow);
}

こちらはシートの底の行数をbottomRowという変数にしましたが、変数にせず以下のように1行で書いてもいいでしょう。

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('シート1');
  const lastRow = sheet.getRange(sheet.getMaxRows(), 1).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  console.log(lastRow);
}

ただ、1行が横に長くなりすぎると読みづらいので、ちょうどいいところで改行してあげましょう。

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('シート1');
  const lastRow = sheet.getRange(sheet.getMaxRows(), 1)
    .getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  console.log(lastRow);
}

こちら実行すると、無事データの最終行である「6」を取得できました。

また、2行目以降にデータがない場合も、最終行「1」を取得してくれます。

ということで、この方法であれば安定的に動作するので、より推奨されます。

しかし、この方法も万能というわけではありません。

以下のようにシートの底までデータが詰まっていると、この方法でも正しい最終行を取得することができません。

このように一番上のセルまでジャンプしてしまうからですね。

なので、なるべくシートの底は余白にしておくような運用をすることをオススメいたします。

以下のように「シートの底にデータがある場合は、10行余白を追加する」という処理を加えるのもいいかもしれませんね。

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('シート1');
  const bottomRow = sheet.getMaxRows();
  const bottomValue = sheet.getRange(bottomRow, 1).getValue();
  if (bottomValue != "") sheet.insertRowsAfter(bottomRow, 10);
  const lastRow = sheet.getRange(sheet.getMaxRows(), 1)
    .getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  console.log(lastRow);
}
  • 4行目でシートの底の行数を取得してbottomRowに代入
  • 5行目でシートの底のデータを取得してbottomValueに代入
  • 6行目でbottomValueが空白かどうかを判定して、空白じゃないなら10行余白を追加

実践例: Googleフォームの回答を別のシートに転記する

問い

ここからは実践例です。

以下のようなGoogleフォームがあるとします。

お名前と生年月日を入力する簡単なフォームです。

このフォームで送信される情報を、とあるスプレッドシートに記録するというケースを考えます。

このとき、Googleフォームにデフォルトで備わっている以下のスプレッドシートではなく、まったく別のシートに転記したい場合はどうしたらいいでしょうか？

今回は以下のシートに記録していくケースを考えます。

その際、「タイムスタンプ」「お名前」「生年月日」に関してはフォームの回答をそのまま転記し、「年齢」に関しては関数を埋め込む形にします。

答え

まずはフォームの回答シートからスクリプトエディタを開きましょう。

エディタに以下のsubmit関数を定義します。

function submit(e) {
  const timestamp = e.namedValues['タイムスタンプ'][0];
  const username = e.namedValues['お名前'][0];
  const birthday = e.namedValues['生年月日'][0];
  const age = '=DATEDIF(R[0]C[-1], TODAY(), "Y")';
  const ss = SpreadsheetApp.openById('xxxxxxxxxxxxxxxxxxxxxxxxxxx');
  const sheet = ss.getSheetByName('シート1');
  const lastRow = sheet.getRange(sheet.getMaxRows(), 1)
    .getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  const values = [
    [timestamp, username, birthday, age]
  ];
  sheet.getRange(lastRow+1, 1, values.length, values[0].length).setValues(values);
}

6行目のxxxxxxxxxxxxxxxxxxxxxxxxxxxには転記先のスプレッドシートIDを指定しましょう。

このsubmit関数をトリガー設定します。

フォーム送信時トリガーを設定しましょう。

ここまでできたら準備完了です。

検証

それではいざ、Googleフォームを送信してみましょう。

スプレッドシートを確認すると…

無事、転記成功しました！

まとめ

以上、スプレッドシートの最終行を取得する方法について解説しました。

最終行の取得は、単純にgetLastRow()メソッドを使えればそれが一番いいのですが、実際にはそう簡単にいかないケースも多々あると思います。

getLastRow()以外の方法もいくつかご紹介しましたが、どの方法を採用するのかはケースバイケースで変わってくるので、柔軟に対応できるように根本的な仕組みを理解しておきましょう。

それではまた！

コメント

タイトルとURLをコピーしました