GASでSpreadSheetの特定列の最終行を求める方法「Direction.DOWN」と「array.filter(String)」についての考察

しばり(使用場面)

• 複数のSheetを対象として使用する為、複数回実行する。その為、関数に処理をまとめる。
• 項目名として、「String」または「空白」が最初の数行に入っている。(これが何行かは可変。Sheetによって違う）
• Recordの開始行番号は、それぞれのGrobal変数に格納されている。
• 最終行を調べたい列番号は、それぞれのGrobal変数に格納されている。
• Recordの開始行以降で「空白」が出現したら、それ以降はその列は「空白」もしくは「セルが無い」
• 調べたい列の最終行が、Sheet内の最終行とは限らない。

速度計測に使用したSheet

• 実際に私が進行形で使っているSheet。グラフが4つと、グラフや閲覧の為の補正したDataや表等、そしてRecordが入っている。
• Record件数は「1149件」。最終行は「1151」（項目名に2行使用している）

function main(){
  const Sh1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  
  console.time("DirectionDOWN");
  const LastRow1 = DirectionDOWN(Sh1,INIfirstRecordRow,INIfield1Col);
  console.timeEnd("DirectionDOWN");

  console.time("ArrayFilter");
  const RastRow2 = ArrayFilter(Sh1,INIfirstRecordRow,INIfield1Col);
  console.timeEnd("ArrayFilter");
}

function DirectionDOWN(Sh,FirstRecordRowNo,TargetColNo){
  let result;
  const FirstSecondValue = Sh.getRange(FirstRecordRowNo,TargetColNo,2,1).getValues();
  if(FirstSecondValue[0][0] === ""){//Recordが0件の場合
    result = FirstRecordRowNo - 1;
  }else if(FirstSecondValue[1][0] === ""){//Recordが1件の場合
    result = FirstRecordRowNo;
  }else{//Recordが複数件の場合
    result = Sh.getRange(FirstRecordRowNo,TargetColNo).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
  }
  return result;
}

function ArrayFilter(Sh,FirstRecordRowNo,TargetColNo){
  const AlphabetCol = ExcelUtils.convertNumericColumnToAlphabet(TargetColNo);//自前関数
  const ArrOfColData = Sh.getRange(AlphabetCol+FirstRecordRowNo+":"+AlphabetCol).getValues();
  const arrLength = ArrOfColData.filter(String).length;
  return arrLength + FirstRecordRowNo - 1;
}

5回の計測結果

	1	2	3	4	5
DirectionDOWN	332	351	527	267	274
ArrayFilter	879	1217	1659	661	636
単位は「ms」。数値にばらつきがあるのは、計測した時間帯がバラバラな為。
（この結果だけ見ると誤解をしてしまう恐れがあります。ぜひ「何の処理が遅いのか調べてみた」も読んでください。）

何の処理が遅いのか調べてみた。

function DirectionDOWN(Sh,FirstRecordRowNo,TargetColNo){
  let result;

  console.time("DirectionDOWN1")
  const FirstSecondValue = Sh.getRange(FirstRecordRowNo,TargetColNo,2,1).getValues();
  console.timeEnd("DirectionDOWN1");//⇒ 135ms

  if(FirstSecondValue[0][0] === ""){//Recordが0件の場合
    result = FirstRecordRowNo - 1;
  }else if(FirstSecondValue[1][0] === ""){//Recordが1件の場合
    result = FirstRecordRowNo;
  }else{//Recordが複数件の場合
  
    console.time("DirectionDOWN2");
    result = Sh.getRange(FirstRecordRowNo,TargetColNo).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
    console.timeEnd("DirectionDOWN2");//⇒ 179ms
  }
  return result;
}

function ArrayFilter(Sh,FirstRecordRowNo,TargetColNo){
  console.time("ArrayFilter1");//自作関数
  const AlphabetCol = ExcelUtils.convertNumericColumnToAlphabet(TargetColNo);
  console.timeEnd("ArrayFilter1");//⇒ 1ms

  console.time("ArrayFilter2");//getRange("AZ3:AZ").getValues()
  const ArrOfColData = Sh.getRange(AlphabetCol+FirstRecordRowNo+":"+AlphabetCol).getValues();
  console.timeEnd("ArrayFilter2");//⇒ 652ms

  console.time("ArrayFilter3");
  const arrLength = ArrOfColData.filter(String).length;
  console.timeEnd("ArrayFilter3");//⇒ 3ms

  return arrLength + FirstRecordRowNo - 1;
}

感想など

計測環境では、Record件数は「1149件」。最終行は「1151」（項目名に2行使用している）。
まず注目したいのは「ArrayFilter2(652ms)」の圧倒的遅さ。
ここでやっているのは、target となる Column の (RecordData の開始行から下の) Value を配列として取得すること。
今回の計測環境では、ArrOfColData.lengthが 1149 となる。
一方、「DirectionDOWN1(135ms)」では、FirstSecondValue.lengthは 2 である。
つまり、getValues()はData数2個で161ms、1149個だと652ms かかったということになる。
ここで、もしもの話しを考えてみると。
良く用いられる手法で、最初にSheetの全てのDataをgetValues()で配列に取り込むというものがある。
もしこの手法を使うのであれば、getValues()を最終行取得の為に使う必要がなくなるので、DirectionDOWN より ArrayFilter の手法の方が速くなりそう。
(でも、このSheetでそれやるのは、気が引ける。人間から見た時の見栄えを考えていろいろな数字やらCodeには必要ないものも大量に書き込まれているし）
また、もし件数が少なければ、ArrayFilterの方が早くなりそう。ただし、0件と1件の時は、DirectionDOWNは、分岐処理により格段に速くなるはずだから、どっちが速いかわからないけど、その場合は、もはや速度は気にならないレベルだと思われる。

色々考えればケースバイケースで、どっちが速い遅いとも言えない気がする。
だけど、私の目的とする環境(SpreadSheet)では、DirectionDOWN を使っておく方が良さげ。

もう少し広い視野で考えてみると

もしもRecordDataだけを専門で書き込むSheetをつくったら・・・
たぶんgetLastRow()が使えて、もっとプログラムは単純になるし、実行速度も向上する。
だけど、私が見る時便利が悪いので嫌。。
グラフを見て、グラフの根拠となっている数字も見たりする時あるし・・・。

もしもSheetの中のとあるセルに最終行の数字を書き込んだら・・・
プログラムはシンプルになるし、速度も向上する。
問題なのは、そのセルの値を間違って手動で不適切な数字に書き直してしまったら、メインのプログラム上のあらゆる箇所で、問題が起きそうなところだけど、今のところ私が使う私の為のSheetだから、別に問題ないような気がする。
今後にこれを公開・配布したりする場合は、若干気になる。

プログラムを複雑にしている諸悪の根源は、

• 項目名として、「String」または「空白」が最初の数行に入っている。(これが何行かは可変。Sheetによって違う）
• 調べたい列の最終行が、Sheet内の最終行とは限らない。

この2つのしばり条件な気がする。でも上で言ったようにやっぱり見た目にはこだわりたい。
結論、このままが良さげ。