Office 365 SharePoint Online に Excel ブックを保存して他のユーザーと共有する、という使い方でもメリットがあるが、できれば保存したブックの中を簡易的に確認したい、編集する必要はなく参照だけで良い、という使い方もあるだろう。
このような場合、Office 365 SharePoint Online では以下の使い方が用意されている。
・ Excel Online で Excel ブックを開く
・ Excel Web Access Web パーツ(Excel Services) でサイトのページに貼り付ける
いずれの場合も「現時点での最新データを見たい」という目的であることは明確だ。
ブックで扱うデータがワークシートへの手入力の場合、Excel ブックの SharePoint に保存した状態を参照できる。ある意味、それが最新であり問題はない。問題になるのは「データ接続」している場合である。
Excel はさまざまなデータ ソースに外部データ接続機能を使って接続できるが、今回は SharePoint リストに接続したブックを Excel Online や Excel Web Access Web パーツで扱う場合を紹介したいと思う。
注意点としては、一部 TechNet や MSDN に明確に書かれていない方法を紹介することになる。米国マイクロソフトの英語版 Office ブログやフォーラムで Microsoft の担当者からの情報などを参考にしているが、私自身はそれを元に TechNet/MSDN といった公式技術文書で同様の記述をまだ見つけ出すことができていない。その点は留意されたい。
1つではない SharePoint リストとのデータ接続方法
SharePoint リストのデータを Excel にインポートする方法の代表格は SharePoint リストの「リスト」タブにある「Excel にエクスポート」だろう。
通常業務でリストのアイテムを Excel に取り込んで PC で集計・分析・レポートを作るのであれば、このエクスポートでほとんど問題がない。
ところが、この接続方法を使ったブックを SharePoint に保存し、それを Excel Online で開こうとして以下のようなメッセージを見たことがある人は多いだろう。
[詳細の表示] ボタンをクリックすると以下のダイアログが表示される。
このメッセージを見れば、多くの人は「SharePoint リストを使った Excel ブックは Excel Online で使えないのか」と思っても仕方ない。この接続方法を含んだブックのデータ接続は Excel Online で使えない、サポートされていないことは事実である。
実は SharePoint リストのデータを Excel にエクスポートする方法は数種類ある。区別を明確にするために、「接続のプロパティ」の「接続の種類」で使われている名称を使って分類したものが以下だ。
a) 接続の種類 : SharePoint リスト
SharePoint の [リスト] タブの [Excel にエクスポート] を使ったデータ接続。
b) 接続の種類 : Office データ接続
Excel のデータ タブの [その他のデータソース] の [OData データ フィード] を使ったデータ接続。
データ モデルは強制的に作成される。
c) 接続の種類 : OLE DB クエリ
PowerQuery の [その他のデータソース] の [OData フィードから] を使ったデータ接続。
ただし、データモデルの作成はしていないタイプ。
d) 接続の種類 : モデル OLE DB クエリ
PowerQuery の [その他のデータソース] の [OData フィードから] を使ったデータ接続。
データ接続作成の際、データモデルの作成も指定。
Excel と Office 365 SharePoint Online との接続という意味では上記の4つがある。
「SharePoint リスト」という接続の種類を含んだブックは Excel Online では利用できないが、MSDN や TechNet、Office Online などを調べると、OData フィードによる接続は Excel Online で利用可能、という記述を見つけることができる。
ところが、OData フィードによる接続も上記のように「Office データ接続」、「OLE DB クエリ」、「モデル OLE DB クエリ」の3種類存在し、かつ、そのまま利用しても、いずれの OData フィードのデータ接続でデータ接続の「更新」(refresh)ができないのが現状だ。
結論からいえば、ある「設定」をすることで、Excel Online や Excel Web Access Web パーツでもブックのデータ接続を更新して最新のデータを見ることが可能だ。その手順およびそれに対応した接続方法を紹介する。
Office データ接続で SharePoint リストをエクスポートする
Excel Online や Excel Web Access Web パーツ（Excel Services) での利用を考えているならば、SharePoint リストからのデータ取得は接続の種類「Office データ接続」を使うべきと言える。この接続方法であれば、ある設定（アプリ権限の付与）をすることで Excel Online 上でデータ接続更新が可能になる。
では、Office データ接続による OData データ フィードの構成をしてみよう。
1) エクスポートしたい SharePoint リストの URL を控える。
たとえば、以下のようにブラウザで SharePoint リストを表示した時、控えておきたい URL は "_layouts/15/start.aspx#/Lists/Seminar/" の前にある "https://jpwa.sharepoint.com/sites/r2co/" を控えておく。
2) Excel のデータ タブ - その他のデータ ソース の OData データ フィードで接続を構成する。
データ タブの [OData データ フィード」を開く。
データ接続ウィザードのダイアログが開く。
控えておいた URL の後に "_vti_bin/listdata.svc " と入力して [次へ] をクリックする。
このサンプルの場合は、"https://jpwa.sharepoint.com/sites/r2co/_vti_bin/listdata.svc "と入力する。
[追記] ここで Office 365 へのサインイン画面が表示される場合がある。一度、接続に対してアカウントとパスワードを登録することで、接続情報を削除しない、パスワードを変えないかぎり、接続の際のサインイン画面をスキップすることが可能になる。
[追記終わり]
テーブルの選択をする。ここでのテーブルは SharePoint の「リスト」を指している。
取り込みたいリストにチェックを入れて [次へ] をクリックする。
ファイル名や説明を変更できる最終ダイアログが表示される。Excel Services などの認証は変更せずにこのまま [完了] ボタンをクリックする。
データのインポート ダイアログが開く。表示方法の選択肢があるが、テーブルとしてインポートするのであれば、そのまま [OK] をクリックする。ここで [このデータをデータ モデルに追加する] オプションがチェック済みになっていてグレイアウトされている。強制的にデータ モデルを作成していることがわかる。
SharePoint Online のリストが Excel のテーブルとしてインポートされた。
この素のままのテーブル データを見るより、ピボット テーブルを使ってレポート形式にした方が実用的だ。このテーブルを利用してピボット テーブルを作成する。もちろん、この前の処理でのデータ インポートで「ピボット テーブル レポート」を選択して、素のテーブルを取り込まないことも可能だ。
OData データ フィード接続を含んだブックを SharePoint に保存する
このブックを SharePoint Online のドキュメントライブラリに保存する。
一旦ローカルに保存したものをアップロードしてもよいし、直接 SharePoint Online のドキュメントライブラリーを指定してもよい。この時、上で作ったピボットテーブルだけを Excel Online で表示・参照させたい場合は、ブラウザーオプションでピボットテーブルだけを指定しておく。
ではこのブックを SharePoint Online から Excel Online を使って開いてみる。
SharePoint リスト接続と違うのは、ブックを開いたとき、SharePoint リスト接続のような警告メッセージは表示されずに、指定したピボットテーブルが表示される。
なお、この状態でピボットテーブルのフィールドリストを操作してデータの分析が可能であり、これだけでも使い道は多いにあるだろう。
しかし、まだ、これだけでは SharePoint とのデータ接続のデータ更新は成功しない。Excel Online でデータ更新しエラーになる状況をアニメーションGIFでとったものが以下である。なお、データは上記とは別のブックで、データ接続更新設定をしていない別のテナント(Office 365) で実施したものになる。
SharePoint リスト接続とは違う以下のエラーメッセージが表示され、データ更新に失敗している。
----
外部データの更新が失敗しました。
ブック内のデータ モデルを処理しているときにエラーが発生しました。もう一度やりなおしてください。
このブックに指定されている１つ以上のデータ接続を更新できません。
以下の接続を更新できませんでした：
----
接続名はデータ接続ファイルを保存した時に指定したものが表示される。
Excel Online でデータ接続更新を可能にする設定（アプリ権限付与）を行う冒頭に述べたように私自身が Office Online/TechNet/MSDN 内で公式な技術文書として探し出せていないのが、この設定である。ただし、この情報ソースは米国マイクロソフトの英語による社員ブログやフォーラムでマイクロソフト社員より提供されているものである。
(参照)
Office Blogs - Project Online and Excel Web App: Cloud data improves reportingProject Online の OData フィードを Excel Web App で利用しデータ更新を可能にする設定について書かれたブログ（2013年3月29日）
http://blogs.office.com/2013/03/29/project-online-and-excel-web-app-cloud-data-improves-reporting/[SOLVED] Excel service refresh issueSharePoint リストを OData データ フィードで Excel 2013 で取り込み、Excel Online でデータ更新できない件について、MSFT Support から、この設定を提示しているフォーラムの投稿（2014年12月20日）
http://community.office365.com/en-us/f/172/t/284523.aspxもし、Office データ接続 OData データ フィードを使った Excel Online でのデータ更新が成功している場合、すでにこの設定が他の管理者権限を持っているユーザーによって行われていると考えられる。この設定登録はサイト コレクションレベルでの登録だが、設定そのものはOffice 365 の「テナント」レベル（契約している Office 365 全体）にも登録される。そのため、例えば、テスト用のサイト コレクションでアプリ権限付与設定を行い、テスト終了後にサイト コレクションに登録された権限付与設定を削除しても、テナントレベルの登録を削除しない限り、テナント全てのサイト コレクションで有効状態になっている。そのため、該当するサイト コレクションで登録していなくてもデータ接続更新が可能になっている場合がある。
[追記] 上記の表現は正確でなかった。リンク先の記事の XML で「テナント」範囲での指定をしているからだ。スコープの指定がサイト コレクションであれば、登録したサイト コレクションのみ有効になる。サイト コレクションのみ有効になる XML は追記した。
[追記終わり]
登録するアプリのプリンシパル ID は "
00000009-0000-0000-c000-000000000000" である。現在このプリンシパル IDのアプリ名は "Power BI" もしくは "Microsoft Power BI Reporting and Analytics" となっているはずだ。上述の Office Blogs では "Microsoft Azure Analysis Services" だった。(2013年3月末）
今後もアプリ名(Title)が変わる可能性があることに注意されたい。1) テナントでの権限付与状態の確認上記のプリンシパル ID のアプリへの権限がテナントに登録されていないことを一応確認する。
この確認は全体管理者権限を持っていないとできないのでユーザーの権限に注意すること。
管理ポータルを開く。
もしくは、以下から管理ポータルを開く
SharePoint 管理センターに移動する。
SharePoint アプリの管理へ移動する。左サイドバーの [アプリ] をクリックする。
アプリの権限をクリックする。
アプリの表示名に「Power BI」もしくは「Microsoft Power BI Reporting and Analytics」が無い、もしくは、「00000009-0000-0000-c000-000000000000」を含んだアプリIDが無いことを確認する。
なお、登録されているアプリはそれぞれのテナント環境で違うので上記図と同じにならない場合もあることを留意されたい。
2) サイト コレクションレベルでの確認とアプリの登録登録は管理センターからはできず、サイト コレクションから行う。
どのサイト コレクションから登録しても結果としてテナント レベルの登録になるが、一応、Excel Online で使いたいブックを含んだサイトから登録する。
[追記] テナントレベルの登録になるのは、後述する XML によるアプリの権限要求でテナントレベルを指定したためだった。
参考：
http://msdn.microsoft.com/ja-jp/library/office/fp142383(v=office.15).aspx追記したアプリの権限要求 XML で登録作業をしたサイト コレクションのみ有効にすることが可能。
[追記終わり]
SharePoint サイトに移動して右上の「歯車アイコン」から「サイトの設定」を選択する。
[サイト コレクションの管理] の [サイト コレクションのアプリの権限] をクリックする。
サブサイトのサイトの設定画面を開いている場合は [トップ レベルのサイト設定に移動] をクリックして、[サイト コレクションのアプリの権限] をクリックすること。
Microsoft Power BI Reporting and Analytics が無いことを確認する。
次はアプリの登録とアクセス権の設定をするのだが、これまで行ってきたメニューからの操作が現状ではできない。登録画面の URL を直接入力することになる。
現在、[サイトの設定 > サイト コレクションのアプリの権限] の画面を開いている。その URL は以下のようなものだ。/_layouts/ より前の部分はそれぞれの環境で違うが、/layouts/ 以降は同じだ。
https://hogehoge.sharepoint.com/sites/hoge/_layouts/15/start.aspx#/_layouts/15/appprincipals.aspx
この appprincipals.aspx を appinv.aspx に変更して Enter キーを押す。
すると以下の画面が表示される。
アプリID: に 00000009-0000-0000-c000-000000000000 を入力し、[参照] ボタンをクリックする。
タイトルに [Power BI]（違う場合もある)、アプリ ドメインに [analysis.windows.net] が表示される。タイトルはテナントの SharePoint Online のリリースによって違う場合があることを確認している。
アプリの権限要求 XML に以下の XML をコピーして貼り付け、[作成] ボタンをクリックする。
<AppPermissionRequests><AppPermissionRequest Scope = "http://sharepoint/projectserver/reporting" Right="Read"></AppPermissionRequest><AppPermissionRequest Scope = "http://sharepoint/content/tenant" Right="FullControl"></AppPermissionRequest></AppPermissionRequests>
[追記] http://msdn.microsoft.com/ja-jp/library/office/fp142383(v=office.15).aspx を参考にして、必要ない projectserver の AppPermissionRequest Scope を除き、サイト コレクションでの権限にしたものが以下になる。
<AppPermissionRequests>
<AppPermissionRequest Scope = "http://sharepoint/content/sitecollection" Right="FullControl"></AppPermissionRequest>
</AppPermissionRequests>
この XML で Excel Online のデータ接続更新が可能を確認している。[追記終わり]
タイトル名のアプリを信頼しますか？という確認画面がでるので、[信頼する] ボタンをクリックする。
サイトの設定画面にもどるので再度[サイト コレクションのアプリの権限]を開いて登録されていることを確認する。繰り返しになるが、アプリのタイトルはテナントのリリースによって違うことが確認されている。重要なのはアプリIDであることに留意されたい。
この状態で、(追記： アプリ権限要求の XML で Scope をテナント指定していれば）再度テナントレベルを確認すると以下のようにアプリが登録されていることがわかる。
なお、上記の操作でアプリのタイトルが「Power BI」になっているが、この操作をしたテナントでは Power BI for Office 365 のサブスクリプションは購入していない。
もし Power BI for Office 365 をすでに購入していた場合は、テナントレベルでのアプリの権限で「Power BI」というアプリの表示名が表示されるが、そのアプリ ID は 00000009-0000-0000-c000-000000000000 ではない。これで判別ができるだろう。
3) Excel Online でデータ接続更新の確認
Office データ接続 OData データ フィードによるデータ接続を使ったブックをアップロードし、レポートのアプリ ID を登録してアクセス権を付与した状態で、はじめて Excel Online 上でのデータ接続更新が可能になる。実際に試してみよう。
以下は、上述でアニメーションGIFで失敗例としてあげた使ったブックと同じものである。
上記手順でアプリの登録と権限付与を行い、データ ソースである SharePoint リストでアイテムを追加登録した状態で Excel Online でデータの更新をした。
Excel Web Access Web パーツをサイトに貼り付け、データ更新を実行したのが以下だ。
いつものお約束 - 注意点
実務で実際にこの機能を運用すると、以下の事にすぐ気づくはずだ。
1) データ更新しても、その状態でブックは保存されていないよって、次に開いたときやブラウザを F5 でリロードすると「元のデータ」に戻る。
これは、通常のローカル PC の Excel のピボットテーブルを考えてもらえれば想像に難くない。データ更新しても、ブックを保存しないで Excel を終了させているようなものだ。
ただ、この設定をすることで、[Excel Online で編集] においてもデータ接続の更新が可能になるので、編集モードにしてデータ接続の更新をすれば「保存」したことになり、データも最新になった状態になる。
結局、参照のみの Excel Online でのデータ更新や、Excel Web Access Web パーツでのデータ更新より、Excel Online の編集モードでデータ更新、そして保存、という運用になってしまう。PC の Excel で更新、アップロード、という手間がなくなった、ということだ。
2) データ接続の自動更新はできないデータ接続のプロパティで自動更新のオプションがあることを知っている人も多いだろう。
これは使えない。設定してもなんの変化もない。
理由は、データ モデルが更新されていないためである。データ モデルはピボットキャッシュのようなものだと考えれば理解できる人もいるだろう。実データはデータ モデルを介してサーバー側からとりこむため、データ モデルを更新しないかぎり、ピボットテーブル レポートのデータは更新されない。そして、データ モデルの接続プロパティの [定期的に更新する] オプションはグレイアウトされて設定不可能になっている。
3) Office データ接続のみ有効で PowerQuery によるデータ接続の更新はできない PowerQuery のデータ モデルを使った接続 (モデル OLE DB クエリ）であっても、上記のアプリ ID とアプリ権限設定後、Excel Online や Excel Web Access Web パーツ内でのデータ更新はできない。
以下のメッセージが表示されエラーになる。
外部データの更新が失敗しました
ブック内のデータ モデルを処理しているときにエラーが発生しました。もう一度やり直してください。
このブックに指定されている 1 つ以上のデータ接続を更新できません。
以下の接続を更新できませんでいた：
Power Query - List01
接続： Power Query - List01
エラー： OnPremise エラー：問題が発生しました。もう一度やり直してください。
テーブル "List01" の処理中にエラーが発生しました。
トランザクションの別の操作が失敗したため、現在の操作は取り消されました。
もう一度やりなおして、、、とあるが、何度やり直してもデータの更新はできない。
Power Query によるモデル OLE DB クエリ / OData フィードは Power BI for Office 365 の Power BI サイトで使用する。
現状、複数あるデータ接続が、使用する機能別に用意されているため難解になっていることは否めないが、ここは過去の資産の蓄積と将来のために追加された新機能として理解するしかないかもしれない。
まとめOffice 365 SharePoint リストと Excel 連携を最大限に活用するならば、SharePoint のリスト タブにある「Excel へエクスポート」（SharePoint リスト接続）を使わず、Excel のデータ タブにある「OData データ フィード」（Office データ接続）を使ったほうが便利になりそうなことは理解できたと思う。
しかし、ものすごく便利になるか、といえば微妙であるのは否めない。
ローカル PC の Excel で集計・分析し、それを SharePoint にアップロード、アップロードした時点での情報を Excel Online/Excel Web Access Web パーツで表示、という運用で多くはカバーできるのも事実である。
Office データ接続の OData データ フィードで、Excel Online / Excel Web Access Web パーツのデータ更新が可能になるメリットを享受できるが、たぶん、実務でこの機能を求めるのであれば「自動更新」というニーズがあるはずだ。
残念ながら、Excel Online と OData データ フィードだけでは自動更新のニーズを満たすことはできない。
この自動更新のニーズを満たすのが Power BI だと考えている。
事実、Power BI には以下の設定オプションがある。
残念ながら PowerBI はサブスクリプション購入したばかりで実務運用のレベルまで使っておらず、かつ、その設定も確実に理解していないため、これ以上の紹介はできないが、近いうちに紹介することができるだろう。
非常に長いエントリーになってしまったが、マイクロソフトによる日本語ドキュメントがまだ整備されていないようなので、何等かの参考になれば幸いである。
