概要
DataverseのテーブルをPowerShellを使用してWeb APIでCRUD操作します。Power AppsでDataverseにデータを蓄積し、Power Automate for desktopでオンプレ基幹にデータを入力したいと思い試しました。Mac版PowerShellを使用していますがWindowsでも可能です。
環境
- macOS Monterey 12.1
- MacBook Pro (13-inch, M1, 2020)
- PowerShell 7.2.1
- Visual Studio Code
注意
- AzureAD（組織アカウント）が必要です。
- アプリ作成、セキュリティロール設定可能な権限が必要です。
- 認可グラントはクライアントクレデンシャルフローを使います。
- データの機密度や社内規定などの諸条件によって認可グラントは変更してください。
- Microsoft 365開発者プログラムおよびPower Apps開発者向けプランで試しています。
- 自己責任でお願いいたします
- 2021年12月の情報です.
macOSへのPowerShellインストールについて
homebrewからインストールして使用しました。
インストーラー版は試していません。
作業の目次
- Power Apps
- Dataverseにテーブルを作成
- AzureAD
- アプリ登録
- アプリケーション（クライアント）IDの取得
- ディレクトリ（テナント）IDの取得
- クライアントシークレットの取得
- APIアクセス許可の追加
- Power Platform管理センター
- APIエンドポイントの確認
- セキュリティロールの追加
- アプリケーションユーザーの追加とロール設定
- PowerSellスクリプトの作成
- 読込操作
- 作成操作
- 更新操作
- 削除操作
1. Power Apps
Dataverseでテーブル作成
使用する環境のDataverseにテーブルを作成しサンプルデータを用意します。
今回はプライマリ列Name、数値列valueを作成し、サンプルデータとしました。
2. AzureAD
- AzureADでアプリ登録をします。
-
概要に戻り、アプリケーション（クライアント）IDとディレクトリ（テナント）IDを取得。
-
「証明書とシークレット」に移動し有効期限を選択して追加します。
追加したクライアントシークレットをコピーしてテキストエディットなどに控えておきます。後から参照することはできません。わからなくなってしまった場合は再取得します。
- APIのアクセス許可の「アクセス許可の追加」からDynamics CRMを追加し、「user_impersonation」にチェックします。
3. Power Platform管理センター
-
APIエンドポイントを取得
Dataverseのテーブルがある環境設定を開き「リソース」を展開し、「すべてのレガシ設定」にアクセスします。
Dynamics365が開いたら設定を展開し「カスタマイズ」に進みます。
「開発者リソース」に進みます
インスタンスのWeb APIにエンドポイントが表示されているので控えておきます。
-
セキュリティロールの追加
設定＞セキュリティロールに移動して「新しいロール」を作成します。
ロール名を設定し、ユーザー定義エンティティタブに移動します。
作成したテーブルを探し「作成」「読み込み」「書き込み」「削除」の権限をセットし保存します。
-
アプリケーションユーザーの追加とロール設定
環境に戻りS2Sアプリをすべて表示します。
アプリ追加にAzureADでアプリ登録した名前が出てくるので選択します。
環境タブに現在の環境を選択し、セキュリティロールに作成したロールを割り当て保存します。
4. PowerSellスクリプトの作成
読込操作
アクセストークンの取得
- 認可グラントはクライアントクレデンシャルで行います。
- OAuth2.0トークンエンドポイントはv1を使用します。
- $resourceAppIdUriはDataverseにテーブルを作成したPower Platform環境です。
$tenantId = '********-****-****-****-************' # your tenant ID
$appId = '********-****-****-****-************' # your app ID
$appSecret = '**************************************' # your app secret
$resourceAppIdUri = 'https://***********.crm*.dynamics.com' #your env
$tokenEndpoint= "https://login.microsoftonline.com/$tenantId/oauth2/token"
$body = @{
resource = "$resourceAppIdUri"
client_id = "$appId"
client_secret = "$appSecret"
grant_type = "client_credentials"
}
$response = Invoke-RestMethod -Method Post -Uri $oAuthUri -Body $body -ErrorAction Stop
$token = $response.access_token
Write-Output $token #test output
クエリの作成および実行
作成したテーブルの列名からクエリを構成します。
データの作成日時 (createdon)でフィルターをかけてみます。
- ODataを使用したクエリ
https://docs.microsoft.com/ja-jp/powerapps/developer/data-platform/webapi/query-data-web-api
日付の形式に注意が必要です。「2021/12/16 14:34:00」のような形式はエラーになります。この例では「2021-12-16T14:34:00Z」とします。
$query = '$select=cr1ee_name,cr1ee_value,cr1ee_sampletableid,createdon,modifiedon&$filter=(createdon gt 2021-12-16T14:34:00Z)'
- FetchXmlを使用したクエリ
https://docs.microsoft.com/ja-jp/powerapps/developer/data-platform/use-fetchxml-construct-query
nullを含むプロパティは省かれてしまうので注意が必要です。Docsから引用
OData構文を使用するクエリとは異なり、事前定義されたクエリまたはfetchXmlから返されるデータは、null値を持つプロパティを返しません。 値がnullの場合、プロパティは結果に含まれません。
$query = 'fetchXml=<fetch mapping="logical">
<entity name="cr1ee_sampletable">
<attribute name="cr1ee_name" />
<attribute name="cr1ee_value" />
<attribute name="cr1ee_sampletableid" />
<attribute name="createdon" />
<attribute name="modifiedon" />
<filter>
<condition attribute="createdon" operator="gt" value="2021/12/17 14:33:59" />
</filter>
</entity>
</fetch>'
クエリの実行
環境のAPIエンドポイントにSchemaNameではなく「LogicalCollectionName」＋「？」とし、GETメソッドでクエリをリクエストします。LogicalCollectionNameはシステムで生成されるテーブル名の複数形です。困ったことに、正しく複数形になるとは限らないのが最大のハマりポイントでした。たとえばholidayはholidaysではなくholidaiesになっていました。Metadata Toolsを使用して確認したほうが無難です。
$headers = @{Authorization = " Bearer $Token" }
$endPoint='https://***********.api.crm7.dynamics.com/api/data/v9.2/cr1ee_sampletables' #Your API Endpoint/LogicalCollectionName
$result = Invoke-RestMethod -Uri $endPoint"?$query" -Method 'GET' -Headers $headers
Write-Output $result
データの整形
- JSONで出力
```ps1:PowerShell
Write-Output $result.value | ConvertTo-Json
```
- テーブル形式で表示
$data = 0..($result.value.count - 1) | ForEach-Object {
[PSCustomObject]@{
RowID = $result.value."cr1ee_sampletableid"[$_]
Name = $result.value."cr1ee_name"[$_]
Value = $result.value."cr1ee_value"[$_]
作成日 = $result.value."createdon"[$_]
修正日 = $result.value."modifiedon"[$_]
}
}
Write-Output $data | Format-Table -AutoSize
- CSVに出力
$data| Export-Csv -path "/Users/ユーザー名/パス/ファイル名.csv" -Encoding UTF8 -NoTypeInformation #Your save file path
作成操作
APIエンドポイント/LogicalCollectionNameにJSON形式でPOSTリクエストします。
$headers = @{Authorization = " Bearer $Token" }
$endPoint = 'https://*******.api.crm7.dynamics.com/api/data/v9.2/cr1ee_sampletables'#Your API Endpoint/LogicalCollectionName
$body = '{"cr1ee_name":"XYZ","cr1ee_value":385'
Invoke-RestMethod -Uri $endPoint -Method 'POST' -Headers $headers -Body $body -ContentType "application/json"
更新操作
APIエンドポイント/LogicalCollectionName(RowID)にJSON形式でPATCHリクエストします。
$endPoint = 'https://*******.api.crm7.dynamics.com/api/data/v9.2/cr1ee_sampletables'#Your API Endpoint/LogicalCollectionName
$headers = @{Authorization = " Bearer $Token" }
$rowId = '*******-****-****-****-***********' #Patch RowID
$body = '{"cr1ee_name":"MKJ","cr1ee_value":676}'
Invoke-RestMethod -Uri $uri"($rowId)" -Method 'PATCH' -Headers $headers -Body $body -ContentType "application/json"
削除操作
APIエンドポイント/LogicalCollectionName(RowID)にDELETEメソッドをリクエストします。
$endPoint = 'https://*******.api.crm7.dynamics.com/api/data/v9.2/cr1ee_sampletables'#Your API Endpoint/LogicalCollectionName
$headers = @{Authorization = " Bearer $Token" }
$rowId = '*******-****-****-****-***********' #Delete RowID
Invoke-RestMethod -Uri $uri"($rowId)" -Method 'DELETE' -Headers $headers -Body $body
まとめ
Power Automate for desktopの12月アップデートでDataverseのアクションが追加される予定でしたが、今回は見送られたようです。そこで我慢できず😔PowerShellを使用してDataverseのテーブルをWeb APIからCRUD操作しました。今回はMac版PowerShellから試しましたがとくに不具合はありませんでした。Power Automateのクラウドコネクター経由するよりレスポンスがよく、Power Automate for desktopから呼び出してもストレスなさそうです。Dataverseのこと以外にも認可フローやロール設定など学習範囲は広いですがいろいろ応用出来そうです。
参考
https://docs.microsoft.com/ja-jp/powerapps/developer/data-platform/
https://docs.microsoft.com/ja-jp/azure/active-directory/develop/