2019年1月20日 / 最終更新日時 : 2024年6月18日 officeの杜 Google

Google Apps Scriptで外部のMySQLデータベースに接続する【GAS】

社内で様々なアプリケーションのデータ記録場所として、Googleスプレッドシートを使っているわけなのですが、スプレッドシートには最大500万セルという制限があり(最近、200万セルから拡張されました)、またLockServiceで排他制御があるとは言え簡易的なものでしかありません。また、Google SpreadsheetとCloud Platformではサポートが異なるので、より安定してるCloud SQLは魅力的。Google Apps Scriptでのアプリ作成の幅がグンっと広がります。Google App Engineというものもあるのですが、GASではなく主にJavaで作成するものなので、あくまでGASでこれまで通り出来るという点が重要です。

今回は、用意したDBサーバや最近流行りのDBaaSサービスで数分で用意できるクラウドデータベースなどを保存先として、クライアントはこれまでのGoogle Apps Scriptを使うというやり方です。主にAmazon AWSやGoogle Cloud SQLなどがそれらに該当します。GASからなので、素直にGoogle Cloud SQLのMySQL Serverを選択しました。

※2024年、あらためて本記事をリライトしました。以下のエントリーが最新版となります。本エントリーも決して使えないわけではないですが大分変わってる点が多いので、参考程度までに。

Google Apps ScriptからCloud SQLへの接続を再検証【GAS】

1 今回使用するクラス等やサービス
2 事前準備
3 Google Apps Scriptから接続
4 Access to Google Cloud SQL
5 他のデータベースに接続する
6 ポイント
7 関連リンク

今回使用するクラス等やサービス

事前準備

データベースを使う利点

データベースはスプレッドシートと違って、課金されるサービスなのですが近年は単価も下がってきており、また何よりも大きなメリットがあるため、ビジネスでは普遍的に利用されています。主な利点は

スプレッドシートと異なりSQLによる大容量・高速なデータの読み書きが可能です。
リレーショナルデータベースなので、リレーションシップを利用したデータの整合性を常に取る事が可能です。
連鎖更新・連鎖削除・ロックなどDBならではの機能を利用して管理を軽減する事が可能です
MySQLは様々なウェブサービスやDBaaSでサービス展開されてるので引っ越しが楽です。
接続元制限や接続メンバーの制限など細かな設定が可能です（Azure ClearDBでは出来ません）
他のスクリプトからでも容易にデータベースへアクセスが可能です。
スプレッドシートのようなセル数での制限はなく、データベース容量での制限があります（後者のほうが遥かに広いです）。
ベンダーロックインじゃないですが、Googleサービスに完全依存しない上でも利点があります。
スマートフォンアプリ等への横展開がし易いです。スマフォ用の場合は、Firebaseを使うのも手ですね。
もちろん、Accessから直接ODBC接続でつなげて運用が出来るので、AccessをクライアントにするのもGoodですね。
G SuitesのFusion Tableが廃止されるので、移行先として使うのも良いかと思います。

利用料金と確認法

気になるGoogle Cloud SQLの利用料金ですが、わかりにくいです。今回の設定は

MySQL第二世代
東京リージョン（asia-northeast1）
マシンタイプはdb-g1-small

この場合の料金になります。３つの課金がなされるのですが、起動におよそ15分は消費するので注意。

インスタンス 1ヶ月時間分の料金：1時間あたり「$0.0455(108円レートで4.914円)」* 24時間 * 31日 = 3,656円
ストレージ １ヶ月分の料金：SSDの場合「$0.22/GB/月」なので10GBで「$2.2(108円レートで237.6円)」
ネットワーク :上りは無料。下りは「$0.19/GB」なので 1GBあたり「0.19円（108円レートで20.52円）」ここが可変なので注意。

ネットワーク部分が要するにクエリをぶん投げてデータを拾ってくる場合の料金ですが、10GBも拾っても200円くらいなので、よほどオカシナ使い方しない限りは気にする必要無し。一番ボリュームが大きいのはインスタンスの部分。ここはおいそれと止められないので、トータルで最低でも「3,914円/月以上」は掛かることになります。MySQL第一世代のほうがパッケージプランがあって安そうだ。

インスタンスが本番使用での標準の「db-n1-standard-1」ならば、$0.0878なので換算値で7,054円は標準的に掛かるという事。安くなったとはいえ、まだまだ高いですねぇ。SakuraでVPSでMySQL借りて運用したほうが安いなぁ。まぁ、セキュリティやバックアップ、MySQL構築の手間とか考えたらってのはあるんですけれどね。Nifty Cloudもいいかもしれない。

※とは言え、G Suite上で作ってユーザに入力だけしてもらうならば、こちらは固定費で済むので、利用者数が多いなら、G Suiteアカウントではなく、専用アプリ用意して使わせたほうが、トータルコストは安く済む計算（G Suite Basic 12名分の料金で済むので、ソレ以上の利用者がいるならば）

※Azureに以前あったClearDBというMySQLサービスは削除されてしまい、現在はcleardb.netというサイトに分離独立してしまっています。今まで使っていた人は、支払い情報やアカウントについては、こちらのサイトのように自分で手続しなければならないのです。また、Azureには2018年10月にMariaDBがプレビューながら追加されているので、これに置き換えたようですね。

料金の確認は以下の手順で確認可能です。

「≡」というアイコンが左上にあるので、これをクリックして「お支払い」をクリック
請求アカウントへ移動をクリック
概要には紐付けられている課金プロジェクトが出てきます。初期ボーナスの30,000円分の残りクレジットなどはここで確認できます。
料金の履歴をクリックするとこれまで課金された金額がわかります。
予算とアラートを作成しておくと、予算到達時にアラートが飛んできます。

Google Cloud SQLを使う場合

プロジェクトを移動

今回の発表直前の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プロジェクト）上で作成されたプロジェクトでなければならないということです。詳細はこちらのページを見てください。

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

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

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

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

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

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

APIの有効化

Google Apps Scriptから使うためには、Google Cloud Platformでいろいろ作業を行わなければなりません。以下の手順でAPIを有効にしましょう。

スクリプトエディタに入って、メニューより「リソース」⇒「Googleの拡張サービス」を開きます。
ダイアログ下にある「Google Cloud Platform API ダッシュボード」をクリックします。
APIとサービスの有効化をクリックします。
検索窓より「Cloud SQL」を検索しクリックします。
有効化のボタンをクリックします。
これで、API自体は有効になりましたが、まだ使えません。

図：APIをまずは使えるようにしておく

図：実行承認時にCloud SQLのスコープが出てくる

インスタンスの作成

APIを有効化したら、しばらく待つとデータベースのインスタンスというものが作れるようになります。「≡」というアイコンが左上にあるので、これをクリックして、左サイドバーを開きます。

下のほうにある「SQL」という項目を開きます。
インスタンスを作成をクリックします。
MySQLとPostgreSQLのどちらかを選ぶことができます。ここはMySQLを選んでおきます。
インスタンスIDには適当なインスタンス名を付けます。変更は不可。ここでは、「rurikosan」と名付けました。
rootパスワードはこのインスタンスに接続するマスターパスワードです。忘れないものを入力しましょう。
リージョンですが、現在は東京リージョンが存在します。「asia-northeast1」を選択します。遠くのリージョンを選ぶと通信が遅くなります（一方でネットワーク単価は下がりますが・・・）
ゾーンは東京リージョンのどのサーバを使うかを選ぶものですが、適当に選んでおきます。asia-northeast1-cを選んでおきました。
まだ、作成はクリックせずに、設定オプションを表示をクリックします。
「マシンタイプとストレージの設定」を開き、変更をクリックします。
ここでは、マシンタイプを選ぶのですが、選ぶマシンタイプで料金が全然変わってきます。ハイスペックほど高い料金が掛かります。通常は、「db-n1-standard-1」の1CPU、RAM:3.75GBを選べばとりあえずは良いのですが、今回はテスト開発なので、「共用のdb-g1-small」を選びました。CPU共用でRAM:1.75GBです。
ストレージの種類は、HDDのほうが安いのですが、それほど大きな値差があるわけでもないようなので、高速なSSDを選びました。
ストレージ容量は最低が10GBからなので、それで行きます。増えればそれだけ料金は掛かります。
ストレージの自動増量を有効化をしておくと、ストレージがいっぱいになっても自動でスケールするようになります。今回はオフにしてあります。
次に、「自動バックアップの有効化」をクリックします。今回はテストなので不要。ですので、チェックを外してあります。通常は付けたままで良いでしょう。
これで、保存をクリックすると、インスタンスが作成されます。結構時間が掛かるので待ちます。

図：インスタンスを作成開始

図：今回はMySQLを選びます

図：インスタンス情報を設定します。リージョンはasia-northeast1を

図：調子に乗ってハイスペック選ぶとエライことに

図：ストレージはあまり気にしなくて良い

図：本番環境ではバックアップは有効にしておきましょう。

作成後の設定を施す

さて、これでインスタンスも立ち上がり、MySQLは稼働しているのですが、まだやることがあります。データベースの作成と、ローカルからMySQL WorkBenchで接続出来るようにIPの許可を追加します。

接続タブをクリックする。
パブリックIPにある「承認済みネットワーク」に追加するIPアドレスを、確認くんで調べておく
ネットワークを追加をクリックして、2.のIPアドレスと名前をつけて保存する。企業などからの場合には、プロキシーのアドレスなどを入れると内部 or VPN経由からじゃないと接続させないように出来ますね。
SSL接続のみを許可にするとGASからアクセスができなくなるので注意。
ユーザタブをクリックする。
rootが登録されていますが、それとは別にユーザアカウントを作っておきましょう。ユーザアカウントを作成をクリックして登録しておきます。
データベースタブをクリックする
適当なDBを作る。今回は、gas_dbという名前でデータベースを作りました。文字コードはutf-8でオッケー
これですべての準備が整いました。
概要タブに戻り、「このインスタンスに接続」に記述されている「IPアドレス」と「インスタンス接続名」を控えておきます。IPアドレスはMySQL Workbenchから接続する際に使用し、インスタンス接続名はGoogle Apps Scriptから接続する際に使用します。

図：DBは予め作っておきましょう。

図：接続情報は非常に重要です。

図：IP接続許可しないとローカルから接続できません

Cloud Shellで作業をする

Google Cloud Consoleには、Cloud Shellという標準でターミナルが用意されています。画面右上にある左端にあるアイコンがそれ。クリックすると、画面下部にターミナルが起動します。

//rurikosanインスタンスに接続する
gcloud sql connect rurikosan --user=root

1 2	//rurikosanインスタンスに接続する gcloud sql connect rurikosan --user=root

今回は、rurikosanというインスタンスに接続して、rootアカウントで入っています。Enterで実行するとしばらく接続まで待ちます。Passwordを聞いてきたら、入力。すると、MySQLへログインが完了し、以降は、コマンドラインでMySQLを操作可能になります。quitコマンドで終了です。

※あらかじめ、新しいネットワークで自分のIPアドレスを登録しておかないと接続できませんので注意。

図：Cloud Shellで操作がその場で出来ます。

MySQL Workbenchで接続・作業

取得した情報を元にMySQL Workbenchにて作業をします。コンソールが使える人はそちらで作業をしても良いでしょう。MySQL WorkbenchはGUIでテーブルの作成や設計、データの入力等が可能な非常に良いツールで、Windows/Mac/Linuxで使える優れものです。ポートフォワーディングなどを利用すれば、レンタルサーバのMySQLも操作が可能です。

※MySQL Workbenchは無償のツールですが、OracleのIDが必要です。

新しい接続をつくり、取得したIPアドレス、ユーザID、パスワードでログインします。
DB名も入れておいたほうが良いでしょう。今回は、gas_dbという名前で作っています。default_schemeに入れます。
そして接続してみましょう。
下記の図のような感じの画面が出たら成功。右下のSCHEMESで作業をします。
Tablesで右クリックして、Create New Tableでテーブルを作ります。
AccessのようにGUIでテーブル設計が可能ですがフル英語ですので気合をいれましょう。今回は5個のカラムでIDのみAuto Incrementを入れています。テーブル名はjinjiとしました。
Applyボタンでテーブルが作成されます。再度テーブル設計をする場合はAlter Tableで入れます。
出来たテーブルを右クリックして、今度はselect rowsをクリック実行
この画面は直接テーブルに値を入れることが出来ます。入れたらApplyボタンを忘れずに。IDはAuto Incrementが指定されてるので、空でも自動で数値が入ります。
これでテーブルの準備も出来ました。

図：無事にローカルからCloud SQLへ接続できた

図：Cloud SQLへテーブルを作ってる様子

Google Apps Scriptから接続

JDBCを使うに当たって

JDBCサービスの制限はダッシュボードから確認できますが、以下のような感じになっています。以下の制限に引っかからないように注意しながら、運用する必要性があります。それほど大規模な人数でなければ、制限に引っかかることはないとは思いますが。

操作	一般ユーザー	Google Apps 無償版	G Suite
JDBC 接続	10000 個/日	10000 個/日	50000 個/日
JDBC 接続の失敗	100 個/日	100 個/日	500 個/日

データベース接続用URL

Google Apps Scriptでは、JDBCでコネクションを張るときに接続文字列、ユーザID、パスワード、接続先DB名が必要になります。自分が立てたMySQLサーバや他のクラウドデータベースの場合と違い、Google Cloud SQLのMySQL接続時には、概要タブで取得した「インスタンス接続名」を利用します。

Jdbc.getCloudSqlConnection("jdbc:google:mysql://インスタンス接続名/接続DB名", ユーザID, パスワード);

getCloudSqlConnectionメソッドを利用し、接続文字列の後に、上記の通り、インスタンス名、接続DB名といった形がつなげると接続が可能になります。接続インスタンス名は、主に以下のような感じの文字列です。

プロジェクト名:リージョン:インスタンスID

ここが一番苦労しました・・・ちなみに、Google Cloud SQL以外のサイトのMySQLへ接続する場合には、以下のような形になります。

Jdbc.getConnection('jdbc:mysql://yoursqlserver.example.com:3306/database_name', {user: 'username', password: 'password'});

getConnectionメソッドを利用し、サーバーアドレス、DB名、ユーザID、パスワードと繋げて接続します。メソッドと接続用文字列が異なるので、注意が必要です。

ソースコード

今回はCloud SQL側に１枚だけテーブルを作ってありますので、これに対して１行新規データを追加、またテーブルの中身を取得してAlertで表示するといったものを作ってみたいと思います。まずは現在テーブルに入ってる情報を抜き出して見たいと思います。

セッティング関係のコード

GAS側コード

//管理者用用コンソールをsidebarで表示する
function setting() {
var ui = SpreadsheetApp.getUi();
var html = HtmlService.createHtmlOutputFromFile('setting').setTitle('管理設定').setSandboxMode(HtmlService.SandboxMode.IFRAME);
ui.showSidebar(html);
} 
//コールバック待ち
function waitid(){
var setarray = [];
//プロパティ値を取得
var Prop = PropertiesService.getScriptProperties();  
//設定値を配列に格納
setarray.push(Prop.getProperty("instance"));
setarray.push(Prop.getProperty("dbname"));
setarray.push(Prop.getProperty("userid"));
setarray.push(Prop.getProperty("password"));
//null値の場合には未選択に置き換える
var numRows = setarray.length;
for(var i = 0;i<numRows;i++){
if(setarray[i] == null){
setarray[i] = "未設定";
}else if(setarray[i] == "undefined"){
Logger.log(setarray[i])
setarray[i] = "未設定";
}
}
return JSON.stringify(setarray);
}
//設定を保存する
function setProp(value,proper){
//uiを取得する
var ui = SpreadsheetApp.getUi();
//プロパティ値を取得
var Prop = PropertiesService.getScriptProperties();  
//プロパティに格納する
Prop.setProperty(proper, value);
//完了メッセージ
ui.alert("セット完了");
//サイドバーをリロード
setting();
}

//管理者用用コンソールをsidebarで表示する

function setting() {

var ui = SpreadsheetApp.getUi();

var html = HtmlService.createHtmlOutputFromFile('setting').setTitle('管理設定').setSandboxMode(HtmlService.SandboxMode.IFRAME);

ui.showSidebar(html);

}

//コールバック待ち

function waitid(){

var setarray = [];

//プロパティ値を取得

var Prop = PropertiesService.getScriptProperties();

//設定値を配列に格納

setarray.push(Prop.getProperty("instance"));

setarray.push(Prop.getProperty("dbname"));

setarray.push(Prop.getProperty("userid"));

setarray.push(Prop.getProperty("password"));

//null値の場合には未選択に置き換える

var numRows = setarray.length;

for(var i = 0;i<numRows;i++){

if(setarray[i] == null){

setarray[i] = "未設定";

}else if(setarray[i] == "undefined"){

Logger.log(setarray[i])

setarray[i] = "未設定";

}

return JSON.stringify(setarray);

}

//設定を保存する

function setProp(value,proper){

//uiを取得する

var ui = SpreadsheetApp.getUi();

//プロパティ値を取得

var Prop = PropertiesService.getScriptProperties();

//プロパティに格納する

Prop.setProperty(proper, value);

//完了メッセージ

ui.alert("セット完了");

//サイドバーをリロード

setting();

}

インスタンス名や接続DB名などを格納したり、取り出したりするルーチンです。
サイドバーから初期設定をするようになっているので、利用する場合にはメニューの「作業用」⇒「DBの設定」を開くと実行されるので、セットしてから利用しましょう。

HTML側コード

<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons.css">
<link rel="stylesheet" type="text/css" href="https://officeforest.org/wp/Library/cssman/insert.css">
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script>
google.load("visualization", "1", {packages:["corechart"]});
google.setOnLoadCallback(test);
//初期化
function test(){
google.script.run.withSuccessHandler(onSuccess).waitid();
}
//初期化でGAS側から返ってきた値を
function onSuccess(data){
var json = JSON.parse(data);
//インスタンス接続名を取得
if(json[0] == "未設定"){
document.getElementById("test1").innerHTML = "<b>設定：</b>未設定";
}else{
document.getElementById("test1").innerHTML = "<b>設定：" + json[0] + "</b>";
}  
//接続DB名を取得する
if(json[1] == "未設定"){
document.getElementById("test2").innerHTML = "<b>設定：</b>未設定";
}else{
document.getElementById("test2").innerHTML = "<b>設定：" + json[1] + "</b>";
}    
//ユーザIDを取得する
if(json[2] == "未設定"){
document.getElementById("test3").innerHTML = "<b>設定：</b>未設定";
}else{
document.getElementById("test3").innerHTML = "<b>設定：設定済み</b>";
}   
//保存先フォルダの表示
if(json[3] == "未設定"){
document.getElementById("test4").innerHTML = "<b>設定：</b>未設定";
}else{
document.getElementById("test4").innerHTML = "<b>設定：設定済み</b>";
}
}
//管理者アドレス入力ダイアログを出す為のルーチン
function setInstance(){   
var value = prompt("インスタンス名を入力してください", "");
if(value == null){
alert('キャンセルされました。');
}else if(value.length == 0){
alert("値が入っていません。")
}else{
//スクリプトプロパティにセット
google.script.run.setProp(value,"instance");
}
}
function setDBName(){   
var value = prompt("DB名を入力してください", "");
if(value == null){
alert('キャンセルされました。');
}else if(value.length == 0){
alert("値が入っていません。")
}else{
//スクリプトプロパティにセット
google.script.run.setProp(value,"dbname");
}
}
function setUserId(){   
var value = prompt("ユーザIDを入力してください", "");
if(value == null){
alert('キャンセルされました。');
}else if(value.length == 0){
alert("値が入っていません。")
}else{
//スクリプトプロパティにセット
google.script.run.setProp(value,"userid");
}
}
function setPasswd(){   
var value = prompt("パスワードを入力してください", "");
if(value == null){
alert('キャンセルされました。');
}else if(value.length == 0){
alert("値が入っていません。")
}else{
//スクリプトプロパティにセット
google.script.run.setProp(value,"password");
}
}
//ボタンにコマンドを割り当てる
$(function(){
$('#btnOK').click(function(){
setInstance();
});
$('#btnOK2').click(function(){
setDBName();
}); 
$('#btnOK3').click(function(){
setUserId();
}); 
$('#btnOK4').click(function(){
setPasswd();
});  
});
</script>
<style>
div.wasabi{ 
padding:3px 5px;
border-color:#0B0099;
border-width:0 0 1px 7px;
border-style:solid;
background:#F8F8F8;
vertical-align: middle;
}
div.kousin {
overflow-y: auto;
width:95%; height:200px;
padding:3px;
border:1px solid #000;
color:#000000;
background-color:#ffffff;
line-height:1.0em;
margin-left: auto;
margin-right: auto;
}
div.boxContainer {
overflow: hidden;
width:100%;
}
.box {
float: left;
width: 100%;
margin-right:5px;
}
#linkArea{
position: relative;
width: 100%;
height: 100%;
border-color: #999999;
border-style: none;
}
#linkArea a{
display: block;
position: absolute;
top: 0;
left: 0;
width: 100%;
height: 100%;
}
</style>
<div class="wasabi"><img src='https://officeforest.org/wp/library/icons/token.png' border='0'>&nbsp;&nbsp;インスタンス接続名</div>
<p><button id="btnOK" class="action">接続名を入力</button></p>
<div id="test1">設定：未設定</div><p>
<p>この設定は、Google Cloud SQLのMySQLへ接続する為のインスタンス名を設定する場所です。Cloud Consoleから取得できます。</p>
<div class="wasabi"><img src='https://officeforest.org/wp/library/icons/token.png' border='0'>&nbsp;&nbsp;接続DB名</div>
<p><button id="btnOK2" class="action">接続DB名設定</button></p>
<div id="test2">設定：未設定</div><p>
<div class="wasabi"><img src='https://officeforest.org/wp/library/icons/token.png' border='0'>&nbsp;&nbsp;ユーザID</div>
<p><button id="btnOK3" class="action">ユーザID</button></p>
<div id="test3">設定：未設定</div><p>
<div class="wasabi"><img src='https://officeforest.org/wp/library/icons/token.png' border='0'>&nbsp;&nbsp;パスワード</div>
<p><button id="btnOK4" class="action">パスワード</button></p>
<div id="test4">設定：未設定</div><p>

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

google.load("visualization", "1", {packages:["corechart"]});

google.setOnLoadCallback(test);

//初期化

function test(){

google.script.run.withSuccessHandler(onSuccess).waitid();

}

//初期化でGAS側から返ってきた値を

function onSuccess(data){

var json = JSON.parse(data);

//インスタンス接続名を取得

if(json[0] == "未設定"){

document.getElementById("test1").innerHTML = "<b>設定：</b>未設定";

}else{

document.getElementById("test1").innerHTML = "<b>設定：" + json[0] + "</b>";

}

//接続DB名を取得する

if(json[1] == "未設定"){

document.getElementById("test2").innerHTML = "<b>設定：</b>未設定";

}else{

document.getElementById("test2").innerHTML = "<b>設定：" + json[1] + "</b>";

}

//ユーザIDを取得する

if(json[2] == "未設定"){

document.getElementById("test3").innerHTML = "<b>設定：</b>未設定";

}else{

document.getElementById("test3").innerHTML = "<b>設定：設定済み</b>";

}

//保存先フォルダの表示

if(json[3] == "未設定"){

document.getElementById("test4").innerHTML = "<b>設定：</b>未設定";

}else{

document.getElementById("test4").innerHTML = "<b>設定：設定済み</b>";

}

//管理者アドレス入力ダイアログを出す為のルーチン

function setInstance(){

var value = prompt("インスタンス名を入力してください", "");

if(value == null){

alert('キャンセルされました。');

}else if(value.length == 0){

alert("値が入っていません。")

}else{

//スクリプトプロパティにセット

google.script.run.setProp(value,"instance");

}

function setDBName(){

var value = prompt("DB名を入力してください", "");

if(value == null){

alert('キャンセルされました。');

}else if(value.length == 0){

alert("値が入っていません。")

}else{

//スクリプトプロパティにセット

google.script.run.setProp(value,"dbname");

}

function setUserId(){

var value = prompt("ユーザIDを入力してください", "");

if(value == null){

alert('キャンセルされました。');

}else if(value.length == 0){

alert("値が入っていません。")

}else{

//スクリプトプロパティにセット

google.script.run.setProp(value,"userid");

}

function setPasswd(){

var value = prompt("パスワードを入力してください", "");

if(value == null){

alert('キャンセルされました。');

}else if(value.length == 0){

alert("値が入っていません。")

}else{

//スクリプトプロパティにセット

google.script.run.setProp(value,"password");

}

//ボタンにコマンドを割り当てる

$(function(){

$('#btnOK').click(function(){

setInstance();

});

$('#btnOK2').click(function(){

setDBName();

});

$('#btnOK3').click(function(){

setUserId();

});

$('#btnOK4').click(function(){

setPasswd();

});

</script>

<style>

div.wasabi{

padding:3px 5px;

border-color:#0B0099;

border-width:0 0 1px 7px;

border-style:solid;

background:#F8F8F8;

vertical-align: middle;

}

div.kousin {

overflow-y: auto;

width:95%; height:200px;

padding:3px;

border:1px solid #000;

color:#000000;

background-color:#ffffff;

line-height:1.0em;

margin-left: auto;

margin-right: auto;

}

div.boxContainer {

overflow: hidden;

width:100%;

}

.box {

float: left;

width: 100%;

margin-right:5px;

}

#linkArea{

position: relative;

width: 100%;

height: 100%;

border-color: #999999;

border-style: none;

}

#linkArea a{

display: block;

position: absolute;

top: 0;

left: 0;

width: 100%;

height: 100%;

}

</style>

<div class="wasabi"><img src='https://officeforest.org/wp/library/icons/token.png' border='0'>  インスタンス接続名</div>

<p><button id="btnOK" class="action">接続名を入力</button></p>

<p>この設定は、Google Cloud SQLのMySQLへ接続する為のインスタンス名を設定する場所です。Cloud Consoleから取得できます。</p>

<div class="wasabi"><img src='https://officeforest.org/wp/library/icons/token.png' border='0'>  接続DB名</div>

<p><button id="btnOK2" class="action">接続DB名設定</button></p>

<div class="wasabi"><img src='https://officeforest.org/wp/library/icons/token.png' border='0'>  ユーザID</div>

<p><button id="btnOK3" class="action">ユーザID</button></p>

<div class="wasabi"><img src='https://officeforest.org/wp/library/icons/token.png' border='0'>  パスワード</div>

<p><button id="btnOK4" class="action">パスワード</button></p>

テーブルを作成する

//接続URL
var instanceUrl = 'jdbc:google:mysql://';
//テーブルをつくる
function mysql_createTable(){
//uiを取得
var ui = SpreadsheetApp.getUi();
//プロパティ値を取得
var Prop = PropertiesService.getScriptProperties();
var instance = Prop.getProperty("instance");
var dbname = Prop.getProperty("dbname");
var userid = Prop.getProperty("userid");
var userPwd = Prop.getProperty("password");
//テーブル作成クエリ実行
var conn = Jdbc.getCloudSqlConnection(instanceUrl + instance + "/" + dbname, userid, userPwd);
conn.createStatement().execute('CREATE TABLE jinji '
+ '(ID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(ID), '
+ 'NAME VARCHAR(255), BUSYO VARCHAR(255), '
+ 'AGE INT(11), JOB VARCHAR(255));');
//作ったテーブルを参照
var test = conn.createStatement().execute('SHOW TABLES FROM ' + dbname + ';');
//終了処理
if(test == true){
ui.alert("作成完了");
}else{
ui.alert("なんか失敗したみたいですよ。");
}
}

//接続URL

var instanceUrl = 'jdbc:google:mysql://';

//テーブルをつくる

function mysql_createTable(){

//uiを取得

var ui = SpreadsheetApp.getUi();

//プロパティ値を取得

var Prop = PropertiesService.getScriptProperties();

var instance = Prop.getProperty("instance");

var dbname = Prop.getProperty("dbname");

var userid = Prop.getProperty("userid");

var userPwd = Prop.getProperty("password");

//テーブル作成クエリ実行

var conn = Jdbc.getCloudSqlConnection(instanceUrl + instance + "/" + dbname, userid, userPwd);

conn.createStatement().execute('CREATE TABLE jinji '

+ '(ID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(ID), '

+ 'NAME VARCHAR(255), BUSYO VARCHAR(255), '

+ 'AGE INT(11), JOB VARCHAR(255));');

//作ったテーブルを参照

var test = conn.createStatement().execute('SHOW TABLES FROM ' + dbname + ';');

//終了処理

if(test == true){

ui.alert("作成完了");

}else{

ui.alert("なんか失敗したみたいですよ。");

}

Cloud SQLのMySQLへjinjiというテーブルを作成します。
各種設定値はスクリプトプロパティより取得しています。
instanceUrlは以降のスクリプト共通のグローバル変数です。

テーブルデータを取得しスプレッドシートに追記

//データベースに接続してデータを取得する
function mysql_select() {
//uiを取得
var ui = SpreadsheetApp.getUi();
//プロパティ値を取得
var Prop = PropertiesService.getScriptProperties();
var instance = Prop.getProperty("instance");
var dbname = Prop.getProperty("dbname");
var userid = Prop.getProperty("userid");
var userPwd = Prop.getProperty("password");
//テーブル作成クエリ実行
var conn = Jdbc.getCloudSqlConnection(instanceUrl + instance + "/" + dbname, userid, userPwd);
//開始時間をセット
var start = new Date();
//jinjiテーブルへ最大20件限定で取得
var stmt = conn.createStatement();
stmt.setMaxRows(20);
var results = stmt.executeQuery('SELECT * FROM jinji');
//取得結果より列数を取得
var numCols = results.getMetaData().getColumnCount();
//取得結果を配列に取得
var dataArray = [];
while (results.next()) {
var row = [];
for (var col = 0; col < numCols; col++) {
row.push(results.getString(col + 1));
}
dataArray.push(row);
}
//データベース接続を終了する
results.close();
stmt.close();
//取得内容をスプレッドシートに書き込む
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("cloudsql");
ss.getRange("A2:E").clearContent();
var endrow = Number(ss.getLastRow()) + 1;
var lastColumn = dataArray[0].length;　　//カラムの数を取得する
var lastRow = dataArray.length;  　　　　//行の数を取得する
ss.getRange(endrow,1,lastRow,lastColumn).setValues(dataArray);
//終了時間をセット
var end = new Date();
//開始時間と終了時間の差をAlert表示
var sTime = start.getTime();
var eTime = end.getTime();
var timecount = (eTime - sTime) / 1000;
ui.alert(timecount + "秒でデータを取得しました。");
}

//データベースに接続してデータを取得する

function mysql_select() {

//uiを取得

var ui = SpreadsheetApp.getUi();

//プロパティ値を取得

var Prop = PropertiesService.getScriptProperties();

var instance = Prop.getProperty("instance");

var dbname = Prop.getProperty("dbname");

var userid = Prop.getProperty("userid");

var userPwd = Prop.getProperty("password");

//テーブル作成クエリ実行

var conn = Jdbc.getCloudSqlConnection(instanceUrl + instance + "/" + dbname, userid, userPwd);

//開始時間をセット

var start = new Date();

//jinjiテーブルへ最大20件限定で取得

var stmt = conn.createStatement();

stmt.setMaxRows(20);

var results = stmt.executeQuery('SELECT * FROM jinji');

//取得結果より列数を取得

var numCols = results.getMetaData().getColumnCount();

//取得結果を配列に取得

var dataArray = [];

while (results.next()) {

var row = [];

for (var col = 0; col < numCols; col++) {

row.push(results.getString(col + 1));

}

dataArray.push(row);

}

//データベース接続を終了する

results.close();

stmt.close();

//取得内容をスプレッドシートに書き込む

var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("cloudsql");

ss.getRange("A2:E").clearContent();

var endrow = Number(ss.getLastRow()) + 1;

var lastColumn = dataArray[0].length;　　//カラムの数を取得する

var lastRow = dataArray.length; 　　　　//行の数を取得する

ss.getRange(endrow,1,lastRow,lastColumn).setValues(dataArray);

//終了時間をセット

var end = new Date();

//開始時間と終了時間の差をAlert表示

var sTime = start.getTime();

var eTime = end.getTime();

var timecount = (eTime - sTime) / 1000;

ui.alert(timecount + "秒でデータを取得しました。");

}

料金が怖いので、現在このコードは、stmt.setMaxRows(20);にて20件の取得に留めています。
データは洗い替えで取得しますので、現在のスプレッドシートの値はクリアされます。
取得に掛かる時間を計測してアラート表示します。
実際には、「SELECT * FROM jinji」なんて大雑把なやり方ではなく、様々な抽出条件を加えて必要なテーブルの必要なカラムだけ絞って取得を行いますが、今回は大したデータ量でもないためこのような書き方をしています。
また、サービスによってはDBではなくキャッシュからであれば安価にデータを取得できるといったようなものもあるため、自分で使用するDBaaSサービスの内容をよく把握してから、クエリは投げましょう。
接続する部分で変数connにデータベース接続用URLを構築し、stmt.executeQueryにてクエリ発行をしています。これでデータの塊が取得できたので、配列に１つずつ収めて、スプレッドシートに追記するといった形を取っています。
最後にかならずデータベースはクローズしましょう。時間課金の場合余計な時間消費の原因になったりします。使ったらさっさと閉じるというのがお約束です。（いちおうGASでは関数が実行終了と共に自動クローズされるようになってはいるみたいですが）。

データベースにレコードを追加する

次にHTMLサービスで作成したフォームに情報を入れて、このDBへデータを１行追加するというのをやってみたいと思います。スプレッドシート上でHTMLサービスで作成したダイアログに入力後、送信を押すとCloud SQLのMySQLへデータがインサートされるという単純な仕組みです。

GAS側コード

//データベースに接続してデータを登録する
function mysql_insert(data) {
//uiを取得
var ui = SpreadsheetApp.getUi();
//プロパティ値を取得
var Prop = PropertiesService.getScriptProperties();
var instance = Prop.getProperty("instance");
var dbname = Prop.getProperty("dbname");
var userid = Prop.getProperty("userid");
var userPwd = Prop.getProperty("password");
//テーブル作成クエリ実行
var conn = Jdbc.getCloudSqlConnection(instanceUrl + instance + "/" + dbname, userid, userPwd);
//開始時間をセット
var start = new Date();
//jinjiテーブルへデータをインサート
var query = "INSERT INTO `" + dbname + "`.`jinji` (`NAME`, `BUSYO`, `AGE`, `JOB`) VALUES (?,?,?,?)";
var stmt = conn.prepareStatement(query);
stmt.setString(1,data[0]);
stmt.setString(2,data[1]);
stmt.setString(3,data[2]);
stmt.setString(4,data[3]);
stmt.execute();
//データベース接続を終了する
stmt.close();
conn.close();
//終了時間をセット
var end = new Date();
//開始時間と終了時間の差をAlert表示
var sTime = start.getTime();
var eTime = end.getTime();
var timecount = (eTime - sTime) / 1000;
ui.alert(timecount + "秒でデータを追加しました。");
}

//データベースに接続してデータを登録する

function mysql_insert(data) {

//uiを取得

var ui = SpreadsheetApp.getUi();

//プロパティ値を取得

var Prop = PropertiesService.getScriptProperties();

var instance = Prop.getProperty("instance");

var dbname = Prop.getProperty("dbname");

var userid = Prop.getProperty("userid");

var userPwd = Prop.getProperty("password");

//テーブル作成クエリ実行

var conn = Jdbc.getCloudSqlConnection(instanceUrl + instance + "/" + dbname, userid, userPwd);

//開始時間をセット

var start = new Date();

//jinjiテーブルへデータをインサート

var query = "INSERT INTO `" + dbname + "`.`jinji` (`NAME`, `BUSYO`, `AGE`, `JOB`) VALUES (?,?,?,?)";

var stmt = conn.prepareStatement(query);

stmt.setString(1,data[0]);

stmt.setString(2,data[1]);

stmt.setString(3,data[2]);

stmt.setString(4,data[3]);

stmt.execute();

//データベース接続を終了する

stmt.close();

conn.close();

//終了時間をセット

var end = new Date();

//開始時間と終了時間の差をAlert表示

var sTime = start.getTime();

var eTime = end.getTime();

var timecount = (eTime - sTime) / 1000;

ui.alert(timecount + "秒でデータを追加しました。");

}

別に用意した入力フォームから呼び出して利用しています。
Insert Intoにてレコードを一個追加しています。スプレッドシートに記述しても追加はされたりしません。
HTMLサービスのダイアログの値を取得して、query文のInsert intoに続けて値をはめ込むコードです。ハマりどころは必ずDB名を指定しなければならないので、テーブル名だけでInsert Intoを実行するとエラーになります。
IDを入れていませんが、DB側でAuto Increment指定をしていないと、やはりエラーになるので、主キーになるIDはAuto Incrementするか？IDは独自に重複しないように生成する必要があります。
setStringで１個目〜４個目のパラメータにはめ込んでゆきます。そして、executeでインサート実行です。insert intoはprepareStatementというメソッドを使用する点も注意して下さい。

HTML側コード

<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons.css">
<script type="text/javascript">
//バックアップの実行
function insertman(){
//テキストボックスの値を取得
var array = [];
var text = document.getElementById("name").value;
array.push(text);
text = document.getElementById("busyo").value;
array.push(text);
text = document.getElementById("age").value;
array.push(text);    
text = document.getElementById("job").value;
array.push(text);    
//プログレス表示する
document.getElementById("button").style.display = "none";
document.getElementById("manual").style.display = "none";
document.getElementById("inputman").style.display = "none";
document.getElementById("progress").style.display = "block";
//データを登録するコマンドを実行
google.script.run.mysql_insert(array);
}
</script>
<p id="manual">実行すると、Azureのデータベースに人員が登録されます。</p>
<div id ="inputman">
<p><b>名前：</b><input type="text" id="name" name="name" size="20" placeholder="例：桜木花道"></p>
<p><b>部署：</b><input type="text" id="busyo" name="name" size="15" placeholder="例：総務部"></p>
<p><b>年齢：</b><input type="text" id="age" name="name" size="10" maxlength="2" placeholder="例：28"></p>
<p><b>役職：</b><input type="text" id="job" name="name" size="15" placeholder="例：部長"></p>
</div>
<p>
<hr>
<div id="button" style="text-align:center;display:block">
<button class="action" onclick='insertman()'>登録実行</button>
</div>
<div id="progress" style="text-align:center;display:none">
<img border="0" src="https://officeforest.org/wp/library/ProgressSpinner.gif">
<b><p id="backman">登録作業中・・・</p></b>
</div>

//バックアップの実行

function insertman(){

//テキストボックスの値を取得

var array = [];

var text = document.getElementById("name").value;

array.push(text);

text = document.getElementById("busyo").value;

array.push(text);

text = document.getElementById("age").value;

array.push(text);

text = document.getElementById("job").value;

array.push(text);

//プログレス表示する

document.getElementById("button").style.display = "none";

document.getElementById("manual").style.display = "none";

document.getElementById("inputman").style.display = "none";

document.getElementById("progress").style.display = "block";

//データを登録するコマンドを実行

google.script.run.mysql_insert(array);

}

</script>

<p id="manual">実行すると、Azureのデータベースに人員が登録されます。</p>

</div>

<p>

<hr>

</div>

<b><p id="backman">登録作業中・・・</p></b>

</div>

入力用のダイアログのHTMLです。
登録実行をすると、mysql_insertに値なげて、実行します。

図：UIフォームはじっくり作り込みましょう。

データベースのレコードを更新する

次にレコードを更新をする事例です。。今回はIDが1の人間の役職名を変更するというシーンを想定してコードを記述してあります。よって、UPDATE文だけでなくWHERE句も出てきます。

//データベースに接続してデータを登録する
function mysql_update() {
//uiを取得
var ui = SpreadsheetApp.getUi();
//プロパティ値を取得
var Prop = PropertiesService.getScriptProperties();
var instance = Prop.getProperty("instance");
var dbname = Prop.getProperty("dbname");
var userid = Prop.getProperty("userid");
var userPwd = Prop.getProperty("password");
//テーブル作成クエリ実行
var conn = Jdbc.getCloudSqlConnection(instanceUrl + instance + "/" + dbname, userid, userPwd);
//開始時間をセット
var start = new Date();
//jinjiテーブルへデータをインサート
var query = "UPDATE `"  + dbname + "`.`jinji` SET JOB=? WHERE ID=?";
var stmt = conn.prepareStatement(query);
stmt.setString(1,"職員");
stmt.setInt(2,1);
stmt.execute();
//データベース接続を終了する
stmt.close();
conn.close();
//終了時間をセット
var end = new Date();
//開始時間と終了時間の差をAlert表示
var sTime = start.getTime();
var eTime = end.getTime();
var timecount = (eTime - sTime) / 1000;
ui.alert(timecount + "秒でデータを更新しました。");
}

//データベースに接続してデータを登録する

function mysql_update() {

//uiを取得

var ui = SpreadsheetApp.getUi();

//プロパティ値を取得

var Prop = PropertiesService.getScriptProperties();

var instance = Prop.getProperty("instance");

var dbname = Prop.getProperty("dbname");

var userid = Prop.getProperty("userid");

var userPwd = Prop.getProperty("password");

//テーブル作成クエリ実行

var conn = Jdbc.getCloudSqlConnection(instanceUrl + instance + "/" + dbname, userid, userPwd);

//開始時間をセット

var start = new Date();

//jinjiテーブルへデータをインサート

var query = "UPDATE `" + dbname + "`.`jinji` SET JOB=? WHERE ID=?";

var stmt = conn.prepareStatement(query);

stmt.setString(1,"職員");

stmt.setInt(2,1);

stmt.execute();

//データベース接続を終了する

stmt.close();

conn.close();

//終了時間をセット

var end = new Date();

//開始時間と終了時間の差をAlert表示

var sTime = start.getTime();

var eTime = end.getTime();

var timecount = (eTime - sTime) / 1000;

ui.alert(timecount + "秒でデータを更新しました。");

}

INSERT INTOとほぼおなじスタイルですが、SQL文が異なります。UPDATE文を使用します。
SET JOBにて役職フィールドに値をセットしています。WHRER句でIDを限定しています。
それぞれ、をsetStringとsetIntで値を入れると、IDが1の人間の役職が平社員から職員へと変わりました。WHEREで制限をしなければ全員変わることになります。
stmt.setString(1,"職員");、stmt.setInt(2,1)が肝です。SQL文の1個目の「?」に「職員」をString型でセットするという意味。これで、ID=7がSQL文にセットされるわけです。つづけて、SQL文の2個目の「?」に1をInt型でセットし、これで、IDが1のものに対して、JOBを職員にするという意味になります。

データベースのレコードを削除する

データベースのレコードを同じくWHRER句で条件をつけて削除します。今回はIDが7の専務を消し去りたいと思います。POSITIONで専務としても良いのですが、それだと無関係の専務まで消えてしまうので注意してください。あくまでIDが7の魔王専務です。WHRER句はANDをつけて２つ条件をつけても良いでしょう。

//データベースに接続してデータを削除する
function mysql_delete() {
//uiを取得
var ui = SpreadsheetApp.getUi();
//プロパティ値を取得
var Prop = PropertiesService.getScriptProperties();
var instance = Prop.getProperty("instance");
var dbname = Prop.getProperty("dbname");
var userid = Prop.getProperty("userid");
var userPwd = Prop.getProperty("password");
//テーブル作成クエリ実行
var conn = Jdbc.getCloudSqlConnection(instanceUrl + instance + "/" + dbname, userid, userPwd);
//開始時間をセット
var start = new Date();
//jinjiテーブルへデータを削除
var query = "DELETE FROM `" + dbname + "`.`jinji` WHERE ID=?";
var stmt = conn.prepareStatement(query);
stmt.setInt(1,7);
stmt.execute();
//データベース接続を終了する
stmt.close();
conn.close();
//終了時間をセット
var end = new Date();
//開始時間と終了時間の差をAlert表示
var sTime = start.getTime();
var eTime = end.getTime();
var timecount = (eTime - sTime) / 1000;
ui.alert(timecount + "秒でデータを削除しました。");
}

//データベースに接続してデータを削除する

function mysql_delete() {

//uiを取得

var ui = SpreadsheetApp.getUi();

//プロパティ値を取得

var Prop = PropertiesService.getScriptProperties();

var instance = Prop.getProperty("instance");

var dbname = Prop.getProperty("dbname");

var userid = Prop.getProperty("userid");

var userPwd = Prop.getProperty("password");

//テーブル作成クエリ実行

var conn = Jdbc.getCloudSqlConnection(instanceUrl + instance + "/" + dbname, userid, userPwd);

//開始時間をセット

var start = new Date();

//jinjiテーブルへデータを削除

var query = "DELETE FROM `" + dbname + "`.`jinji` WHERE ID=?";

var stmt = conn.prepareStatement(query);

stmt.setInt(1,7);

stmt.execute();

//データベース接続を終了する

stmt.close();

conn.close();

//終了時間をセット

var end = new Date();

//開始時間と終了時間の差をAlert表示

var sTime = start.getTime();

var eTime = end.getTime();

var timecount = (eTime - sTime) / 1000;

ui.alert(timecount + "秒でデータを削除しました。");

}

実行すると、IDが7の専務が消えてくれます。このようにSELECT, INSERT INTO, UPDATE, DELETEの４つが主なデータベースの操作になります
stmt.setInt(1,7);が肝です。SQL文の1個目の「?」に7をInt型でセットするという意味。これで、ID=7がSQL文にセットされるわけです。複数ある場合には、複数セットする。7を変数に置き換えれば動的に変更が出来ますね。
他にもテーブルの作成や削除などといったものもGoogle Apps ScriptのJDBC Serviceには用意されているので、使いやすいようにラッピングした関数などを用意しておくと良いでしょう。

Access to Google Cloud SQL

手元にあるAccessアプリケーションからも接続出来るようにし、Google Apps ScriptではなくAccessをクライアントにして運用ができたら、手持ちのアプリを簡単にクラウド化する事が出来るのではないか？と思い、チャレンジしてみました。

MySQL Migration Toolkitで変換

MySQL Migration Toolkitはすでに開発終了済みで古いツールなのですが、AccessデータベースをMySQLのテーブルに変換し送り込むツールです。使用する為には、旧Java SE 5.0が必要なので、そちらも別途インストールが必要。また、インポート時に型や文字コードを自動判定するのですが、失敗するので、適当に進めず文字コードなどはとくにしっかり指定しておきましょう。。

あらかじめaccdbファイルをmdbファイルへ変換して作っておく
MySQL Migration Toolkitにてmdbを指定
接続先にはGoogle Cloud SQLのIPアドレスとID、パスワードを指定。
主キーのないテーブルは変換に失敗します。
そのままポチポチ適当に進めていくと、無事に接続完了。インポートが始まる。
途中のObject Mappingに於いて、Migration of Type schemeは「Multilanguage」、Migration Type Tableは「Data Consistency/Multilanguge」にしないと、テーブルの文字コードが一部Latin1になってしまうので注意。（10.がそれです）
インポートが完了したら、データの転送が開始されるのだが、ここはなぜか失敗していた。
完了
ただし、一部の列の型がおかしな型になっていて、JANコードなどの大きな数値が入らなかったので、手動で型変換をしてあげました。こればかりは、避けられないので、データ転送が失敗するのはこれが原因です。
また、Incorrect string valueというエラーが入力時に出るので、見てみたらdatabaseの文字コードがlatin1になってた・・・データ転送が失敗したのはこういうのが原因ですね・・・schema editorからUTF8に変更しました。
テーブルのVarchar型のフィールドも「スウェーデン語」の文字コードになっているので、こちらもUTF8に変換してあげましょう。。。。
もう一度同じ作業をすると上書きでインポートされます。

MySQL WorkbenchでGoogle Cloud SQLの中身を見てみたところ、データは空っぽでしたが、無事にテーブルは出来ていて、きちんとテーブル定義も変換して取り込めていました。テーブル定義が非常に面倒なので、これだけでも非常に助かるツールですね。Windows版しかありませんが。

図：mdbファイルのテーブル構造をインポート出来る

図：データの転送は失敗しました。

図：テーブル定義はばっちりインポート完了

図：文字コードがスウェーデン語になってた・・・

図：データーベース自体、UTF8に変更してあげました。

図：テーブルおよびvarchar型の全フィールドの文字コードも変更が必要

図：本当はこの画面できちんと文字コード指定してあげないと駄目

AccessからODBC接続してみた

手持ちのAccess2013よりODBC接続でテーブルをリンクできれば、データはGoogle Cloud SQL、開発はコレまで通りAccessで可能になるのではと、MySQL Connector ODBCドライバをインストールし、設定をしてリンクテーブルでつなげてみました。なお、5.3 DriverはVisual Studio 2013 の Visual C++ 再頒布可能パッケージに依存しているので、それもインストール済みでなければインストールできません。

※「作成後の設定を施する」にて、接続元IP許可に自分のアクセス元のIPアドレスを登録しておく必要があります。

MySQL Migration Tool kitにドライバが入ってるのでドライバインストールはスキップ
Access 2013を起動する
外部データタブを開く
インポートとリンクに「ODBCデータベース」があるのでクリックする
「リンクテーブルを作成しソースデータにリンクする」を選んで次へ
データソースの選択画面では、コンピュータデータソースを開く。
まだ作ってないので、新規作成ボタンをクリック
データソースの新規作成では、ユーザデータソースを選ぶ
MySQL ODBC 5.3 Unicode Driverを選択
次へ進んで完了する
接続画面が出るので、Google Cloud SQLのIPアドレスとIDおよびパスワードを入力。
11の画面はコントロールパネルの管理ツールにあるODBCデータソースアドミニストレーターにあるので再編集が可能です。
データソースの選択画面に戻ったら、作ったソースを選んでOKを押す。
接続に成功すると、テーブル一覧が出てくるので、選べばリンクテーブルとしてAccessに登録されます。

図：MySQL Driverが登録されている

図：ODBCドライバからの接続画面

図：ユーザDSNとしてMySQLでの接続設定が出来た

図：無事にAccessから接続完了。

図：きちんとデータを入力できました。

Cloud SQL Proxyにてポートフォワーディングさせる

AccessでODBC接続させてデータの取得には成功しました。これを実運用する場合、企業などのように「プロキシーを利用した固定IP」が接続元ならば、それらのIPを登録すれば問題ありません。必ずユーザはプロキシーを経由するからです。

しかし、そうではない環境の場合、接続元のIPアドレスは固定ではなく、場合によっては特定のアドレスレンジまるごと登録といった雑な許可をする必要があります。そこで用意されているのが、Google Cloud SQL Proxyを利用した接続です。この方式の場合、DBへはグローバルIPアドレスではなく、ポートフォワーディングされたlocalhostで接続が可能になります。クライアント側のIPアドレスが変動しても問題なく、継続して接続が可能になります。接続手順は以下の通り（今回はWindows版で説明します）

※Linux, OSX, Windowsのそれぞれ32bitと64bit用にプロキシープログラムが用意されています。

※Cloud SDKを利用した方法もあるのですが、PCにPythonがインストールされている必要があるため、手間が多いので今回はやめておきました。専用のサーバでも用意する場合には良い選択肢かもしれません。

サービスアカウントの作成

Google Cloud Consoleにて該当のプロジェクトの「IAMと管理」に入ります。
サービスアカウントをクリックします。
上部にある「サービスアカウントを作成」をクリックします。
適当なアカウント名を入れて、作成ボタンを押します。
役割は通常は「編集者」でオッケーです。閲覧のみも可能。続行ボタンを押して続けます。
次に、キーの作成ボタンをクリック
キータイプを問われるので「JSON」を選択します。
秘密鍵がダウンロードされます。これを適当なフォルダに保存します。大切なものなので、流出しないように！！

プロキシープログラムの設定と実行

サービスアカウントの作成とキーを手に入れました。続けて、cloud_proxyのプログラムをダウンロードし設定・実行します。キーはこのプログラムの中で利用します。

Cloud SQL Proxyのページにおいて「プロキシーをインストールする」にて、Windows用のexeをダウンロードします。ファイル名はcloud_sql_proxy.exeと変更しておきます。これを適当な場所に保存する。今回はマイドキュメント直下にしました。
コマンドプロンプトを立ち上げる
マイドキュメント直下にcmdにて移動する
あらかじめ、前項8.の秘密鍵のファイルのパスを調べておきましょう。
Cloud SQLのインスタンスのページにて、該当のインスタンスに入り、インスタンス接続名を控えておきます。
以下のコマンドを実行します。プロキシーが起動し常駐します。
127.0.0.7:3306にODBCで接続が可能になります。もちろん、MySQL Workbenchもこのlocalhostへ接続が可能になります。
Accessなどからこの処理を自動化する場合には、accdbのカレントディレクトリにcloud_sql_proxy.exeおよび秘密鍵のJSONを配置し、shellを実行するコードを実行すれば良いと思います。ファイルのパスも簡単にCurrentProject.pathで取得できますし。

//クラウドプロキシ起動のコマンドライン
cloud_sql_proxy -instances=インスタンス接続名=tcp:3306 -credential_file=秘密鍵JSONファイルのパス &

1 2	//クラウドプロキシ起動のコマンドライン cloud_sql_proxy -instances=インスタンス接続名=tcp:3306 -credential_file=秘密鍵JSONファイルのパス &

図：接続ログも表示されます。

Accessで起動からODBC接続までやらせてみた

cloud_sql_proxy.exeおよび秘密鍵のJSONファイルを元にMySQL ODBC 5.3 Unicode Driverにて、DSNレスで接続させてみました。Autoexecマクロで、Accessファイル起動時に実行させれば、自動的にプロキシー起動⇒ODBC接続でリンクテーブル作成が可能になります。ただしこのコードには以下の課題があります。

VBAからのコマンドライン実行は

起動時にとりあえずリンクテーブル全削除コードを入れる必要がある。
データベース接続用のIDおよびPASSワードを入力させるフォームが必要（現在はVBAに直書きであるため）
2.を自動でやるならば、VBAからPowershell経由で資格情報マネージャにPasswordを読み書きさせるコードを記述する必要があります（VBAから直接、資格情報マネージャは触れない為）
できれば、インスタンス接続名も資格情報に格納できたら最高だ。
終了時にコマンドプロンプトどうやって終わらせようか・・・

とりあえず、簡単なコードは以下の通り。MySQL ODBC 5.3 Unicode Driverを利用していますが、DSN設定を利用せずにリンクテーブルを作成しています。

Option Compare Database
Private Const WAIT_TIME As Single = 0.1
Public Const CNBASE = "ODBC;Driver={MySQL ODBC 5.3 Unicode Driver};SERVER=localhost;PORT=3306;" & _
"DATABASE=database;UID=ユーザID;PWD=パスワード;"
'Cloud SQLにProxy経由で接続する
Public Function startsql()
'接続用変数
Dim instance As String
Dim clientjson As String
Dim cmdline As String
Dim CmdOut As String
'リンクテーブル関係
Dim td As Variant
Dim uid As String
Dim pwd As String
Dim table As String
'接続情報をセットする
instance = "インスタンス接続名をここに入れる"
clientjson = CurrentProject.Path & "\client.json"
proxy = CurrentProject.Path & "\cloud_sql_proxy -instances="
'コマンドラインでcloud sql proxyを立ち上げる
cmdline = proxy & instance & "=tcp:3306 -credential_file=" & clientjson & " &"
Dim WSH As Object
Set WSH = CreateObject("WScript.Shell")
CmdOut = WSH.Run("%ComSpec% /c " & cmdline)
Set WSH = Nothing
'127.0.0.1:3306に対して、MySQL ODBCでリンクテーブル接続（DSN無しで実現）
Dim TBL As DAO.TableDef
Set TBL = CurrentDb.CreateTableDef("実績データ")
TBL.Connect = CNBASE
TBL.SourceTableName = "実績データ"
CurrentDb.TableDefs.Append TBL
CurrentDb.TableDefs("実績データ").RefreshLink
Set TBL = Nothing
End Function

Option Compare Database

Private Const WAIT_TIME As Single = 0.1

Public Const CNBASE = "ODBC;Driver={MySQL ODBC 5.3 Unicode Driver};SERVER=localhost;PORT=3306;" & _

"DATABASE=database;UID=ユーザID;PWD=パスワード;"

'Cloud SQLにProxy経由で接続する

Public Function startsql()

'接続用変数

Dim instance As String

Dim clientjson As String

Dim cmdline As String

Dim CmdOut As String

'リンクテーブル関係

Dim td As Variant

Dim uid As String

Dim pwd As String

Dim table As String

'接続情報をセットする

instance = "インスタンス接続名をここに入れる"

clientjson = CurrentProject.Path & "\client.json"

proxy = CurrentProject.Path & "\cloud_sql_proxy -instances="

'コマンドラインでcloud sql proxyを立ち上げる

cmdline = proxy & instance & "=tcp:3306 -credential_file=" & clientjson & " &"

Dim WSH As Object

Set WSH = CreateObject("WScript.Shell")

CmdOut = WSH.Run("%ComSpec% /c " & cmdline)

Set WSH = Nothing

'127.0.0.1:3306に対して、MySQL ODBCでリンクテーブル接続（DSN無しで実現）

Dim TBL As DAO.TableDef

Set TBL = CurrentDb.CreateTableDef("実績データ")

TBL.Connect = CNBASE

TBL.SourceTableName = "実績データ"

CurrentDb.TableDefs.Append TBL

CurrentDb.TableDefs("実績データ").RefreshLink

Set TBL = Nothing

End Function

他のデータベースに接続する

今回はGoogle Cloud SQLをテーマに外部のデータベース接続についてこれまで記述しました。しかし、Google Apps ScriptのJDBCサービスでは他にもMicrosoft SQL Server、Oracleに対応しています。これらは前項で紹介したgetConnectionにて接続用URLを渡せば、操作する事が可能です。

JDBCサービスからのアクセスは以下のIP範囲からのものを、相手方DBサーバで許可（ホワイトリスト）してあげる必要があります。

64.18.0.0 - 64.18.15.255
64.233.160.0 - 64.233.191.255
66.102.0.0 - 66.102.15.255
66.249.80.0 - 66.249.95.255
72.14.192.0 - 72.14.255.255
74.125.0.0 - 74.125.255.255
173.194.0.0 - 173.194.255.255
207.126.144.0 - 207.126.159.255
209.85.128.0 - 209.85.255.255
216.239.32.0 - 216.239.63.255

64.18.0.0 - 64.18.15.255

64.233.160.0 - 64.233.191.255

66.102.0.0 - 66.102.15.255

66.249.80.0 - 66.249.95.255

72.14.192.0 - 72.14.255.255

74.125.0.0 - 74.125.255.255

173.194.0.0 - 173.194.255.255

207.126.144.0 - 207.126.159.255

209.85.128.0 - 209.85.255.255

216.239.32.0 - 216.239.63.255

また、MySQLからフォークして作成されているMariaDBやCloud SQLにあるPostgreSQLについては、JDBCサービスではサポートされていません。stackoverflowでは、Google Apps ScriptからPostgreSQLに関しては、REST APIアクセス化する為のPostgRESTというものがGithubにて公開されています。GASからはUrlfetchAppでアクセスする方法を使うようです。しかし、UrlfetchAppは結構使用制限の厳しいものなので、素直にMySQLのレンタルを選んだほうが幸せになれると思います。

其の場合、GASからのアクセスはねこの足跡Rさんのページにて、IP範囲についてのエントリーが紹介されています。

MariaDBについては、MySQLからフォークされているものの、お互い最新版では互換性が薄れています。其のため、Google Apps ScriptからMySQLとして「MariaDB」を使う場合には、MariaDBのバージョンは5.5である必要があります。これは、MySQL WorkbenchでMariaDBに接続する場合も同じです。

ポイント

DBaaSサービスは時間や転送量などに応じての従量制課金サービスなので、SQLなどを発行する場合には、その時必要なレコードだけに絞って取得するようにしないと、どんどん課金されるので注意。
その為、Googleスプレッドシートなどを土台にしたアプリケーションを作ってる感覚で作ってはいけません。先にDBに投げるに当って必要な情報をすべて用意し、必要な情報に絞った状態でデータの取得をするように心がけましょう。
データベースシステムですので、データの要求はSQL言語を扱える必要があります。
GROUP BY句やHAVING句、JOIN句, ORDER BY句、DISTINCT句、サブクエリ等覚えることはたくさんありますが、AccessやスプレッドシートのQUERY関数などで予行演習をしておくと良いでしょう。
今回のプログラムは、DB接続パスワードやユーザIDがそのままスクリプト内にあるので、ここはログインフォームを作って対応が必要です。この辺のうまい仕組みはまた今後考えてみたいと思います。
また、ユーザIDに対しては、操作できる範囲を絞るよう設定をしましょう。

図：ユーザの権限、テーブル範囲を絞っておく

G Suite値上げに見る他のサービス棚卸し

Google Apps Scriptで書籍検索を実装してみる【GAS】

今回使用するクラス等やサービス

事前準備

データベースを使う利点

利用料金と確認法

Google Cloud SQLを使う場合

プロジェクトを移動

APIの有効化

インスタンスの作成

作成後の設定を施す

Cloud Shellで作業をする

MySQL Workbenchで接続・作業

Google Apps Scriptから接続

JDBCを使うに当たって

データベース接続用URL

ソースコード

セッティング関係のコード

GAS側コード

HTML側コード

テーブルを作成する

テーブルデータを取得しスプレッドシートに追記

データベースにレコードを追加する

GAS側コード

HTML側コード

データベースのレコードを更新する

データベースのレコードを削除する

Access to Google Cloud SQL

MySQL Migration Toolkitで変換

AccessからODBC接続してみた

Cloud SQL Proxyにてポートフォワーディングさせる

サービスアカウントの作成

プロキシープログラムの設定と実行

Accessで起動からODBC接続までやらせてみた

他のデータベースに接続する

ポイント

関連リンク

コメントを残す コメントをキャンセル

G Suite値上げに見る他のサービス棚卸し

Google Apps Scriptで書籍検索を実装してみる【GAS】

コメントを残すコメントをキャンセル