PowerAutomateとOfficeScriptの落とし穴にハマって埋めていった話を共有するよ

自己紹介
この記事って何……？
なぜ自動化を行ったの？
PowerAutomateとは？
PowerAutomateの落とし穴
- ①指定したパスが長すぎるとエラーになる（ときがある）
- ②PowerAutomateでは限界のある処理が存在する
  - ・複雑な処理や並列処理は時間がかかる場合がある
  - ・書き込み先のファイルを開いている場合は、データの書き込みができない
Officeスクリプトとは？
Officeスクリプトの落とし穴
まとめ

自己紹介

NTTドコモデータプラットフォーム部（以下DP部）江口です。本記事では、Microsoft PowerAutomate(以下、PowerAutomate)の活用における障壁とその解決策について紹介していきます。 DP部では、全国に存在するドコモの支社および本社組織において管理するべき情報を、社員間で共有するためのプラットフォームを構築・運用しています。

その場合、データ発生源の作業者が馴染み深く、柔軟性の高いSharepointと相性のよいPowerAutomateがデータ連携・加工方法に選択肢にあがってきます。しかしながら、PowerAutomateに関しては、ノーコードツールということもあってなのかWEB上にあまり情報がなく思わぬエラーや不具合・不都合に出会い、試行錯誤的に対応していきました。本記事ではその備忘とPowerAutomateコミュニティ活性化の想いも込めて紹介させていただきます。

なお、本記事の掲載の取組については、支援メンバーであるNEC通信システムの堀島さんに詳細検討・実装を進めてもらっており、以下は、堀島さんに執筆いただいています。

この記事って何……？

Sharepoint上のファイルの更新をPowerAutomateで自動化する際に、四苦八苦した記録を書き記した記事です。
今後、PowerAutomateでの自動化を行ったり、簡易的なシステムを組んだりする方の参考になれば幸いです。また執筆時点での、執筆者の環境での状況なので、現状の正式な仕様を表したものではないことをご留意ください。しかしながら、どこかのだれかの環境で似たようなことが起きていれば同様のトラブルシューティングが可能となるはずです。

なぜ自動化を行ったの？

Microsoft Formsの回答内容や、Sharepoint上のExcelの記載内容を、Tableauで可視化するためにファイル更新の自動化を行いました。
Sharepointコネクタを使うだけでもTableauとExcelを接続できるのですが、「ユーザが入力するインターフェースとしてのファイル」と「Tableauへ連携するファイル」を分離することで、ヒューマンエラーを含むエラーを最小限に抑えられると判断しました。
今回は特に「Excelを高頻度で様々な人が更新する」という条件があったため、記入用ファイルと連携用ファイルを分けて自動で転記する方法を試みています。

PowerAutomateとは？

PowerAutomateとは、Microsoftが提供する自動化ツールです。
SharepointやOnedrive、Formsといった様々なアプリケーションとの接続が可能で、ファイルの作成やデータの取得などの操作を自動で実行することができます。
「フロー」を作成して、その中に記載された操作を自動実行します。

「自動化したクラウドフロー」では特定の動作をトリガーとした完全自動のフローが作成でき、「インスタントクラウドフロー」では実行のタイミングを手動で決められるフローを作成できます。

今回が初めての使用でしたが、基本的な使い方についてはWeb上である程度理解することができました。
ただし、細かい仕様で詰まったり、仕様変更によりWeb上の情報が使えなかったりと、つまづきながらもやっと実装することができました……。

ここからは、自動化でハマりやすいポイント「落とし穴」を紹介していきたいと思います。よくあるのはDesktop版とWeb版での仕様差ですが、今回はWeb版を中心にそれ以外の落とし穴を紹介します。

PowerAutomateの落とし穴

①指定したパスが長すぎるとエラーになる（ときがある）

PowerAutomateでは、操作を行うファイルやフォルダを以下のように指定します。

この時、フォルダがかなり深い層に存在していたり、ファイル名が長すぎたりすると、エラーとなりフローが失敗してしまいます。

かなり基本的なエラーにも関わらずWeb上にほとんど情報が無く、初めて遭遇した際に自力で気付くのは困難です。
見出しに”ときがある”としたのは、今回のブログ執筆のために、スクリーンショットをとろうと同様の状況をつくろうとしたのですが、エラー内容を再現できなかったためです。もしかしたら現状は解消されているかもしれません。

②PowerAutomateでは限界のある処理が存在する

PowerAutomateにも苦手な処理や不可能な処理も存在します。
特に私が行き詰った処理は、以下の2つです。

・複雑な処理や並列処理は時間がかかる場合がある

Excelのデータ連携において、10枚のシートから1つのファイルへデータを転記させようとしていたのですが、処理時間が30分以上のフローとなってしまいました。

このように並列処理は作成可能ですが、テーブル関係の処理は時間がかかるため現実的ではありません。

タイムアウトでフローが停止してしまうリスクがある上、今回は即時性が求められる場面での利用を想定していたため後に紹介する別の方法で高速化を図りました。

・書き込み先のファイルを開いている場合は、データの書き込みができない

PowerAuomateでは、「Excel上のテーブルのデータを1行ずつ読み込んで、別のテーブルに転記する」ことが可能なのですが、書き込み先のファイルが開かれている場合はフローが失敗します。
WebやローカルでExcelを開いている時にエラーとなるのですが、Tableauに接続されている場合は、「常にTableauがExcelを開いている」状態となるため常にエラーとなるようです。

このファイルロックの仕様がTableauに常時連携するためのファイルを書き込みしなければいけないシステム上の仕様の構築に致命的でした。

ここで解決策として見つかったのが「Officeスクリプト」です。

Officeスクリプトとは、PowerAutomateで呼び出せるスクリプトで、Tableauに接続されたファイルにも書き込みができるだけでなく、処理時間も劇的に短いため、前述の問題を同時に解決することができました。

Officeスクリプトとは？

Officeスクリプトは、Excelの操作を自動化できるツールです。
VBAとの主な違いは、「デスクトップへアクセスせずに、対象のExcelにのみアクセスする」という点です。
上記の仕様により、セキュリティで保護されているファイルにも安全に実行することができます。

Excelの上部タブから「Officeスクリプト」を選択して、編集や実行が可能です。
PowerAutomateと連携することで、対象のExcelを開いていない状態でも操作が可能になります。

Officeスクリプトの落とし穴

Officeスクリプトにも陥りやすい「落とし穴」が存在します。

①ファイルを跨ぐ操作には複数のスクリプトが必要

VBAでは別ファイルへのコピペを行う際も、2つのファイル名を指定するだけで簡単に実装できます。
しかし、Officeスクリプトでは1つのExcelにしか操作ができないため、ファイルを跨いだコピペはできません。

この問題を解決するには、コピー結果を戻り値としてPowerAutomateへ引き渡し、新たなスクリプトでペーストしなければなりません。

上記のように、コピーとペーストで2つのスクリプトに分け、コピー結果をresultとしてペーストスクリプトに渡しています。

実際のコピーとペーストのスクリプトを以下に紹介します。
1~5という名前のワークシートに対して、範囲指定でコピペを行うだけのスクリプトです。

コピーのスクリプト

function main(workbook: ExcelScript.Workbook): WorksheetData[] {
  // 固定のシート名リスト
  const sheetNames = ["1", "2", "3", "4", "5"];

  // 結果を保存する配列
  let cellData: WorksheetData[] = [];

  sheetNames.forEach(sheetName => {
    // シートを取得
    let selectedSheet = workbook.getWorksheet(sheetName);
    if (selectedSheet) {
      // シートのデータを取得
      let values = selectedSheet.getRange("A9:AE8009").getValues();
      cellData.push({
        data: values as string[][] // データを取得
      });
    } else {
      console.log(`シート ${sheetName} が見つかりません`);
    }
  });

  return cellData; // コピーしたデータを返す
}

// データを保存するための構造体
interface WorksheetData {
  data: string[][];
}

ペーストのスクリプト

function main(workbook: ExcelScript.Workbook, copiedData: WorksheetData[]) {
  // 固定のシート名リスト
  const sheetNames = ["1", "2", "3", "4", "5"];

  sheetNames.forEach((sheetName, index) => {
    // シートを取得
    let sheet = workbook.getWorksheet(sheetName);

    // シートをクリアしてデータを貼り付け
    sheet.getRange("A2:AE8002").clear(); 
    let data = copiedData[index].data;
    sheet.getRange("A2").getResizedRange(data.length - 1, data[0].length - 1).setValues(data);
  });
}

// データを保存するための構造体
interface WorksheetData {
  data: string[][];
}

②Web上に情報が少ない

Webで検索してもOfficeスクリプトに関する情報が極端に少なく、VBAのような基本の構文がなかなか見つかりませんでした。

JavaScriptに詳しい場合はそこまで苦戦しないかもしれませんが、プログラミングが苦手な方はとっつきにくいかもしれません。

③複数人での編集したい場合はSharepointか共有Onedriveに保存する必要がある

Officeスクリプトはデフォルトで個人のOnedriveと紐づいており、他のアカウントからは編集や実行ができなくなっています。

他のアカウントと共有するには、保存先を変更する必要があります。
具体的には、Sharepointや共有Onedriveに保存することで複数人での編集が可能になります。

また、Officeスクリプトはフローの作成者の認証情報を利用して実行するため、フローの所有者が変更されると認証エラーで実行できなくなってしまいます。
こちらに関しても、上記の共有可能なクラウド上に保存することで、所有者の変更時のエラーも回避することができます。

まとめ

ここまで紹介した落とし穴だけでなく、様々な仕様に悩まされながらも自動化を達成することができました。
特にPowerAutomateは高い頻度で仕様変更が起きるため、Web上の情報も鵜呑みにできません……。本記事の内容もすぐに役に立たないものになってしまう可能性がありますので、その点もご留意いただければ幸いです。

もしこの記事を読んで興味を持ってくださった方、参考にしてPowerAutomateやOfficeスクリプトを使ってみた方がいらっしゃれば、ぜひ自分の経験や知見を発信してみてください。

様々な情報が出回れば、「より多くの方が活用できて、また知見が増える」という良い循環を生み出せると思います。
この記事も、困っている誰かの役に立てることを願っています。