PowerAutomateでなるべく高速にExcelテーブルを更新する

PowerAutomateを使用して組織内の自動化を行っていると、ワークフローでExcelのテーブルに更新をかけることもしばしば。今回はこうしたときに発生しがちな性能問題への対処法です。

Background

PowerAutomateのワークフローから Excelのテーブルのデータを更新する（行の追加だけでなく行の更新もする）場合、まず思いつくのはExcel Onlineコネクターが提供する「行の更新」アクションを利用する方法です：

実際には、「更新対象になる行がそもそもテーブル上に存在するかどうかを確認し、存在しなければ追加、存在すれば更新」という判断を伴うロジックや、「更新対象となる行をテーブル上から一旦すべて削除して、しかるのち行を追加」という事前削除を伴うロジックを考えることになるでしょう。

Problem

Excelのテーブルにデータを更新する（行の追加だけでなく行の更新もする）ワークフローの所要時間が長い。

さらに所要時間が長いことによる副次的な影響として、当該Excelファイルに対するフローの操作とユーザーの操作との競合が原因と思われるエラーもしばしば発生する。

テーブルに行を追加するアクションはともかく、すでにある行を取得したり削除したりするアクションも加わると、それらが実行されるトータルの回数は膨大になりがち。
そもそも個々のアクションもそれほど高速ではないので、回数が増えて所要時間が問題になるのは当たり前です。

Solution

更新する行のいちいちについて取得したり削除したりするのは非効率なので、「まず何も考えず行を追加してしまい、しかるのち重複した行を一括削除」という戦術をとります。一括削除にはOffice Scriptsを利用します：

この戦術には前提条件（準備作業）が1つあり、更新対象のExcelテーブル内に「同じキーを持つ行のうち最新の行かどうか」を示す列を追加します。
どういうことかというと、こんなふうに：

ここではサンプルとして「重複するキーを持つ行のうち最新の行かどうか」を示す列としてisLatestがという列を定義しています。
isLatestはMAXIF関数などを使い、keyが同じ行のうちinsetedOnが最新のものについてTRUEとなるようにしています。
insetedOnはその名の通り「表に行を追加」アクションで行を追加するとき、その処理日時を設定している列です。

こうしてテーブル側の下準備ができたら、Office Scriptsを用意します。Excelブックに関連付けられたものでも、SharePointライブラリで管理されたものでも構いません。
isLatestのような列を使って 温存する行と削除する行を識別しながら、可能な限り一括で行を削除するロジックを組みます。
つまり、こんなスクリプトです：

function main(workbook: ExcelScript.Workbook, tableName: string, flagColumn: string)
    : { totalRowCount: number; deletedRowCount: number; deletedRanges: { start: number; count: number; }[] } {

    const table = workbook.getTable(tableName);
    const usedRange = table.getRangeBetweenHeaderAndTotal().getUsedRange();
    if (!usedRange) {
        return {
            totalRowCount: 0,
            deletedRowCount: 0,
            deletedRanges: []
        };
    }

    const columns = table.getColumns();
    const targetColumnIndex = getTargetColumnIndex(columns, flagColumn);
    if (targetColumnIndex === -1) {
        throw "Unknown column name.";
    }

    const sheet = table.getWorksheet();
    const totalRows = usedRange.getTexts();
    const usedRangeRowsStart = usedRange.getRowIndex();
    const usedRangeColumnsStart = usedRange.getColumnIndex();
    const columnCount = columns.length;
    const rangeIndexes = getTargetRangeIndexes(totalRows, targetColumnIndex);
    let deletedRowCount = 0;

    // NOTE: reverse order!
    for (let r of rangeIndexes) {
        const range = sheet.getRangeByIndexes(usedRangeRowsStart + r.start,
            usedRangeColumnsStart, r.count, columnCount);
        range.delete(ExcelScript.DeleteShiftDirection.up);
        deletedRowCount += r.count;
    }

    return {
        totalRowCount: totalRows.length,
        deletedRowCount: deletedRowCount,
        deletedRanges: rangeIndexes
    }
}

function getTargetColumnIndex(columns: ExcelScript.TableColumn[], name: string) {
    for (let i = 0; i < columns.length; i++) {
        if (columns[i].getName() === name) return i;
    }
    return - 1;
}

function getTargetRangeIndexes(rows: string[][], columnIndex: number)
    : { start: number; count: number; }[] {

    const ranges: { start: number; count: number; }[] = [];
    let checkResult = false;
    let rowCount = 0;

    // NOTE: reverse order!
    for (let i = rows.length - 1; 0 <= i; i--) {
        checkResult = (rows[i][columnIndex] === "FALSE");
        if (checkResult) {
            rowCount++;
        } else if (rowCount > 0) {
            ranges.push({
                start: i + 1,
                count: rowCount
            });
            rowCount = 0;
        }
    }
    if (checkResult) {
        ranges.push({
            start: 0,
            count: rowCount
        });
    }
    return ranges;
}

あとはこれをPowerAutomateの「スクリプトの実行」アクションや「SharePointライブラリからスクリプトを実行する」アクションで実行するだけです。
引数として処理対象のテーブル名とisLatestのような識別用の列名を与えます。
この関数は列の値が FALSEである行を、可能な限り一括して削除します。

補足

ExcelブックやOffice Scriptsの下準備、そしてワークフローの作成というわりと大掛かりな仕掛けですが、これら全体をもって所要時間短縮、競合回避を狙っています：

• PowerAutomateワークフローでテーブル内に更新対象の行がすでにあるかどうかをチェックしない。アクション実行数を削減する（アクション実行時間削減、ブック競合リスク回避、アクション実行タイムアウトリスク低減）
• PowerAutomateワークフローでテーブル内のレコードを一件々々を削除しない。アクション実行数を削減する（アクション実行時間削減、ブック競合リスク回避、アクション実行タイムアウトリスク低減）
• Office ScriptsでExcelの行を一括削除しない。「全データをパージ→最新の全データを登録」という戦術をとらない（Office Scripts内のExcelScript API利用時のタイムアウトリスク低減）

PowerAutomateはアクションの実行回数の増加が性能影響しやすいです。
Excelブックの操作はもともとユーザーの操作と競合しやすいのですが、アクションの実行回数が増えるにしたがい、そして実行時間が伸びるにしたがい、競合リスクはさらに増加します。
アクション単位でみると、処理データの膨張や競合の発生により、アクションの実行がタイムアウトしてしまうリスクが増加します。
アクションよりも細かいレベルでみると、Office Scriptsの中で利用できるExcelScript APIなどはそれ自体、処理データの膨張や競合の発生により、関数の実行がタイムアウトするリスクがあります。

以上のもろもろを考慮して、妥協点を探ったのがここでご紹介した戦術でした。
皆さんのご参考になればさいわいです。