参考
Qiita：GoogleSpreadSheetで、指定した範囲の、データが含まれている最終行を取得する方法。
ITライフ：JavaScript - 配列から null、undefined、空文字を削除する
やりたいこと
下記のようなデータで、太字箇所の行番号を取得したい。
※1行目、1列目もデータに含まれているものとする
| 通番 | 都道府県 | 担当 |
|---|---|---|
| 1 | 北海道 | 愛川 |
| 2 | 青森 | 伊藤 |
| 3 | 岩手 | |
| 4 | 宮城 | 江口 |
| 5 | 秋田 | |
| 6 |
やったこと
- 指定した列の開始行から(シート全体の)最終行までを配列として取得して、
- いろんなやり方で指定した列の最終行を取得する
うまくいかない例
うまくいかない例：getLastRow()
test1.gs
console.log(sheet.getLastRow()); //7
getLastRow()はシート全体でデータが入っている最終行を取得するので、もちろんうまくいかない
特定列がどうのこうのも出来ない
一部うまくいかない例：列番号を入力して配列取得→filterで配列の長さを返す
参考：Qiita：GoogleSpreadSheetで、指定した範囲の、データが含まれている最終行を取得する方法。
test2_getLastRowWithFilter.gs
function test2_getLastRowWithFilter(targetColumn) {
const sheet = SpreadsheetApp.getActiveSheet();
const targetColumnValues = sheet.getRange(1,targetColumn,sheet.getLastRow(),1).getValues(); // 指定した列の値を配列で取得
const LastRow = targetColumnValues.filter(String).length; //空白を除き、配列の数を取得
return LastRow;
}
function test(){
console.log(test2_getLastRowWithFilter(1)); //7
console.log(test2_getLastRowWithFilter(2)); //6
console.log(test2_getLastRowWithFilter(3)); //4 間に空白行があるとうまくいかない
}
上記の参考URLの解決例だが、今回のようにデータの途中に空白(空文字)があるとうまくいかない(配列の長さを擬似的に最終行とみなして返しているため)
解決例
解決例：配列で取得後、後ろから要素を調べて最初のfalsyでない行を返す
test3_getLastRowWithCheckFalsy.gs
function test3_getLastRowWithCheckFalsy(targetColumn) {
const sheet = SpreadsheetApp.getActiveSheet();
let targetColumnValues = sheet.getRange(1,targetColumn,sheet.getLastRow(),1).getValues().flat(); // 指定した列の値を配列で取得
let lastRow = 0;
//配列を後ろから見ていって、(nullかundefinedか空文字)でない要素に当たったらループを抜ける
for(let i=targetColumnValues.length-1; i>0; i--){
if(!(targetColumnValues[i] === null || targetColumnValues[i] === undefined || targetColumnValues[i] === '')){
lastRow = i + 1;
break;
}
}
return lastRow;
}
function test3(){
console.log(test3_getLastRowWithCheckFalsy(1));//7
console.log(test3_getLastRowWithCheckFalsy(2));//6
console.log(test3_getLastRowWithCheckFalsy(3));//5
}
たぶんこれでうまくいってる
厳密にはfalsyな要素をチェックしてるわけじゃない(厳密にfalsyで判定すると「0」とか「NaN」とかも判定してしまうため)
関数化
getLastRowWithCheckFalsy.gs
/**
* @param {object} targetSheet - sheet
* @param {number} targetColumn - column you want to get lastRow
* @return {number}
*/
//シートとターゲット列(number)を受けて、その列の最終行を返す関数
function getLastRowWithCheckFalsy(targetSheet, targetColumn){
const targetColumnValues = targetSheet.getRange(1, targetColumn, targetSheet.getLastRow(), 1).getValues().flat();
let lastRow = 0;
//配列を後ろから見ていって、(nullかundefinedか空文字)でない要素に当たったらループを抜ける
for(let i=targetColumnValues.length-1; i>0; i--){
if(!(targetColumnValues[i] === null || targetColumnValues[i] === undefined || targetColumnValues[i] === '')){
lastRow = i + 1;
break;
}
}
return lastRow;
}
汎用化するためにシートとターゲット列を指定して最終行を返す関数に書き換えた
あとがき
こういうやり方もあるっぽかったけど、rangeで扱うことになるのでやめた。たぶんこっちでもいけそう
特定列の最終行を取得したい場合は結構あるので、以降パーツとして使っていきたい
2021/9/2追記：シートを指定できるように関数化