Excelにある大量のデータを高速に取得したいと言われたので、Officeスクリプトで実現してみた。
「表内に存在する行を一覧表示」ではパフォーマンスと安定性に懸念
Excel内のデータを一覧で取得するには、Excelコネクタの「表内に存在する行を一覧表示」アクションを使うのが最もスタンダード。
ただし、このアクションは速度と安定性に若干の不安があり、僕の環境だと5000件の取得に44秒ほどかかったし、
取得できる最大の「10万行」を取得しようとするとアクションが失敗することも多かった。
今回は大量のデータをより安定して取得するため、Officeスクリプトを使用してExcel内のデータの取得をしてみる。
汎用的に使えるスクリプト
まずはテーブルのスキーマに依存せずに値を取得できる関数の構築。
構築
OfficeスクリプトではanyのObject配列を戻り値にすることができなかったので、テーブルの全行をJSON文字列として返すよう実装。
コードがこちら。
function main(workbook: ExcelScript.Workbook, tableName:string) {
// 指定された名前のテーブルを取得
const table = workbook.getTable(tableName);
// テーブルが存在しない場合はエラーをスロー
if (!table) {
throw new Error(`テーブル "${tableName}" が見つかりません。`);
}
// テーブルのヘッダー行を取得
const headers = table.getHeaderRowRange().getValues()[0];
// テーブルのデータ範囲を取得（ヘッダーと合計行を除く）
const dataRange = table.getRangeBetweenHeaderAndTotal();
// テーブルの値を取得
const values = dataRange.getValues();
// 結果を格納する配列
let rowObjects: object[] = [];
// 各行をオブジェクトに変換して結果配列に追加
for (let i = 0; i < values.length; i++) {
let rowObject = {};
for (let j = 0; j < headers.length; j++) {
// ヘッダー名をキーとして、セルの値を設定
rowObject[headers[j].toString()] = values[i][j];
}
rowObjects.push(rowObject);
}
return JSON.stringify(rowObjects);
}
このスクリプトを実行し、戻り値をPower Automate側でJSONの解析を使用して扱う。
動作確認
実行してみたところ、500件のテーブルでは7秒と「表内の行を一覧にする」アクションとあまり変わらないけど、
10,000件のテーブルでも7秒で、
100,000件のテーブルは26秒で完了。きちんとデータも取得できている。
たぶんスクリプトの起動に数秒かかってしまうので、件数が増えれば増えるほど速度が実感しやすい。
テーブルに特化したスクリプト
ただ、このコードだとJSONで返すことで多少効率が悪いのと、全ての列を取得してしまうためデータ量的にもあまり好ましくない。
さらにデータ型を特に指定していないので、日付型のデータはSerial値になってしまう。
さらにデータ型を特に指定していないので、日付型のデータはSerial値になってしまう。
そこで、取得するテーブルに特化したスクリプトを組むのもおすすめ。
構築
ということでこちらのテーブルに特化したスクリプトを作成。
コードはこちら。
// 戻り値の型を定義
type TableRow = {
str: string;
num: number;
date: string;
};
function main(workbook: ExcelScript.Workbook): TableRow[] {
// テーブルを取得
const table = workbook.getTable("table500");
// テーブルの範囲を取得
const range = table.getRange();
// テーブルの値を取得
const values = range.getValues();
// 取得する列のインデックスを取得
const headers = values[0];
const strIndex = headers.indexOf("str");
const numIndex = headers.indexOf("num");
const dateIndex = headers.indexOf("date");
// 列のインデックスが見つからない場合はエラーを投げる
if (strIndex === -1 || numIndex === -1 || dateIndex === -1) {
throw new Error("必要な列 'str' または 'num' がテーブルに見つかりません");
}
// 結果を格納する配列
const result: { str: string, num: number, date:string }[] = [];
// ヘッダー行をスキップして各行を処理
for (let i = 1; i < values.length; i++) {
const row = values[i];
const serial_date = Number(row[dateIndex]);
const date = excelSerialDateToISOString(serial_date);
result.push({
str: row[strIndex].toString(),
num: Number(row[numIndex]),
date: date
});
}
return result;
}
/**
* Excel日付シリアル値をISO文字列に変換する
* @param serialNumber Excel日付シリアル値
* @param use1904Windowing 1904年基準を使用するかどうか（Macのレガシー対応）
* @returns ISO形式の日付文字列
*/
function excelSerialDateToISOString(serialNumber: number, use1904Windowing: boolean = false): string {
// 基準日の設定（1900年または1904年）
const baseDate = use1904Windowing ? new Date(Date.UTC(1904, 0, 1)) : new Date(Date.UTC(1900, 0, 1));
// Excel日付のバグ対応（1900年はうるう年ではないのにExcelは誤って扱っている）
let adjustedSerialNumber = serialNumber;
if (!use1904Windowing && serialNumber >= 60) {
adjustedSerialNumber--; // 1900年2月29日のバグ対応
}
// シリアル値を日数に変換（Excelでは整数部分が日付、小数部分が時刻）
const days = Math.floor(adjustedSerialNumber);
const timeFraction = adjustedSerialNumber - days;
// ミリ秒に変換して基準日に加算
const milliseconds = Math.round(timeFraction * 24 * 60 * 60 * 1000);
const date = new Date(baseDate.getTime() + ((days - 1) * 24 * 60 * 60 * 1000) + milliseconds);
// yyyy-MM-dd 形式で返す
const year = date.getFullYear();
const month = String(date.getMonth() + 1).padStart(2, '0');
const day = String(date.getDate()).padStart(2, '0');
return `${year}-${month}-${day}`;
}
動作確認
残念ながら速度は汎用的なスクリプトとそこまで変わらなかったけど、Power Automate側のJSONの解析が不要になるし、日付をISO形式で取得することも可能なので、テーブルごとにスクリプトを作るのもおすすめ。
コメント