最終列の取得

ここではオフィススクリプトを使って、特定のワークシートでデータが入力されている最終列を取得する方法を、サンプルコードと併せて解説します。

考え方は最終行の取得と同じで、行の代わりに列を扱うメソッドを使います。

最終行の取得オフィススクリプトを使って、特定のワークシートの最終行を取得する方法を、サンプルコードと併せて解説します。

利用するメソッド

ExcelScript.Worksheetオブジェクトに含まれているgetUsedRangeメソッドと、ExcelScript.Rangeオブジェクトに含まれているgetColumnIndexメソッド、getColumnCountメソッドを使用します。

/**
 * @param valuesOnly trueを指定した場合、値が入力されているセルのみを使用済みとみなす
 * @return 使用済みの範囲(ワークシート全体が空白の場合はundefined)
 */
ExcelScript.Worksheet.getUsedRange(valuesOnly?: boolean): Range;

/**
 * @return 範囲の最初のセルの列番号(0から始まる)
 */
ExcelScript.Range.getColumnIndex(): number;

/**
 * @return 列の合計数
 */
ExcelScript.Range.getColumnCount(): number;

getUsedRangeメソッドは、値または書式が設定されているセルをすべて含む最小の範囲(使用済みの範囲)を返します。引数にtrueを指定すると、値が入力されているセルのみが対象になります。

使用済みの範囲は A 列から始まるとは限らないため、範囲の開始列(getColumnIndexは 0 から始まる点に注意)に列数を足すことで、最終列の列番号を求めます。

例えば、以下のように B 列から D 列にデータが入力されているシートの場合、使用済みの範囲はB2:D5です。getColumnIndexは B 列を表す1、getColumnCountは3を返すため、1 + 3で最終列の4(D 列)が求まります。

	Aインデックス 0	Bインデックス 1	Cインデックス 2	Dインデックス 3	Eインデックス 4
1インデックス 0
2インデックス 1		商品	4月	5月
3インデックス 2		リンゴ	30	42
4インデックス 3		ミカン	55	48
5インデックス 4		バナナ	21	26

Sheet1

B2:D5getUsedRange(true) の範囲(getColumnIndex() は 1、getColumnCount() は 3)
D2:D5最終列(1 + 3 = 4 列目 = D 列)

小さい数字は 0 から始まるインデックスです。

サンプルコード

シート名を指定して、データが入力されている最終列の列番号を取得するサンプルコードです。

対象シートが見つからない場合は、エラーをスローします。

function main(workbook: ExcelScript.Workbook) {
  /** 対象シート名 */
  const sheetname = 'Sheet1';
  /** 対象ワークシート */
  const sheet = workbook.getWorksheet(sheetname);

  if (!sheet) {
    throw `${sheetname}という名前のワークシートは存在しません`;
  }

  /** 値が入力されている範囲 */
  const usedRange = sheet.getUsedRange(true);

  if (!usedRange) {
    console.log(`${sheetname}にはデータがありません`);
    return;
  }

  /** ワークシートの最終列の列番号 */
  const lastColumn = usedRange.getColumnIndex() + usedRange.getColumnCount();

  console.log(`最終列は${lastColumn}列目です`); // 最終列は4列目です
}

求めたlastColumnは、A 列を 1 とする列の番号です。getCellなど 0 から始まる番号を指定するメソッドに渡す場合は、1 を引いてください。

最終列の範囲や番地を取得する

列番号ではなく、最終列そのものを範囲として扱いたい場合は、ExcelScript.RangeオブジェクトのgetLastColumnメソッドを使用します。

/**
 * @return 範囲の最後の列(例えば B2:D5 の最後の列は D2:D5)
 */
ExcelScript.Range.getLastColumn(): Range;

使用済みの範囲に対して実行すると、データが入力されている最終列の範囲を取得できます。getAddressメソッドで番地を確認すると、Dのような列名も分かります。

function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet();

  /** 値が入力されている範囲 */
  const usedRange = sheet.getUsedRange(true);

  if (!usedRange) {
    console.log('データがありません');
    return;
  }

  /** 最終列の範囲 */
  const lastColumnRange = usedRange.getLastColumn();

  console.log(lastColumnRange.getAddress()); // Sheet1!D2:D5

  // 最終列の右隣の列に見出しを追加する(E2 セル)
  lastColumnRange.getCell(0, 1).setValue('6月');
}

最終列の右隣に新しい列を追加していくような処理では、getCell(0, 1)のように最終列を基準にした位置でセルを指定すると便利です。

	A	B	C	D	E
1
2		商品	4月	5月	6月
3		リンゴ	30	42
4		ミカン	55	48
5		バナナ	21	26

Sheet1

サンプルコードを実行した後のシート

D2:D5getLastColumn() → D2:D5
E2lastColumnRange.getCell(0, 1) → E2

行番号・列番号でセルを指定するオフィススクリプトを使って、A1 形式の番地ではなく、行番号・列番号でセルや範囲を指定する方法を、VBA の Cells との違いと併せて解説します。