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

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

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

事前準備

プロジェクトの作成

今回は、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テーブルに格納しています。

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

  • 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回はファイルのダウンロードを実行する必要があります。

関連リンク