VBAからGoogle Driveにファイルをアップロードする

前回、GASでデータからGmailにメールをInsertするコードを作りました。この元データになるExcelのデータおよび添付ファイルのアップロード部分がまだ未作成の部分。ここはVBAで行う必要があります(eml形式にしてから取り込みは難しい)。

事前にOAuth2.0認証が必要ですが、Drive API v3を叩いてアップロードし、ファイルのIDを取得しExcelに記述するまでを行います。ファイルのIDを記述できたら、ExcelのデータをGoogleスプレッドシート側に追記するところまで実装します(とりあえずmainシートのみ)。

Google Apps ScriptでGmail APIにてメールデータを変換して追加する

今回利用するファイル等

上記のファイルに、index.exeというBox API認証用のEXEが入っています。IE11は既に廃止されてるので、Puppeteerとpkgにて作成したEXEを使ってOAuth2認証を行わせます。

今回はファイルサイズ制限が5MB以下の「シンプルアップロード」を利用し、ファイルID取得→ファイル名変更・格納場所変更の2回でアップロードを実行します。

※認証後のAccess Tokenはsetting.iniファイルに直書きで記録しているので、実用する場合は暗号化するなり安全な場所に保存するなりの作業が必要になります。

図:リボンに作業コマンドを集約

事前準備

参照設定

今回はVBA-JSONを利用している為、VBAの開発画面にて、参照設定が必要になります。以下の手順で有効化しておきましょう。

  1. 開発画面を出す
  2. メニューよりツール→参照設定を開く
  3. Microsoft Scripting Runtimeを探してチェックを入れてOKをクリックする

この設定はDictionaryでも利用したりするので、有効化しておくと色々捗るでしょう。

図:参照設定で有効化が必要です

認証関係の処理

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

以下のエントリーを参考に、GoogleからClient IDおよびClient Secretを取得します。その後、認証用のコードを実行して、Access Tokenを取得します。但し今回はアップロードが含まれているので、scopeは以下のようになります(https://www.googleapis.com/auth/driveが必要です。)

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

データの構造について

前回の記事に関係してるため、今回のサンプルは以下の2つのシートによって構成されています。

  • メインとなるメールデータの1つずつの内容を記録したデータ(mailというテーブル名)
  • サブとしてそのメールに添付する複数の添付ファイルへのファイル名を記述した内容(filesというテーブル名)
  • mimeというシートで拡張子に応じたMIMETYPEを返すテーブル(mimeというテーブル名)

メインとサブは同じIDで連結されており、サブ側はファイルIDの欄が空のままになっているので、ここにファイルIDを追記するのが今回の目的の1つです。データ領域はテーブル化してあります。テーブルについては以下のエントリーを参考にしてみてください。

図:単純なテーブルにしてあります

Excelではテーブルとクエリを活用すると楽になる

ソースコードと解説

ファイルのアップロード

ファイルのアップロードはDrive API v3にてアップロードします。バイナリデータであるため、ちょっと扱いが難しいです。

ソースコード

解説

今回はファイルサイズ上限5MBのシンプルアップロードという方式でアップロードしています。また、この場合2段階に分けてリクエストをしてアップロード→リネーム&格納場所へ移動を行う必要があります。この他にmultipartとresumableの2つの方式があり、5MB以上のものをアップロードする場合には、resumableの方式で構築が必要です(この場合、1度のアップロードでファイル名や格納先を指定できる)

  • upfolderに格納先のフォルダのIDを入れる必要があります。
  • アップロード時にリクエストのcontent-typeに対象ファイルのMIMETYPEが必要なので、mimeに使うファイルのMIMETYPEを事前に格納しておく必要があります。
  • MIMETYPEはファイルの拡張子から判定するcheckmime関数で返すようにしています。
  • subのテーブルデータを取得し、Access Tokenを取得してループでリクエストを回します。
  • 1回目のリクエストでアップロードします。POSTで送信し、Streamを利用してファイルを取得し、Content-Lengthでファイルサイズの指定が必要です。
  • アップロードが成功するとレスポンスの中にファイルIDが入ってるので、CallByName関数で取り出します。
  • 2回目の取得はDictionaryでリクエストBodyを作成し、違うエンドポイントでPATCHにてリクエストを送ります。
  • このときのエンドポイントにaddParentsとフォルダのIDを指定することで、指定のフォルダにファイルが移動します。
  • 移動とリネームが完了したら、ExcelファイルへファイルのIDを書き込みします。
  • 連続してリクエストすると429エラーになることがあるので、3秒間のSleepをリクエスト間に入れてあります。

シートデータの送信

シートデータの送信は、Sheets API v4を使って送信します。送信するデータはJSONにする必要がある点と、2次元配列を作る必要があるため、そこがネックになります。GASでSheets APIを使う手法は以下のエントリーを御覧ください。

Google Apps ScriptでSheets APIを使ったら爆速だった【GAS】

追記する形でデータを送信する

ソースコード

解説

今回はExcelのmainシートの内容を、Google Spreadsheet側のmainシートに「追記型」で記入するようにしています。追記なのでデータが存在する場合、一番下の場所から自動的に判定して入れてくれます。書き込み先スプレッドシートは冒頭に掲示してるものを使っています。

  • rangeman変数には、シート名!書き込み開始セル番地を指定します。これでシート名と配列データの書き込みする左上のポイントが指定出来ます。特にバッチリサイズを合わせる必要は有りません。
  • リクエストエンドポイントに「?valueInputOption=USER_ENTERED」を追加することで、値の型を判定して挿入してくれます。ROWを指定した場合は、変換せずそのまま追記します。
  • テーブルデータを取得して、1行文のdataCollコレクションにデータを追加。これが1次元目の配列になります。
  • 都度、bufCollにdataCollを追加。これが2次元目になります。
  • 最後にbufmanに対してvaluesとしてDictionaryを構築して、bufCollを追加することでリクエストするJSONオブジェクトを構築出来ます。
  • POSTでリクエストをし、JsonConverter.ConvertToJsonでbufmanをJSON化して送信。200が返ってくれば無事に書き込みが完了です。

図:無事にデータの送信が出来ました。

構築したJSONのサンプル

Debug.Print JsonConverter.ConvertToJson(bufman)でデバッグ出来ます。スプレッドシートのデータをvaluesの配列の中に、1行ずつ配列を加えて、二次元配列の形にしています。

複数のシートデータをまとめて送信する

ソースコード

解説

今回はExcelのmainおよびsubの2つのシートをまとめて送信して、「上書き型」で記入するようにしています。上書きであるため、書き込む範囲をしっかり指定する必要があるのと、データは上書きされてしまうので要注意です。

  • 前述に加えて、最後にsheetmanというcollectionにbufmanを加える形にしてあります。
  • 前回と異なり、valuesだけでなくrangeでmain!A2:F3といった形での指定が必要です。そのためにテーブルからDataBodyRange.SpecialCells(xlCellTypeVisible).Addressで範囲を取得しています。
  • 次のsubシートを処理する前に、bufmanとbufcollを初期化してからでないとデータが混ざってしまうので初期化します。
  • subシートも処理が終わったら最後に、mastermanというDictionaryの「data」にsheetmanを加えてデータの構築は完了
  • リクエストエンドポイントは前回と異なり、「https://sheets.googleapis.com/v4/spreadsheets/シートID/values:batchUpdate」となります。
  • リクエストの処理そのものは、前述のものと同じです。
構築したJSONのサンプル

前述とは違い、dataの中に複数のvaluesの中身とRange指定を追加する必要があります。故にそこをCollectionとDictionaryの2つでうまいこと構築するのがVBAでのポイントになります。

関連リンク

コメントを残す

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

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