お仕事用オンラインストレージとして使えるので便利なのだ。ただ、Power Query で扱えるデータソースのひとつとしてよく理解しておくべきだ。ついさっきできたのにできなくなった。夕方試したらやっぱりできた。と、いうようなことが起きるのだけど、データ取得が失敗してもその原因がわからないというのはよくないよな。
少なくとも言えるのは、
多くのファイルを扱うことができても、時には失敗することもある
データソースと解釈しておくとよいと思うのです。
SharePoint Online の 5,000件問題 / 20,000件問題
有名なこの問題については、上記の超おススメ コンテンツをよーく読んでね。ただなんとなくで 5,000件問題ってどうなの？って言いだす人多いよね。なお Power Query ではこの問題を回避できている。
SharePoint.Files 関数
サイトの配置したドキュメント ライブラリはひとつと限らないのだけど、それらまとめて取得できるのは合理的ではある。そして、
5,000/20,000件というしきい値の影響を受けない
なぜなら、5,000件とかまとめて情報を取得しようとしないから。
まず、サイトに存在するドキュメント ライブラリすべてのリストアイテムをRenderListDataAsStream を使って取得する。このとき URIパラメタを使ってページングしている。(1,000件づつだったかな)
POST /_api/web/lists/getbyid('<guid>')/RenderListDataAsStream
次に、取得したリストアイテムごと、ファイルのダウンロードリンクなどメタデータ取得
POST /Shared%20Documents/File1_csv/_api/contextinfo
最後に、ファイルごとにバイナリ取得
GET /_api/web/getfilebyserverrelativeurl('/Shared%20Documents/File1.csv')/$value
Power Query エディタでの作業中とデータの読み込み時で違いはあれど、この順番でリクエストが発生する。
SharePoint.Contents 関数
5,000件問題の影響を受ける
5,000件を超えてリストアイテムを取得しようとするから。
フォルダ タイプのアイテム含め 5,001 以上のアイテムが存在しているとき、閾値を超え Status code 500 で取得が失敗する。
GET /_api/Web/GetFolderByServerRelativePath(decodedurl='<url>')/Folders
GET /_api/Web/GetFolderByServerRelativePath(decodedurl='<url>')/Files
SharePoint.Files 関数とちがって速く処理が進む可能性はあるけど、できないことや個別の対応よる影響のほうが大きいのでは？と思うのです。
SharePoint Online による調整
データソースの勉強をおろそかにしている人にはとってもやっかい。なぜなら、データの取得が失敗した契機や理由がよくわからないから。勉強している人にとっては取り立てて問題なく対応できるはずよね。
SharePoint.Files 関数 / SharePoint.Contents 関数の動作をよく理解をしておくべきだ。
頻繁にリクエストを繰り返すと SharePoint Online はその時点での状況に応じ Status code 429 (Too Many Requests) を返すことがある。SharePoint Online による "スロットリング"や"調整"といわれるものだ。サービス稼働に影響を及ぼす可能性があるというとき、Retry-After をレスポンスヘッダに追加して、もうちょっと間をあけてリクエストしてほしいんだよね。と、お知らせしてくれるのだ。
例えば、ドキュメント ライブラリに配置された 500 のファイルを結合するクエリが評価されるとき、ファイルメタデータの取得とファイルのダウンロードで少なくとも 1000 のリクエストが発生する。そのうちStatus code 429 のレスポンスがまったくないときもあるし、何度も発生することもある。Power Query エディタでの作業時に発生することもあるし、データをロードするときになって初めて発生することもある。よく観察していると、再試行して再開されるときはあるんだけど、パタッと止まってしまうこともあるのだ。
該当する事象が発生したら、ちょっと休憩入れて再度作業開始にするとよいんじゃねと。Retry-After が 300 とかもあるし。
扱うファイル数によってではないからやっかいなんだけど、もしかして "調整？" ってとき、ドキュメントライブラリ見に行ったらこんなのだったり、
Excel だとこうなるし、
Power BI Desktop ならこんな感じ。
Excel Online でファイルを参照したら Throttle.htm
対策
待てば解決することもあるけれども、そもそも調整が発生するものとしてあらかじめ検討しておくとよいよね。
大容量になっているドキュメント ライブラリをデータソースにしない
だって、すべてのリストアイテム情報を収集しなければならないのだから。
扱うファイル数を分割して取得する
一気に大量のファイルを扱うことをしなければ、調整の影響を受ける可能性が下がるのだ。手段としては、Power BI データフローを利用するとよい。で、更新時間差を設けておけばよいのだ。だって、即時性を要求できるデータソースではないわけだし。
SharePoint.Files 関数 / SharePoint.Contents 関数を使用しない
ファイルの結合やフィルタによるファイルの特定をしないのであれば、SharePoint.Files 関数 / SharePoint.Contents 関数 を使わないほうがよい。速い遅いの問題ではなく、必要がないのなら使わないほうがよいのである。ファイルが特定できているのであれば、Web.Contents 関数を使ったほうがよいことが多い。
ローカルもしくは別のストレージに移動してから
とはいえ大量のファイルを処理しなければならないのなら、得策となりえるよね。移動する方法は何でもいいわけだし。
そのほか
そのほかの対策がないわけではないけど、"調整" の影響を受けないという解決はなし。
思ったこと🙄
確かにお手軽で便利なストレージだよね。権限管理もできているのだし。でもさ、用途に適しているのか、起こりえる事象についてどうするかなど、あらかじめ理解し考えておくべきだよね。
Power Query の勉強が大事としても、利用するデータソースのことをよく知らなければ価値がないに等しいね。