からめもぶろぐ。

俺たちは雰囲気で OAuth をやっている

SharePoint REST API を使うときの 5,000 件問題について振り返ってみる

みんな大好き SharePoint の 5,000 件問題ですが、いつも結局のところを忘れてしまうので、備忘録として整理しておきます。
先にこちらの記事を見ておくといろいろ参考になります。

idea.tostring.jp

準備

SharePoint に連番と色とサイズの列を持つ簡単なリストを作成します。色は「白」「黒」の 2 種類、サイズは「小」「中」「大」の 3 種類の値が入ります。

f:id:karamem0:20201125105327p:plain

アイテム数としては 12,500 件を登録しました。よって、色でフィルターした場合は 6,250 件、サイズでフィルターした場合は 4,166 件が取得できることになります。

f:id:karamem0:20201125105501p:plain

色とサイズの列にインデックスがついています。

f:id:karamem0:20201125105813p:plain

検証

Postman を使って API を呼び出します。

まずは何もフィルターしない場合です。フィルターがない場合は ID 順に最初の 100 件が取得されます。件数は $top の指定で変更することが可能ですが、5,000 を超える数を指定するとエラーになります。

f:id:karamem0:20201125110004p:plain

フィルターにインデックスされていない列を指定した場合はエラーになります。これは内部的にフィルター操作によって 5,000 件を超えるデータを操作しようとするためです。このため、フィルターをする列にはインデックスを指定することが必須になります。

f:id:karamem0:20201125105857p:plain

インデックス化されているサイズをフィルターとして指定してみます。検索が成功していることがわかります。

f:id:karamem0:20201125112242p:plain

しかし色でフィルターするとエラーになってしまいます。これはフィルターの結果の件数が 5,000 件を超えているためです。つまり、何でもインデックスすればよいというわけではなく、データが分散されることが予想される列をインデックスの対象として選択するべきであることを示しています。

f:id:karamem0:20201125112429p:plain

複数条件を指定したときは、結果は 5,000 件以下になるはずですが、これはエラーになります。これはまず色でフィルターされ、その結果に対してサイズのフィルターが処理されるためです。最初のフィルターの結果が 5,000 件を超えるとエラーが発生します。

f:id:karamem0:20201125112844p:plain

条件の順番を変更してみると成功することからも動作が理解できると思います。複数条件を指定する際は、できるだけ絞り込みが可能な列を先に指定することでパフォーマンスの改善にも繋がります。

f:id:karamem0:20201125113513p:plain

まとめ

以上の検証結果をまとめてみます。

  • 5,000 件を超える列のフィルターにはインデックスが必須になる
  • インデックスしても結果が 5,000 件を超える場合はフィルターは無効になる
  • 複数条件でフィルターする場合は順序を考慮する

「なぜエラーになるのか」の仕組みさえ覚えておけば 5,000 件問題は決して難しくありません。動作を理解しうまく設計してあげることが肝心です。

Power Apps で親子関係のある入力フォームを作ってみる

親子関係のあるような複雑なデータを編集する場合は、できればモデル駆動型アプリを使っていただくのが正解だとは思うのですが、いろいろな都合により(主にライセンスの関係で)Microsoft Dataverse を使うことができないという場合もあるでしょう。そこで SharePoint リストを使ったキャンバス アプリで親子関係を持つデータをひとつの画面で編集するアプリを作ってみたいと思います。今回は見積書を作成するサンプル アプリを作成します。

リストの作成

SharePoint の任意のサイトに以下の 2 つのリストを作成します。

見積書リスト

表示名 内部名 種類
見積番号 Title 1行テキスト
見積日 QuotationDate 日付と時刻
会社名 CompanyName 1 行テキスト
納期 Delivery 1 行テキスト
有効期限 Expiry 1 行テキスト
支払条件 PaymentTerm 1 行テキスト

見積書明細リスト

表示名 内部名 種類
見積書 ID QuotationId 数値
連番 Index 数値
摘要 Title 1 行テキスト
数量 Quantity 数値
単価 UnitPrice 数値
金額 Amount 集計値

注意点として、親子関係を定義するのに、参照列を使うのではなくて、ID の値を格納する列を作るようにしています。参照列は Power Apps では委任の関係から逆に扱いづらくなるため、このような方式を採っています。

アプリの作成

一覧画面

f:id:karamem0:20201118092912p:plain

一覧画面はあまり本題ではないので詳細は省略します。見積書リストのアイテムの一覧をギャラリーで表示します。

作成画面

f:id:karamem0:20201118092949p:plain

上部にフォームで見積書リストのアイテム、下部にギャラリーで見積書明細のリストのアイテムの一覧を表示します。

スクリーンの OnVisible プロパティでは QuotationDetails と QuotationDetailsIndexed という 2 つのコレクションを初期化します。QuotationDetails はギャラリーにバインドされるデータで、QuotationDetailsIndexed は保存時に連番を付けるために使用します。

NewForm(CreateForm);
ClearCollect(QuotationDetails, Filter(見積書明細, '見積書 ID' = 0));
ClearCollect(QuotationDetailsIndexed, Filter(見積書明細, '見積書 ID' = 0))

ギャラリーの Items プロパティにはアイテムが連番で並ぶように以下の数式を設定します。

SortByColumns(Filter(QuotationDetails, 連番 > 0), "Index", Ascending)

ギャラリーのテキスト入力の OnChange プロパティでは入力したテキストがコレクションに反映されるように Patch を呼び出すようにします。

Patch(QuotationDetails, ThisItem, { 摘要: Self.Text })

保存ボタンの OnSelect プロパティでは、まず親の見積書リストのアイテムを SubmitForm で登録し、次に子の見積書明細リストのアイテムを ForAll と Patch を使って登録していきます。ギャラリーは項目が追加されたり削除されたりして連番が続きになっていない可能性があるため、いったん QuotationDetails のコレクションを QuotationDetailsIndexed にコピーする形で連番を振り直しています。

If(
    SubmitForm(CreateForm),
    Clear(QuotationDetailsIndexed);
    ForAll(
        QuotationDetails,
        Collect(
            QuotationDetailsIndexed,
            {
                ID: ThisRecord.ID,
                '見積書 ID': CreateForm.LastSubmit.ID,
                連番: CountRows(QuotationDetailsIndexed) + 1,
                摘要: ThisRecord.摘要,
                数量: ThisRecord.数量,
                単価: ThisRecord.単価
            }
        )
    );
    ForAll(
        QuotationDetailsIndexed,
        Patch(
            見積書明細,
            ThisRecord
        );
        Navigate(
            MainScreen,
            ScreenTransition.None
        )
    )
)

更新画面

f:id:karamem0:20201118093121p:plain

基本的には作成画面とそれほど変わりません。

スクリーンの OnVisible プロパティではコレクションを初期化しますが、見積書 ID でアイテムを取ってくるようにします。

EditForm(UpdateForm);
ClearCollect(QuotationDetails, Filter(見積書明細, '見積書 ID' = MainGallery.Selected.ID));
ClearCollect(QuotationDetailsIndexed, Filter(見積書明細, '見積書 ID' = 0))

ギャラリーから項目を削除するときに、新規に追加したものでよければそのままコレクションから削除してしまえばいいのですが、既存のアイテムを削除するときはリストから削除しなければならないので、そのようなアイテムはいったん連番を -1 としておき、保存時にまとめて削除するようにします。削除ボタンの OnSelect プロパティは以下のようになります。

If(ThisItem.ID = 0, Remove(QuotationDetails, ThisItem), Patch(QuotationDetails, ThisItem, { 連番: -1 }))

保存ボタンの OnSelect プロパティでは、作成画面とほぼ同じですが、削除とマークされたアイテムを削除するロジックが追加されます。

If(
    SubmitForm(UpdateForm),
    Clear(QuotationDetailsIndexed);
    ForAll(
        Filter(
            QuotationDetails,
            連番 < 0
        ),
        Remove(
            見積書明細,
            ThisRecord
        )
    );
    ForAll(
        Filter(
            QuotationDetails,
            連番 > 0
        ),
        Collect(
            QuotationDetailsIndexed,
            {
                ID: ThisRecord.ID,
                '見積書 ID': UpdateForm.LastSubmit.ID,
                連番: CountRows(QuotationDetailsIndexed) + 1,
                摘要: ThisRecord.摘要,
                数量: ThisRecord.数量,
                単価: ThisRecord.単価
            }
        )
    );
    ForAll(
        QuotationDetailsIndexed,
        Patch(
            見積書明細,
            ThisRecord
        );
        Navigate(
            MainScreen,
            ScreenTransition.None
        )
    )
)

削除ボタンの OnSelect プロパティでは親子関係のアイテムをすべて削除するようにします。

RemoveIf(見積書明細, '見積書 ID' = MainGallery.Selected.ID);
Remove(見積書, MainGallery.Selected); Navigate(MainScreen, ScreenTransition.None)

実行

実行してみます。作成画面で項目を入力して保存をクリックします。

f:id:karamem0:20201118095810p:plain

見積書リストにアイテムが追加されます。

f:id:karamem0:20201118095834p:plain

同時に見積書明細リストにもアイテムが複数追加されているのがわかります。

f:id:karamem0:20201118095924p:plain

明細を編集してみます。マウスを削除して LAN ケーブルを追加します。

f:id:karamem0:20201118100125p:plain

見積書明細リストに編集が反映されています。ちゃんと連番もきれいに振られていますね。

f:id:karamem0:20201118100213p:plain

まとめ

SharePoint の親子関係の表現には参照列を使いたくなりますが、参照列は ID でのフィルターが委任制約に引っかかるため、使わないのが正解です。件数が増えてきたときのために適切にインデックスを貼っておくとよいと思います。

Global Microsoft 365 Developer Bootcamp 2020 Tokyo に登壇しました

2020/11/09 および 2020/11/10 に開催された Global Microsoft 365 Developer Bootcamp 2020 Tokyo で Microsoft Teams 開発のハンズオン担当として登壇しました。

connpass.com

今回ははじめてのオンラインでのハンズオン イベントということで不慣れな点も多々ありましたが最後までやりきれてよかったです。

スライドは Speaker Deck にアップロードしています。

今回のハンズオンについては Microsoft Learn のモジュールを使っていますのでそちらを参照いただくのがいいかと思います。

docs.microsoft.com

docs.microsoft.com

ハンズオン中に「Teams タブのデバックはどのようにやればいいのか」という質問をいただきました。こちらでも試してみたのですが、Visual Studio Code の Debugger for Chrome 拡張を使ってブレーク ポイントに止めることはできたのですが、ステップ インがうまく動かなかったので、ちょっと難しいかもしれません。Visual Studio Code に詳しい人だったらなんとかなるのかなあと思ったりしています。

Microsoft Teams の Power Apps からメッセージを投稿してみる

前回の記事では Param("hostClientType") を使って Microsoft Teams の実行されている環境を取得しました。

blog.karamem0.jp

Microsoft Teams から Power Apps には他にも情報が取得できるので、サンプルとして、現在表示されているチームとチャネルの情報を取得して、メッセージを投稿してみたいと思います。

画面イメージは以下のような感じです。Param("groupId") と Param("channelId") でチーム ID とチャネル ID をそれぞれ取得できます。メッセージを入力して [Post] をクリックすると Power Automate の呼び出しを行います。

f:id:karamem0:20201028142330p:plain

Power Automate ではシンプルに引数を受け取って Teams のメッセージを投稿するアクションを実行します。

f:id:karamem0:20201028142836p:plain

試しに実行してみます。適当にメッセージを入れて [Post] をクリックします。

f:id:karamem0:20201028142921p:plain

メッセージが投稿されているのがわかります。

f:id:karamem0:20201028143007p:plain

Microsoft Teams と Power Apps の相互作用ができるようになると Power Apps の作成はもっと可能性が見えてくると思います。ぜひいろいろ試してみていただければと思います。

Microsoft Teams の Power Apps でアプリがどの環境で実行されているのかを判断する

Project Oakdale によって Microsoft Teams で Power Apps を作成することができるようになりましたが、例えば特定のユーザーとのチャットにリンクするなど、Power Apps から Microsoft Teams の機能を呼び出したいことがあると思います。これは Microsoft Teams のディープ リンクを使用することで解決できます。

docs.microsoft.com

ただし以下のようにして呼び出すと必ずブラウザーが開いてしまいます。

Launch("https://teams.microsoft.com/_#/l/chat/0/0?users=someone@example.com")

Web 版の Teams はまだ問題ないのですが、デスクトップ版の Teams の場合はそのまま遷移してほしいので、以下のように URI スキームで呼び出したいです。

Launch("msteams://teams.microsoft.com/l/chat/0/0?users=someone@example.com")

ということで Web 版の Teams と デスクトップ版の Teams のどちらで実行されているかを判断したいのですが、これは Microsoft Teams から Power Apps を呼び出すときに渡される URL パラメーターを取得することで判断できます。具体的には Param("hostClientType") を呼び出すことで実行されている環境を取得することができます。

  • Web 版の場合

f:id:karamem0:20201028103226p:plain

  • デスクトップ版の場合

f:id:karamem0:20201028103420p:plain

他にも例えば Param("theme") とすれば Microsoft Teams で適用しているテーマの情報が取れたりもするので、テーマに合わせてデザインを変えるようなこともできます。