Google Apps ScriptとAccessデータベースを連携させる【GAS】

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

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

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

今回使用するシートやメソッド類,参照設定,ライブラリ

※今回使用するAccessデータベースは、32bit版と64bit版の2つが入っています。

新方式が登場しました

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側の準備

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

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

プロジェクトを移動

今回の発表直前の2019年4月8日より、Google Apps ScriptからCloud Platform Projectへ直接アクセスが出来なくなりました。これまでにデプロイしてるものについては、これまで通り「リソース」⇒「Google Cloud Platform API ダッシュボード」からアクセスが可能です。

今回の変更はスプレッドシート上で動かすスクリプトやGoogleの拡張サービスを利用しないタイプのスクリプトであれば特に問題はありませんが、「Apps Script API」や「Google Picker API」、「Cloud SQL接続」などGCP上のAPIを利用する場合には以下の手順を踏んで、Google Apps Scriptにプロジェクトを連結する必要があります。これまでは、自動的にGCP上にGoogle Apps Script用のプロジェクトが生成されていたのですが、今後は自分の組織(もしくはGCPプロジェクト)上で作成されたプロジェクトでなければならないということです。詳細はこちらのページを見てください。

連結する手順は以下の通り

  1. Google Cloud Consoleを開く
  2. 左上にある▼をクリックする
  3. ダイアログが出てくるので、新規プロジェクトを作るか?既存のプロジェクトを選択する。この時、G Suiteであれば選択元は「自分のドメイン」を選択する必要があります。
  4. プロジェクト情報パネルから「プロジェクト番号」をコピーする
  5. 対象のGoogle Apps Scriptのスクリプトエディタを開く
  6. 「リソース」⇒「Cloud Platform プロジェクト」を開く
  7. 4.で入手した番号をプロジェクトを変更のテキストボックスに入れて、プロジェクトを設定ボタンをクリックする
  8. 無事に移動が完了すればメッセージが表示されます。
  9. この時、元の自動作成されたプロジェクトはシャットダウンされて消えます。これで設定完了です。

今回のこの変更だと1つ作ったプロジェクトに集約する必要があるので、クォータについてプロジェクト毎のカウントだったので問題なかったものが、集約されることで、クォータに引っ掛かる可能性があります。

図:プロジェクト番号をコピーしておきます

図:プロジェクトを他のプロジェクトに紐付けしました。

図:GCPの拡張サービスを使うには手順が必要になった

Cloud Consoleでの作業

ここでは、クライアントIDクライアントシークレットを取得します。また、Google Apps Script APIを有効にします。以下の手順で取得しましょう。今回使用するスプレッドシートを開いて作業を行います。

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

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

図:Apps Script APIが今回の主役

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

実行可能APIとして導入

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

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

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

図:実行可能APIとして導入をしないと使えません。

図:スクリプト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をチェックしておく必要があります。
  3. Google Apps Script側で取得したスクリプトID, クライアントID, クライアントシークレットを記述に追記しましょう
OAuth2.0認証するコード

  • Authgoogle()がメインのaccess_tokenを取得する為のコードです
  • testTokenInfo()が取得済みAccess Tokenがexpireしていないかどうかのチェックと、expireしてる場合のrefresh_tokenを使って、新しいaccess_tokenを取得するコードです。
  • データベース起動時にAutoexecマクロを使って、expireチェックをし、refreshしておくとスムーズにデータを取得したり、データを送信するルーチンに繋げられると思います。
  • 2019年1月1日より、ブラウザ上で表示されるAuthrization Codeの取得の為の仕様が変更になっているため、旧来のgetElementById("code")でelement指定しても取れません。auth_code = .Document.getElementsByClassName("qBHUIf")(0).valueといった形でClassName指定で取得するようにしています。

図:認証を実行してみた

データを取得するコード

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

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

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

データを送信するコード

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

VBAコードの64bit対応

今回のソースコードは、Access 32bit版でなければ動作しません。コードの中で使われている「Microsoft ScriptControl 1.0」が64bit対応していない為です。Microsoft365以降は64bit版が標準でインストールされるようになってきている為、このままでは連携ができません。また、VBS-JSONも64bit環境では不具合が確認されています。

この問題をクリアする為に、64bit対応では以下のようにコードを変更する必要があります。参照設定からScript Controlは外しておきましょう。64bit版ではVBA-JSONも利用しない書き方に変更します。

Windows10 64bit対応

このエントリーは前項のVBA 64bit対応と同じく、64bit Windows10の場合に於ける「IEを使ったOAuth2認証」をする場合に生じる問題とその解決方法です。問題の箇所はInternet Explorerのobject生成時にあり、以下のようなエラーが出ます。また、この問題はIEの保護モードの有無でも生じる場合がある。以下の作業を行い、32bitのIEを起動するようにしましょう。

  1. インターネットオプションを起動する
  2. 詳細設定タブをクリックする
  3. 拡張保護モードで64bitプロセッサを有効にするのチェックを外す
  4. 拡張保護モードを有効にするのチェックを外す。
  5. これでコードを実行してみる。32bit版IE11なので問題なく動くはず。

図:64bit版IEを利用するメリットは全くありません。

ポイント

  • 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 APIを使えば、レガシー資産をクラウド対応させたり、CordovaのようなHTML5のスマートフォンアプリに強力な機能を持たせる事が出来るので、VBAでも使えるのはありがたい。
  • 複数のGoogleスプレッドシートを扱う場合は、1個だけ今回の処理を記述したGoogleスプレッドシートを用意し、複数のスプレッドシートはリスト化しておけば、この1個からそれらを読み書き可能です。よって、複数のシートに今回の処理を記述する必要はありません。
  • 企業内で使う場合、Proxy Serverが存在していることがあります。その場合WinHTTPに.setProxyオプションを使って、プロキシーのURLを指定しましょう(例:.setProxy 2, proxyuri

関連リンク

Google Apps Script 関係

VBAでJSON相互変換

その他

Google Apps ScriptとAccessデータベースを連携させる【GAS】” に対して2件のコメントがあります。

  1. トリニティ より:

    ACCESSのテーブルと google スプレッドシートの接続を検索していて こちらにたどり着きました。
    こちらの構文ですと IEを起動して接続されていますが、IEですでにgoogleにアクセスできないようです、そのためAPIの許可ができませんでしたクロームで機能するようにすることは可能でしょうか?
    また 既存のテーブルと同期だけしたい場合のスクリプトもアップいただけると大変参考になりまうす

    1. officeの杜 より:

      トリニティ様

      かなり時間がかかってしまいましたが、Google側のIE非対応およびWindows10/11などに於けるIE11の廃止に伴い、古いIE11を利用したOAuth2.0認証は出来なくなりました。
      ということで、Box APIやGraph APIのケース同様に、PuppteerにてChromeを使った認証を作成しました。

      以下のページを参考に実装してみてください。

      VBAからGoogle APIを叩いてみる – IE11廃止対応版
      https://officeforest.org/wp/2022/03/22/vba%e3%81%8b%e3%82%89google-api%e3%82%92%e5%8f%a9%e3%81%84%e3%81%a6%e3%81%bf%e3%82%8b-ie11%e5%bb%83%e6%ad%a2%e5%af%be%e5%bf%9c%e7%89%88/

officeの杜 へ返信する コメントをキャンセル

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

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