Microsoft365

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

2021年11月16日 / 最終更新日時 : 2025年1月1日 officeの杜 Microsoft365

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

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

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

1 今回利用するサービスやファイル等
2 新方式が登場しました
3 事前準備
4 認証を実行するコード
- 4.1 ソースコード
  - 4.1.1 VBA側コード
  - 4.1.2 Node.js側コード
- 4.2 単一実行ファイルを作成する
  - 4.2.1 pkgコマンドの注意点
  - 4.2.2 nexeコマンドの注意点
5 認証を実行する
6 関連リンク

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

今回は、いつもの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）

コントロールパネルより「インターネットオプション」を開く
「接続」タブを開き、「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というものが設定されています。とはいえ、シンプルな制限で以下の２つが設定されています。

全テナント毎アプリ別 - 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側コード

'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認証だとちょっとコードが足らないので、追加しています。

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

//使用するモジュール
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を取得する仕組みに投げて上げれば良い。

単一実行ファイルを作成する

Node.js 18よりSingle executable applicationsという機能が装備され、標準で単独実行ファイルが作成できるようになりました。結果pkgはプロジェクト終了となっています。よって、以下のエントリーの単一実行ファイルを作成するを参考に、Node18以降はexeファイルを作成することが可能です。

Puppeteerを使ってX(旧Twitter)へのポストを自動化する

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関数あたりでエラーが出てしまうので、注意。

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にプロキシ通過の設定をしておくこと
```
npm config set https-proxy https://hogehoge.com:8080
npm 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を取り出す

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

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

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

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

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

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

新方式が登場しました

事前準備

プロキシー設定を調べる

Azureでプロジェクトを作成

Excelサービスの制限

認証を実行するコード

ソースコード

VBA側コード

Node.js側コード

単一実行ファイルを作成する

pkgコマンドの注意点

nexeコマンドの注意点

認証を実行する

関連リンク

コメントを残すコメントをキャンセル

Formsからの投稿でOneDriveのExcel書き込みとカレンダー登録

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

新方式が登場しました

事前準備

プロキシー設定を調べる

Azureでプロジェクトを作成

Excelサービスの制限

認証を実行するコード

ソースコード

VBA側コード

Node.js側コード

単一実行ファイルを作成する

pkgコマンドの注意点

nexeコマンドの注意点

認証を実行する

関連リンク

コメントを残す コメントをキャンセル

Formsからの投稿でOneDriveのExcel書き込みとカレンダー登録

WordPressのスピードチューニングをしてみた

コメントを残すコメントをキャンセル