VBAとMicrosoft Graph APIの連携 – Calendar編

MicrosoftのOutlookには予定表としてカレンダーが用意されています。VBAで何がしかの処理をした際にその記録として、カレンダーに残りしたり、タスクの開始と終了をカレンダーに自動登録しておいて、リマインダーを送らせたりしたい場合があります。

また、他の人が自身の標準カレンダーへのアクセス権限を限定公開したい場合もこの操作のうちに入ります。今回はこの予定表の読み書きやカレンダー共有設定をVBAから行ってみたいと思います。

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

以下のエントリーにあるようにPuppeteerを利用したOAuth2.0認証をするように変更しています。Node.jsにて作成し、EXEにパッケージ。これをVBAから叩いて利用しています。Graph API認証用のEXEはサンプルファイルと同じディレクトリに入れておく必要があります。

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などの暗号化などの対処が別途必要になります)。

はじめての Microsoft Graph

プロキシー設定を調べる

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

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

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

なお、Proxyを使わない場合には、コード内のproxyuriおよびWinHTTPの.setProxy 2, proxyuriについては、コメントアウトしておかないと「定数式が必要です」というエラーが出てしまうので注意してください。

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

Calendar IDについて

Calendarを操作する場合、自身の予定表に複数のカレンダーを追加してる場合であれ、そうでない場合であれ、各カレンダーには固有のIDが振られています。これがなければ操作ができないのですが、事前に調べて入れておくといった事ができない為、カレンダー名で一度対象のカレンダーのIDを取得しておいて、再度読み書きのリクエストを送ることになります。

また、今回はカレンダーの共有設定については、所属組織内の人のアクセス権限を操作するので、nameが「My Organization」のものを操作し、カレンダーは初期表示の「予定表」を操作します。

図:所属組織内の権限を変更する

Azureでプロジェクトを作成

今回のプログラムは管理者権限は不要です(Graph Explorerだと管理者権限の必要なスコープが出てきますが、実際には不要です)

  1. アプリの登録にて登録を開始する
  2. 新規登録をクリックする
  3. 名前を入力(今回はcalendarmanと入力しました)、リダイレクトURIは「webを選択」し、今回URLはこのサイトのURLを入力。
  4. 登録ボタンをクリックする
  5. 出てきた中で、「アプリケーション(クラと書かれているのがクライアントID」なので、このコードをメモしておく
  6. 左サイドバーより、「証明書とシークレット」をクリック
  7. 新しいクライアントシークレット」をクリックする
  8. 今回は特に有効期限を設けないで追加をクリック
  9. これで「値」に「クライアントシークレット」が生成されて手に入りました。このシークレットはこの時だけしか表示されないので、注意してください(IDは不要なのでメモらなくていいです)
  10. つづけて、左サイドバーより「APIのアクセス許可」をクリックする
  11. Microsoft APIの中にある「Microsoft Graph」をクリックする。
  12. 「委任されたアクセス許可」をクリックする
  13. デフォルトでUser.ReadがすでにONなので、今回はopenid, offline_access, profile, Calendars.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ファイルへ記述

今回はExcelファイルの標準モジュールの上部に記載しますが、通常はこの手の値は、外部のファイルへ切り出しておいたほうが安全ですので、iniファイル等やレジストリ等に値は保存し、読み書きする仕組みを用意しておくと良いでしょう。

auth3のモジュールの冒頭に4つの値を書き込みます。

Scopeは%20で区切って入れておく必要があります。

認証を実行するコード

今回はNode.js + Puppeteer + pkgにてWindows用のEXEを作っており、VBAから叩いてAuthcodeを取得する所まではそちらで対応しています。以前にも全く同じ内容の手順を記述していますので、以下のエントリーを参考にしてみてください。

Authcodeを取得したら、続けて、Access TokenやRefresh Tokenを取得する仕組みになっています。

図:認証を実行する一歩手前

VBAとMicrosoft Graph APIの連携 – Planner編

Calendarを読み書きしてみる

カレンダーIDを取得する

今回はいくつかあるカレンダーのうち「予定表」と名前のついてるデフォルトのカレンダーのIDを取得します。このIDはイベント登録等で利用するので、取得したらグローバルの変数やiniファイルに記述しておくと良いでしょう。このAPIについてはこちらにマニュアルがあります。

  • これで長いカレンダーIDが取得できますので、次項の読み書きが可能になります。基本最初の一度だけで良いので、何回も使う機会は無いと思います。
  • レスポンスはJSONで返ってくるので、valueの配列を取得させ、その中にあるidという項目とnameという項目を取得し、予定表という名前のものだけをピックアップします。

カレンダーイベントを登録をする

既存の予定表に対して、カレンダーのスケジュールを登録します。今回は単品で登録するだけなので、例えば複数期間に渡っての登録や、パターン登録、グループカレンダー登録ではありません。こちらにマニュアルが掲載されています。既存の予定表以外のカレンダーに登録する場合は、カレンダーIDが必要になります。

今回は開始日、終了日、表題、場所の4点を登録します。

  • Dictionaryを使って一つの連想配列をうまく作っていく必要があります。
  • 大本のJsonObjectという連想配列に最終的に含めて1つのリクエストボディを作成し、リクエスト時にJsonConverter.ConvertToJsonにてJSON変換を行っています。
  • エンドポイントに於いて、me/calendarsのみでリクエストした場合は、標準の予定表に書き込まれ、カレンダーIDを指定すると対象のカレンダーに書き込まれるようになっています。
  • 他にも招待者の指定や、本文の指定(HTML表記可能)が可能になっています。

イベントIDは後で書き換えたり、削除したりする場合に必要なので、どこかに退避させておいて取っておきましょう。Plannerと違って更新時にはodata.etagなどの値は不要のようです。

図:イベントが登録された

カレンダーイベントを削除する

登録済みのカレンダーイベントを削除する場合には、対象のイベントのイベントIDが必要です。リストの取得や前述のカレンダー登録時にレスポンスで返ってくるので、これを控えておいて、リクエストを行います。こちらにマニュアルが掲載されています。

  • DELETEメソッドでリクエストを行います
  • 基本的にはイベントIDのみでリクエストし削除を行えます。
  • 成功レスポンスコードは204なので注意。

カレンダーイベントのリストを全取得する

対象のカレンダーに於けるイベントを取得します。各イベントの細かな情報を取得できる為、常にクリック時から1週間分の未完了のイベントを取得みたいな形で自分は取得させています。こちらにマニュアルが掲載されています。

ただし他のカレンダーイベントを取得する場合には事前にその人が次項の「カレンダーの共有設定」に於いて、所属組織内に対して公開をしていなければ、アクセスが拒否されますので要注意です(同時に他の人のカレンダーの場合、useridが必要になります)

今回は、レスポンスデータのうち、表題・開始日・終了日の3つだけをフィルタ指定して取得します。ただしこの手法は期間指定ができません

  • リクエストURLにて、eventsの後に「?$select=subject,start,end」で取得項目を絞っています。
  • レスポンスデータのvalueをfor eachで順次取得して最後にdebug.printにて表示させています。

カレンダーイベントを期間指定で取得する

前述の方法は日付で期間指定する手段が無い為、過去のも含めてずらーっと取得されてしまうのですが、calendarViewを用いると、URLリクエスト時に期間指定が可能であるため、通常はこちらを使ったほうが利便性は上です。

こちらにマニュアルが掲載されています。

  • startとendの2つの日付を指定し、URLのオプションとして追加します。
  • 前述同様にレスポンスから、開始日、終了日、表題の3つを取得してDebug.Printで表示させています。

カレンダーの共有設定を変更する

他の人のカレンダーを取得したい場合、相手が所属組織内に対して対象のカレンダーを公開していなければ取得ができません。そこで、自分自身のカレンダーの共有設定をGraph APIから変更させて、公開したり非公開にしたりが可能です。

ただしこの設定変更をするためには現在のカレンダーにセットされてるpermissionidを取得してから、そのidを元に変更のリクエストを送る2段構えが必要になります。今回は所属してる組織(My Organization)を対象に行います。

なお、現在のroleがnoneならばlimitedRead, noneじゃなければnoneにするといった判定を入れています。roleはこちらに一覧があります。

  • カレンダー共有が「共有しない」の場合には、「タイトルと場所を閲覧可能」なLimitedReadに変更します。
  • permissionidを取得し、そのidを用いて再度更新のリクエストでroleを変更させています。リクエストメソッドはPATCHになるので注意。
  • 個別の権限変更をする場合はpermnameの判定にユーザ名等の判定結果を入れて処理をします(今回は所属組織内の人なのでMy Organizationでフィルタ)

図:カレンダーへのアクセス許可

図:権限を変更する画面

関連リンク

コメントを残す

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

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