ブログ

大量のセル処理を行うOfficeスクリプトのパフォーマンスを向上させる方法

スタッフの五十島です！

今回は、Officeスクリプトのループ処理の注意点についてご紹介いたします。

for等のループ処理内でのパフォーマンス警告

Officeスクリプトは、ExcelなどのMicrosoft Office製品で自動化スクリプトを作成するための便利なツールです！しかし、実行内容によってはパフォーマンスが著しく低下することがあります・・・

特に、ループ内でセルの読み込みや書き込みを行うと、大量のセルや多数のループ回数がある場合にはスクリプトの実行がかなり遅くなることがあります。

実際にfor文の中にgetValueメソッドを記述すると、オレンジの波線がかかり、「Invoking read methods inside of a loop could lead to slow performance of the script. For more information, please visit https://aka.ms/office-scripts-performance」という警告が表示されます。

そこで、getValueメソッドを使わずにパフォーマンスを向上させる方法として、getUsedRangeメソッドとgetValuesメソッドをご紹介します！

getUsedRangeを使うことで、シート上で使用されているセルの範囲を簡単に取得できます。そして、getValuesは、取得した範囲の値を一度に配列として取得できます。

このアプローチを採用することで、getValueメソッドをループ内で使用する必要がなくなり、スクリプトの実行時間を短縮することができます！

※ただし、getValuesメソッドを使用する場合は、取得した時点の値からリアルタイムで更新されないため、注意が必要です。

参考：Office Scripts のパフォーマンスの改善 – Office Scripts | Microsoft Learn

パフォーマンスの差異について検証

実際に各メソッドの実行時間にどの程度の差が出るのか検証してみました！

function main(workbook: ExcelScript.Workbook) {
  for(let i=0;i<10;i++){
    const start = performance.now();
    for(let j=0;j<1000;j++) {
        const tmp = workbook.getActiveWorksheet().getCell(j, 0).getValue();
    }
    const end = performance.now();
    workbook.getActiveWorksheet().getCell(i, 1).setValue(end - start);
  }
}
function main(workbook: ExcelScript.Workbook) {
  const usedRange = workbook.getActiveWorksheet().getUsedRange();
  const rangeValues = usedRange.getValues();
  for(let i=0;i<10;i++){
    const start = performance.now();
    for(let j=0;j<1000;j++) {
      const tmp = rangeValues[j][0];
    }
    const end = performance.now();
    workbook.getActiveWorksheet().getCell(i,2).setValue(end - start);
  }
}

どちらのスクリプトも、セルの値を1000回読み込む時間を10回計測するスクリプトになります。

この比較の参考結果は以下のようになりました。

getValuegetUsedRange&getValues
1回目3515.30.8
2回目3305.40.4
3回目3192.70.3
4回目3053.30.2
5回目3064.70.1
6回目3050.20.1
7回目2909.60.1
8回目3156.70.4
9回目3352.70.2
10回目3335.80.2
平均時間3193.640.28
(単位：ms)

こちらの結果から、getValuesメソッドを使った場合は配列に格納した値を読んでいるため高速ですが、getValueメソッドでは都度セルの値を読み取るため実行時間が遅いことがハッキリとわかりました！

なお、（私の環境では）1回のセルの読み込みは平均で3ms程度であると推測されるため、大量のセル処理を行わない限り、警告を無視しても影響は少ないといえるでしょう（笑）

getUsedRangeとgetValuesメソッドを使って、Officeスクリプトのパフォーマンスを向上させる方法をご紹介しました。

forなどのループ内でセルの値を扱う処理を大量に行う場合、このアプローチで実行時間を短縮することができますので、ぜひお試しください！

関連記事

TOP