#概要
- つい最近、PowerAppsでSharePoint上のExcelもデータソースにできるようアップデートがありました。
- 今まではOneDriveのみで、機能も限定的だっため業務ではほとんど活用されていなかったと思います。
- 今回のアップデートで業務で使えると思ったこと、また実際にやってみたことがありましたので簡単にまとめてみます。
Excelの面倒を見る必要があるため内容的には情シスではなく業務部門向けかもしれません。
ExcelのPowerQueryと組み合わせると、PowerAppsで手軽に社内データを読み取り専用で使えるようになります。
コネクタのドキュメントはまだ反映されていません。
https://docs.microsoft.com/ja-jp/connectors/excelonlinebusiness/
#簡単なまとめ
元からな部分もありますが
- SharePointのドキュメントライブラリ(又はTeamsのファイルタブ)に置いたExcelに接続し、テーブル形式にした表をデータソースとして利用可能。
- 新規、更新、削除に対応、人がファイルを開いていてもOK。
- 数値、テキストなど一部の型と=やStartsWithなど一部の演算子、Filter、LookUp、SortByColumnsが委任可能
- 数式入のセルも表示可能。そして数式は動的に評価される(すばら)
- 日付型のセルは今の所空白になり、Excel側でText関数でテキスト化しておけば大丈夫でした。
- PowerQueryで取得したテーブルにも対応。読み取り専用ならデータフロー的なことが可能。
- いきなり数千行のテーブルを読み込ませるとエラーになったのでまずは一行とかで。
- アプリにアクセスするだけでファイルの更新者名が更新される。(Office365ホームに出てくる)
こちらのMVPの方の記事が参考になりました。
https://mofumofupower.hatenablog.com/entry/excel-online-powerapps
仕様はアップデートですぐ変わると思われます。
PowerPlatformは公式ブログよりもドキュメントよりもサービスが先にアップデートされるところが面白いですね。
#活用例
#1. 普通にExcelテーブルデータソース
###アプリの例
アプリ自体は面白みのない普通の自動生成アプリです。遅いですが新規、編集、削除が一通りできます。
ソートや更新をすると分かるのですが、Excel数式の列は動的に評価されています。
PowerApps
Excelデータソース
###作成方法
アプリを自動生成したいですが、今の所データから開始の「Excel Online」はOneDriveのファイルしか指定できません。
対応策として一旦ExcelをOneDriveに置いてアプリ自動生成し、その後ファイルをSharePointに移動させて接続情報を修正します。
※ギャラリーのSearch関数など一部書き換えが必要になる箇所はあります。
データ画面で既存の接続情報を削除し、Excel Online(Business)コネクタからSharePoint上のExcelに接続し直します。
データソース(テーブル名)名が同じだと問題なく修正されます。
###その他
・Excel Onlineに接続したときに聞かれるIDは「自動的に生成」を選択しておきます。
・数式列はデータ登録画面でフィールドから外しておくと、Excel側で勝手に数式コピーしてくれました。
###注意事項など
- Excelへの書き込み/反映は非常に遅いです。SubmitFormした直後にギャラリーに戻っても編集結果が反映されていません。Submit直後にAPIから返ってくるデータはまだ変更前のものです。
- ※PowerAppsは編集フォームでSubmitFormすると自動的にデータが再取得されます。SharePointであれば送信直後には編集結果が反映されています。
- なのでExcelデータソースは次項の読み取り専用で使用するのがおすすめです。
- すぐに結果を反映させたい場合は後述の対処方法を。
- 日付関数はSharePointのタイムゾーンが反映されます。Teamsから作ったサイトは注意です。
#2.オンプレデータをキーにしたデータ登録アプリ
これは実際業務で使えました。
###アプリの例
生産現場でバーコードから製品情報を読み取って、その情報をキーに何か処理を行うアプリです。
バーコードにはオーダーIDなど部分的な情報しかありませんが、事前にPowerQueryを使ってオンプレミスにあるデータをExcelに読み込んでおくと、IDから詳細データを引き出せます。
今回は元データがExcelですがPowerQueryで取得できればDBMSでもいけると思います。
PowerApps
Excel
###アプリの仕様
- PowerQueryでオンプレのExcelからSharePoint上のExcelにデータを引っ張り、それをデータソースにデータテーブルに表示します。もちろんデスクトップのExcelで。
- 生産現場でバーコードからオーダーIDを読み取って検索、生産情報をキーに納期調整、不具合登録などの処理を実行します。
- 今回の例ではボタンを押すとオーダーIDや生産情報が入力フォームに反映され、そこに不具合情報を入力して保存します。
- 登録データの保存先はSharePointリストです。
登録をトリガーに自動でTeams通知とかしてやるといいと思います。
後でオンプレデータと紐付けて分析なんかもできますね。
###使い所
- 読み取り専用でいいからfor365プランでもオンプレシステムのデータを使いたい。マスター関係のデータなど。
- 非IT企業で、皆が入力するExcelはファイルサーバーに置かないと混乱する。SharePointリストへ入力させるのも難しい。
- 管理工数がかかるのでper Appプランは禁止な会社。
###その他
- SharePointと比べてやや遅いので、リアルタイム検索はやめて検索ボタンにしたほうが良かったです。
- インデックスはPowerQuery側でつけておき、それを一意のキーにします。
- Excelの接続データの更新が課題となります。
Excelの更新は、リアルタイム性が必要ないデータならPowerShellで定期的にExcelを開いて自動更新するやり方があります。
→ExcelとPowerShellでPowerBIのオンプレミスデータゲートウェイ
後ほど全テーブル更新に対応したコードを載せておきます。
#3.オンプレデータに列を足して記録するアプリ(実用性低)
オンプレデータの書き換えはできませんが、列を足すことなら可能です。
PowerQueryで取得したテーブルに普通に列を足して入力しても当たり前ですが行がズレていきます。
そこで、Excelの別シートにオンプレデータと一対一となるテーブルを作成し、そこにPowerAppsから値を登録、PowerQueryのテーブルからLookUpして参照することでズレなく列を追加します。
###アプリの例
PowerApps
Excelデータソース
①オンプレデータのテーブル(PowerQueryで取得)
生産ステータス列は別のExcelテーブルへLookUpする列です。
②追加列のテーブル(生産ステータスを管理/PowerAppsから更新)
上記のテーブルには直接列が足せないので、追加列の値はこちらのテーブルで管理します。
キーとなるオーダーIDと生産ステータスの列があります。
###問題点
こちらは反映までかなり時間がかかるようで、保存直後にデータソースをRefreshしてもlookup列は表示されませんでした。
実用性は低いですね。開きっぱなしのExcelからは3秒くらいで反映されていました。
対応策として、こういうものは最新何件のデータしか使わないため委任問題を許容して②のテーブルはコレクションに保持、①のテーブルからAddColumns内でLookUp(レコードがあれば表示)でもいいかもしれません。
###その他
更新日時はISO8601形式で記録されているので以下の数式でJSTに変換します。
=DATEVALUE(LEFT([@更新日時],10)) + TIMEVALUE(MID([@更新日時],12,8)) + TIME(9,0,0)
##4.入力はExcelのマトリックス表、表示はPowerApps
予定表への入力など、ピボット形式で入力する場合はExcelの方が得意で柔軟です。入力はExcelの良さを活かします。
PowerAppsは2次元表に直さないと読み込めないので、PowerQueryで前処理してPowerAppsに読み込みます。
以下のサンプルアプリでは一週間単位のシフト表で、行に作業、列に日付、値に作業担当者がある表です。
PowerAppsで表示する意味・・ カッコいいからですｗ
PowerAppsでピボット形式の入力アプリを作る場合はこちら
###アプリ
Excelデータソース
①入力用のピボット形式のExcel
管理者はこちらのExcelにデータ入力します。
②ピボット解除テーブル
①をPowerQueryでピボット解除しこちらをPowerAppsのデータソースにします。
PowerApps
②の表をPowerAppsでデータソースとして取り込みます。
以下の例ではGroupeByを使って再度ピボット化しています。Sortは委任できるのでそちらで最新500件取得しておけば使用上問題は無さそうです。
###使い所
- PowerAppsでピボット形式の入力表を作るのが大変
- 表示だけPowerAppsでカッコよくしたい、Excel表をそのまま見せたくない
現場のタブレットで表示用として使い、タッチでアプリから作業完了報告や予定変更のリクエストを送れるようにするなど活用できそうです。
#あとがき
これまでfor365プランではデータは全てSharePointListに置く必要があり、基幹系のシステムと関係ないアプリしか作ることができませんでした。
有償のプランを利用するとデータフロー機能によってオンプレミスのデータを利用できましたが、業務部門にはハードルが高かったりします。
メンテは必要ですが、Excelを介して社内データを利用できるようになると、作成できるアプリが大幅に広がるのではないでしょうか。
企業によっては申請しても管理の問題から有償プランが利用できない場合もありますしね・・・
※PowerPlatformは設定項目多すぎて管理者大変だと思います。
#番外編 Excelデータソースへの書き込みアプリ
Excelに書き込むようなアプリでは、フォームでSubmitを行ってもすぐに結果が反映されません。
ギャラリーコントロールの画面に戻っても反映されていないので、皆が使うアプリだと混乱の元になります。
対処方法を考えてみました。
###アプリの例
Excelで作った作業計画があり、そこに現場で完了実績を入力するようなアプリです。
「作業完了」列：完了 or 空白
「開始日時」、「完了日時」：時間を入力
後日画像追加。
###対処方法
後日詳細追加。
フォームでSubmitした後、ギャラリーコントロールで編集結果が反映されるようにする対処です。
編集フォームでは、「作業完了」、「開始日時」、「完了日時」だけを入力するようにします。
SubmitFormと同時にコレクションにレコードの内容を追加します。
ギャラリーコントロールのフィールドのTextで、コレクションに対してLookUp、
レコードがあればその値を表示、なければThisItemを表示します。
これで見かけ上すぐに反映されたように見せられます。
後は、タイマーコントロールで30秒後とかにRefreshしてあげます。そのときコレクションはクリアします。