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等

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上にファイルを作成しておく

今回は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/ にてアクセス

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

  1. https://graph.microsoft.com/v1.0/search/query を POSTでリクエスト
  2. 要求本文のqueryStringにファイルの名前(今回はsakusakupanda.xlsxで検索)
  3. クエリを実行
  4. 成功すると、OK 200が返ってくる。
  5. 中に情報が入っており、ファイル名ほか目的のファイル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という配列に順次書き込みをし、一発でシートに書き出しをしています。

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

関連リンク

コメントを残す

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

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください