GoogleスプレッドシートでArrayFormula関数を使うときに重宝する最終行の自動判定～GASと併用する際の注意事項～

こんにちは、おかちゃんせんせいです！

今回はExcelにはなくGoogleスプレッドシートである便利関数
ArrayFormula関数をGASと併用する際の注意事項
に関する記事になります。

Googleアドセンス

◆お知らせ◆

今回の記事を書こうと思ったきっかけ

ArrayFormula関数の使い方

Excelでは適用させたい関数を一つずつのセルに入力しなければならず、オートフィルのし忘れや、最終行が更新されるたびにオートフィルしなければならない手間があるので、どうしても自動化ができない悩みの種でした。

一方でGoogleスプレッドシートで実装されているArrayFormula関数は、始点のセルに入力した関数や数式を指定した位置まで一気に反映させることができる超便利な関数です。

たとえば、下図のような表データがあったとします。
売上 – 経費 = 利益
を月ごとに計算する場合、毎月売上・経費が確定したら入力するとします。

この際に、毎月利益以外は手で入力する必要があったとしても、利益は下記のように数式で計算ができます。

=B2-C2

入力した関数を毎回計算式が適用される行までオートフィルすればいいだけではありますが、月が増えていくにしたがって何度もオートフィルをする必要があります。

最初から数式を下の行まで反映させておくという手段もありますが、
個人的には見栄えが悪く、不要な数式は軽量化のために極力控えた方が良いと考えています。

ここで出番になるのがArrayFormula関数。
たとえば、下記のように数式を組んだとします。

=ARRAYFORMULA(IF(A2:A<>””,B2:B-C2:C,””))

この数式の意味するところは、
A列が空欄ではなかったら、B列 – C列。
それ以外の場合は空欄にする。
その数式をA2セルからA列の最終行まで反映させるということ。

ここでいう最終行とは、
スプレッドシートで表示されている最終行で、デフォルトでは1000行になります。

つまり、D2セルに上記数式を入力しておけば、D1000セルまで自動的に数式の内容が反映されるので以後のメンテナンスが一切不要になる、ということです。

2024年2月27日【Googleスプレッドシート】QUERY関数とARRAYFORMULA関数の組み合わせでハマった３つのトラブル

2024年2月27日【解決】GoogleスプレッドシートのArrayformula関数を使いながら、フィルターを活用できるようにする方法

GASとの併用で生じた問題点

上述した内容を理解してからはArrayFormula関数を活用しまくっていたのですが、、、
GASと併用して活用しようとしたときに１つ問題点が浮上してきました。

それが
最終行の判定
です。

先ほどもお伝えいたしました通り、
たとえば、A2:Aとしてしまいますと、A2セルからA列の最終行まで含んでしまいます。

つまり、デフォルトの状態では1000行を最終行と判定してしまうことになるため、GASのgetLastRowメソッドを考えなしにつかってしまうと、本当は6行目までしかデータがない場合でも1000行目を最終行として判定してしまいます。

数式のメンテナンスを一切不要にしつつ、GASでgetLastRowメソッドを使ったときに実際に値が入っている最終行を判定させようとすると、手段は二つ。

一つ目は、GASのコードで特定列の最終行を取得する方法です。
getLastRowメソッドを使うよりは少し複雑になってしまいますが、使うシーンによっては必要不可欠になることがあります。

GAS

// 対象のシートを取得
let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('データ');
 
// ①B列の先頭行から下方向に取得する
let lastRow1 = sheet.getRange(1, 2).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
 
// ②B列の最終行から上方向に取得する
let lastRow2 = sheet.getRange(sheet.getMaxRows(), 2).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();

もう一つの方法が、
Googleスプレッドシート上の関数で最終行を自動判定するようにして、ArrayFormula関数と併用する方法です。