VBAとMicrosoft Graph APIの連携 – Excel Online編

Microsoft 365にはSharepointおよびOneDrive Businessにて、ExcelのファイルをExcel Onlineという形で利用が可能です。Boxのようなストレージサービスと異なり、Googleスプレッドシート同様に同時に読み書きが可能であるため、読み書きがバッティングしてファイルが分裂なんてこともない(但し、SharePointでは過去、Excelファイルが破損するというのが報告されている)

この仕組を利用しつつ、ローカルのExcelからExcel Online上のファイルの読み書きが出来ればアプリ作成の幅が広がるのではないか?ということで、例の如くGraph APIを利用して読み書きが出来ないか?やってみることにしました。アクセストークン取得〜Teamsログ取得まで装備しています。

今回利用するサービスやファイル等

今回は、いつものIE11を使っての認証ではなく、以下のエントリーにあるようにPuppeteerを利用したOAuth2.0認証をするように変更しています。Node.jsにて作成し、EXEにパッケージ。これをVBAから叩いて利用しています。

Graph API認証用のEXEはサンプルファイルと同じディレクトリに入れておく必要があります。

※公式ドキュメントでExcelの項目にたびたび出てくる「session_id」とは、一般的にはAzure ADで作成してある「Client ID」の事です。これが説明無しに出てくるので、MSのドキュメントは非常に読みにくい。

VBAでOAuth2.0認証 - Windows11対応版

新方式が登場しました

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

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

事前準備

これまで、ElectronやGoogle Apps ScriptなどでGraph APIを叩く準備は構築してきていますが、VBAからも同じような形で事前準備が必要になります。以下の手順でClient IDとClient Secretを取得します(実用時はAccess Tokenなどの暗号化などの対処が別途必要になります)。

プロキシー設定を調べる

企業内で使う場合、ウェブアクセスにプロキシーを使ってる場合には、VBAからアクセスする場合もその設定を利用する必要性があります。プロキシーを経由しなければ外に出ることができないので、プログラムが動作しません。プロキシーの設定はいろいろなパターンがありますが、一般的な設定の調べ方は以下の通り。

サーバーのアドレスとポート番号について、http://を除外して、コロンでポート番号でつなげて利用します。(例:hiroproxy.net:8080

  1. コントロールパネルより「インターネットオプション」を開く
  2. 「接続」タブを開き、「LANの設定」を開く
  3. この画面でプロキシーサーバの部分にアドレスとポート名が入ってるならばこれを控えておく。
  4. 場合によっては、詳細設定の中の「HTTP」で指定してるサーバーアドレスとポート番号を控えておく。
  5. 自動構成スクリプトを使ってる場合、そこに指定されてるアドレスのファイルの中に、様々なプロキシーアドレスが入っていますので、それを一旦ダウンロードして中身をテキストエディタで開いてみる(通常はpacというファイル)
  6. 5.のケースの場合、pacファイル内はIF文を使ってアクセスするサイト別にプロキシーが設定されてることが多いので、もっとも一般的なサイトアクセスもしくはBoxについてだけ定義している場合には、そのサーバーアドレスとポート番号を控えておく。

図:プロキシー設定がない場合はこの作業は不要です。

Azureでプロジェクトを作成

  1. アプリの登録にて登録を開始する
  2. 新規登録をクリックする
  3. 名前を入力(今回はexcelmanと入力しました)、リダイレクトURIは「webを選択」し、今回URLはこのサイトのURLを入力。http://localhostでも良いのですが、Internet Explorerを使うので、この設定ですとAccess Tokenが取れない場合があります。
  4. 登録ボタンをクリックする
  5. 出てきた中で、「アプリケーション(クラと書かれているのがクライアントID」なので、このコードをメモしておく
  6. 左サイドバーより、「証明書とシークレット」をクリック
  7. 新しいクライアントシークレット」をクリックする
  8. 今回は特に有効期限を設けないで追加をクリック
  9. これで「値」に「クライアントシークレット」が生成されて手に入りました。このシークレットはこの時だけしか表示されないので、注意してください(IDは不要なのでメモらなくていいです)
  10. つづけて、左サイドバーより「APIのアクセス許可」をクリックする
  11. Microsoft APIの中にある「Microsoft Graph」をクリックする。
  12. 委任されたアクセス許可」をクリックする
  13. デフォルトでUser.ReadがすでにONなので、今回はopenid, offline_access, User.ReadBasic.All, Files.Read, Files,ReadWriteを検索してONにしましょう。
  14. アクセス許可の追加をクリックする
  15. 追加出来たら、xxxxxに管理者の同意を与えますをクリックします。すると、状態が緑色になります。今回は管理者の権限を要求するものは無いのでしなくても大丈夫だと思う
  16. 次に左サイドバーより「認証」をクリック
  17. 暗黙の付与にて、「アクセストークン」にチェックを入れる
  18. サポートされているアカウントの種類に於いては、「マルチテナント」にしておきました。
  19. 保存をクリック
  20. 概要のエンドポイントをクリックすると、いろいろなエンドポイントURLが出る。
  21. 概要のディレクトリ(テナントの数値はメモっておきます。あとでプログラム中で使用します。
  22. デフォルトでは組織アカウントでなければOAuth2.0認証が出来ません。

※3.でWebを選ばないSPAを選んでしまうと、Proof Key for Code Exchange by OAuth Public Clientsといったエラーが出てしまい認証ができませんので注意。

図:アプリの登録から全ては始まります。

図:Graphを選択する

図:アクセス権限付与した状態

図:認証の設定変更に注意

Excelサービスの制限

OutlookやTeamsのサービス利用時同様に、Excelの読み書きに対してもやはりQuotaというものが設定されています。とはいえ、シンプルな制限で以下の2つが設定されています。

  1. 全テナント毎アプリ別 - 10秒間に5000リクエストまで
  2. テナント毎アプリ別 - 10秒間に1500リクエストまで

リミットに達してしまった場合にはエラーコードは429(Too Many Requests)が返ってきます。エラーレスポンスのヘッダにあるRetry-Afterの値分だけ待機させる事で再試行が可能です。

VBAでExcel操作をするような感覚で細かくリクエストを投げていると、容易に引っかかる可能性がありますし、復数ユーザに対して同一アプリを割り当てるわけなので、結構あっさり429エラーになりがちなので、リクエストの出し方は要注意です。Outlookのようにaccount throttle limitがあるのかは不明。

認証を実行するコード

冒頭にあるように、いつものようにIE11で認証を実行してAccess Tokenを取得するのではなく、Windows11 64bitを見据えて、今回はNode.js + Puppeteer + pkgにてWindows用のEXEを作っており、VBAから叩いてAuthcodeを取得する所まではそちらで対応しています。

ソースコード

VBA側コード

  • Authenticate Codeを取得するまでのコードです。
  • Node.jsで作られてるexeに対して、引数で認証用URLを渡し、相手側のプログラムが完了するまで待機させてあります
  • scopeにはAzure側で利用するスコープ名を半角スペースで区切りますが、半角スペースは%20として指定しています
  • &で引数を区切り渡すと壊れるので、こちらも%26として置き換えて指定(相手側のプログラムでこれは&に変換させています)
  • 出力を受け取ったら、今回はDebug.Printで表示していますが、次の「Access Token」を取得するプログラムへ渡します

Node.js側コード

今回は、command-line-argsおよびpuppeteer-coreを利用して、VBAからのコマンドライン引数で認証用URLを取得し、PuppeteerでMicrosoft Graph APIの認証⇒Authenticate Codeの取得までを担当させています。それ以降はVBA単体で行えます。また、過去の以下のエントリをベースに作っていますが、OAuth2.0認証だとちょっとコードが足らないので、追加しています。

Puppeteerで途中でユーザに入力してもらうようにする

  • index-win -g 認証用URLの引数でこのプログラムに渡してあげます
  • 受け取った引数のうち、%26の文字は「&」に変換します。理由はそのままVBA側から&で渡すと引数が壊れる為
  • ログイン後の「ログインしたままにするか?」の画面の「いいえ」のボタン(#idBtn_Back)が表示されるまでウェイトを掛けています。
  • 上記ボタンのwaitForSelectorの引数timeout:90000を入れてるのは、デフォルト値だと入力などの時間が足らずに、タイムアウトしやすい為(0だとタイムアウト無しになる)
  • ユーザがID、パスを入力し、サインインを維持したままにするか?にて「はい」をクリックすると、waitEvent関数が発動し、リダイレクトURL先に飛ぶまで待機させています(故にnetworkidle2のwaitForNavigationも入れている)
  • 成功するとリダイレクト先のURLにcode=でAuthenticate Codeが付与されている。このURLは2個目のタブにあるURLなので、allPages[1].url()で取得させています。response.request().redirectChain()などでは取れないので、要注意(responseの中にはそれっぽいURLがいるのだけれど)
  • code=以降のauthenticate code以外にも、stateやら何やら入ってるので、まずはcode=以降を取得、次に&で区切って配列にし、1個目の値がソレになるので取り出す
  • 取り出したら、console.logで出力するとVBA側で拾ってくれる
  • 初回認証時のURLにはclient_idは入っているが、client_secretは使われていない。
  • 2022/02/25 - main関数を二段階認証時に対応するようにコードを変更しました。

あとは、VBAの側でこのAuthenticate Codeを引き換えにAccess TokenやRefresh Tokenを取得する仕組みに投げて上げれば良い。

pkgコマンドの注意点

前述のNode.jsとPuppeteer-coreで作ったアプリをEXE化するものとして、pkgを利用しています。類似のものにnexeがあります。最新版pkgでは、Node.js v14に対応しているものの、別の環境でビルドをしようとしたらGitやらpatch.exeやらNASMやらが入っていないとエラー?になった為、ちょっと癖があるプログラムです。

よって、今回はpkg@5.3.3およびNode.js 14.17.5でビルドしています。ビルドする時は以下のコマンドでビルドしないと、evaluate関数あたりでエラーが出てしまうので、注意。

作成してできたindex.exeをindex-win.exeにリネームして今回のVBAからは呼び出させています。

nexeコマンドの注意点

同じくEXEを作るnexeですが、keytarなどのネイティブモジュールをパックしてくれなくなってしまったのと、インストール自体がかなり面倒な事になっていたので、改めてここにインストール方法とビルドの注意点を。

注意点としてv3.3.3の時点では

  • nexeは自身が用意してる版のNode.jsでしか対応していないので、ビルドする場合にはNode.jsのバージョンを合わせる必要があります(現時点では、v14.15.3まで対応)
  • コマンドラインは、nexe index.jsでビルドが出来ます。
  • プロキシがある場合には、実行する為にnexeがプロキシを透過できるようにする必要があります以下のコマンドを入れて、npmにプロキシ通過の設定をしておくこと
  • keytarなどのネイティブモジュールはEXEにパックされないので、node_modulesのフォルダ毎作成したexeの配布が必要。但し、node_modulesの中はkeytarのフォルダのみあればOK

また、command-line-argsなどにコマンドラインパラメータを渡すようなケースでは、VBAで使う場合はメアド、URLなどの文字列は必ず、Chr(34)などで対象の文字列を括ってからでないと、Account is Requiredというエラーが出て止まります(keytarが原因)。

認証を実行する

ここまでで、OAuth2.0認証のAuthenticate Codeの取得までが装備出来ているので、VBA側で「GraphAuthorization」を実行します。まだ、今回の装備ではAccess Tokenなどは取得出来ていないので、Graph APIを叩ける状態にないのですが、次回の実装編ではこれまでのエントリー同様に、Access Tokenの取得、Tokenのリフレッシュ、Excel Onlineの読み書きを実装します。

実行すると

  1. 用意しておいた認証用URLをindex-win.exeに引き渡す
  2. Puppeteerにてインストール済みのChromeもしくはChromium Edgeが起動し認証ページが開かれる
  3. ログイン作業をして、「サインインを維持したままにするか?」では「はい」をクリックする
  4. リダイレクトURL先にcode=付きのURLで飛ばされて、Authenticate Codeを取り出す

という作業が行われます。

次回実装編は以下のエントリーより

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

図:サインインは維持する

関連リンク

コメントを残す

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

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