範囲の値の一括取得と書き込み

ここではオフィススクリプトを使って、セル範囲の値を 2 次元配列としてまとめて取得・書き込みする方法をサンプルコードと併せて解説します。

VBA との違い

VBA でもRange("A1:C4").Valueで範囲の値を 2 次元配列として取得できましたが、配列のインデックスは 1 から始まり、arr(行, 列)の形式で要素を指定していました。

Office スクリプトでは、インデックスは 0 から始まり、values[行][列]の形式で要素を指定します。

値をまとめて取得する

利用するメソッド

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

/**
 * @return 範囲の値(行の配列の中に、各列の値の配列が入った 2 次元配列)
 */
ExcelScript.Range.getValues(): (string | number | boolean)[][];

返り値は「行の配列」です。配列の 1 つ 1 つの要素が 1 行分のデータを表し、その中にさらに各列の値が並んでいます。

配列のインデックスとセルの対応

例えば以下のシートでA1:C4の値を取得すると、次のような配列が返されます。

	Aインデックス 0	Bインデックス 1	Cインデックス 2
1インデックス 0	商品	単価	数量
2インデックス 1	リンゴ	120	3
3インデックス 2	ミカン	80	5
4インデックス 3	バナナ	100	2

• Sheet1

• A1:C4getRange('A1:C4').getValues() で取得する範囲
• B3values[2][1] → 80

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

[
  ['商品', '単価', '数量'], // values[0]
  ['リンゴ', 120, 3], // values[1]
  ['ミカン', 80, 5], // values[2]
  ['バナナ', 100, 2], // values[3]
];

values[2]は 3 行目のデータ['ミカン', 80, 5]を、values[2][1]はその 2 番目の値である80(B3セル)を表します。

範囲がA1から始まる場合は、表の小さい数字(0 から始まるインデックス)が、そのまま配列のインデックスに対応します。

インデックスは範囲の左上から数える

配列のインデックスは、シート全体ではなく取得した範囲の左上のセルを基準に数えます。

以下のようにB2:D5の値を取得した場合、values[0][0]はA1セルではなく、範囲の左上にあるB2セルの値になります。

	A	B	C	D
1
2		商品	単価	数量
3		リンゴ	120	3
4		ミカン	80	5
5		バナナ	100	2

• Sheet1

• B2:D5getRange('B2:D5').getValues() で取得する範囲
• B2values[0][0] → '商品'

値をまとめて書き込む

利用するメソッド

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

/**
 * @param values 書き込む値の 2 次元配列
 */
ExcelScript.Range.setValues(values: (string | number | boolean)[][]): void;

引数には、getValuesの返り値と同じ「行の配列」の形式で値を指定します。

1 列だけの範囲に書き込む場合も、[[360], [400], [200]]のように、1 行ごとに配列で囲む必要があります。

配列と範囲の大きさをそろえる

書き込む配列の行数・列数は、対象の範囲の行数・列数と一致している必要があります。一致していない場合、スクリプトはエラーで停止します(The number of rows or columns in the input array doesn't match the size or dimensions of the range.のようなメッセージが表示されます)。

また、配列の各行の要素数もそろえる必要があります。

サンプルコード

B2:C4の単価と数量を掛け合わせて金額を求め、D2:D4にまとめて書き込むサンプルコードです。

範囲の値をまとめて取得・書き込みするサンプル

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

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

  /** 単価と数量(見出しを除く) */
  const values = sheet.getRange('B2:C4').getValues();

  /** 金額(1 行に 1 つの値を持つ 2 次元配列) */
  const amounts: (string | number)[][] = values.map((row) => {
    const [price, quantity] = row;

    if (typeof price !== 'number' || typeof quantity !== 'number') {
      return [''];
    }
    return [price * quantity];
  });

  sheet.getRange('D2:D4').setValues(amounts); // [[360], [400], [200]]
}

	A	B	C	D
1	商品	単価	数量	金額
2	リンゴ	120	3	360
3	ミカン	80	5	400
4	バナナ	100	2	200

• Sheet1

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

• B2:C4getValues() で取得した範囲
• D2:D4setValues() で書き込んだ範囲

単価や数量が数値でない行は、計算せずに空文字を書き込んでいます。

データの件数に合わせて範囲を決める

書き込むデータの件数が実行のたびに変わる場合は、配列の大きさから書き込む範囲を求めると、大きさの不一致によるエラーを防げます。

ExcelScript.RangeオブジェクトのgetResizedRangeメソッドを使うと、範囲の右下の位置を指定した行数・列数だけ広げた範囲を取得できます。

/**
 * @param deltaRows 広げる行数
 * @param deltaColumns 広げる列数
 */
ExcelScript.Range.getResizedRange(deltaRows: number, deltaColumns: number): Range;

書き込みを始めるセル(1 行 × 1 列)から広げるため、広げる数は「配列の行数 - 1」「配列の列数 - 1」になります。

データが 0 件の場合はdata[0]が存在しないため、範囲を求める前に件数を確認しておきます。

配列の大きさに合わせて範囲を求める

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

  /** 書き込むデータ */
  const data = [
    ['商品', '単価'],
    ['リンゴ', 120],
    ['ミカン', 80],
  ];

  if (data.length === 0) {
    console.log('書き込むデータがありません');
    return;
  }

  /** A1 セルから、データの大きさ(3 行 × 2 列)に広げた範囲 = A1:B3 */
  const target = sheet.getRange('A1').getResizedRange(data.length - 1, data[0].length - 1);

  target.setValues(data);
}

まとめて読み書きする方が高速

セルを 1 つずつgetValue・setValueで読み書きすると、そのたびに Excel とのやり取りが発生するため、セルの数が多いと処理に時間がかかります。

多くのセルを扱う場合は、getValuesで値をまとめて取得し、配列の上で処理してからsetValuesでまとめて書き込むようにしましょう。

セルの値の取得と書き込みオフィススクリプトを使って、特定のセルの値を取得する方法と、セルに値を書き込む方法を、サンプルコードと併せて解説します。

配列の操作VBA では配列の操作に苦戦された方も多いかと思いますが、オフィススクリプトの場合は、配列に関しても柔軟に操作することができます。オフィススクリプトにおける配列の体系的な操作方法について解説します。