【発見】OfficeScriptsでExcelシートの最終行を取得するベストな方法!

Office Scriptsで「Excelシートの最終行を取得する」メソッドを検索で調べると、いろんな方法が出てくるのですが、どれも微妙…というより、正確に最終行の値を取得できないのです。

WEBで紹介されてる主な方法

getUsedRange()→getLastCell()
- データ範囲を取得して、その範囲の最終行を取得するメソッド。
getRangeEdge()→getRowIndex()
- Ctrl＋↓(Macならcommand＋↓)のキー操作をして、止まった行の数を取得するメソッド。

詳細は省きますが、getUsedRange()を例に取ると、これは使用しているデータ範囲を取得するメソッドです。例えば一度100行まで文字を入れて、その後で95行消して、残り5行にしても最終行はやはり「100」になります。一度100行まで使っていたから、データ範囲もそうなるようなんですが。

でも、これでは例えば「データAの最終行の次の行からデータBを転記」ということはできなくなります。特にOfficeScriptsを使った作業はシートに転記したり消したりを繰り返すので、最終行の数値がガバガバになってしまいます。他のメソッドを試しても、最終行ガバガバになることがあって、正確に最終行を取得する、もっといい方法はないか、悩んでいろいろ試していました。

それをついこの間、とうとう見つけたのです。

例えばこんなシートで、A列の最終行を求める場合。

function main(workbook: ExcelScript.Workbook, stringResult: object) {
  let sheet = workbook.getWorksheet('シートの名前');
  let lastrow = sheet.getRange(A1:A10000).getValues().filter(v => v[0][0] !== v[0][""]);
  
  console.log(lastrow.length);
  // 配列の長さ=文字の入っている行数(最終行)
｝

何をしているか、お分かりでしょうか。各部について説明すると。

.getRange(A1:A10000)

まず、ここでシートA列の1万行を範囲取得。

.getValues()

次に、値を二次元配列として取得。

.filter(v => v[0][0] !== v[0][""]);

そして、配列から空の配列(空行)を取り除いて、文字が入った配列だけに絞る。

lastrow.length

最後に、配列の長さをカウント。

これが、A列の最終行の数になる、という考え方です。5行まで文字が入っていれば、lastrow.length = 5になります。

正確には最終行というより、「文字の入った行の数」を数える方法ですね。

ですから例えば、行にこういう「空き」が2ヶ所ある場合。

A列の最終行を求める時は、lastrow.length + 2をあらかじめ足しておきます。そしてlastrow.length + 2 + 1に次のデータを転記すれば良いわけです。

簡単だし、取得に時間もかからない。

私はこれがベストな方法だと思います。