Officeスクリプトでデータ範囲を取得する方法いろいろ。

こんにちは。ぽこがみさま（@teijilabo）です。

今回はOfficeスクリプト（Office scripts）でセル範囲のデータを取得して別シートのセル範囲に書き出す方法について解説します。

セル範囲（表）のデータをまるごと一括で読み取る

function main(workbook: ExcelScript.Workbook)
{
 //headerを含め表ごとコピーして、別シートに出力します。

 let stA = workbook.getWorksheet("Sheet1");
 let stB = workbook.getWorksheet("出力シート");
 
 //stAの有効なセル範囲の値を取得する。
 let data = stA.getUsedRange().getValues();
 //stBに書き出す。
 stB.getRangeByIndexes(0, 0, data.length, data[0].length).setValues(data);
}

.getUsedRange()は、データ範囲を一発で取得できるめちゃくちゃ便利で良く使うメソッドです。ただし、シート内の表範囲以外にデータが入ったセルが存在したり、余分な罫線が引かれていたりすると、それらの領域を含めてスコープしてしまうため、狙った範囲を取得出来るないことも多くあります。そういったときはこれから紹介するような他の手段を選択する必要があります。

セル範囲（表）のデータをヘッダーを含めずに一括で読み取る。

function main(workbook: ExcelScript.Workbook)
{
 //headerを含めずにデータ範囲だけコピーして、別シートに出力します。
 let stA = workbook.getWorksheet("Sheet1");
 let stB = workbook.getWorksheet("出力シート");
 let rng = stA.getUsedRange();
 let data = rng.getOffsetRange(1, 0).getResizedRange(-1, 0).getValues();
 //stBに書き出す。
 stB.getRangeByIndexes(1, 0, data.length, data[0].length).setValues(data);
}

ポイントは下記です。

 let data = rng.getOffsetRange(1, 0).getResizedRange(-1, 0).getValues();

.getOffsetRange(1, 0)で、セル範囲が行方向に”１”シフトします。
.getResizedRange(-1, 0)で、セル範囲の行数を”１”減らします。

これで正しく狙ったセル範囲が取得できます。

このテクニックは.getUsedRange()でそのまま狙った範囲を取得出来ないときに使えるテクニックです！

セル範囲のデータを1セルずつ処理して処理結果で配列をつくる（非推奨）

1セルずつデータ操作する場合の例です。この例では、各セルの値を10で掛け算しています。

function main(workbook: ExcelScript.Workbook)
{
 let stA = workbook.getWorksheet("Sheet1");
 let stB = workbook.getWorksheet("出力シート");
 let rowArr :number[] = []; //列の配列
 let arr: number[][] = []; //行の配列
 let value : number ;
 let usedRange = stA.getUsedRange();
 let rowCnt = usedRange.getRowCount();
 let colCnt = usedRange.getColumnCount();
 
 //row loop
 for (let i = 1; i < rowCnt ;i++) {
   //column loop
   for (let j = 0; j < colCnt; j++){
     value = Number(stA.getCell(i, j).getValue());
     rowArr.push(value * 10);
   } 
   arr.push(rowArr)
   rowArr = [];
 }
 stB.getRangeByIndexes(1,0,arr.length , arr[0].length).setValues(arr);
}

上記のコードは動くのですが、「ループの中でgetvalueはパフォーマンスが低下するよ」というような警告がでます。このメッセージの通り、1セルずつgetするのは得策ではなく非推奨です。

このようなことをするときは、配列として操作対象のデータを一括取得し、配列関数を使ってデータ操作をするのが良いでしょう。次項で配列関数の1例を紹介します。

セル範囲を1セルずつ処理して処理結果で配列をつくる（map関数を使う場合）

function main(workbook: ExcelScript.Workbook)
{
 let stA = workbook.getWorksheet("Sheet1");
 let stB = workbook.getWorksheet("出力シート");
 let rng = stA.getUsedRange();
 let sourceData = rng.getResizedRange(-1, 0).getOffsetRange(1, 0).getValues();
 let r:number[] = []
 let resultData:number[][] = []
 for (let i = 0; i < sourceData.length; i++) {
   r = sourceData[i].map(value => value * 10);
   resultData.push(r);
 }
 stB.getRangeByIndexes(1, 0, sourceData.length, sourceData[0].length).setValues(resultData);
}

配列関数としてmap関数を使いました。前項のコードと全く同じ結果になります。

テーブルのデータ範囲を取得する。（ヘッダーを含めない）

function main(workbook: ExcelScript.Workbook)
{
 let stA = workbook.getWorksheet("Sheet1");
 let stB = workbook.getWorksheet("出力シート");
 let table = stA.getTable("table1")
 if (table){
   let data = table.getRangeBetweenHeaderAndTotal().getValues();
 }else{
   table = stA.addTable(stA.getUsedRange(), true);
   table.setName("table1")
   let data = table.getRangeBetweenHeaderAndTotal().getValues();
 }

 stB.getRangeByIndexes(1, 0, data.length, data[0].length).setValues(data);
}

下記は、テーブルオブジェクトの時に使えるメソッドで、この1行でヘッダーを含めないデータ範囲を取得できます。

st.getTable("table1").getRangeBetweenHeaderAndTotal().getValues();

テーブルのデータ範囲を取得する。（ヘッダー含める）

function main(workbook: ExcelScript.Workbook)
{
 let stA = workbook.getWorksheet("Sheet1");
 let stB = workbook.getWorksheet("出力シート");
 let table = stA.getTable("table1");
 if (table){
   let data = table.getRange().getValues();
 }else{
   table = stA.addTable(stA.getUsedRange(), true);
   table.setName("table1");
   let data = table.getRange().getValues();
 }
 stB.getRangeByIndexes(0, 0, data.length, data[0].length).setValues(data);
}

下記コードでテーブル範囲を取得できます。

st.getTable("table1").getRange().getValues()

数式としてデータを取得する。

function main(workbook: ExcelScript.Workbook)
{
 let stA = workbook.getWorksheet("Sheet1");
 let stB = workbook.getWorksheet("出力シート");
 let table = stA.getTable("table1");
 if (table){
   let data = table.getRange().getFormulas();
 }else{
   table = stA.addTable(stA.getUsedRange(), true);
   table.setName("table1");
   let data = table.getRange().getFormulas();
 }
 stB.getRangeByIndexes(0, 0, data.length, data[0].length).setValues(data);
}

getValue()やgetValues()では数式が入っているセルは、数式の結果が取得されます。数式を取得する場合は、getFormulas()を使います。数式じゃないセルはそのまま値を取得します。

文字列としてデータを取得する。

function main(workbook: ExcelScript.Workbook)
{
 let stA = workbook.getWorksheet("Sheet1");
 let stB = workbook.getWorksheet("出力シート");
 let table = stA.getTable("table1");
 if (table){
   let data = table.getRange().getTexts();
 }else{
   table = stA.addTable(stA.getUsedRange(), true);
   table.setName("table1");
   let data = table.getRange().getTexts();
 }
 stB.getRangeByIndexes(0, 0, data.length, data[0].length).setValues(data);
}

getText()、getTexts()で文字列として取得します。

まとめ

以上で、セル範囲のデータを取得するいろいろな方法についてご紹介しました。わたしのブログでは、このほかにもMicrosoft365などの情報を発信しています。良かったら見てね。

Power AutomateからExcelファイルにOfficeスクリプトを実行する。 | てじらぼ