VBAとMicrosoft Graph APIの連携 – Teams編

現在業務の自動化の為に、Electron, VBA, SAP GUI Scripting, Puppeteer等の他に「Microsoft Graph API」も非常に多く利用しています。決して使いやすいAPIではないのですが、認証の手段と大体の使い方を身に着けて、なるべくボタンひとつで業務が片付くように構築中です。

そんな中、以前Google Apps ScriptでGraph APIと連携してTeamsのログを取得するものを作成しましたが、今回Excel VBAで実装してみました。殆ど内容は同じですが、やはりローカルでデータを取得できるのは便利なので、Excel VBAはなかなか無くならないなと思います。

Google Apps ScriptとMicrosoft Graph APIの連携 – Teamsログ取得編

今回使用するファイル等

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

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

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

事前準備

これまで、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. 名前を入力(今回はteamsgetterと入力しました)、リダイレクトURIは「webを選択」し、今回URLはこのサイトのURLを入力。http://localhostでも良いのですが、Chromeを利用するので、自社のウェブサイトなどのURLを利用しましょう。
  4. 登録ボタンをクリックする
  5. 出てきた中で、「アプリケーション(クラと書かれているのがクライアントID」なので、このコードをメモしておく
  6. 左サイドバーより、「証明書とシークレット」をクリック
  7. 新しいクライアントシークレット」をクリックする
  8. 今回は特に有効期限を設けないで追加をクリック
  9. これで「値」に「クライアントシークレット」が生成されて手に入りました。このシークレットはこの時だけしか表示されないので、注意してください(IDは不要なのでメモらなくていいです)
  10. つづけて、左サイドバーより「APIのアクセス許可」をクリックする
  11. Microsoft APIの中にある「Microsoft Graph」をクリックする。
  12. 委任されたアクセス許可」をクリックする
  13. デフォルトでUser.ReadがすでにONなので、今回はopenid, offline_access、Group.Read.All、User.Read.All、Group.ReadWrite.AllChannelMessage.Read.Allを検索して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を選択する

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

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

Teamsのログ取得に必要な情報を集める

Teamsのログ取得に必要な情報を集めなければなりません。集めた情報でGraph Explorerを使い実際に取得できるのかどうかをテストする事が可能です。

  1. Graph Explorerに行き、ログインしておく
  2. サンプルクエリにてteamsで検索。Beta内にあるチャネル内のメッセージをクリックする
  3. 取得するURLは、https://graph.microsoft.com/beta/teams/{group-id-for-teams}/channels/{channel-id}/messagesといったようなスタイル
  4. グループIDがチームのIDとなります。チームを開きこのチームへのリンクを取得でリンクをまず取得します。
  5. 4.にgroupIdが入ってるので、そのIDを取得します。
  6. 次にチャネルIDが必要です。対象のチャネルを開きます。
  7. URLを見てみると、threadId以下に数字2桁:半角英数文字列~@thread.tacv2の文字があります。これがチャネルIDとなるので控えておきます。
  8. チームのIDをgroup-id-for-teamsに入れ、チャネルIDをchannel-idに入れる。
  9. 組み立てたURLを入れてクエリ実行をする
  10. まだこの段階では取れていません。ここでアクセス許可の修正をクリック
  11. 出てきたアクセス一覧の全ての同意をクリックする。この時、onmicrosoft.comアカウントではない場合、ChannelMessage.Read.Allの同意が出来ない事があります。
  12. 無事に取得できると、OK – 200が返ってきて対象のチャネル内の全メッセージが取得出来ます。
  13. ちなみに、なんとなくわかると思いますが、JSONの中のbodyがメッセージ本文、reactionsの中のreactionTypeがいいねに該当します。displayNameが投稿した人の名前ですね。

図:求められたアクセス許可

図:認証が通ると全メッセージがJSONで取得される

調べたデータを追記する

ExcelのVBA開発画面を開き、標準モジュールのauth3の項目内にある変数に、ここまでに調べた各種値を追記します。

  • tenant – テナントIDを記述する
  • client_id – クライアントIDを記述する
  • client_secret – クライアントシークレットを記述する
  • redirecturl – リダイレクトURLを記述する
  • groupId – TeamsのグループIDを記述する
  • channelId – TeamsのチャンネルIDを記述する
  • proxyuri – プロキシーのURLを記述する(使わない場合には、コード内の.setProxy 2, proxyuriの行はコメントアウトが必要)

scopeなどは設定いる内容のまま弄る必要はありません。

認証を実行する

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

ソースコード

VBA側コード

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

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は使われていない。
  • 二段階認証対応のウェイトを入れています。

あとは、VBAの側でこのAuthenticate Codeを引き換えにAccess TokenやRefresh Tokenを取得する仕組みに投げて上げれば良い。上記のコードでpkgやnexeを使ってビルドする事で、単体の認証用のEXEが出来上がります(そちらの方法はこちらのエントリーを参考にしてください)

認証作業

ここまでで、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. 二段階認証をセットしてる場合は、3.の前にスマフォアプリのAuthenticatorなどによる認証作業が間に入ります。
  5. リダイレクトURL先にcode=付きのURLで飛ばされて、Authenticate Codeを取り出す

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

※今回のコードでは、Access TokenやRefresh Tokenなどがsetting.iniに平文で書き出されますので、暗号化復号化などの処理を追加実装して、セキュリティ的に高める必要があります。

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

シートに書き出しをする

事前準備を終えて、認証を実行完了すれば、後はボタンひとつで、対象のチャンネル内のレスを取得することができます。仕様上、親スレッドを先に取得してからリプライを取得して1つの塊としていますので、1つのエントリーで2回HTTPリクエストが必要です。

また、レスポンスデータはJSONなので、JsonConverterで分解しながら配列に格納し、Sheet1に一気に書き出しています。

ソースコード

  • 親スレッドのデータを取得し、メッセージIDを取得します。
  • そのメッセージIDを元に再度、Graph APIにてリプライデータを取得するURLを構築してリクエストをします。
  • リプライデータまで含めた変数を最後シートに塊で書き出しています。
  • 今回は先に配列で10000レコード分を用意し、最後に実際のレコード数に戻す為にRedim Preserveしていますが、Transpose関数で行列逆転してから行う必要があるので、上記のような処理を追加しています(Redimでは列は増やせても行は増やせない為)

レスポンスデータ

  • reactionが、イイネなどのアクションに該当します。今回は件数しか利用していません
  • reactionType別にカウントして、イイネを貰った回数などを集計したい場合に便利です。

関連リンク

共有してみる:

コメントを残す

メールアドレスが公開されることはありません。

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