VBAからGoogle APIを叩いてみる – IE11廃止対応版

Windows11では既にIE11が使えない為、VBAからのOAuth2.0認証時にIEを立ち上げて認証が出来ない為、こちらのエントリーにあるように「Puppeteerを使って認証するEXE」をNode.jsとPuppeteerを使って作り、クリアさせています。基本的には同じ仕組みなのですが、REST APIを提供するサイトによってちょっとずつ事なるので、困りものですが。

しかし、Windows10でも2022年6月には基本的にIE11が廃止となるため、このままだとGoogle APIを叩くのに認証が出来ず困ります。という事で、Google API用の認証用EXEを作成し、それをもってしてOAuth2.0を行わせ、Access Tokenを取得するものを作成しました。以下のサイトの改訂版となります。他のREST APIよりも複雑で、また以前の認証方法よりも複雑となっています。

※Googleの場合IE11対応自体を2021年10月に終了させているので、他のサイトに先んじてVBAからのIE11を使った認証も完了していました。

今回利用するファイル等

上記のファイルに、index.exeというGoogle API認証用のEXEが入っています。IE11の認証と違って不安定さが無く、また64bit絡みの問題も無いので非常に軽快に動作します。また、今回のEXEで使ってるPuppeteerですが、v11.0を利用していましたが、リダイレクトする部分で「no frame for given id found」のエラーが出てしまうバグがあったので、最新版のv13を使っています。

また、今回のGoogleの認証内容変更にはよりセキュアにする為のnonceパラメータの追加などやlocalhostなどへのループバック認証の廃止、オプションが多数追加されているので、必要に応じてそれらを追加実装すると良いでしょう。

事前準備

Google APIを利用するためには、Google Cloud Consoleより事前準備が必要です。また、企業内で利用する場合には、プロキシーサーバを使っているケースがあるので、そのプロキシーサーバのURLとポート番号がアクセスに必要になります。

プロキシー設定を調べる

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

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

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

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

Google側の設定を行う

ここでは、クライアントIDとクライアントシークレットを取得します。また、Google Apps Script APIを有効にします。以下の手順で取得しましょう。今回使用するスプレッドシートを開いて作業を行います。

  1. Google Cloud Consoleの認証情報作成を開く
  2. 認証情報を作成をクリックする
  3. OAuthクライアントIDを選択する
  4. クライアントIDの作成では、「ウェブアプリケーション」を選択する
  5. 承認済みのリダイレクト URIはVBA側と同じリダイレクトURLを指定します。
  6. 作成ボタンを押すと、クライアントIDクライアントシークレットが手に入るので控えておく。
  7. Cloud Consoleを閉じ、スクリプトエディタの画面ではOKを押して閉じる

これで必要な情報の半分が手に入りました。この2つは大切なものなので、漏れたりしないように保存しておく必要があります。また、今回のケースは内部利用目的なので、OAuth同意画面に於いては、ユーザの種類は「内部」とし、スコープでは、VBA側で指定してるものを指定しています。

※今回はOAuth同意画面の名称は「G Suite Applications」にしています

図:クライアントID、シークレット取得しておきましょう。

図:OAuth同意画面の設定

認証を実行するコード

ここまでの情報でOAuth2.0認証を実行し、Access Token他を取得する準備が整いました。今回はAccessを利用しているので、Access Token、Refresh Token、expireする時間を取得してテーブルに格納します(本来は第三者が容易にこのコードを知られないようにする仕組みを用意しましょう)。IE11で認証するケースとはちょっと異なり、今回はNode.js + Puppeteer + pkgにてWindows用のEXEを作っており、VBAから叩いてAuthcodeを取得する所まではそちらで対応しています。

ソースコード

VBA側コード

  • oauthurlおよびtokenurlがこれまでのものから変更になっていますので要注意です。
  • oauthpageのURL構築内容が変更になっており、access_type=offlineがここに入っていないと「refresh_token」が返ってきません。(これまではAccess Token取得側で指定していました)
  • また、同様に新規に「prompt=consent」を入れておかないと、最初の1回目以降、「refresh_token」が返ってきません(access_typeが指定されていても返ってこない)。無い場合で認証後、再度認証を行ってrefresh_tokenを取得したい場合は、Googleアカウントのアカウントにアクセスできるアプリから、OAuth同意画面で設定してる名称の「G Suite Applications」を削除しないといけません。
  • getAccessToken2の関数内では、access_type=offlineは無効なのでURL構築では入れない
  • 出力を受け取ったら、今回はDebug.Printで表示していますが、次の「Access Token」を取得する関数へ渡します。

Node.js側コード

今回は、command-line-argsおよびpuppeteer-coreを利用して、VBAからのコマンドライン引数で認証用URLとリダイレクトURLの2つを取得し、PuppeteerでGoogle APIの認証⇒Authenticate Codeの取得までを担当させています。それ以降はVBA単体で行えます。

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

  • 今回はコマンドライン引数は、1つ取得させています。
  • リダイレクトURLは1つ目の引数から分解して取得しています。
  • 受け取った引数のうち、%26の文字は「&」に変換します。理由はそのままVBA側から&で渡すと引数が壊れる為。同じく%20は半角スペースに変換するために、一括でdecodeURIComponentにてURLデコード処理をしています。
  • 成功するとリダイレクト先のURLにcode=でAuthenticate Codeが付与されている。このURLは2個目のタブにあるURLなので、allPages[1].url()で取得させています。response.request().redirectChain()などでは取れないので、要注意(responseの中にはそれっぽいURLがいるのだけれど)
  • code=以下を取得させていますが、ここは配列になっているので、ここからcode=の部分だけを取得させています。
  • 取り出したら、console.logで出力するとVBA側で拾ってくれる
  • ウェイトはsleep関数だけで行っています。
  • commandLineArgsというモジュール、非常に癖というかバグが強めで、2つ目の引数にリダイレクトURLを今回渡したにも関わらず、きちんとVBAからは受け取れていなかったり(BoxやGraphでは問題なく受け取れてるのだが)

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

作成してできた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にプロキシ通過の設定をしておくこと
  • 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側で「googleAuthorization」を実行します。今回のは認証までのコードのみしか入っていませんが、ソレ以降のGoogle APIを叩くコードやrefresh tokenでAccess Tokenを交換するコードはこれまでと同じです。

実行すると

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

という作業が行われます

図:承認画面

関連リンク

共有してみる:

コメントを残す

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

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