Google Spreadsheetの表データをxlsxファイルを経由してにMicrosoft Accessに渡してみた

経緯

私の職場ではこれまで会計等の記録や統計をGoogle Spreadsheetに入力することで行ってきた。

しかしながら最近では購入項目や項目理由が細分化し、表で管理されているデータではレポート作成にはその都度かなり時間をかかっている。

Google Spreadsheetに備わっているquery関数を用いて管理するのも悪くはなかったが、ほぼ手動的であるレポートシステムになることには変わりない上、Microsoft Excelにない関数を他の職員に覚えさせるのは難しい。そこでMicrosoft Accessの機能である関数の知識を要しないクエリの作成機能とレポート機能によって、UIベースでこれまでの集計から報告までのプロセスを改造しようと考えた。

必要になったもの

• Google Spreadsheetの表データ
• Microsoft Accessが読み取り可能な形式への成型
• Google Apps Script(GAS)の作成
• (これじゃなくてもいいが)Google Backup and Sync

設計

• Microsoft Accessが読み取り可能なデータはxlsおよびxlsx、CSV、XMLやHTML (VBAによってjsonなども読めるらしいが今回はしない) なので今回はおそらく一般に馴染みのあるxlsxを選択する
• データへの接続のためにMicrosoft Accessの VBAをコーディングしない(Google Drive APIを用いて可能だが、長いコードになる上OAuthの知識が必要なので僕がいなくなる時に楽に継承できるものではない)

このため、現実的にはGoogle Spreadsheetをなんらかの方法でxlsxに変換、これを自動更新させた上、Microsoft Accessの標準機能でこのxlsxを参照できる様な場所(ローカルもしくは別の標準で接続可能なクラウドストレージ)に置いておく必要がある。

今回はGASとトリガーを使ってSpreadsheetからxlsxの生成をさせGoogle Driveに自動で保存、Backup and Syncを使ってそのxlsxをローカルに自動保存してもらい、Accessにはローカルに保存させているものを参照させた。

Google Apps ScriptによるSpreadsheetの読み込み

読み込み.gs

  var sheetid  = 'SpreadsheetのID'
  var ss = SpreadsheetApp.openById(sheetid).getSheetByName('sheet1');  //spreadsheet idとシート名を直接指定
  var FileName = SpreadsheetApp.openById(sheetid).getName() + ".xlsx" //保存するファイル名をspreadsheetの名前から引用

Spreadsheet読み込み後のxlsx生成と取得

xlsx生成.gs

  var fetchUrl = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + sheetid + "&exportFormat=xlsx"; //xlsxをexportするURLを作成

 /*exportするURLからGETするためTokenを取得*/
  var params = {
      method      : "get",
      headers     : {"Authorization": "Bearer " + ScriptApp.getOAuthToken()},
      muteHttpExceptions: true
    };
  var blob = UrlFetchApp.fetch(fetchUrl, params).getBlob(); //GETしてバイナリを作成する

Google Spreadsheet自体にはxlsxでデータをダウンロードする機能があるが、GASではその操作を命令できる関数がない。そのため、xlsxでダウンロードする際のエクスポートに用いられるURLクエリーを参考にして、xlsxを生成するURLをまず作らせている。このURLから実際にxlsxを取得するにはOauth認証をクリアする必要があるそうです。認証についてはgetOAuthToken()があるので、これを使って認証を通してアクセストークンを得ます。
最終的にxlsxをGETしてきてバイナリデータを作成させます。

xlsxをGoogle Drive上に自動保存させる

更新と保存.gs

blob.setName(FileName); //バイナリ名を変更（ファイル名に相当）
  
  var folderId = '1n0o-ndBHOtgErAKw11o5tNxvGm8krrnF'; //Google Drive上の保存先フォルダのID
  
    /* 既存のxlsxを削除する */
  deleteFileId = DriveApp.getFolderById(folderId).getFilesByName(FileName).next().getId(); //削除するファイルのIDを取得
  DriveApp.getFolderById(folderId).removeFile(DriveApp.getFileById(deleteFileId));　//IDからファイル情報を取得し、ファイルを削除。
  
  /*指定したIDのフォルダにバイナリからxlsxを生成、保存する*/
  var fileId = DriveApp.getFolderById(folderId).createFile(blob).getId();

保存先フォルダをIDで指定します。もし以前にも生成されていた同じ名前のxlsxがある場合は、そのファイルを削除の上で保存して更新します。

コード全体

spreadsheet_to_xlsx.gs

/*xlsxにするデータセットをspreadsheetから取得*/  
  var sheetid  = 'SpreadsheetのID'
  var ss = SpreadsheetApp.openById(sheetid).getSheetByName('sheet1');  //spreadsheet idとシート名を直接指定
  var FileName = SpreadsheetApp.openById(sheetid).getName() + ".xlsx" //保存するファイル名をspreadsheetの名前から引用

  var fetchUrl = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + sheetid + "&exportFormat=xlsx"; //xlsxをexportするURLを作成

 /*exportするURLからGETするためTokenを取得*/
  var params = {
      method      : "get",
      headers     : {"Authorization": "Bearer " + ScriptApp.getOAuthToken()},
      muteHttpExceptions: true
    };
  var blob = UrlFetchApp.fetch(fetchUrl, params).getBlob(); //GETしてバイナリを作成する
blob.setName(FileName); //バイナリ名を変更（ファイル名に相当）
  
  var folderId = '1n0o-ndBHOtgErAKw11o5tNxvGm8krrnF'; //Google Drive上の保存先フォルダのID
  
    /* 既存のxlsxを削除する */
  deleteFileId = DriveApp.getFolderById(folderId).getFilesByName(FileName).next().getId(); //削除するファイルのIDを取得
  DriveApp.getFolderById(folderId).removeFile(DriveApp.getFileById(deleteFileId));　//IDからファイル情報を取得し、ファイルを削除。
  
  /*指定したIDのフォルダにバイナリデータからxlsxを生成、保存する*/
  var fileId = DriveApp.getFolderById(folderId).createFile(blob).getId();

これを実行すると指定のSpreadsheetの特定のシートがxlsxとして、指定したフォルダに作成されているはずです。
この状態でbackup and syncをダウンロード&インストールしてGoogle Driveを連携させると、インストールしたPCのローカルファイルのうちユーザーディレクトリにGoogle Driveが追加されます。共有フォルダにxlsxを保存した場合は、マイドライブに追加してbackup and syncの同期対象に含めるようにしましょう。

ローカルに保存されるので、DriveのファイルをCドライブの絶対パスで指定できるようになります。

これをMicrosoft Accessに読み込ませるには、外部データタブのExcelをクリックしてCドライブ上のGoogle Drive内のファイルを指定します。UIにしたがって設定が完了すると、テーブルとしてデータを読み込みます。

感想

正直かなり泥くさいやり方だと作業しながら感じていたが、コードが長くなりすぎず契約上のリクエスト回数をなるべく減らすようにするためにもこの構成はそれほど悪くないように感じる。実行時間はデータセットの大きさにも依存するが、少なくとも会計処理に必要な内容であればそれほど長くはならないと思う。
実際に利用価値があるかどうかは今後検証するので、もしまた何か思いついたら続報します。