VBAとMicrosoft Graph APIの連携 – Planner編

Microsoft 365にはPlannerと呼ばれるメンバーで利用するタスク管理プログラムが用意されています。しかし、実際に使ってみると主要な項目は抑えられているものの、若干使いにくい。という事でプログラムから利用できる形にして、インターフェースは別に用意するといった作業を今行っています。

そしてプログラムから利用できるようにする為のものがGraph APIであり、タスクの登録・更新・一覧の取得をやってみたいと思います。

図:シンプルなタスク一覧画面

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

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

シートの設定にグループIDおよびプランIDの入力欄があるので入力してから利用して下さい。

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については、コメントアウトしておかないと「定数式が必要です」というエラーが出てしまうので注意してください。

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

Plannerの追加・更新の為に必要な情報を収集する

PlannerをGraph APIで実行するのに必要な情報は、以下のように取得する事が出来ます。下記以外にもbucketidやtaskidなどが実際には必要ですが、下記の情報でリクエストを投げる事で入手が可能であるため、プログラム自体が必要とするのは、groupIdとplanIdだけで十分です。

  1. Planner新しいプランを作成する
  2. 作成したプランを開く
  3. URLの中にあるgroupId=以下の部分が、groupIdになります。
  4. 同じくURLの中にあるplanId=以下の部分が、planIdになります。

Graph Explorerで上記の情報を元に、リクエストをする際にアクセス許可に管理者権限を要求されるものが出てきたりしますが、実際にはこれらに同意せずとも、リクエストを実行する事が可能です(つまり必須ではない)

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

Azureでプロジェクトを作成

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

  1. アプリの登録にて登録を開始する
  2. 新規登録をクリックする
  3. 名前を入力(今回はplannermanと入力しました)、リダイレクト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, Tasks.ReadWrite.Shared, Tasks.Read, Tasks.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を選択する

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

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

認証を実行するコード

冒頭にあるように、いつものように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単体で行えます。EXEの作成や詳細については、以下のエントリーに記載していますので、ここでは省略致します。

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

認証を実行する

ここまでで、OAuth2.0認証のAuthenticate Codeの取得までが装備出来ているので、VBA側で「GraphAuthorization」を実行します。まだ、今回の装備ではAccess Tokenなどは取得出来ていないので、Access Tokenの取得やRefresh Tokenの処理については過去のエントリーで紹介しているので、以下のエントリーを参照して下さい。

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

実行すると

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

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

図:認証を実行してる様子

Plannerを読み書きしてみる

Plannerのデータはplan => bucket => taskという階層で登録されており、更にtaskの下に説明文等の情報が詳細情報として分けられてるため、完全なタスク情報を取得する為には、数回Graph APIをリクエストする必要性があります。また、現在、タスクに対するコメントは取得が出来ません

更にリクエストバージョンがBetaでなければ、priorityが取得出来ない為、今回のリクエストはv1.0ではなくBetaを利用しています。

図:これらの情報を取得する

Plan内のバケット一覧を取得する

Planの中には初期で用意されてるTo Doと呼ばれるバケット以外は存在しません。タスクはそれらのバケット以下に登録されるので、まずはPlan内にあるバケットの一覧を取得する必要があります。この時に利用するのはplanIdで、取得できるbucketidをExcelシートに書き出します。

この時に利用するAPIのエンドポイントは、「https://graph.microsoft.com/beta/planner/plans/ここにplanIdを入れる/buckets」となります(GETリクエスト)。

この時のレスポンス成功のコードは200となります。

VBAのコード

  • GETでリクエストしてレスポンスデータのJSONを分解してバケットシートに書き込みをしています。
  • 書き込み先はテーブルとなっています。

レスポンスデータ

レスポンスデータで利用するのは、valueの中にあるnameとidの2つのみです。このidがbucketidになります。

バケット内のタスク一覧を取得する

前述のplanの中にあるbucketidを元に、このバケットに登録されているタスクの一覧を取得します。この時に利用するのがbucketidで、これがこのプログラムで最も重要なリクエストになります。

この時に利用するAPIのエンドポイントは、「https://graph.microsoft.com/beta/planner/buckets/ここにbucketidを入れる/tasks」となります(GETリクエスト)

※他にもユーザIDに基づいてユーザに割り振られたタスクを取得するAPIもあります。

この時のレスポンス成功のコードは200となります。

VBAのコード

  • 上記のコードは処理の途中部分になりますがエンドポイントとしてbucketidをつけてリクエストします。
  • 返り値のうち、必要な項目だけを取り出してrecarrに追加しています。
  • アサイン先は、Countにて存在確認した後で、存在すればそのユーザのIDを格納します。
  • ラベルも複数存在するケースがあるので、Countにて存在確認した後で、key名を一時配列に格納。その後Joinにてカンマ区切りにして格納します。

なお、このrecarrのデータをテーブルに追記する形で追加をしなければならないので、以下のコードを利用して、2つ以上のバケットのタスク内容を順次追加しています。

  • 10000レコードに一旦広げてからデータを入れてるため、書き出し時には空白行は削る必要があるため。
  • ReDim Preserveにて実際に存在するレコード分に相当する配列を用意し、そこへ既存のrecarrからのデータをコピー。これを書き出すようにしています。
  • 最後のコードで、テーブルの最終行の位置を把握して、ここにtemparrを追記するようにしています。

図:無事に現在のタスクリスト全部を取得出来た

レスポンスデータ

  • percentCompleteが50で処理中、100で完了済みとなります。
  • priorityの値は、9=低・5=重要・3=中・1=緊急となります。
  • idがtaskidとなります。これを元に詳細なデータを取得することになります。
  • 他にもchecklistなども取得出来るようですが、今回はスルーしています。
  • assignmentsにタスクを割り振った相手の情報が入っています。対象者のIDは個別のM365上のIDとなります。
  • appliedCategoriesが、「ラベル」に該当します。これを応用して、詳細な進捗状況の管理に使ったり可能です。
  • この段階では、「メモ」「添付ファイル」に関しての情報が取得出来ないので、taskidを利用して追加取得することになります。
  • この時に表示されてる「@odata.etag」の値は更新時に必要となるため、データ取得時に控えておく必要があります。

タスクの詳細情報を取得する

前述までで殆どの情報が取得出来ているのですが、メモと添付ファイルに関する情報が取得出来ていないので、さらにリクエストをしてタスクの詳細を取得させます。この時に利用するのがtaskidであり、これを元にリクエストをして追加取得します。

この時に利用するAPIのエンドポイントは、「https://graph.microsoft.com/beta/planner/tasks/ここにタスクのIDを入れる/details」(GETリクエスト)

この時のレスポンス成功のコードは200となります。

VBAのコード

  • 前回のタスクの内容の続きになります。その際にrecords("id")でタスクIDを取得しておき、これを元にエンドポイントを構築します。
  • リクエスト結果のうちdescriptionが説明文になります。
  • ファイルのURLはちょっと取り出すのが厄介です(JSON keyの名前になってしまってるため)。referencesに登録があるので、ない場合は0が返ってくる。0じゃない場合は、.keysで回してkey名を取得し、これを配列に格納します。
  • ファイルURLはURLエンコードされてしまってるのでDEcodeURLMSHTML関数にてデコードしてから格納します。

レスポンスデータ

  • 今回はファイルそのものではなく、Boxの対象のファイルのURLをただ入れてるだけなので、referencesのURLを取得する必要があります。
  • descriptionの中にメモの中身が入っている。
  • checklistを登録している場合は、ここに詳細が出てきます。
  • この中に入ってる@odata.etagの値はタスクを更新する度に変化し、更新時や削除時に必要となる大切な値なので、控えておきます。

タスクを新規登録する

タスクを新規登録する為には、planId・bucketIdが必要になります。同時にタスク名を登録する必要があります。また、リクエストヘッダーには「application/json」が必要になります。またリクエスト時に同時にタスクに対して色々と初期値をセットする場合には、plannerTaskの項目を要求本文に追加する事で、セットする事が可能です。

この時に利用するAPIのエンドポイントは「https://graph.microsoft.com/beta/planner/tasks」となります(POSTリクエスト)

※またリクエスト結果(201)のレスポンスデータには前述のように初期値の内容がすべて返ってきます。

VBAのコード

  • DictionaryにてリクエストボディのJSONを構築する(プランIDとバケットIDが必要です)
  • POSTにて送信する際に、JsonConverter.ConvertToJsonにてJSONへと変換する必要があります。
  • .SetRequestHeader "Content-Type", "application/json"の追加が必要です
  • 201が返ってきたら投稿成功です。
  • 必要に応じて、リクエストボディに項目を追加すれば初期値をセットした状態で投稿可能です。

図:無事に投稿できました。

リクエスト要求本文

  • 最低限上記のリクエスト要求は必要になります。

タスクを更新する

タスク内容の更新がやや難しい内容です。「バケット内のタスク一覧を取得する」の際に取得した「@odata.etag」の値がリクエストに必須になります。また、リクエストURLには対象のtaskidも必要になります。

また、リクエストヘッダには、「application/json」の他に「If-Match:@odata.etagの値」、「Prefer: return=representation」の追加が必要になります。

この時に利用するAPIのエンドポイントは「https://graph.microsoft.com/beta/planner/tasks/ここにtaskidを入れる」となります(PATCHリクエスト)

※またリクエスト結果(200)レスポンスデータには前述のように初期値の内容がすべて返ってきます。

VBAのコード

  • タスクIDをもって、エンドポイントを構築します。
  • @odata.etagの値が必要なのですが、形式が「W/"JzEtVCCCCCCCCCCCCCCCCCCCCCC="」といった様に、値にダブルコーテーションが含まれてしまってるので、直接入れるコードで記述する場合には、「etag = "W/""JzEtVCCCCCCCCCCCCCCCCCCCCCC="""」といった様に、ダブルコーテーションをエスケープする必要があります。
  • 今回は、percentComplete(進捗状況)を50にすることで、処理中にするというリクエストを送っています。
  • リクエストヘッダとして、「Prefer: return=representation」を追加が必須です
  • リクエストヘッダとして、「If-Match:@odata.etagの値」を追加が必須です。
  • リクエストはPATCHで行う点に注意が必要です
  • 200が返ってきたら投稿成功です。

図:進行状況を変更してみた

リクエスト要求本文

新規追加時同様に、更新をする対象のplannerTaskの項目を要求本文に追加する事で、セットする事が可能です。taskidで指定してるので、planIdやbucketIdは必要ありません(変更する場合はセットする)。

  • 上記の事例は、ラベルを1個追加し、1個を剥がしてるリクエストになります。

タスクを削除する

通常削除するコード

タスクは完了とする場合は、前述の更新の手順でリクエスト本文にてpercentCompleteを100にする事で完了となります。しかし、この場合、Plannerには依然としてタスクは残り続けるので、一覧から消したい場合には、タスクの削除を実行する必要があります。

また、リクエストヘッダには、「If-Match:@odata.etagの値」の追加が必要になります。

この時に利用するAPIのエンドポイントは「https://graph.microsoft.com/beta/planner/tasks/ここにtaskidを入れる」となります(DELETEリクエスト)

  • タスクIDをもって、エンドポイントを構築します。
  • @odata.etagの値が必要なのですが、形式が「W/"JzEtVCCCCCCCCCCCCCCCCCCCCCC="」といった様に、値にダブルコーテーションが含まれてしまってるので、直接入れるコードで記述する場合には、「etag = "W/""JzEtVCCCCCCCCCCCCCCCCCCCCCC="""」といった様に、ダブルコーテーションをエスケープする必要があります。
  • リクエストヘッダとして、「If-Match:@odata.etagの値」を追加が必須です。
  • リクエストはDELETEで行う点に注意が必要です
  • 204が返ってきたら投稿成功です。
  • 成功時は特にResponseHeaderが無いので、空({})が返ってきます。

タスクに割り当てる

新規追加時やタスク更新時に、そのタスクに対して実行する人を割り当て(アサイン)する場合は、前述のタスクを更新すると殆ど同じコードを利用しますが、アサイン時のリクエスト要求本文が少々変則的なので、注意が必要です。また、割り当てる対象のメアドではなくMicrosoft365上のユーザのIDが必要になるので、予め取得しておく必要があります。

VBAのコード

  • リクエスト本文の構築が少々厄介で、assignmentsをAddした後に、m365uidにてNew Dictionaryを追加してから、odata.typeやorderHintを追加しようとするとエラーになるので、別々にオブジェクトを用意してから、最後にassignmentsにuidOjbectを連結するようにしています。
  • m365uidのオブジェクトに@odata.typeやorderHintを追加する

リクエスト要求本文

Microsoft365のユーザIDが要素の名称となっているので、やや作成方法が厄介です。

409, 412エラーについて

Plannerのタスクを削除したり更新、タスク割当しようとした場合に、409もしくは412のエラーが返ってきて処理がキャンセルされる場合があります。これらはそれぞれ

  • 409エラー : Conflict
  • 412エラー:Precondition Failed。「The ETag value is too old, the item must be read again」というメッセージの表示がされる。

と呼ばれるエラーで、Plannerはその内容を更新したり、完了させたり、誰かにタスクを割り当てたりした場合、バージョン管理のために@odata.etagの値が変わってしまいます。そのため、これらの作業をした場合には予め取得してあるデータを差し替えておき、そのetagの値を持ってしてリクエストを行わないと、このエラーが出て削除が出来ない仕組みになっています。

一々変更した内容を記録したり、またアプリ外で更新された場合の事を考えると、このエラーは非常に厄介です。この場合の対処法ですが

  1. リクエストをして409, 412が返ってきたら条件を分岐
  2. タスクIDは変わらないので、バケット内のタスク一覧を取得するで行ったような単体でタスクIDを持って、再度現在のタスクの詳細情報を取得
  3. 改めて取得したタスクIDの中に新しい@odata.etagが入ってるはずなので、コレを取り出す。
  4. 取り出した@odata.etagを持ってして、再度削除や更新、タスク割当のリクエストを実行する

但しこれだと1タスクの削除に3回もリクエストを投げることになるので、初めからリクエストは最新のetag取得⇒リクエストの2回で行えば、大量に削除する場合は、結果的にリクエストコストを減らす事が可能です(本来は変更時に取得して差し替えておけば1回で済みますが、アプリ外で更新されると対処出来ない為。また起動時にバケットの情報を全部取ってくるのも量が多いと現実的ではないです。)

公式ドキュメントには詳細な内容が記載されていないので注意。

関連リンク

コメントを残す

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

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