VBAからGraph APIでExcel Onlineを読み書きしてみた - 実装編

前回のエントリーにて、VBAからMicrosoft Graph APIのOAuth2.0認証に於いて、Authenticate Codeの取得までを実装しました。PuppeteerでChromeを操縦しての認証用に専用のexeを用意して対応することで、IE11の消えたWindows11でも使えると思います。

今回は、このcodeからAccess Token取得、Tokenリフレッシュ、そしてExcel Onlineの読み書きを実装してみたいと思います。

今回使用するファイルやAPI等

※Google WorkspaceやBoxのような外部サービス上でExcel Onlineは起動しますが、Graph APIからはアクセス出来ないので、これらのファイルはAPIから操作は出来ません。OneDriveおよびSharePoint Onilne上のExcelファイルに対してだけ操作できるので、注意が必要です。

新方式が登場しました

IE11の廃止に伴い、SeleniumやNode.jsやらといった手段を使わず、またPuppeteerと同様の手法(CDPを叩く)でVBAとEdge/ChromeのみでOAuth2.0認証する手段が登場しました。スクレイピングも可能になっています。以下のエントリーを参考にしてみてください。この手法は最も制限が無く、もっともすぐれた選択肢になると思います。

VBAでOAuth2.0認証 - 新方式を試してみた

Tokenを取得するコード

Access Tokenを取得する

前回ラストで取得したAuthenticate CodeがAccess Token引換券みたいなものなので、これを使ってAccess Token, Refresh_Token, Expire_inといった値を取得し、setting.iniに書き出す処理を実装します。実務ではAccess Tokenなどは暗号化してから保存する必要があります。今回は便宜上単純に書き出しているだけなので流出等しないよう厳重な管理が必要です。

Access Tokenの取得等ではWinHttpを利用しているため、企業内で使う場合はプロキシー設定が必要なケースがあります。その場合はオプションでプロキシURLの指定が必要です。

  • token取得用のURLはテナント毎に異なり通常企業で使用する場合はcommonは使えないので、コード内で組み立てています。
  • POSTで投げるオプションは変数datに格納しています。ここでclient_secretと前回取得したauth_codeを指定しています。
  • プロキシーを使っていない場合は、setProxyの行は不要なのでコメントアウトしておきます。
  • JSONデータが返ってくるので、パースして、setting.iniファイルに書き込みをしています。Excelファイルと同じ場所に作成されています。
  • この時、expireチェックの為のexpireする日付も生成して書き出ししています。

図:無事に取得出来ました

Token期限切れをチェックする

Graph APIのTokenはおよそ1時間で期限が切れます。故にAPIを叩く場合にはまず、期限切れをチェックし切れているもしくは切れそうな場合は、refresh_tokenを使って新しいTokenを取得するようにしています。しかし、あまりにもギリギリの場合は実行中にtoken期限切れを迎える可能性もあるため、自分の場合は残り30分を切っていたら更新するようにしています。

そのToken期限切れの確認をする為の関数を実装します。

  • iniファイルに書き込んだexpiredateの値と現在の時刻を比較しています
  • OKならばtrueを返してそのままAPIを実行させ、falseならば次のTokenリフレッシュのコードを実行させるようにします。

Tokenリフレッシュ

Tokenチェック用関数が出来上がったので、次にexpireしていた時用にTokenをリフレッシュする関数を作ります。しかし、殆どの構文がAccess Tokenを取得するコードと同じであるので、GetAccessToken関数にflgという引数を追加して、改造する事にしました。flgが0の場合は新規のAccess Tokenを取得とし、0以外ならばrefresh tokenでAccess Tokenを取得するように条件分岐しています。

  • iniファイルからrefresh_tokenを取得して利用します
  • datの中身がcodeではなくrefresh_tokenとし、grant_typeもrefresh_tokenとする点が違う点です。
  • あとは新しいAccess Token他を取得し直して、同じ用にiniファイルに書き戻すだけ

Excel Onlineを読み書きする

Access Token周りの装備が終わったのでいよいよExcel Onlineの操作をするのですが、Googleスプレッドシートのように簡単ではありません。通常はExcel Online上のファイルに付与されてるIDを使ってREST APIで叩くのですが、表向きこのIDはどこにも表示されておらず、これもDrive APIを利用してでないと確認出来ないのです。しかし、Googleスプレッドシートと違って、Drive上のファイルのパスを利用可能なので、これを利用して操作する事になります。

OneDrive上にファイルを作成しておく

ファイルを共有する

OneDrive上のExcelに対して読み書きをすることになるので、「編集権限」を付けた状態で、アクセスする人のアカウントを共有にて追加する必要があります(でなければ相手から見えない)。ちょっとBoxやGoogle Driveからしたら共有権限の付与がわかりにくいので注意。

  1. Excel Onlineの対象のファイルの右上にある「アクセス許可の管理」をクリックする
  2. 直接アクセスのプラスをクリックする
  3. 相手のMicrosoft365アカウントのメアドを入力する
  4. 編集権限か?閲覧権限か選択する
  5. 通知が相手に飛びアクセス可能になる

図:かなりわかりにくい権限付与方法

ファイルの配置とURLの取得

今回は1個のExcelファイル(タスクマン.xlsx)を用意し、taskmanというシート1個とテーブル1個(テーブル1という名前)で用意しました。場所はOneDriveのルート直下に配置している状態です。これをsakuというフォルダを作ってその中に移動して、そのファイルに対して操作を行います。xlsxファイルはOKですが、xlsファイルは未対応です。

このファイルへGraph APIでアクセスするには以下の2パターンになります。

  • https://graph.microsoft.com/v1.0/me/drive/root:/saku/タスクマン.xlsx:/workbook/ にてアクセス(ファイルのパスで指定)。ファイルの最後にコロンがつく点に注意
  • ファイルIDを調べて、https://graph.microsoft.com/v1.0/me/drive/items/ここにファイルID/workbook/ にてアクセス(但しこの場合自分だけしかアクセス出来ない)
  • 他人にもアクセスしてもらうようにするには、以下のようなURL構成にする

    オーナーのUIDはMicrosoft365上のファイルオーナーのUIDで、Graph Explorerにて「https://graph.microsoft.com/v1.0/me」を実行すると、自分のUIDが取れるので、自分がオーナーならばレスポンスにある「id」の項目がソレになります。

ちなみにファイルIDの調べ方ですが、これもAPIで調べられますが、Graph Explorerからであれば、一例として

  1. https://graph.microsoft.com/v1.0/search/query を POSTでリクエスト
  2. 要求本文のqueryStringにファイルの名前(今回はsakusakupanda.xlsxで検索)。以下がその内容になります。
  3. 要求ヘッダに「content-type」の「application/json」を追加しておく
  4. クエリを実行
  5. 成功すると、OK 200が返ってくる。
  6. 中に情報が入っており、ファイル名ほか目的のファイルIDも入ってる

図:用意したファイルの様子

図:ファイルIDを調べてみた

テーブルにレコードを追加

予めテーブルにしてあるのは、Graph APIからデータを追加したり、取得がとても楽だからです。まずは、テーブルに対して1行レコードを追加してみます。

  • checkExpireTokenにてまずは期限切れチェック
  • リクエストするURLはhttps://graph.microsoft.com/v1.0/me/drive/root:/saku/タスクマン.xlsx:/workbook/tables('テーブル1')/rowsで構築
  • 期限切れだった場合には、GetAccessToken(authcode, -1)にて、トークンリフレッシュを実行
  • Access Tokenを取得したら、送信するレコードをJSONの形式で構築する。レコードの列数と合わせないとエラーになります。
  • 構築したJSONとともにリクエストを投げる。201が返ってきたら成功です。
  • JSONの構築部分が今回の関数では非常に癖が強いので、Dictionaryなどを使った手法を使ったほうが楽になれるかもしれません。
  • テーブル名で指定してるのでいちいちシート名を気にすることなく流し込めます

Excel Online側は画面をリロードするとデータが確認出来ます。ちょっと反応がモッサリしてる感じがする。

VBAで利用できる様々な配列処理

図:リクエストはこんな感じで行う

図:データが追加された様子

テーブルデータを取得する

Excel Onlineのテーブルデータを取得し、自身のExcelファイルに書き出す処理を実装します。やや取り出し画面なJSON形式ではありますが、VBA-JSONを利用する事で割とスムーズにレコードデータを取れます。

  • 前半はレコード追加とほぼ同じように、Tokenの期限切れと再取得のコードです
  • リクエストURLについてもレコード挿入と同じURLですが、メソッドがPOSTではなくGETである点に注意
  • 受信したレコードデータはJsonConverter.ParseJsonでパースします
  • valueの中に1レコードずつvaluesという二次元配列(でも1レコードだけしか入っていない)を取り出す事になるので、For Eachで回して取り出します
  • レコード数をJson("value").Countで調べて、VBA側で空の二次元配列を用意しておきます。
  • For Eachでレコードデータをrecarrという配列に順次書き込みをし、一発でシートに書き出しをしています。
  • indexの値はテーブル上に於ける行番号であり、0から始まります。行を更新する場合などに必要になります)

図:こんな感じでデータが入っている

テーブルデータの行を更新する

Graph APIを使って特定の行のデータを更新することが出来ますが、公式ドキュメントの通りに構築すると、400エラーで動作しません。こちらのサイトでも報告されているのですが、公式ドキュメントが更新されておらず、非常にハマるポイントです。また、更新をする為には、Excel Online上のレコードに自動で割り振られてるindexの値(行番号)が必要となりますので、受信するローカルのExcelファイル側でそのIndexの値を追加して追記しておく必要もあります(Excel Online側はRow番号は特に追加する必要はないですがしておいたほうが、便利ではあります)

エンドポイントの構築

リクエスト時に使うエンドポイントですが、変則的でここが公式の通りに構築すると動きません。以下のように構築します。

rowsの後にitemAt(index=1)/rangeをつなげることで動きます。このindex=1の値が行番号になります。テーブルの2行目を意味し、セル番地的にはA3からの位置になるので注意(テーブル1行目の行番号はindex=0、つまりA2からとなるので要注意)

これで特定の行を更新することが可能になります。

※但し計測してみたら、この値が他者にまで反映するのに2分ものタイムラグがある。よって自分は掛けても他者からみた時に反映していない事があります。解決策模索中・・・(Googleスプレッドシートだとこういう事がないのですが)

リクエストの内容

送信するリクエストの内容は以下の通り。これを構築して、送信します。indexには同じく行番号を入れておきます。values以下が値になりますが、JSONで構築する点に注意。

ソースコード

  • 基本的にはレコードの追加とほぼ同じですが、リクエストメソッドはPATCHになるので注意
  • エンドポイントURLの構築も注意が必要です。

図:IDが2のレコードが更新された様子

永続セッションを作成する

前項までの手法で、テーブルデータの作成およびレコードの更新は出来るのですが、このままでは「他者に情報が反映するまで2分以上のタイムラグ」が発生する為、具合が悪いです。そのため、書き込みをしてもすぐにデータが反映されないので、この問題を解消するには永続セッションというものを作成し、session-idを取得後に、データの取得やレコードデータの更新時に、リクエストヘッダに取得したsession-idを加えることで、リアルタイムに読み書きが可能になります。

よって、このテクニックは実務で利用する場合は、Excel Onlineを読み書きするアプリとしては必須のテクニックとなります。

セッション作成のコード

  • persistChangesをfalseにすると非永続セッションとなってしまうので実験用で用いるものになり、反映もしません。
  • セッションIDを取り出したら、呼び出し元に返します。

取得したセッションIDでリクエスト

テーブルデータの取得の事例をセッションIDを使ってリクエストするサンプルです。

  • createSessionを呼び出してセッションIDを取得させます。
  • リクエストヘッダに「.SetRequestHeader "workbook-session-id", sessionman」を追加して、セッションIDにてリクエストを行う

関連リンク

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

日本語が含まれない投稿は無視されますのでご注意ください。(スパム対策)