大規模データセットを書き込む
API は Range.setValues() 、データを範囲に配置します。 この API には、データ サイズやネットワーク設定など、さまざまな要因に応じて制限があります。 つまり、大量の情報を 1 つの操作としてブックに書き込もうとする場合は、 大規模な範囲を確実に更新するために、より小さなバッチでデータを書き込む必要があります。
サンプルの最初の部分では、Excel で大規模なデータセットを記述する方法を示します。 2 番目の部分では、Power Automate フローの一部として例を展開します。 これは、 Power Automate アクションのタイムアウトよりもスクリプトの実行に時間がかかる場合に必要です。
Office スクリプトのパフォーマンスの基本については、「Office スクリプト のパフォーマンスを向上させる」を参照してください。
サンプル 1: 大規模なデータセットをバッチで記述する
このスクリプトは、範囲の行を小さな部分に書き込みます。 一度に書き込む 1,000 個のセルを選択します。 空白のワークシートでスクリプトを実行して、更新バッチの動作を確認します。 コンソールの出力により、何が起こっているかについてさらに詳しい分析情報が得られます。
注:
の値 SAMPLE_ROWSを変更することで、書き込まれる合計行数を変更できます。 の値を変更することで、1 つのアクションとして書き込むセルの CELLS_IN_BATCH数を変更できます。
function main(workbook: ExcelScript.Workbook) {
const SAMPLE_ROWS = 100000;
const CELLS_IN_BATCH = 10000;
// Get the current worksheet.
const sheet = workbook.getActiveWorksheet();
console.log(`Generating data...`)
let data: (string | number | boolean)[][] = [];
// Generate six columns of random data per row.
for (let i = 0; i < SAMPLE_ROWS; i++) {
data.push([i, ...[getRandomString(5), getRandomString(20), getRandomString(10), Math.random()], "Sample data"]);
}
console.log(`Calling update range function...`);
const updated = updateRangeInBatches(sheet.getRange("B2"), data, CELLS_IN_BATCH);
if (!updated) {
console.log(`Update did not take place or complete. Check and run again.`);
}
}
function updateRangeInBatches(
startCell: ExcelScript.Range,
values: (string | boolean | number)[][],
cellsInBatch: number
): boolean {
const startTime = new Date().getTime();
console.log(`Cells per batch setting: ${cellsInBatch}`);
// Determine the total number of cells to write.
const totalCells = values.length * values[0].length;
console.log(`Total cells to update in the target range: ${totalCells}`);
if (totalCells <= cellsInBatch) {
console.log(`No need to batch -- updating directly`);
updateTargetRange(startCell, values);
return true;
}
// Determine how many rows to write at once.
const rowsPerBatch = Math.floor(cellsInBatch / values[0].length);
console.log("Rows per batch: " + rowsPerBatch);
let rowCount = 0;
let totalRowsUpdated = 0;
let batchCount = 0;
// Write each batch of rows.
for (let i = 0; i < values.length; i++) {
rowCount++;
if (rowCount === rowsPerBatch) {
batchCount++;
console.log(`Calling update next batch function. Batch#: ${batchCount}`);
updateNextBatch(startCell, values, rowsPerBatch, totalRowsUpdated);
// Write a completion percentage to help the user understand the progress.
rowCount = 0;
totalRowsUpdated += rowsPerBatch;
console.log(`${((totalRowsUpdated / values.length) * 100).toFixed(1)}% Done`);
}
}
console.log(`Updating remaining rows -- last batch: ${rowCount}`)
if (rowCount > 0) {
updateNextBatch(startCell, values, rowCount, totalRowsUpdated);
}
let endTime = new Date().getTime();
console.log(`Completed ${totalCells} cells update. It took: ${((endTime - startTime) / 1000).toFixed(6)} seconds to complete. ${((((endTime - startTime) / 1000)) / cellsInBatch).toFixed(8)} seconds per ${cellsInBatch} cells-batch.`);
return true;
}
/**
* A helper function that computes the target range and updates.
*/
function updateNextBatch(
startingCell: ExcelScript.Range,
data: (string | boolean | number)[][],
rowsPerBatch: number,
totalRowsUpdated: number
) {
const newStartCell = startingCell.getOffsetRange(totalRowsUpdated, 0);
const targetRange = newStartCell.getResizedRange(rowsPerBatch - 1, data[0].length - 1);
console.log(`Updating batch at range ${targetRange.getAddress()}`);
const dataToUpdate = data.slice(totalRowsUpdated, totalRowsUpdated + rowsPerBatch);
try {
targetRange.setValues(dataToUpdate);
} catch (e) {
throw `Error while updating the batch range: ${JSON.stringify(e)}`;
}
return;
}
/**
* A helper function that computes the target range given the target range's starting cell
* and selected range and updates the values.
*/
function updateTargetRange(
targetCell: ExcelScript.Range,
values: (string | boolean | number)[][]
) {
const targetRange = targetCell.getResizedRange(values.length - 1, values[0].length - 1);
console.log(`Updating the range: ${targetRange.getAddress()}`);
try {
targetRange.setValues(values);
} catch (e) {
throw `Error while updating the whole range: ${JSON.stringify(e)}`;
}
return;
}
// Credit: https://www.codegrepper.com/code-examples/javascript/random+text+generator+javascript
function getRandomString(length: number): string {
var randomChars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
var result = '';
for (var i = 0; i < length; i++) {
result += randomChars.charAt(Math.floor(Math.random() * randomChars.length));
}
return result;
}
トレーニング ビデオ: 大規模なデータセットを作成する
サンプル 2: Power Automate フローからデータをバッチで書き込む
このサンプルでは、次の手順を完了する必要があります。
- SampleData.xlsxという名前のブックを OneDrive に作成します。
- TargetWorkbook.xlsxという名前の 2 つ目のブックを OneDrive に 作成します。
- Excel でSampleData.xlsx を開きます。
- サンプル データを追加します。 [ 大規模なデータセットをバッチで書き込む ] セクションのスクリプトを使用して、このデータを生成できます。
- 次の両方のスクリプトを作成して保存します。 [新しいスクリプトの自動化]> を使用してコードを貼り付け、推奨される名前でスクリプトを保存します。
- 「Power Automate フロー: ループ内のデータの読み取りと書き込み」の手順に従ってフローを作成します。
サンプル コード: 選択した行を読み取ります
function main(
workbook: ExcelScript.Workbook,
startRow: number,
batchSize: number
): string[][] {
// This script only reads the first worksheet in the workbook.
const sheet = workbook.getWorksheets()[0];
// Get the boundaries of the range.
// Note that we're assuming usedRange is too big to read or write as a single range.
const usedRange = sheet.getUsedRange();
const lastColumnIndex = usedRange.getLastColumn().getColumnIndex();
const lastRowindex = usedRange.getLastRow().getRowIndex();
// If we're starting past the last row, exit the script.
if (startRow > lastRowindex) {
return [[]];
}
// Get the next batch or the rest of the rows, whichever is smaller.
const rowCountToRead = Math.min(batchSize, (lastRowindex - startRow + 1));
const rangeToRead = sheet.getRangeByIndexes(startRow, 0, rowCountToRead, lastColumnIndex + 1);
return rangeToRead.getValues() as string[][];
}
サンプル コード: 行の場所にデータを書き込む
function main(
workbook: ExcelScript.Workbook,
data: string[][],
currentRow: number,
batchSize: number
): boolean {
// Get the first worksheet.
const sheet = workbook.getWorksheets()[0];
// Set the given data.
if (data && data.length > 0) {
sheet.getRangeByIndexes(currentRow, 0, data.length, data[0].length).setValues(data);
}
// If the script wrote less data than the batch size, signal the end of the flow.
return batchSize > data.length;
}
Power Automate フロー: ループ内のデータの読み取りと書き込み
Power Automate にサインインし、新しいインスタント クラウド フローを作成します。
[ フローを手動でトリガーする ] を選択し、[ 作成] を選択します。
読み取りと書き込み中の現在の行を追跡する変数を作成します。 フロー ビルダーで、[アクションの追加] + ボタンを選択 します。 [ 変数の初期化 ] アクションを選択し、次の値を指定します。
- 名前: currentRow
- 型: 整数
- "Value/値": 0
1 つのバッチで読み取る行数を設定するアクションを追加します。 列の数によっては、データ転送の制限を回避するために、これを小さくする必要がある場合があります。 次の値を使用して、新しい 変数の初期化 アクションを作成します。
- 名前: batchSize
- 型: 整数
- 値: 10000
Do until コントロールを追加します。 フローは、データがすべてコピーされるまで、データのチャンクを読み取ります。 -1 の値を使用して、データの末尾に達したことを示します。 コントロールに次の値を指定します。
- 値の選択: currentRow (動的コンテンツ)
- が と等しい (ドロップダウン リストから)
- 値を選択します: -1
残りの手順は 、Do コントロール内に追加されます。 次に、スクリプトを呼び出してデータを読み取る。 Excel Online (Business) コネクタの [スクリプトの実行] アクションを追加します。 名前を [ データの読み取り] に変更します。 アクションには次の値を使用します。
- 場所: OneDrive for Business
- ドキュメント ライブラリ: OneDrive
- ファイル: "SampleData.xlsx" (ファイル ピッカーによって選択)
- スクリプト: 選択した行を読み取ります
- startRow: currentRow (動的コンテンツ)
- batchSize: batchSize (動的コンテンツ)
スクリプトを呼び出してデータを書き込みます。 2 つ目の [スクリプトの実行] アクションを追加します。 名前を [ データの書き込み] に変更します。 アクションには次の値を使用します。
- 場所: OneDrive for Business
- ドキュメント ライブラリ: OneDrive
- ファイル: "TargetWorkbook.xlsx" (ファイル ピッカーによって選択)
- スクリプト: 行の場所にデータを書き込む
- data: result ( データの読み取りからの動的コンテンツ)
- 最初 に[入力を配列全体に切り替える] を押します。
- startRow: currentRow (動的コンテンツ)
- batchSize: batchSize (動的コンテンツ)
現在の行を更新して、データのバッチが読み取りおよび書き込まれたことを反映します。 次の値を使用して 、変数の増分 アクションを追加します。
- 名前: currentRow
- 値: batchSize (動的コンテンツ)
スクリプトがすべて読み取られた場合にチェックに Condition コントロールを追加します。 "行の場所にデータを書き込む" スクリプトは、バッチ サイズよりも少ない行を書き込むと true を返します。 これは、データ セットの最後にあることを意味します。 次の値を使用して 、条件 コントロール アクションを作成します。
- 値の選択: result ( データの書き込みから動的コンテンツ)
- が と等しい (ドロップダウン リストから)
- 値の選択: true (式)
Condition コントロールの True セクションで、currentRow 変数を -1 に設定します。 次の値を使用して 、変数の設定 アクションを追加します。
- 名前: currentRow
- 値: -1
フローを保存します。 フロー デザイナーは次の図のようになります。
フロー エディター ページの [テスト ] ボタンを使用するか、[ マイ フロー ] タブでフローを実行します。プロンプトが表示されたら、必ずアクセスを許可してください。
"TargetWorkbook.xlsx" ファイルには、"SampleData.xlsx"のデータが必要です。
Office Scripts