Officeスクリプトで最終行・最終列を正確に取得する！getUsedRangeの限界と関数化での解決法

1. function main(workbook: ExcelScript.Workbook)
   • Officeスクリプトのメイン関数「main」を定義しています。
     この関数は「workbook」パラメータを通じてExcelのワークブック全体を操作します。
2. let sheet: ExcelScript.Worksheet = workbook.getWorksheet(“Sheet1”);
   • getWorksheet(“Sheet1”) で指定したシート “Sheet1” を取得し、変数「sheet」に格納します。
     この変数で特定のシートを操作できるようになります。
3. let usedRange: ExcelScript.Range = sheet.getUsedRange();
   • getUsedRange() メソッドを使用し、シート内で「使用されている範囲」を取得しています。
     この「使用範囲」は、データが入力されているセルだけでなく、書式設定されたセルや半角スペースが入力されたセルも含むため、実際のデータ範囲よりも広くなる場合があります。
4. let lastRowByCount: number = usedRange.getRowCount();
   • getRowCount() メソッドを使って、使用範囲内の行数を取得し、変数「lastRowByCount」に格納します。
     getUsedRange() で取得された範囲が「A1:E5」の場合、この値は「5」になります。
5. let lastColumnByCount: number = usedRange.getColumnCount();
   • getColumnCount() メソッドを使用して、使用範囲内の列数を取得し、変数「lastColumnByCount」に格納します。
     取得範囲が「A1:E5」の場合、列数は「5」です。
6. let lastRowByIndex: number = usedRange.getLastRow().getRowIndex();
   • getLastRow() で使用範囲内の最終行を取得し、さらに getRowIndex() メソッドを使ってその行のインデックス（0ベース）を取得しています。
     行番号として表示するには +1 が必要です。
7. let lastColumnByIndex: number = usedRange.getLastColumn().getColumnIndex();
   • getLastColumn() で使用範囲内の最終列を取得し、getColumnIndex() メソッドで列インデックスを取得しています。
     こちらも0ベースで返されるため、列番号として使用する場合は +1 が必要です。
8. console.log(“getRowCount() 最終行: ” + lastRowByCount);
   • getRowCount() で取得した行数をコンソールに出力します。
     例では「A1:E5」で使用範囲が認識されているため、出力は「5」になります。
9. console.log(“getColumnCount() 最終列: ” + lastColumnByCount);
   • getColumnCount() で取得した列数をコンソールに出力します。
     この例では「5」が出力されます。
10. console.log(“getLastRow() 最終行: ” + (lastRowByIndex + 1));
    • getLastColumn() で取得した最終列インデックスに +1 を加え、列番号として出力します。
11. console.log(“getLastColumn() 最終列: ” + (lastColumnByIndex + 1));
    • getLastColumn() で取得した最終列インデックスに +1 を加え、列番号として出力します。
12. }
    • 関数「main」の終了を示しています。

1. function main(workbook: ExcelScript.Workbook)
   • Officeスクリプトのメイン関数「main」を定義しています。
     この関数は、workbook オブジェクトを通してExcelワークブックを操作し、実際の最終行を確認するために getLastDataRow() 関数を呼び出します。
2. let sheet : ExcelScript.Worksheet = workbook.getWorksheet(“Sheet1”);
   • getWorksheet(“Sheet1”) を使用して、ワークブック内の “Sheet1” という名前のシートを取得し、「sheet」という変数に格納します。
     この変数「sheet」を介してシート内での操作が可能になります。
3. let lastRow : number = getLastDataRow(sheet);
   • getLastDataRow(sheet) 関数を呼び出し、取得した sheet オブジェクトを引数として渡します。
     この関数は、シート内で実際にデータが存在する最終行の番号を返し、変数「lastRow」に格納します。
4. console.log(実際の最終行: ${lastRow});
   • console.log() メソッドを使って、取得した「lastRow（実際の最終行番号）」をコンソールに出力します。
     getLastDataRow() 関数が返す結果をここで確認することができます。
5. }
   • 関数「main」の終了を示しています。

■ getLastDataRow 関数の詳細解説

1. function getLastDataRow(sheet: ExcelScript.Worksheet): number {
   • 関数「getLastDataRow」を定義しています。
     この関数は、引数として渡された sheet（シート）内で、データが入力されている最終行の番号を取得して返します。戻り値の型は「number」です。
2. let usedRange: ExcelScript.Range = sheet.getUsedRange();
   • 「sheet.getUsedRange()」を使用して、シート全体の「使用範囲」を取得し、「usedRange」に格納します。
     この範囲にはデータが入力されたセルのほか、書式設定された空白セルも含まれることがあります。
3. if (!usedRange) return 0;
   • 「usedRange」が空（データが一切ないシート）である場合、「0」を返して関数を終了します。
     これにより、データがない場合でもエラーを回避できます。
4. let startRow: number = usedRange.getRowIndex();
   • 「usedRange」の開始行インデックスを取得し、「startRow」に格納します。
     この行インデックスは「0」ベースのため、Excelの実際の行番号とは異なります。
5. let rowCount: number = usedRange.getRowCount();
   • 「usedRange」の行数を取得し、「rowCount」に格納します。
     この行数は「使用範囲の総行数」を表しています。
6. let columnCount: number = usedRange.getColumnCount();
   • 「usedRange」の列数を取得し、「columnCount」に格納します。
     これで「使用範囲の総列数」を取得できます。
7. for (let i: number = rowCount – 1; i >= 0; i–) {
   • 「rowCount – 1」から「0」まで、1行ずつ上に向かってデータの有無を確認するループを開始します。
     最終行からチェックすることで、効率よく最終行を特定します。
8. let rowIndex: number = startRow + i;
   • 現在の行インデックスを「startRow」に「i」を加えて計算し、「rowIndex」に格納します。
     この値はExcelの「0」ベースのインデックスで表されています。
9. let range: ExcelScript.Range = sheet.getRangeByIndexes(rowIndex, 0, 1, columnCount);
   • 「sheet.getRangeByIndexes(rowIndex, 0, 1, columnCount)」を使用し、特定行の全列を範囲として取得します。
     「range」は「rowIndex」行全体のセルデータを参照します。
10. let values = range.getValues()[0];
    • 「getValues()」で「range」内のセル値を「1」行分の配列として取得し、「values」に格納します。
11. if (values.some(cell => { … })) {
    • some() メソッドを使用して、「values」配列の各セルをチェックし、行内のいずれかにデータがあるかを確認します。
      条件を満たすセルが見つかれば、some() は「true」を返し、if 文の中に処理が移ります。
12. if (cell === null || cell === undefined) { return 0; }
    • 各セルの値が「null」または「undefined」である場合、データがないものとして「0」を返します。
      「null」や「undefined」は「データが空のセル」とみなされるため、この条件に当てはまる場合はデータがないと判断します。
13. if (typeof cell === ‘string’) { return cell.trim() !== “”; }
    • セルが文字列の場合、trim() メソッドで前後の空白を除去し、その結果が空文字列でないかをチェックします。
      空白だけの文字列セルをデータなしとみなすため、このチェックが行われています。
      • true
        • データが含まれていると判定します。
      • false
        • 空白だけのセルとして、データなしと判定します。
14. return true;
    • 文字列以外の型（数値、ブール値など）はデータがあるとみなされるため、「true」を返します。
      データの有無を確認するためのチェックであり、文字列以外のセルがあればデータありと判定します。
15. return rowIndex + 1;
    • 行内にデータが見つかった場合、その行インデックス「rowIndex」に「+1」を加え、Excel の行番号に合わせて返します。
      ここでの「+1」は、Excel の行番号が「1」ベースであるため、「0」ベースの「rowIndex」に「1」を足して調整しています。
16. return 0;
    • ループが終了してもデータが見つからなかった場合、シートが空であるとみなし「0」を返します。
      データがないシートに対応するための処理です。
17. }
    • 関数「getLastDataRow」の終了を示しています。

1. function main(workbook: ExcelScript.Workbook)
   • Officeスクリプトのメイン関数「main」を定義しています。
     この関数は、workbook オブジェクトを通してExcelワークブックを操作し、実際の最終行を確認するために getLastDataRow() 関数を呼び出します。
2. let sheet: ExcelScript.Worksheet = workbook.getWorksheet(“Sheet1”);
   • getWorksheet(“Sheet1”) を使用して、ワークブック内の “Sheet1” という名前のシートを取得し、「sheet」という変数に格納します。
     この変数「sheet」を介してシート内での操作が可能になります。
3. let lastColumn: number = getLastDataColumn(sheet);
   • getLastDataColumn(sheet) 関数を呼び出し、取得した sheet オブジェクトを引数として渡します。
     この関数は、シート内で実際にデータが存在する最終列の番号を返し、変数「lastColumn」に格納します。
4. console.log(実際の最終列: ${lastColumn});
   • console.log() メソッドを使って、取得した「lastColumn（実際の最終列番号）」をコンソールに出力します。
     getLastDataColumn() 関数が返す結果をここで確認することができます。
5. }
   • 関数「main」の終了を示しています。

■ getLastDataColumn 関数の詳細解説

1. function getLastDataColumn(sheet: ExcelScript.Worksheet): number {
   • ここからが「getLastDataColumn」関数の定義です。
     この関数は、渡された sheet オブジェクトをもとに、データが含まれている最終列の列番号を 「number 型」で返します。
2. let usedRange: ExcelScript.Range = sheet.getUsedRange();
   • 「sheet.getUsedRange()」を使用して、シート内で「使用されている範囲」を取得し、「usedRange」に格納します。
     この範囲にはデータが入力されたセルのほか、書式設定された空白セルも含まれます。
3. if (!usedRange) return 0;
   • 「usedRange」が空（シートにデータがない場合）である場合は、「0」を返して関数を終了します。
     これにより、データが一切ないシートでもエラーを回避できます。
4. let startColumn: number = usedRange.getColumnIndex();
   • 「usedRange」の開始列インデックスを取得し、「startColumn」に格納します。
     この列インデックスは「0」ベースのため、Excelの実際の列番号とは異なります。
5. let rowCount: number = usedRange.getRowCount();
   • 「usedRange」の行数を取得し、「rowCount」に格納します。
     これで使用範囲内の総行数を取得できます。
6. let columnCount: number = usedRange.getColumnCount();
   • usedRange の列数を取得し、columnCount に格納します。これで使用範囲内の総列数を取得できます。
7. for (let i: number = columnCount – 1; i >= 0; i–) {
   • 「columnCount – 1」から「0」まで、1列ずつ左方向に向かってデータの有無を確認するループを開始します。
     最終列から順に確認することで、効率よく最終列を特定します。
8. let columnIndex: number = startColumn + i;
   • 現在の列インデックスを「startColumn」に「i」を加えて計算し、「columnIndex」に格納します。
     この値は「0」ベースのインデックスで表されています。
9. let range: ExcelScript.Range = sheet.getRangeByIndexes(0, columnIndex, rowCount, 1);
   • sheet.getRangeByIndexes(0, columnIndex, rowCount, 1) を使用し、現在の columnIndex 列全体を範囲として取得し、range に格納します。
     この範囲は1列全体のデータを確認するために設定されています。
10. let values = range.getValues();
    • getValues() メソッドを使って、「range」内のセルデータを2次元配列「values」として取得します。
      各セルのデータが配列として格納されており、データの有無を確認できます。
11. if (values.some(row => { … })) {
    • some() メソッドを使用し、「values」配列内のセルにデータが含まれているか確認します。
      条件を満たすセルが見つかれば some() が「true」を返し、if 文の処理が実行されます。
12. let cell = row[0];
    • 現在の行内にあるセルのデータを「cell」という変数に格納します。
      「values」は2次元配列のため、各行の最初の要素（row[0]）を取得して確認します。
13. if (cell === null || cell === undefined) { return 0; }
    • セルの値が「null」または「undefined」である場合は、データがないものとして「0」を返します。
      「null」や「undefined」は「データが空のセル」とみなされるため、データなしと判断します。
14. if (typeof cell === ‘string’) { return cell.trim() !== “”; }
    • セルのデータが文字列型の場合、trim() メソッドで前後の空白を除去し、空文字でないか確認します。
      空白だけの文字列セルをデータなしとみなすためのチェックです。
      • true
        • データが含まれているとみなします。
      • false
        • 空白文字列としてデータなしとみなします。
15. return true;
    • 数値やブール値など、非文字列型のセルはデータがあるとみなされるため「true」を返します。
      この処理により、文字列以外のデータが見つかった場合もデータありと判定します。
16. return columnIndex + 1;
    • データが見つかった列インデックス「columnIndex」に「+1」を加え、Excel の列番号に合わせて返します。
      Excel の列番号は「1」ベースのため、「0」ベースの「columnIndex」に「+1」しています。
17. return 0;
    • ループが終了してもデータが見つからない場合、シートが空であるとみなし「0」を返します。
      データがないシートに対応するための処理です。
18. }
    • 関数「getLastDataColumn 」の終了を示しています。