AccessでGoogle認証とファイルのダウンロード

現在、VBAで業務用アプリケーションを作成していますが、アップデータの配布に於いてイントラネット内に配置ですと、外部ではアップデートが出来ないことになります。かといって、VPNの設定などをして入れるようにするというのも考えもの。ということで、アップデータの配布にG SuiteのGoogle Driveを使えないか?

ということで、VBAにてGoogle認証をし、Drive APIを叩いてファイルをダウンロードする手段を考えました。Client IDや
Client Secretがあっても今回の手法の場合、組織内のアカウントでない場合、認証が通らずダウンロードが出来ません。また、この手法の場合、ファイルへのアクセス権が全くなくとも、ファイルオーナーの自分だけはダウンロードが可能になったりします。とても便利。今回はAccess VBAにて作成しています。

今回使用するファイルや資料

新方式が登場しました

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

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

IE11を使わない認証対応版

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

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

事前準備

プロジェクトの作成

今回は、Google Apps Scriptは使用しません。が、Google Drive APIは必要になります。そのため、Google Cloud PlatformにてDrive APIの有効化とClient IDおよびClient Secretが必要になります。以下の手順でプロジェクトを作成します。

  1. Google Cloud Platformにアクセスする
  2. ページの上部、Cloud Platformの文字の横にある▼をクリックする
  3. 出てきたダイアログの右上、「新しいプロジェクト」をクリックする
  4. プロジェクト名を設定し、作成ボタンをクリックします。
  5. 左サイドバーのAPIとサービスをクリックします。
  6. 検索にて、Drive APIを検索し、有効にするをクリック。
  7. 左サイドバーの認証情報をクリックします。
  8. 認証情報を作成をクリックします。
  9. OAuthクライアントIDを選択します。
  10. OAuth クライアント ID を作成するには、まず同意画面でサービス名を設定する必要があります。」と出るので、同意画面を設定をクリック。
  11. OAuthの同意画面では、アプリケーションの種類を今回は「内部」にしておきます。これで組織外のユーザは認証出来ません。
  12. アプリケーション名を適当に設定し、保存をクリック。
  13. 次の画面では、アプリケーションの種類を選びます。ここでは、「その他」を選択します。名前は適当につけて、作成をクリックします。
  14. ここで、OAuthクライアントIDとシークレットが出るので控えておきます。

図:プロジェクトを作成しましょう。

図:組織内のユーザに限定しちゃいましょう。

図:重要なコードなので他人に開示したりしちゃだめです。

ダウンロードファイルの準備

事前にGoogle Driveにファイルをアップロードしておきます。ファイルは今回G Suite内のユーザ共有か?自分だけがアクセスできる状態にしておきます。前者であれば「リンクを知ってるxxxの全員が閲覧可」や後者であれば、「オフ - 特定のユーザだけがアクセス出来ます」の状態にしておきます。

また、アップロードが完了したら、ファイルのIDを控えておきます。ファイルIDはURLの中で、/d/の後ろから/viewの前までの文字列。これが今回VBAからダウンロードするファイルとなります。

例:https://drive.google.com/file/d/1pB38TVRqX7BnxU294RpTMhSsFkXBkwEx/view?usp=sharing

図:ファイルは限定公開の状態にしておきます。

ソースコード

OAuth認証の為のコード

  • Authgoogleを実行することで、認証が実行されます。
  • 続けて、GetAutherizationCodeが実行されて、IEが立ち上がりログインを要求してきます。
  • sleepを入れている理由は、さっと閉じてしまうと認証結果のコードを取りそこねるのを防ぐためです。
  • checkAccessTokenは、Access Tokenがexpireしていないかチェックを行い、expireしている場合には、refresh Tokenを使って自動でAccess Tokenを取り直しています。
  • 取得したAccess TokenおよびRefresh Tokenはsettingテーブルに格納しています。
  • 2019年1月1日より、ブラウザ上で表示されるAuthrization Codeの取得の為の仕様が変更になっているため、旧来のgetElementById(“code”)でelement指定しても取れません。auth_code = .Document.getElementsByClassName(“qBHUIf”)(0).valueといった形でClassName指定で取得するようにしています。

ファイルのダウンロードの為のコード

  • WinHttpRequestでリクエストするURLはDriveのファイルダウンロードURL直にアクセスさせます。
  • Box APIを叩くでも利用したコードにて、バイナリファイルを組み立てて、カレントディレクトリに保存しています。
  • DecodeURLMSHTMLという関数を利用しています。利用する為には、参照設定にて「Microsoft HTML Object Library」が必要です。
  • ファイル名などの情報は、Content-Dispositionヘッダー内に含まれていて、ここから取り出します。ヘッダの内容は以下のような感じ

実行と結果

今回のファイルはAccessで作成していますが、主要なコマンドはリボンに組み込んであります。あらかじめ、Google Cloud Platformで控えておいた「Client ID」および「Client Secret」またGoogle DriveのファイルのIDを記述しておき、以下の手順で実行します。

  1. Google認証を実行し、Googleへログインする
  2. settingテーブルにAccess TokenとRefresh Tokenが格納されます。
  3. 続けて、ドライブファイルダウンロードを実行すると、このファイルと同じフォルダ内に対象のファイルがダウンロードされます。

図:これでオンライン配信がより確実に行える!!

Accessファイルのオンラインアップデート配信では、XMLファイルおよびアップデート用のインストーラやZIPファイルをGoogle Driveに格納し、外部ユーザに限定配信(組織のユーザのみ)が可能なので、VPNといった仕組みが不要です。対象のアカウントが削除されれば、Access Tokenは無効化されます。

※つまり、バージョン確認用のXMLのアクセスに1回、ファイルのダウンロードに1回の合計2回はファイルのダウンロードを実行する必要があります。

関連リンク

コメントを残す

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

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