VBAからBox APIを叩いてみる - 準備編

基本普段はG Suiteユーザなので、以前ほどVBAを弄らなくなりましたが、会社では通信環境や開発環境が2000年レベルで制限されているので、VBAがいつまでも現役です。ウェブアプリケーション全盛の今でもVBAが現役で使えるのは、Web APIへアクセスする手段が昔からの手法で可能な面が大きいですね。

さて、そんなウェブサービスの一つに「Box」というストレージサービスがあります。正直一般ユーザではメジャーでもなんでもありません。また機能面でもGoogle Driveのほうが断然扱いやすく、学習コストも低い。このサービスもAPIがありますが、その情報源はほとんどが海外。とは言え、使わざるを得ない状況なので、VBAからAPIを使えるようにチャレンジしてみました。

※Access Token取得部分(getAccessToken, getNewToken)にて、urlencodeのheaderが追加しないと通らなくなっていたので、修正しました。

今回使用するファイルとライブラリ

新方式が登場しました

IE11の廃止に伴い、SeleniumやNode.jsやらといった手段を使わず、またPuppeteerと同様の手法(CDPを叩く)でVBAとEdge/ChromeのみでOAuth2.0認証する手段が登場しました。スクレイピングも可能になっています。以下のエントリーを参考にしてみてください。この手法は最も制限が無く、もっともすぐれた選択肢になると思います。

VBAでOAuth2.0認証 - 新方式を試してみた

IE11を使わない認証対応版

IE11廃止に伴い、本ページの認証部分については、IE11を使わない認証方法を利用する必要があります。この部分について、対応版を作成しました。以下のエントリーを参考に、Puppeteerを使った認証を利用すると、2022年6月以降も、VBAでBox API認証が可能になります。

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

事前準備

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

プロキシー設定を調べる

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

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

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

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

Box側の設定を行う

Box側ではClient IDやClient Secret等を作る必要性があります。以下の手順でBox Developers Portalにて作成しましょう。

  1. Box Developers Portalにログインする
  2. マイアプリにて「アプリの新規作成」をクリックする
  3. 次の画面では「カスタムアプリ」をクリックし、次へ進みます。
  4. 認証方法のページでは、「標準OAuth2.0」をクリックし、次へ進みます。
  5. アプリの名前は適当に設定し、「アプリの作成」をクリックします。アプリ名は同じものが設定できませんので注意!!
  6. アプリの表示をクリックして設定データを取得しておきます。
  7. OAuth2.0資格情報の欄にて、クライアントIDおよびクライアント機密コード(Secret)をコピーして控えて置きます。
  8. OAuth2.0リダイレクトURIですが、https://localhostでも良いのですが、Internet Explorerを使うので、この設定ですとAccess Tokenが取れない場合があります。(故に今回はこのサイトを指定しました:https://officeforest.org/)
  9. アプリケーションのスコープでは、許可するアクションにチェックを入れます。
  10. 高度な機能では、「ユーザとして操作を実行」を有効にします。
  11. CORSドメインはウェブなどで利用する場合に許可送信元として入力する場合にそのサイトのドメインを入れておきます。
  12. 変更を保存するボタンをクリックして完了

図:リダイレクトURLの設定が嵌りどころだったりします。

認証を実行するコード

ここまでの情報でOAuth2.0認証を実行し、Access Token他を取得する準備が整いました。今回はAccessを利用しているので、Access Token、Refresh Token、expireする時間を取得してテーブルに格納します(本来は第三者が容易にこのコードを知られないようにする仕組みを用意しましょう)。

ソースコード

  • IE11で動作検証しています。URLのリダイレクトURLのあとにcode=という形で、認証コードが帰ってきます(Access Tokenじゃないいですよ)。
  • 認証コードを持って、getAccessTokenに渡し、Access Tokenを取得させています。
  • 取得したAccess Token、Refresh Token、expire_in(有効期限)、取得時刻、期限切れ時刻をテーブルに格納しています。
  • expireする時刻はおよそ60分後です。
  • expireした場合には次項で紹介する、refresh Tokenを使ったAccess Tokenの取得を実行するようにします。
  • プロキシーを使わない場合には、setProxyの行をコメントアウトしてください。
  • Access Tokenの取得はWinHttpRequestを使ってPOSTで送信します。

認証の実行

上記のboxAuthorizationを実行すると、IEが起動しBoxのログイン画面が出ます。そこでログインに認証を実行すると、Access Tokenが取得されて、tokeninfoテーブルにそれぞれデータが追記される仕組みになっています。

デバッグしてみると、最後リダイレクト先に遷移した際のURLの後半に認証コードが含まれています。リダイレクト先がlocalhostの場合、ここが失敗してcodeが入っていないことがあります。

例:https://officeforest.org/wp/?state=authenticated&code=ここに認証コードが入ってる

図:Boxへのログイン画面が出てきます。

図:認証を要求してくるので許可しましょう。

期限切れ時に再度Access Tokenを取得する

およそ60分でAccess Tokenは失効するようになっています(これはOAuth2.0の共通の仕様です)。そのため、この実装のままでは再度利用しようとした時にはすでにそのAccess Tokenは失効済みで、もう一度認証を実行しなければなりません(セキュリティを求めるならばこのままの仕様でも良いかもしれませんが)。

しかし、OAuth2.0ではその手間を排除するために、Refresh Tokenを使って再びAccess Tokenを取得する仕組みが備わっています。そのためには現在のAccess Tokenが失効しているかどうか?チェックする仕組みも必要です。この項目ではこの2つを実装します。

失効してるかどうかチェックするコード

  • Boxは60分でAccess Tokenが失効します。
  • また、取得した日から60日で再度認証を要求する仕様になっているので、60日リミットチェックも必要です。
  • 3種のフラグで受け取った側で処理を分岐させてあげます。

再度Access Tokenを取得するコード

  • ほぼAccess Tokenを取得するコードと同じですが、dat変数の中でrefresh tokenを指定しています。
  • 再び新しいAccess TokenとRefresh Token等がもらえるので、tokeninfoテーブルに格納しています。

Office 64bit対応

本コードは、64bit VBAですと、boxAuthorization()にて問題がでます。問題の箇所はInternet Explorerのobject生成時にあり、以下のようなエラーが出ます。また、この問題はIEの保護モードの有無でも生じる場合がある。32bit VBAでは上記のコードで問題なく動くのですが。

図:こんな感じのエラーがBusyやreadystateのあたりで出る。

これを解消するには、以下の手順で修正します。

  1. 参照設定で「Microsoft Internet Control」を追加する
  2. CreateObject("InternetExplorer.Application")を、New InternetExplorerMediumに置き換える

ただしこの方法には問題があって、起動し確かにAuthcodeが取得できるんですが、.Quitで閉じる事ができません。IEが起動した直後、セキュリティ中レベルでのインスタンスに切り替わり、プログラム側から操作が出来なくなる為。閉じられなくなると困るのでInternetExplorerMediumを利用した場合には以下のようなコードになります(起動しているiexplorer.exeを閉じるループを回しています)。

また、最後3秒間ほどsleepを入れていますが、これも入れておかないとIEが閉じませんので、入れてあります(msgboxを適当に表示しても閉じるのですが、msgboxだと邪魔なので)

素直に以下の作業を行い、32bitのIEを起動するようにしましょう。

  1. インターネットオプションを起動する
  2. 詳細設定タブをクリックする
  3. 拡張保護モードで64bitプロセッサを有効にするのチェックを外す
  4. 拡張保護モードを有効にするのチェックを外す。
  5. これでコードを実行してみる。32bit版IE11なので問題なく動くはず。

この作業を各クライアントでやってもらうというのも不毛なので、レジストリを操作するモジュールを利用して、VBAからセットしてやる方法がスマートです。以下のコードでこの2つの設定を無効化する事が可能です。

関連リンク

VBAからBox APIを叩いてみる - 準備編” に対して4件のコメントがあります。

  1. きっちゃん より:

    いつも参考にさせて頂いております。
    こちらのコードを元にアクセストークンを取得しようとしているのですが、
    どうしても”invalid_request”が発生し取得できません。
    認証コードまでは問題なく取得できています。

    使用しているエクセルは「Excel 2016 64bit版」です。
    アドバイスが頂けますと幸いです。

    1. akanemaru2017 より:

      よーく調べてみたら、変更箇所1箇所でした。
      getAccessTokenとgetNewTokenの2つの関数に於いて、WinHttpRequestのヘッダー追加において

      .setRequestHeader “Content-Type”, “application/x-www-form-urlencoded”

      を追加するだけでOKでした。。。

      コードも追加しておきました。

      1. きっちゃん より:

        !!!
        私もご指摘を受けて、Boxのガイドで確認できました。。。
        確かにトークンが取得できないんだから、POST通信の様式が違うはずなんですよねorz

        大変ありがとうございました。
        今後ともどうぞよろしくお願いいたします。

        1. akanemaru2017 より:

          ちょっと前までは、urlencodeなしだったのが、有りに変わっただけで良かったです。
          JSONでコーティングして送るとかはちょっとVBAでは面倒ですから。

          修正できてよかったです

コメントを残す

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

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

Alexa

前の記事

AlexaスキルとGoogle連携