Google Apps Scriptを使うようになってから、クライアントサイドでAccessを使う機会は大分減りましたが、それでもAccess需要がゼロになるという事はありません。まだまだクラウドのデータベースサービスは高価であり、おいそれとクエリを投げようものなら翌月の請求書金額は怖いものになります。また、複雑な計算やクエリを組んでとなると、Accessのほうがまだまだ作りやすく、何よりもレポート機能が強力で価格が安いので、手放せません。

そんなAccessですが、Googleスプレッドシートと連携出来たらより一層利便性が向上し、既存のGoogle Apps Scriptのプログラムと連携出来たらいいなと思い、色々調査してみました。これまでもCordovaでAndroidアプリや、Node.jsなどのプログラムの回で利用したGoogle Apps Script Execution APIが使えそうなので、これを使ってみて実装をしてみました。

※今回は、スプレッドシートのデータの読み書きですが、Google Apps Script Execution APIを使ってるのでGAS側でコードを書き足せばメールの送信やカレンダーの登録、ドライブの操作、その他GASで可能な事が全てAccess側から実行可能になります。

目次

今回使用するシートやメソッド類

Google Apps Script側を準備する

事前準備

今回のスクリプトは以下の2つを実装する必要性があります。また、今回はGoogle Apps Script Execution APIを用いてデータの入出力を行いますので、事前にDeveloper ConsoleにてクライアントIDを作っておく必要があります。

  1. スプレッドシートのデータを取得してJSON加工して返す
  2. Access側からのデータをJSONにて取得してスプレッドシートに反映する

Developer Consoleでの作業

ここでは、クライアントIDとクライアントシークレットを取得します。また、Google Apps Script Execution APIを有効にします。以下の手順で取得しましょう。

  1. スクリプトエディタを開き、メニューから「リソース」⇒「Googleの拡張サービス」を開く
  2. Googleデベロッパーコンソールのリンクを開く
  3. APIを有効にするをクリックし、検索画面でGoogle Apps Script Execution APIを探す
  4. 有効にする」をクリックする
  5. 次に左のパネルの「認証情報」をクリックする
  6. 認証情報を作成をクリックする
  7. OAuthクライアントIDを選択する
  8. クライアントIDの作成では、「その他」を選択する
  9. 作成ボタンを押すと、クライアントIDクライアントシークレットが手に入るので控えておく。
  10. デベロッパーコンソールを閉じ、スクリプトエディタの画面ではOKを押して閉じる

これで必要な情報の半分が手に入りました。この2つは大切なものなので、漏れたりしないように保存しておく必要があります。また、事前に一度、どのfunctionでも良いので実行して、承認をしておく必要があります。

図:クライアントID、シークレット取得しておきましょう。

実行可能APIとして導入

ここでは、スクリプトIDとスコープを取得します。同じくスクリプトエディタの画面で行います。以下の手順で手に入れます。

  1. スクリプトエディタのメニューより、「公開」⇒「実行可能APIとして導入」をクリック
  2. 公開ボタンを押す。実行可能権限は通常は「自分のみ」でOK。外部に公開しても良い場合だけ、全員にする。この画面に出てるAPI IDは使用しません。
  3. スクリプトエディタのメニューより、「ファイル」⇒「プロジェクトのプロパティ」を開く
  4. 情報タブ内の「スクリプトID」を控えておく
  5. スコープタブ内のスコープを控えておく。Google Apps Scriptで使用したAPIによって変動するので、注意。今回は「https://www.googleapis.com/auth/spreadsheets」のみ

これで必要な情報が全て揃いました。これらの情報はAccess側で使用します。

図:スクリプトIDを取得しておきましょう

ソースコード

データを出力するコード

Google Apps Script側のデータの出力用関数は非常にシンプルです。今回は特にフィルタをせずに全データをAccess側へとreturnするので、以下のようなコードになります。但し、取得データはJSON.stringifyで変換して渡しています。

データを受け入れるコード

データ受け入れ側は少しだけ複雑です。今回はAccess側からJSON化したレコードデータとレコード件数を引数としてparamに入れていますので、これらを加工して、data2というシートにデータを書き込みします。

ポイント

  • データを取得して返すケースは非常に単純にJSON.stringifyしてデータを返すのみです。
  • データを受け取って書き込むケースでは、コード冒頭で配列から引数を分解しています。
  • 書き込み用配列は2次元配列に組み上げて、JSON文字列から一つずつ値を取得し、pushしています。
  • データは一気に追記の形でdata2シートの最終行に書き込みをさせています。
  • 最後にreturnでメッセージを返すのを忘れずに。
  • コードを変更したら、必ず実行可能APIとして導入を再度実行し、バージョンを変更して更新しましょう。これを行わないとコードが反映されません。

Accessデータベース側を準備する

データの取得やテーブルデータをPOST通信で送り込む仕組みが必要です。また、今回はデータにフィルタを掛けずに取得して、特定のIDを元に差分だけをマスターテーブルにインサートするようにしていますので、その為のクエリも必要です。今回は、Windows8.1 / Access2013で動作確認をしています。

アーリーバインディングする場合には、参照設定よりMicrosoft Script ControlとMicrosoft WinHTTP Serviceをチェックしておく必要性があります。

図:参照設定する場合はチェックをいれておきましょう

テーブル構造とクエリ

今回のテーブル構造はシンプルです。Googleスプレッドシート側に合わせています。また、自分のテーブルデータとGoogleスプレッドシート側のデータの差分は、レコードIDを持って不一致クエリを実施し、インサートします。Googleスプレッドシート側のレコードIDは手動ではなく、なんらかのUIを持って自動的に割り当てするような仕組みにしておくと良いですね(今回は、GAS側のレコードIDは文字列を含めた文字列型のIDにしてあります。例:A001)。

よって、同じフィールドを持ったテーブル2個(1つはマスター、1つはGAS側のデータを受け入れるテンポラリ用)、不一致クエリ1個で作成します。また、Access_TokenとRefresh_Tokenを格納する隠しテーブルも用意しておきましょう。

※不一致クエリはそのままGAS側のデータ受け入れるテンポラリ用テーブルへの追加クエリに変更しておいて下さい。

図:テーブル設計はこんな感じ

図:不一致クエリを作っておく

ソースコード

今回はこちらのサイトのコードを改造・修正して使っています。修正ポイントや改造ポイント他注意点等をポイントにまとめてあります。また、今回はJSONコードの取得の為にVBA-JSONを利用していますので、以下の作業が必要です。

  1. VBA-JSONで配布されているJsonConverter.basをインポートしておく必要があります。
  2. 参照設定よりMicrosoft Scripting Runtimeをチェックしておく必要があります。

OAuth2.0認証するコード

Authgoogle()がメインのaccess_tokenを取得する為のコードで、testTokenInfo()が取得済みAccess Tokenがexpireしていないかどうかのチェックと、expireしてる場合のrefresh_tokenを使って、新しいaccess_tokenを取得するコードです。データベース起動時にAutoexecマクロを使って、expireチェックをし、refreshしておくとスムーズにデータを取得したり、データを送信するルーチンに繋げられると思います。

図:認証を実行してみた

データを取得するコード

Google Apps Script側のdataget関数を叩いてスプレッドシートのデータを以下のような形で取り込みます。

  1. dataget関数を実行してスプレッドシートのデータを取得
  2. temp_gasテーブルにデータを流し込む
  3. temp_gasとmasterテーブルの不一致分をmasterテーブルへと追加するクエリを実行

差分のみを追加するようにしています。今回parameterを使っていませんが、ここに例えば日付でフィルタをGAS側へ渡して、GAS側でそれに基いてフィルタして返して上げるのがもっとも良いやり取りの仕方だと思います。

データを送信するコード

データの送信は、GAS側へJSON化したレコードデータと、レコード件数の2つをparametersに入れて送ります。JSONデータは手動で組み上げ、データの取得時にも使用したExecuteGASFunction関数に送っています。masterテーブルのデータをそのまま送っていますが、実際にはクエリなどでデータ数を絞ってから送るのがベストです。

ポイント

  • access_tokenは1時間でexpireしてしまいますので、expire後はrefresh_tokenを使って新しいaccess_tokenを取得する必要があります。今回は認証系のコードでその部分を追加しています。
  • refresh_tokenで新しいtokenを取得した場合、再認証は必要ありません。
  • また、expireしてるかどうかのチェックの為にtokeninfoに投げて結果を受け取るコードも必要です。
  • オリジナルのコードの中ではAPI IDを指定していますが、現在はスクリプトIDでなければ利用できません
  • access_tokenおよびrefresh_tokenは、settingテーブルの1行目に値を格納させています。
  • expireしてなくても、残り時間が60秒以下の場合には、refreshを実行するようにしてあります。
  • refresh_tokenを取得する為、最初の認証時にaccess_type=offlineを追加してあります。
  • JsonConvert.parseJsonで得たデータの中のresponse.resultに当たる部分だけを取り出しています。
  • 取得したデータはカンマ区切りになってるので、splitで配列化、その後日付だけは型を整えてDAOでレコードを追加しています。
  • temp_gasに入ったレコードを差分追加クエリの実行にて、masterテーブルへと追加しています。
  • 一方、Access側でのJSONへの変換はJsonConverter.ConvertToJsonを利用して変換も良いのですが、今回は手動でJSONオブジェクトを組み立てて送信させています。
  • Google Apps Scriptでは一度に送信できるデータのサイズは25MBがリミットのようなので、あまり大きなデータを送ると失敗します。
  • 今回はAccess側にフォームを設けていません。実際にはフォームとボタン類を配置して、各Subルーチンを割り当てて利用しましょう。
  • 出来れば、Access_Tokenをrevokeし、別のアカウントで再認証出来るように仕組みを追加すると尚良いでしょう。その際のrevoke用のエンドポイントは、https://accounts.google.com/o/oauth2/revoke?token={token}です
  • json文字列を組み立てる部分でやたらとダブルコーテーションが記述されていますが、これは文字としてダブルコーテーションでプロパティ名やその値を括らなければならないので、このような書き方をしています。エスケープしないと、文字としてのダブルコーテーションが認識されないので、ちょっと面倒ですね。
  • Cloud SQLを使わずスプレッドシートで十分なレベルのデータ量ならば、VPN無し・DBなしで様々な業務アプリがAccessでも構築出来ます(バックエンド処理を全部、GASに任せられますし、DB接続する場合もJDBC Serviceを使えばGAS側に任せられますし)。
  • Google Apps Script Execution APIを使えば、レガシー資産をクラウド対応させたり、CordovaのようなHTML5のスマートフォンアプリに強力な機能を持たせる事が出来るので、VBAでも使えるのはありがたい。
  • 複数のGoogleスプレッドシートを扱う場合は、1個だけ今回の処理を記述したGoogleスプレッドシートを用意し、複数のスプレッドシートはリスト化しておけば、この1個からそれらを読み書き可能です。よって、複数のシートに今回の処理を記述する必要はありません。

関連リンク

Google Apps Script 関係

VBAでJSON相互変換

その他

Pocket
このエントリーをはてなブックマークに追加
Bookmark this on Yahoo Bookmark
Pocket