この記事はGoogle Apps Script Advent Calendar 2021の8日目の記事です

はじめに＆やってみること

Google Apps Scriptでオンプレ DB のデータを使いたいケースはやはり多いでしょうか？私自身もよく耳にすることがあったりします。
はい、ということで、今回は Google Apps Script からオンプレ上の DB に CRUD してみるような内容をやってみます。とは言え、実現するためにローカル側に CData API Server、GCE 上に SSH サーバーを用意したりします。

CData API Server とは？

CData API Server は3Stepでお好みのRDBやNoSQL、クラウドサービスからREST ful APIを生成して、公開することができるソフトウェアです。
https://www.cdata.com/jp/apiserver/

セルフホスティング型のアプリケーションなので、ローカルマシンでも良いですが基本的にはサーバーやクラウド上の VM を用意して使います。

インターネット上に公開したAPIを作成したい場合は、クラウドホスティング、オンプレミスの場合はDMZなどに配置する必要が出てきます。

そこで今回紹介する Cloud Gatewayの機能を活用することができます。

Cloud Gateway機能について

Cloud Gateway はクラウド上のSSHサーバーのリバースSSHポートフォワーディングを利用し、CData API Serverをインターネット上に公開する機能です。
http://cdn.cdata.com/help/BWE/jp/odata/Windows-Edition.html#cloud-gateway

構成

ローカル DB(MySQL) と GAS の間に SSH サーバーと APIServer を挟んだ構成をとります。

API Server がSSH Server と接続を確立した状態で、SSH Server がHTTP/Sリクエストをポートフォワーディングすることにより、API Serverをインターネットアクセスを可能にします。

これにより、ファイアウォールの変更やDMZへの配置、固定IPアドレス・ドメインといった要素を GCP などのクラウドサービス側に寄せることができ、セキュアにオンプレミスに存在するRDBや基幹システムをクラウド上で扱えます。

SSH サーバー自体はどこに配置しても構いませんが、今回はGASで使うということで GCE の CentOS で作成しました。

色々書いていますが、結構簡単にできます。
ではさっそく見ていきましょう。

必要なもの

CData API Server ※30日間の無償トライアルあり
GCE（SSHサーバーとなるマシンであれば、AzureでもAWSでも）
Windows マシン（API Server を配置するオンプレミス環境）
Teraterm ※鍵生成でのみ利用。ツールは何でも良いです

手順

SSHサーバーとなるGCE インスタンスを構成する

まずはここの GCE インスタンスの構築です。

GCPの管理コンソールを開き、Compute Engine → VM インスタンスと進み、CentOS7 で VM を作成していきます。SSH サーバーはIP アドレスが毎回変わると、ローカル側からのアクセスが出来なくなってしまうので、固定IP をできれば取得しておきましょう。

ブラウザウィンドウで開くで SSH で作成した GCE にログインします。

「sshd_config」に「GatewayPorts yes」を追加してください。デフォルトではコメントアウト状態で「#GatewayPorts no」になっています。

sudo vim /etc/ssh/sshd_config

これでGCEの設定は半分終了です。一旦ローカル側の設定を行ったらまた戻ってきて設定しなおします。

オンプレ側 API Server のセットアップ

それでは API Server を構築していきます。まずはローカル環境内でのみ動く、ベーシックなAPIを作成してみましょう。

以下 URL から Windows 版の API Server をダウンロード、インストールしてください。
https://www.cdata.com/jp/apiserver/download/

CData API Server にログインし「設定」→「接続」の画面に移動し、データソース一覧の中から「MySQL」を選択します。※MySQL じゃなくても同手順で設定可です

MySQL への接続情報を設定したら、「接続のテスト」→「変更を保存」をクリックして接続情報を保存します。

次にリソースを追加するために「設定」→「リソース」へ移動し、「リソースを追加」をクリックします。

sakila にある actor というテーブル情報をもとに RSET API を生成します。

あとはそのまま進んでいけば、REST API の生成完了です。

APIページに行くと、テーブルリソースが追加され、API Reference を確認できます。

実際にブラウザから生成したエンドポイントにアクセスしてみると、GETリクエストが実行されて、データを取得できました。

API Server と SSH Server の SSH 接続設定

APIServer、GCE それぞれで設定していきます。

認証鍵を生成し、公開鍵をGCPに登録

ツールは何でも良いですが、私は Teraterm を利用します。設定 → SSH鍵生成の順にクリックします。

生成ボタンをクリックしたら公開鍵と秘密鍵を保存します。

保存したら公開鍵を開き、中身を全部コピーしたら GCP の管理コンソールに移動します。
移動後、GCE画面に行き「メタデータ」 → SSH 認証鍵と進み、先ほどコピーした値をセットします。

API Server で Cloud Gatewayの構成

ようやく API Server と SSH サーバーの SSH 接続の設定になります。タスクメニューのAPI Serverアイコンを右クリックして「Server Options」を選択します。

Server Options画面の「Cloud Gateway」タブから設定を行うことができます。

「Enable Cloud Gateway」にチェックを入れて、GCEに接続するために必要な情報を入力します。

Host：作成した SSH サーバーの IP アドレスを入力
Port：デフォルトは22
Authentication Type：Public Key
User：GCPのアカウント
Certificate：先ほど Teraterm で生成した秘密鍵

入力後、秘密鍵を指定するため「...」をクリックし、PEM タブにて生成した秘密鍵を指定したら OK ボタンをクリックします。

設定後、接続テストを行って OK であれば接続設定を保存します。

これでローカルの API Server とクラウドの SSH サーバーが接続できるようなりました。

SSHサーバー（GCE）で APIServer のポートを開放

GCP に戻ってAPIServerで設定したポートを開放していきます。

APIServer の CloudGateway ではデフォルトで 8153 というポートで設定されています。

このポートで外部からアクセスできるようにするために、SSH サーバー（GCE）で 8153 ポートが使えるようにする必要があります。

GCP メニューから VPCネットワーク → ファイアウォールの順にクリックします。

ファイアウォールルールを作成をクリックします。

このファイアウォールの名前とターゲットタグを任意の名前で設定します。
送信元のIP アドレスは一旦全て対象の 0.0.0.0/0 にします。次に、「指定したプロトコルとポート」とtcp を選択し、CloudGateway で指定したポート番号（8153）を入力したら、作成ボタンをクリックします。

ファイアウォールが出来上がったら、そのファイアウォールの作成で設定したタグ名を GCE に設定します。

GCEのファイアウォール停止とSELinux をdisableにします。

sudo systemctl disable firewalld
sudo vi /etc/selinux/config

ユーザーごとのAPI エンドポイント権限設定

APIServerで生成したエンドポイントにアクセスできるユーザーを作成していきますので、APIServer を開いた後、設定→ユーザー→追加でクリックします。

今回は test というユーザーを作成し、参照から更新系まで全て行える権限を付与しました。

ユーザーを作成すると、自動的に認証トークンが生成されます。後にGoogle Apps Scriptでアクセスする際に使用します。

最後の設定です。デフォルトでは BaseURL が未設定のためデフォルトのlocalhostになっていますので、API Serverのサーバー設定タブ → BaseURL を参照し、この形式http://localhost:8153/api.rsc/の localhost となってる部分を GCE の外部 IP に変更して設定しましょう。

長くなりましたがこれで設定が完了です！
では試しにブラウザから APIServer が生成したエンドポイントを叩いてみましょう。

ブラウザから APIServer のエンドポイントにアクセス

APIServer の API タブからリファレンスを参照します。

とりあえず今回は条件指定なしで全件取得してみますので、赤枠をコピーしてそのままブラウザのURLに設定して実行します。その後、ユーザー名とパスワードを聞かれますが、先ほどAPIServerで作成したユーザー名と生成された認証トークンを入力します。

データが取得できたので外部からオンプレのMySQLのデータにアクセスできることが確認できました。

接続に失敗する場合

接続に失敗する場合は以下の対応で解消できるか確認してみてください。

GCE 上で対象ポート(8153)が開放しているかを確認

sudo netstat -ltunp4
[miyamotok@sqlgateway-sshsrv ~]$ sudo netstat -ltunp4
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      904/master
tcp        0      0 0.0.0.0:3311            0.0.0.0:*               LISTEN      1092/sshd: miyamoto
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      874/sshd
udp        0      0 0.0.0.0:68              0.0.0.0:*                           510/dhclient
udp        0      0 127.0.0.1:323           0.0.0.0:*                           426/chronyd
[miyamotok@sqlgateway-sshsrv ~]$

表示されてない場合は、APIServer で使用するポートが空いていないことになるので、再度GCP のファイアウォールか、SELinux の無効化などを行い確認してみる。

Google Apps Script からAPIServer 経由で MySQL にアクセスしてみる

MySQL のデータをGASから取得

ではやってみましょう。以下は先ほどブラウザから実行したリクエストを同じ内容ですが、スプレッドシートに取得結果を書き込むことをやってみます。

const sheet = SpreadsheetApp.getActiveSheet(); 
var url = 'http://xxxxxxx:8153/api.rsc/sakila_actor/';
var keys = {
  'actor_id' : "",
  'first_name' : "",
  'last_name' : "",
  'last_update' : "",
};    

function getOnpreData() {
  try{
    //var search = '1';
    var options = {
      'method': 'get',
      'contentType': 'application/json',
      'headers': {
        'x-cdata-authtoken': '5x5C2l0i5V3j1d3J5g0k'
      }
    };
//    var json = UrlFetchApp.fetch(url+"?$search="+search, options);
    var json = UrlFetchApp.fetch(url, options);
    var jsonData = JSON.parse(json);

    var value = jsonData.value

    var i = 1;    
    for (var idx in value) {
      var j=1;
      for(var key in keys) {
        var type = typeof value[idx][key];
        sheet.getRange(i, j).setValue(value[idx][key]);  
        j++;
      }
      i++;
    }
  }
  catch(e){
      Logger.log(e);
  }  
}

このように取得結果が反映されました。

MySQL のデータをGASから登録

固定値ですが以下の値で MySQL に登録します。

const sheet = SpreadsheetApp.getActiveSheet(); 
var url = 'http://xxxxxx:8153/api.rsc/sakila_actor/';

function postOnpreData() {
  try{
    var date = new Date();
    var data = {
      'actor_id': '201',
      'first_name': 'CData',
      'last_name': 'APIServer',
      'last_update': '2021-12-08T04:34:33.0000+09:00'
    };
    var options = {
      method : 'post',
      dataType: "json",
      payload : data,
      headers: {
        'x-cdata-authtoken': '5x5C2l0i5V3j1d3J5g0k'
      },
    };
    UrlFetchApp.fetch(url, options);
  }
  catch(e){
    Logger.log(e);
  }  
}

上記コード実行後、データを再取得して追加されてることを確認してみます。

MySQL のデータをGASから更新

first_name を変更してみます。

const sheet = SpreadsheetApp.getActiveSheet(); 
var url = 'http://xxxxxx:8153/api.rsc/sakila_actor/';

function putOnpreData() {
  try{
    var data = {
      'actor_id': '201',
      'first_name': 'GAS User'
    };
    var options = {
      method : 'put',
      dataType: "json",
      payload : data,
      headers: {
        'x-cdata-authtoken': '5x5C2l0i5V3j1d3J5g0k'
      },
    };
    UrlFetchApp.fetch(url, options);
  }
  catch(e){
    Logger.log(e);
  }  
}

データ再取得後、名前が変わっているのが確認できました。

MySQL のデータをGASから削除

では最後に追加したデータを削除します。

const sheet = SpreadsheetApp.getActiveSheet(); 
var url = 'http://xxxxxxx:8153/api.rsc/sakila_actor/';

function delOnpreData() {
  try{
    var actor_id = 201;
    var options = {
      method : 'delete',
      dataType: "json",
      headers: {
        'x-cdata-authtoken': '5x5C2l0i5V3j1d3J5g0k'
      },
    };
    UrlFetchApp.fetch(url+'('+ actor_id + ')', options);
  }
  catch(e){
    Logger.log(e);
  }  
}

対象レコードが削除されました。

おわりに

色々設定箇所はありましたが、一つこの構成を作成してしまえば、Oracle や SQLServer など CData API Server に追加するだけで利用できるようになります。