LoginSignup
5
5

More than 1 year has passed since last update.

@mizukyf(mizuky fujitani)inパーソルプロセス＆テクノロジー株式会社

PowerAutomateでExcelテーブルの行を一括削除する - Range.delete()編

Posted at

PowerAutomateでワークフローを作成しているとExcel Onlineのテーブル（表）を操作するシーンがしばしばあります。
そしてテーブルのレコードを一括削除したいこともまたしばしばあります。
このような背景をふまえ、半年ほどまえに PowerAutomateでExcelテーブルの行を一括削除する方法を紹介しました。
今回はその別解の紹介です。

Problem

Excel Onlineのテーブル（表）のレコードを削除する手段として、PowerAutomateの標準アクションには、1行ずつ削除する「行の削除」はありますが、多数をまとめてというものはありません。。
この標準アクションを使う場合、おそらく「Apply to each」アクションと併用することになるでしょう。
しかしご存知の通りこのアクションはとても遅い。
所要時間の観点で許容できない場合は他の選択肢を探す必要があります。

Solution

このようなちょっと難しいタスクをこなすならばOffice Scriptsです。
テーブルの行を一括削除するスクリプトを作成し、「スクリプトの実行」アクションで実行します。
その上で、方法はいくつかあります。

Table.deleteRowsAt()関数を使う

1つの解は ExcelScript.Table インターフェースが提供する deleteRowsAt()関数を使用するものです（リファレンス）。
これが半年ほどまえの記事の採用する方法。

この関数はTableインターフェースが提供するAPIということで直感的に利用できますが、公式リファレンスにもある通りタイムアウトエラーを起こすリスクがあり、それを考慮したコーディングが必要になります：
image.png

スクリプトを実行できませんでした。もう一度お試しください。
Office JS エラー:Line 5: Table deleteRowsAt: Timeout
clientRequestId: 3c23c6b9-eefb-44db-82a0-xx1752bdc7xx

タイムアウトエラーを起こすリスクを考慮したコーディングについては、すでに紹介した半年ほどまえの記事で解説しています。

Range.delete()関数を使う

もう1つの解は ExcelScript.Range インターフェースが提供する delete()関数を使用するものです（リファレンス）

この関数はテーブル（表）ではなく、それを介さずに、Excelワークシートの範囲に直接働きかけるもので、 deleteRowsAt()関数に比べると直感的に利用できる度合いは下がります。

では、この方法のメリットは何でしょうか？

1つ目は、タイムアウトリスクが少なそうだということです。 Table.deleteRowsAt()関数を使う方法では、公式リファレンスで明確にこのリスクが説明されています。現実はこのリファレンスの記述よりもシビアなのですが。
Range.delete()関数にはこのようなリスクの説明がないので、リスクは「少なそう」です。

2つ目は、テーブル（表）のデータをすべて削除するのではなく条件にマッチした部分だけを削除するというユースケースと親和性が高いことです。これは後述します。

1つ目の点について補足です。
実際、10,000件のレコードからなるテーブルのデータを全件削除する実験では、Table.deleteRowsAt()関数では必ずタイムアウトエラーを起こしましたが、Range.delete()関数はエラーを起こさず数十秒で処理を終えています。

1つ目の点についてもう1つ補足です。
それでもOffice ScriptsからのExcel Onlineの操作にはタイムアウトリスクがつきまといます。 ExcelScriptが提供するAPIは公式リファレンスで言及がなくともタイムアウトエラーのリスクがあります。

例えば後ほど紹介するサンプルコードの中で ExcelScript.Worksheet.getRangeByIndexes()関数を使用する箇所が登場します。明確なエラー条件はわかりませんが、削除範囲（部分）を多数持つテーブルに対してこの関数を呼び出したところ以下のようなエラーが発生しました：

スクリプトを実行できませんでした。もう一度お試しください。
Office JS エラー:Line 30: Worksheet getRangeByIndexes: Timeout
clientRequestId: 0e481775-6c84-4ca1-9fd8-xxxxbc19xxxx

テーブルのレコード数を減らし（数千→数十）、削除範囲（部分）も数個に収まるレベルにしたところ、エラーなく処理が完了できました。

基本的な使い方

基本的な利用方法としては次に示すように、まずは WorksheetインターフェースやTableインターフェースが提供するAPIを使用して Rangeインターフェースのオブジェクトを作成します：

const tableRange = table.getRangeBetweenHeaderAndTotal().getUsedRange();
// あるいは
const alternativeRange = table.getWorksheet().getRangeByIndexes(...);

そしてこの Rangeを削除します：

someRange.delete(ExcelScript.DeleteShiftDirection.up);
// あるいは
someRange.delete(ExcelScript.DeleteShiftDirection.left);

1つ前のコードではTable.getRangeBetweenHeaderAndTotal()関数の戻り値をそのまま使うのではなく Range.getUsedRange()をチェインしています。これはなぜでしょうか？
Excelのテーブルの挙動を思い出していただきたいのですが、テーブルはレコードがゼロ件のとき、1行分、空の範囲が設定されます。
image.png
getRangeBetweenHeaderAndTotal()関数はこの空の部分も含む範囲を返します。そしてこの範囲をdelete()するとエラーが発生します。違和感と納得感が相交じる結果ですね。
この範囲に対して getUsedRange()を呼び出すとレコードが1件以上あればその範囲を示す Rangeが、レコードが1件もなければ undefinedが返されます。
そしてこれを前提に次のようにコーディングすればエラーを回避することができます：

table.getRangeBetweenHeaderAndTotal().getUsedRange()?.delete(ExcelScript.DeleteShiftDirection.up);

条件にマッチする行だけ削除する

さて、実際にはいろいろな条件で削除対象を決めたいこともあると思います。
このような場合も削除対象行が数件～数十件ありスピードを重視するのならば、 「行の削除」アクションではなく「スクリプトの実行」で Range.delete()関数を使うほうがよいでしょう。

削除対象の部分（範囲）が複数個ある前提でサンプルコードを書いてみました：

function main(workbook: ExcelScript.Workbook, tableName: string, columnName: string, value: 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, columnName);
  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, value);
  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, value: string)
  : { 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] === value);
    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;
}

サンプルコード解説

処理対象のテーブル、条件マッチの対象になるカラム、条件値の文字列を引数に取るスクリプトです：

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

前述の通り、Table.getRangeBetweenHeaderAndTotal()はレコードが0件のテーブルに関しては「空の行」を1件含むRangeを返し、これに対して Range.delete()するとエラーになります。これを回避するために Range.getUsedRange()を行って結果次第ですぐさま処理を終えます：

  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, columnName);
  if (targetColumnIndex === -1) {
    throw "Unknown column name.";
  }

テーブルのレコードの範囲のデータをすべて取得し（Range.getTexts()）、その内容を元にして削除対象範囲の情報を収集します（getTargetRangeIndexes()）。範囲は複数ありえる前提で、該当するすべての範囲の位置情報を、しかもテーブルの下から上へ逆順で取得しています：

  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, value);

取得した情報を元に Worksheet.getRangeByIndexes()を使用して範囲を取得し Range.delete()で削除していきます。上から順に削除すると位置情報が狂ってしまうので、逆順で削除していきます：

  // 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;
  }

Worksheet.getRangeByIndexes()関数については先程注記した通りタイムアウトエラーのリスクがあります。

おわりに

「正直ここまでするの？」というご意見は聞かれそうです。私も半ばはそう思います。
一方でPowerAutomateを組織の業務の中で利用していると、「テーブルの行をクリアしたい」「テーブルの中の特定部分の行をクリアしたい」というケースがしばしば発生します。したがってやはりこうした工夫が必要になります。

5
5
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
5
5