VBAとMicrosoft Graph APIの連携 – Excel Online編
Microsoft 365にはSharepointおよびOneDrive Businessにて、ExcelのファイルをExcel Onlineという形で利用が可能です。Boxのようなストレージサービスと異なり、Googleスプレッドシート同様に同時に読み書きが可能であるため、読み書きがバッティングしてファイルが分裂なんてこともない(但し、SharePointでは過去、Excelファイルが破損するというのが報告されている)
この仕組を利用しつつ、ローカルのExcelからExcel Online上のファイルの読み書きが出来ればアプリ作成の幅が広がるのではないか?ということで、例の如くGraph APIを利用して読み書きが出来ないか?やってみることにしました。アクセストークン取得〜Teamsログ取得まで装備しています。
目次
今回利用するサービスやファイル等
- Graph APIで読み書きサンプル - Excelファイル
- サンプルの読み書き用Excelファイル
- OneDrive Business
- Excel Online
- Microsoft Graph API
- Puppeteer-core
- pkg - npm
- Graph API認証用のEXE
今回は、いつものIE11を使っての認証ではなく、以下のエントリーにあるようにPuppeteerを利用したOAuth2.0認証をするように変更しています。Node.jsにて作成し、EXEにパッケージ。これをVBAから叩いて利用しています。
Graph API認証用のEXEはサンプルファイルと同じディレクトリに入れておく必要があります。
※公式ドキュメントでExcelの項目にたびたび出てくる「session_id」とは、一般的にはAzure ADで作成してある「Client ID」の事です。これが説明無しに出てくるので、MSのドキュメントは非常に読みにくい。
新方式が登場しました
IE11の廃止に伴い、SeleniumやNode.jsやらといった手段を使わず、またPuppeteerと同様の手法(CDPを叩く)でVBAとEdge/ChromeのみでOAuth2.0認証する手段が登場しました。スクレイピングも可能になっています。以下のエントリーを参考にしてみてください。この手法は最も制限が無く、もっともすぐれた選択肢になると思います。
事前準備
これまで、ElectronやGoogle Apps ScriptなどでGraph APIを叩く準備は構築してきていますが、VBAからも同じような形で事前準備が必要になります。以下の手順でClient IDとClient Secretを取得します(実用時はAccess Tokenなどの暗号化などの対処が別途必要になります)。
プロキシー設定を調べる
企業内で使う場合、ウェブアクセスにプロキシーを使ってる場合には、VBAからアクセスする場合もその設定を利用する必要性があります。プロキシーを経由しなければ外に出ることができないので、プログラムが動作しません。プロキシーの設定はいろいろなパターンがありますが、一般的な設定の調べ方は以下の通り。
サーバーのアドレスとポート番号について、http://を除外して、コロンでポート番号でつなげて利用します。(例:hiroproxy.net:8080)
- コントロールパネルより「インターネットオプション」を開く
- 「接続」タブを開き、「LANの設定」を開く
- この画面でプロキシーサーバの部分にアドレスとポート名が入ってるならばこれを控えておく。
- 場合によっては、詳細設定の中の「HTTP」で指定してるサーバーアドレスとポート番号を控えておく。
- 自動構成スクリプトを使ってる場合、そこに指定されてるアドレスのファイルの中に、様々なプロキシーアドレスが入っていますので、それを一旦ダウンロードして中身をテキストエディタで開いてみる(通常はpacというファイル)
- 5.のケースの場合、pacファイル内はIF文を使ってアクセスするサイト別にプロキシーが設定されてることが多いので、もっとも一般的なサイトアクセスもしくはBoxについてだけ定義している場合には、そのサーバーアドレスとポート番号を控えておく。
図:プロキシー設定がない場合はこの作業は不要です。
Azureでプロジェクトを作成
- アプリの登録にて登録を開始する
- 新規登録をクリックする
- 名前を入力(今回はexcelmanと入力しました)、リダイレクトURIは「webを選択」し、今回URLはこのサイトのURLを入力。http://localhostでも良いのですが、Internet Explorerを使うので、この設定ですとAccess Tokenが取れない場合があります。
- 登録ボタンをクリックする
- 出てきた中で、「アプリケーション(クラと書かれているのがクライアントID」なので、このコードをメモしておく
- 左サイドバーより、「証明書とシークレット」をクリック
- 「新しいクライアントシークレット」をクリックする
- 今回は特に有効期限を設けないで追加をクリック
- これで「値」に「クライアントシークレット」が生成されて手に入りました。このシークレットはこの時だけしか表示されないので、注意してください(IDは不要なのでメモらなくていいです)
- つづけて、左サイドバーより「APIのアクセス許可」をクリックする
- Microsoft APIの中にある「Microsoft Graph」をクリックする。
- 「委任されたアクセス許可」をクリックする
- デフォルトでUser.ReadがすでにONなので、今回はopenid, offline_access, User.ReadBasic.All, Files.Read, Files,ReadWriteを検索してONにしましょう。
- アクセス許可の追加をクリックする
- 追加出来たら、xxxxxに管理者の同意を与えますをクリックします。すると、状態が緑色になります。今回は管理者の権限を要求するものは無いのでしなくても大丈夫だと思う
- 次に左サイドバーより「認証」をクリック
- 暗黙の付与にて、「アクセストークン」にチェックを入れる
- サポートされているアカウントの種類に於いては、「マルチテナント」にしておきました。
- 保存をクリック
- 概要のエンドポイントをクリックすると、いろいろなエンドポイントURLが出る。
- 概要のディレクトリ(テナントの数値はメモっておきます。あとでプログラム中で使用します。
- デフォルトでは組織アカウントでなければOAuth2.0認証が出来ません。
※3.でWebを選ばないSPAを選んでしまうと、Proof Key for Code Exchange by OAuth Public Clientsといったエラーが出てしまい認証ができませんので注意。
図:アプリの登録から全ては始まります。
図:Graphを選択する
図:アクセス権限付与した状態
図:認証の設定変更に注意
Excelサービスの制限
OutlookやTeamsのサービス利用時同様に、Excelの読み書きに対してもやはりQuotaというものが設定されています。とはいえ、シンプルな制限で以下の2つが設定されています。
- 全テナント毎アプリ別 - 10秒間に5000リクエストまで
- テナント毎アプリ別 - 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側コード
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
'OAuth認証用 Public Const tenant As String = "ここにテナントIDを入れる" Public Const client_id As String = "ここにクライアントIDを入れる" Public Const client_secret As String = "ここにクライアントシークレットを入れる" Public Const redirecturl As String = "ここにリダイレクトURLを入れる" Public Const oauthurl As String = "https://login.microsoftonline.com/" Public Const tokenurl As String = "https://login.microsoftonline.com/common/oauth2/v2.0/token" Public Const endpoint As String = "https://graph.microsoft.com/beta/" ’利用してるスコープ(スペースで区切るけれど、%20で置き換える) Public Const scope As String = "User.Read%20offline%20access%20User.ReadBasic.All%20Files.Read%20Files.ReadWrite" 'Box APIの認証を行う Public Function GraphAuthorization() 'iniファイルからidとpassを読み込み Dim authcode As String 'WSHの用意 Dim WSH, wExec, sCmd As String, Result As String Set WSH = CreateObject("WScript.Shell") '認証用URLを構築 Dim oauthpage As String Dim param As String 'パラメータは&は%26として渡さないと引数が壊れる(スペースは param = "%26response_type=code%26scope=" & scope & "%26state=91556%26redirect_uri=" & redirecturl oauthpage = oauthurl & tenant & "/oauth2/v2.0/authorize?client_id=" & client_id & param 'コマンドラインの組み立てと実行 sCmd = ThisWorkbook.Path & "\index-win.exe -g " & oauthpage Set wExec = WSH.Exec("%ComSpec% /c " & sCmd) 'ステータスを見てループ Do While wExec.Status = 0 DoEvents Loop '標準出力内容を取得 authcode = wExec.StdOut.ReadAll '取得したコードからcode=以下を取得する Debug.Print authcode '終了処理 Set wExec = Nothing Set WSH = Nothing End Function |
- 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認証だとちょっとコードが足らないので、追加しています。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 |
//使用するモジュール const puppeteer = require('puppeteer-core'); var fs = require('fs'); const path = require("path"); var shell = require('child_process').exec; var spawnSync = require('child_process').spawnSync; const commandLineArgs = require('command-line-args'); //edge/chromeのパスを取得(ユーザ権限インストール時) const userHome = process.env[process.platform == "win32" ? "USERPROFILE" : "HOME"]; var kiteipath = "C:\\Program Files (x86)\\Google\\Chrome\\Application\\chrome.exe"; var temppath = path.join(userHome, "AppData\\Local\\Google\\Chrome\\Application\\chrome.exe"); var edgepath = "C:\\Program Files (x86)\\Microsoft\\Edge\\Application\\msedge.exe"; //chrome場所判定 if (fs.existsSync(kiteipath)) { chromepath = kiteipath } else { if (fs.existsSync(temppath)) { chromepath = temppath; } else { //Chromium Edgeの場合に対応 if(fs.existsSync(edgepath)){ chromepath = edgepath; }else{ //IEを起動してChromeのインストールを促す shellexec('start "" "iexplore" "https://www.google.co.jp/chrome/"') return; } } } //コマンドライン引数を取得 const optionDefinitions = [ { name: 'geturl', alias: 'g', type: String } ]; const args = commandLineArgs(optionDefinitions); //puppeteer実行 main(); //puppeteerメイン関数 async function main() { const browser = await puppeteer.launch({ headless: false, executablePath: chromepath, ignoreDefaultArgs: ["--guest",'--disable-extensions','--start-fullscreen','--incognito',], slowMo:100, }); //引数からURLを取得する var tempurl = args.geturl; //redirecturlを取得する var redirect = tempurl.substr(tempurl.indexOf('redirect_uri=') + 13); //%26を&に変換する var url = tempurl.replace(/%26/g, "&"); //pageを定義 const page = await browser.newPage() const navigationPromise = page.waitForNavigation() //ログインページを開く const response = await page.goto(url) await page.setViewport({ width: 1300, height: 900 }) await navigationPromise //特定要素が出てくるまでウェイト(タイムアウトは90秒を指定) await page.waitForSelector('#idBtn_Back',{timeout:90000}) //パスワード画面だけはユーザに入力させる var result_input = await waitEvent(page); await navigationPromise //現在のURLを取得 var allPages = await browser.pages(); var ret = allPages[1].url(); await navigationPromise //リダイレクトURLが含まれているかチェックし、含まれるまでループ(二段階認証対応) var rcheck = ret.indexOf(redirect); while(rcheck == -1){ //再度、URLを取得 allPages = await browser.pages(); ret = allPages[1].url(); //リダイレクトURLが含まれているかチェックし、含まれるまでループ rcheck = ret.indexOf(redirect); //rcheckが-1の場合はウェイトを入れる if(rcheck == -1){ await page.waitForNavigation('networkidle2'); } } //code=以降の文字列を取得する var cutman = ret.substr(ret.indexOf('code=') + 5); cutarr = cutman.split("&"); //Authenticate Codeを出力する console.log(cutarr[0]); //ブラウザを閉じる await browser.close() } //入力イベント待ちをする関数 async function waitEvent(page){ return new Promise(async resolve=>{ //chromeに一時的な関数を作って送り込む(funcmanという名前にしました) //何度も使い回す場合は、event名を重複しないようにする必要があります。 await page.exposeFunction("funcman",()=>{ //result_inputへ値を返す resolve("ログインできた"); }); //ボタンにクリックすると一時的な関数を実行するイベントを割り当てるコードを実行 await page.evaluate(()=>{ document.getElementById("idSIButton9").addEventListener("click",()=>{ //ダミーのイベント eval('window.funcman();'); }); }); }); } |
- 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関数あたりでエラーが出てしまうので、注意。
1 |
pkg index.js -t win --public |
作成してできた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にプロキシ通過の設定をしておくこと
12npm config set https-proxy https://hogehoge.com:8080npm config set proxy https://hogehoge.com:8080 - 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の読み書きを実装します。
実行すると
- 用意しておいた認証用URLをindex-win.exeに引き渡す
- Puppeteerにてインストール済みのChromeもしくはChromium Edgeが起動し認証ページが開かれる
- ログイン作業をして、「サインインを維持したままにするか?」では「はい」をクリックする
- リダイレクトURL先にcode=付きのURLで飛ばされて、Authenticate Codeを取り出す
という作業が行われます。
次回実装編は以下のエントリーより
図:サインインは維持する
関連リンク
- Office365 Excelの共有ブック機能は非推奨機能になり共同編集が主流に
- SharePointで起こりうる不具合と解決策
- SharePoint上のEXCELファイルが破損してしまう
- Microsoft Graph での Excel の操作
- Microsoft Graph 調整ガイド
- WebDriverManager-for-VBA
- How to authenticate Microsoft Graph on behalf of user in Dialogue Studio
- How to get the current page in Puppeteer?
- Get all navigation redirect urls
- TimeoutError: Navigation timeout of 30000 ms exceededの解消方法
- Puppeteerでリダイレクト元URLとリダイレクト先URLを取得する方法
- How To Get The URL After Redirecting from Current Page To Another Using Puppeteer?
- コマンドラインからGitを使う(for Windows)
- patchコマンドの簡単な使い方
- Passed function is not well-serializable
- Working with Sharepoint Online Excel files in Graph API