7
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

Day 9

Microsoft Power Apps Advent Calendar 2024

@DEmodoriGatsuO(Gatsuo De'modori)

Office 365 ユーザーコネクタを利用したExcel出力

7
Last updated at Posted at 2024-12-08

はじめに

Power Apps界のロックスター、Reza Dorraniが 「Graph APIを使ったExcel出力方法」 をいくつか紹介しています。

海外のMVPも良く紹介されているテーマですが、RezaのYouTubeはとりわけ丁寧です。

Excelへのエクスポートと言えば、Office ScriptsやPower Automateが選択肢として挙げられます。
しかし、Reza Dorraniが紹介している手段は、OneDrive for Businessを出力先に設定し、Office 365 Users コネクタを介して、Grarh APIを使う方法であり、処理速度が非常に速く、魅力的な機能です。

本当に素晴らしい機能です。内容の紹介と同時に、実装するうえで課題に遭遇したため、その内容を紹介したいと思います。

概要

Reza Dorraniが紹介している手段は、SharePoint Listsの項目を取得し、OneDrive for Businessに結果を格納しています。

  1. Power AppsからSharePoint Listsのフィルター クエリを文字列で渡す
  2. OneDrive for Business コネクタで、空のファイルを作成する
    • 拡張子は.xlsxに設定、ファイル コンテンツは空白文字列
  3. Excel Online (Business) コネクタをもちいて、(2)で作成したExcelファイルに、テーブルを作成する
  4. Graph APIで挿入する配列を選択アクションで作成する
  5. Office 365 Users コネクタでExcelに値を挿入

上記をベースに、Power AppsでJSON 関数を用いて、配列を直接Power Automateに渡し、Excel出力する方法で進めてみます。

簡易版で作成した例

まずは下記のようなPower Appsの画面を作成します。

image.png

ChatGPTで生成した200件のデータが表示されています。こちらのテーブルに記載のある値を、Excelに出力するというシナリオで進めてみます。

image.png

表データはHTML 文字列をConcat 関数で組み合わせて表現しています。

HtmlText
With(
    {
        commonPadding: "padding: 12px 15px; text-align: left; border-bottom: 1px solid #e9ecef;",
        headerStyle: "color: #495057; font-weight: 600; text-transform: uppercase; font-size: 0.85rem; position: sticky; top: 0;"
    },
    $"
    <table style='width: 100%; border-collapse: collapse; box-shadow: 0 1px 3px rgba(0,0,0,0.2); border-radius: 8px; overflow: hidden;'>
    <thead>
        <tr style='background-color: #f8f9fa;'>
            <th style='{commonPadding}{headerStyle}'>製品ID</th>
            <th style='{commonPadding}{headerStyle}'>製品名</th>
            <th style='{commonPadding}{headerStyle}'>カテゴリー</th>
            <th style='{commonPadding}{headerStyle}'>ブランド</th>
            <th style='{commonPadding}{headerStyle}'>価格</th>
            <th style='{commonPadding}{headerStyle}'>在庫状況</th>
            <th style='{commonPadding}{headerStyle}'>評価</th>
        </tr>
    </thead>
        <tbody>
        {Concat(Samples,$"<tr style='hover: background-color: #f8f9fa;'>
            <td style='{commonPadding}'>{ThisRecord.ProductID}</td>
            <td style='{commonPadding}'color: #007bff; font-weight: 500;'>{ThisRecord.ProductName}</td>
            <td style='{commonPadding}'>{ThisRecord.Category}</td>
            <td style='{commonPadding}'>{ThisRecord.Brand}</td>
            <td style='{commonPadding}'font-weight: 600; color: #28a745;'>¥{ThisRecord.Price}</td>
            <td style='{commonPadding}'>
                <span style='color: #856404; background-color: #fff3cd; padding: 4px 8px; border-radius: 4px; font-size: 0.85rem;'>{ThisRecord.StockStatus}</span>
            </td>
            <td style='{commonPadding}'color: #6c757d;'>★{ThisRecord.Rating}</td></tr>")}
        </tbody>
    </table>
    "
)

Power Appsで値を持っている場合は、Filter 関数をはじめ、Power Apps側で出力する値を制御できます。
そのシナリオを想定し、複数のデータをJSON 関数で文字列に変換し、Power Automateに渡します。

OnSelect Power Automateの実行
Export.Run(
    JSON(
        Samples,
        JSONFormat.FlattenValueTables
    )
);

image.png

JSON 関数の引数はFlattenValueTablesです。

値テーブル として、[1,2,3] 表記は、各レコードに 1 つの 値 列があるレコードを含むテーブルになります。 JSON では、同じ表記法で 3 つの数値の配列を表します。 2 つの間の相互運用性を容易にするために、このオプションは、レコードの配列ではなく、Power Fx 値テーブルを JSON 対応の配列にフラット化します。

Microsoft learn - JSON 関数

ここまでがPower Appsの解説です。
次にPower Automateを見ていきましょう。

Power Automate

Power Automateの流れは下記のようになります。

  1. Power Appsから出力する二次元配列を文字列で渡す
  2. JSON の解析で値をパースする
  3. 選択 アクションで値を二次元配列として設定する
  4. OneDrive for Business コネクタで、空のファイルを作成する
    • 拡張子は.xlsxに設定、ファイル コンテンツは空白文字列
  5. Excel Online (Business) コネクタをもちいて、(2)で作成したExcelファイルに、テーブルを作成する
  6. Office 365 Users コネクタでExcelに値を挿入する

image.png

上記のうち(3)、(4)、(5)の設定値を紹介します。

(3) 選択 アクションで値を二次元配列として設定する

image.png

  • From: Excel出力する二次元配列　@{body('JSON_の解析')}
  • Map: 行の要素を指定

選択アクションにおいて、テキスト モードに切り替えをクリックし、[]で囲んで要素を指定します。

image.png

Map
[
  @{item()?['Brand']},
  @{item()?['Category']},
  @{item()?['Price']},
  @{item()?['ProductID']},
  @{item()?['ProductName']},
  @{item()?['Rating']},
  @{item()?['StockStatus']}
]

(4) OneDrive for Business コネクタで、空のファイルを作成する

image.png

ファイル コンテンツを （半角スペース）にして空のファイルを作成します。
これが一番驚いたポイントです。

フォルダーパス: / （任意のパスに置き換えるポイントです）

ファイル名
convertTimeZone(utcNow(),'UTC','Tokyo Standard Time','yyyy-MM-dd_HHmmss')}.xlsx

日本時間で現在日時を取得するお作法関数
convertTimeZone(utcNow(),'UTC','Tokyo Standard Time','f')

ファイルコンテンツ:

(5) Excel Online (Business) コネクタをもちいて、(2)で作成したExcelファイルに、テーブルを作成する

image.png

  • 場所: OneDrive for Business
  • ドキュメント ライブラリ: 個人のOneDrive for Businessに紐づいたドキュメント
  • ファイル: (4)の戻り値からIDを取得

表の範囲、テーブル名、列名はハードコードしてしまっています。
テーブル名は後述のGraph APIで用いるため、重要なキーになります。

表の範囲や列名を流動的にするには、データ操作のテクニックが必要です。

(6) Office 365 Users コネクタでExcelに値を挿入する

image.png

Graph APIを使って、作成したテーブルに配列を挿入します。

URI
https://graph.microsoft.com/v1.0/me/drive/items/@{split(actions('テーブルの作成')?['inputs']?['parameters']?['file'],'.')?[1]}/workbook/tables/Sample/rows

上記のポイントは split(actions('テーブルの作成')?['inputs']?['parameters']?['file'],'.')?[1]です。
こちらの正体は、「(4) OneDrive for Business コネクタで、空のファイルを作成する」から取得できるファイル識別子Idを.で分割した最後の値です。

テーブルの作成の入力値を参照するため、ドツボにハマるとすれば、テーブルの作成のファイルの指定を誤るときですね。（体験談）

実行時のアクションの出力を返すか、または式に割り当てることができる他の JSON の名前と値のペアの値を返します。 既定では、この関数はアクション オブジェクト全体を参照しますが、必要に応じて値を取得するプロパティを指定することができます。 短縮版については、 body()を参照してください。 現在のアクションの場合は、action() をご覧ください。

Microsoft learn - 式関数のリファレンス ガイド

  • メソッド: POST
本文
{
  "values": @{body('選択_二次元配列に変換')}
}

非常に素晴らしい機能です。圧倒的な速さで処理が完了します。

課題点

しかしながら、このエクスポート方法にはいくつか課題があります。
Excel Online (Business) コネクタ、テーブルの作成の際に、ファイルの場所、ドキュメント ライブラリの設定が下記のようにされています。

image.png

この設定値driveは、ユーザーによって値が変動するdriveIdです。
アプリケーションを公開し、ユーザーに機能を展開した際に、driveIdの相違から正しく機能しない可能性が発生します。

対応策

対応策としては、このdriveIDをGraph APIで明示的に取得し、テーブルの作成に当てはめる方法があります。

旧情報

OneDrive for Businessをターゲットにすれば、Office 365 Users コネクタを介して簡単にdriveIdは取得できます。

https://learn.microsoft.com/ja-jp/graph/api/drive-get?view=graph-rest-1.0&tabs=http#get-a-users-onedrive

image.png

  • メソッド: GET

エンドポイントは下記のとおりです。

URI
https://graph.microsoft.com/v1.0/me/drive/root

ここで取得できる値の例です。parentReferenceのもとにあるdriveIdが欲しい値です。

本文
{
    "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#Collection(driveItem)/$entity",
    "createdDateTime": "2022-06-04T15:06:22Z",
    "id": "01FGLN35V6Y2GOVW7725BZO354PWSELRRZ",
    "lastModifiedDateTime": "2024-11-30T13:04:21Z",
    "name": "root",
    "parentReference": {
        "driveType": "business",
        "driveId": "b!2ZD9opzPOkWhzqZIQCFx4WyB88c_7exOoyaMumiJ4BfRIaYZstvCRJcaEZUxLaL0"
    },
    "webUrl": "https://personal.sharepoint.com/personal/personal_com/Documents",
    "fileSystemInfo": {
        "createdDateTime": "2022-06-04T15:06:22Z",
        "lastModifiedDateTime": "2024-11-30T13:04:21Z"
    },
    "folder": {
        "childCount": 0
    },
    "root": {},
    "size": 999
}
ドキュメント ライブラリ
body('HTTP_要求を送信します_driveIdの取得')?['parentReference']?['driveId']

新情報

有識者おいしみさんよりご助言いただきました!!
OneDrive for Business ファイルの作成の出力であるIDから値は取得できそうです。

OneDrive for Business コネクタのファイルの作成アクションから下記を取得します。
こちらがdriveIdに該当します。

ドキュメント ライブラリ
split(outputs('ファイルの作成_空のコンテンツを作成')?['body/Id'],'.')?[0]

上記からテーブルの作成アクションのドキュメントライブラリの値を下記のように設定します。

image.png

この場合、ユーザーが多様な場合でも正常に動作します。

結果

200件ほどの値の出力であれば、秒で完了します。
改めてRezaの超絶テクニックには脱帽ですね！

7
2
4

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
7
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?