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

# はじめに
Power Apps界のロックスター、[Reza Dorrani](https://www.youtube.com/@RezaDorrani)が **「Graph APIを使ったExcel出力方法」** をいくつか紹介しています。

https://www.youtube.com/watch?v=xurF9SY68qg

https://www.youtube.com/@RezaDorrani

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

`Excel`へのエクスポートと言えば、`Office Scripts`や`Power Automate`が選択肢として挙げられます。
しかし、[Reza Dorrani](https://www.youtube.com/@RezaDorrani)が紹介している手段は、`OneDrive for Business`を出力先に設定し、[Office 365 Users コネクタ](https://learn.microsoft.com/ja-jp/connectors/office365users/)を介して、Grarh APIを使う方法であり、処理速度が非常に速く、魅力的な機能です。

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

https://learn.microsoft.com/ja-jp/connectors/office365users/

## 概要
[Reza Dorrani](https://www.youtube.com/@RezaDorrani)が紹介している手段は、`SharePoint Lists`の項目を取得し、[OneDrive for Business](https://learn.microsoft.com/ja-jp/connectors/onedriveforbusiness/)に結果を格納しています。

1. Power Appsから[SharePoint Lists](https://learn.microsoft.com/ja-jp/connectors/sharepointonline/)の`フィルター クエリ`を文字列で渡す
1. [OneDrive for Business](https://learn.microsoft.com/ja-jp/connectors/onedriveforbusiness/) コネクタで、空のファイルを作成する
     - 拡張子は`.xlsx`に設定、ファイル コンテンツは空白文字列
1. [Excel Online (Business)](https://learn.microsoft.com/ja-jp/connectors/excelonlinebusiness/) コネクタをもちいて、(2)で作成したExcelファイルに、テーブルを作成する
1. Graph APIで挿入する配列を`選択`アクションで作成する
1. [Office 365 Users コネクタ](https://learn.microsoft.com/ja-jp/connectors/office365users/)で`Excel`に値を挿入

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

## 簡易版で作成した例
まずは下記のような`Power Apps`の画面を作成します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/9f1e8958-ef89-8375-e100-3dfd29c50574.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/92664b6f-1b9b-3fbc-1bf6-a2b84392f00f.png)

表データは`HTML 文字列`を[Concat 関数](https://learn.microsoft.com/ja-jp/power-platform/power-fx/reference/function-concatenate)で組み合わせて表現しています。

https://learn.microsoft.com/ja-jp/power-platform/power-fx/reference/function-concatenate

```javascript: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>
    "
)
```
https://learn.microsoft.com/ja-jp/power-platform/power-fx/reference/function-with

https://learn.microsoft.com/ja-jp/power-platform/power-fx/reference/function-concatenate

`Power Apps`で値を持っている場合は、[Filter 関数](https://learn.microsoft.com/ja-jp/power-platform/power-fx/reference/function-filter-lookup)をはじめ、`Power Apps`側で出力する値を制御できます。
そのシナリオを想定し、複数のデータを[JSON 関数](https://learn.microsoft.com/ja-jp/power-platform/power-fx/reference/function-json)で文字列に変換し、`Power Automate`に渡します。

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/e03c80cb-a772-fbe9-fbe6-5e23148b26f5.png)


[JSON 関数](https://learn.microsoft.com/ja-jp/power-platform/power-fx/reference/function-json)の引数は`FlattenValueTables`です。

> 値テーブル として、[1,2,3] 表記は、各レコードに 1 つの 値 列があるレコードを含むテーブルになります。 JSON では、同じ表記法で 3 つの数値の配列を表します。 2 つの間の相互運用性を容易にするために、このオプションは、レコードの配列ではなく、Power Fx 値テーブルを JSON 対応の配列にフラット化します。
>> [Microsoft learn - JSON 関数](https://learn.microsoft.com/ja-jp/power-platform/power-fx/reference/function-json)

https://learn.microsoft.com/ja-jp/power-platform/power-fx/reference/function-json

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

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

1. Power Appsから`出力する二次元配列`を文字列で渡す
1. `JSON の解析`で値をパースする
1. `選択` アクションで値を`二次元配列`として設定する
1. [OneDrive for Business](https://learn.microsoft.com/ja-jp/connectors/onedriveforbusiness/) コネクタで、空のファイルを作成する
     - 拡張子は`.xlsx`に設定、ファイル コンテンツは空白文字列
1. [Excel Online (Business)](https://learn.microsoft.com/ja-jp/connectors/excelonlinebusiness/) コネクタをもちいて、(2)で作成したExcelファイルに、テーブルを作成する
1. [Office 365 Users コネクタ](https://learn.microsoft.com/ja-jp/connectors/office365users/)で`Excel`に値を挿入する

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/18716ef3-1117-2992-30a3-551ddc8896fe.png)

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/0c30a5bb-3779-e5c7-27ee-8bb923002e9a.png)

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/fcd98546-a24f-7e31-329b-d58f20692f7c.png)

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

#### (4) [OneDrive for Business](https://learn.microsoft.com/ja-jp/connectors/onedriveforbusiness/) コネクタで、空のファイルを作成する

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/df936c36-62a2-89da-f623-7d8510dcb6db.png)

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

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

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

https://learn.microsoft.com/ja-jp/azure/logic-apps/workflow-definition-language-functions-reference#converttimezone


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

`ファイルコンテンツ`: ` `

#### (5) [Excel Online (Business)](https://learn.microsoft.com/ja-jp/connectors/excelonlinebusiness/) コネクタをもちいて、(2)で作成したExcelファイルに、テーブルを作成する

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/2e3dfed6-27da-3512-59a3-33c99e91258b.png)

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

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

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

#### (6) [Office 365 Users コネクタ](https://learn.microsoft.com/ja-jp/connectors/office365users/)で`Excel`に値を挿入する

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/cd4542f6-7ce4-4684-1358-cbd3f9436468.png)

https://learn.microsoft.com/ja-jp/graph/api/table-post-rows?view=graph-rest-1.0&tabs=http

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

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

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

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

- [actions 関数](https://learn.microsoft.com/ja-jp/azure/logic-apps/workflow-definition-language-functions-reference#actions)

> 実行時のアクションの出力を返すか、または式に割り当てることができる他の JSON の名前と値のペアの値を返します。 既定では、この関数はアクション オブジェクト全体を参照しますが、必要に応じて値を取得するプロパティを指定することができます。 短縮版については、 body()を参照してください。 現在のアクションの場合は、action() をご覧ください。
>> [Microsoft learn - 式関数のリファレンス ガイド](https://learn.microsoft.com/ja-jp/azure/logic-apps/workflow-definition-language-functions-reference#actions)

https://learn.microsoft.com/ja-jp/azure/logic-apps/workflow-definition-language-functions-reference#actions

- [split 関数](https://learn.microsoft.com/ja-jp/azure/logic-apps/workflow-definition-language-functions-reference#split)

https://learn.microsoft.com/ja-jp/azure/logic-apps/workflow-definition-language-functions-reference#split

- メソッド: `POST`

```json:本文
{
  "values": @{body('選択_二次元配列に変換')}
}
```

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


### 課題点
しかしながら、このエクスポート方法にはいくつか課題があります。
[Excel Online (Business)](https://learn.microsoft.com/ja-jp/connectors/excelonlinebusiness/) コネクタ、`テーブルの作成`の際に、ファイルの`場所`、`ドキュメント ライブラリ`の設定が下記のようにされています。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/a7418d72-32b3-85d9-44d5-5027c30038ab.png)

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

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

<details><summary>旧情報</summary>

`OneDrive for Business`をターゲットにすれば、[Office 365 Users コネクタ](https://learn.microsoft.com/ja-jp/connectors/office365users/)を介して簡単に`driveId`は取得できます。

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/c6a6b428-aec3-d9bb-7a63-7993e731e31e.png)

- メソッド: `GET`

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

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

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

```json:本文
{
    "@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
}
```

```javascript:ドキュメント ライブラリ
body('HTTP_要求を送信します_driveIdの取得')?['parentReference']?['driveId']
```

</details>

**新情報**

:::note info
有識者[おいしみ](https://qiita.com/ksgiksg)さんよりご助言いただきました!!
`OneDrive for Business ファイルの作成`の出力である`ID`から値は取得できそうです。
:::


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

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/19100454-78a9-e7a2-3dc2-d5b46c08df9b.png)

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

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