【GAS】getLastRow()で関数（数式）を埋め込みたい時は注意が必要！

2022年5月7日

こんにちは！

今回は「GASでgetLastRow()で関数を埋め込みたい時の注意点」についてお伝えしたいと思います。

Contents

1 getLastRow()は（数式を含め）入力のある最終行を取得する
2 解決策１：getNextDataCell()を使って、数式が埋め込まれていない列の最終行を取得する
3 解決策２：setFormulaR1C1()を使って、行追加時に関数を埋め込むようにする
4 参考

getLastRow()は（数式を含め）入力のある最終行を取得する

getLastRow()は入力のある最終行を取得するのですが、数式も含める点に注意が必要です。

例えば以下の場合です。

const func = () => {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); // ブック取得
  const TARGET_SHEET_NAME = '売上管理'; // 対象のシート名
  const sheet = spreadsheet.getSheetByName(TARGET_SHEET_NAME);
  const range = sheet.getRange('A:A');
  console.log(sheet.getLastRow()); // 1000
};

表示上は空ですが関数が埋め込まれているため、getLastRow()は1000が返されます。

解決策１：getNextDataCell()を使って、数式が埋め込まれていない列の最終行を取得する

getNextDataCell()を使うことで特定の列の最終行を取得できます。

const func = () => {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); // ブック取得
  const TARGET_SHEET_NAME = '売上管理 のコピー'; // 対象のシート名
  const sheet = spreadsheet.getSheetByName(TARGET_SHEET_NAME);
  const lastRow = sheet.getRange('B:B').getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
  console.log(lastRow); // 4
};

関数を除く最終行を取得することができました！

解決策２：setFormulaR1C1()を使って、行追加時に関数を埋め込むようにする

setFormulaR1C1()を使って、行追加のタイミングで関数を埋め込むようにすることで解決できるかもしれません。

const func = () => {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); // ブック取得
  const TARGET_SHEET_NAME = '売上管理 のコピー'; // 対象のシート名
  const sheet = spreadsheet.getSheetByName(TARGET_SHEET_NAME);
  sheet.getRange('A5').setFormulaR1C1(`=if(RC[1]="", "", row()-1)`);
};

工数はかかりますが、確実だと思いますし、ファイルも軽くなります。

参考

https://tonari-it.com/gas-setformular1c1/

以上、お疲れさまでした〜🍵