社内で様々なアプリケーションのデータ記録場所として、Googleスプレッドシートを使っているわけなのですが、スプレッドシートには最大200万セルという制限があり、またLockServiceで排他制御があるとは言え簡易的なものでしかなく、また昨今の不安定なGoogle Driveの状況を見ていると、フォームもデータ記録先も同じDriveというのはなかなかに心もとないところがあります。

本格的に社内アプリケーションを構築するのであれば、スプレッドシートではなくDBを用いるほうが良いです。Google App Engineというものもあるのですが、GASではなく主にJavaで作成するものなので、あくまでGASでこれまで通り出来るという点が重要です。

そこで利用したいのが外部データベース。自前で用意したDBサーバや最近流行りのDBaaSサービスで数分で用意できるクラウドデータベースなどを保存先として、クライアントはこれまでのGoogle Apps Scriptを使うというやり方です。主にAmazon AWSGoogle Cloud SQLなどがそれらに該当するのですが、今回は無償で20MBほどの容量しかないのですが、テスト目的でMicrosoft AzureのMySQLサービスであるClearDBにデータを保存し、呼び出してみることにしました。

※Azureのサービスは30日間無料で使用する事が出来ます。現在、20,000円分のクレジットがもらえます。

目次

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

AzureのClearDBの利用手順

Azureの無償サブスクリプション内で利用できるのがClearDBと呼ばれるMySQLサービスです。わずか20MBほどのスペースしかないのですが、Google Cloud SQLのように無償枠がないサービスでいきなり契約して使うというのはちょっと・・・という人や、試しにこちらでDBを作って、他の有望なDBサービスに引越しするなんて時にはもってこいのサービスです。

データベースを使う利点

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

  1. スプレッドシートと異なりSQLによる大容量・高速なデータの読み書きが可能です。
  2. リレーショナルデータベースなので、リレーションシップを利用したデータの整合性を常に取る事が可能です。
  3. 連鎖更新・連鎖削除・ロックなどDBならではの機能を利用して管理を軽減する事が可能です
  4. MySQLは様々なウェブサービスやDBaaSでサービス展開されてるので引っ越しが楽です。
  5. 接続元制限や接続メンバーの制限など細かな設定が可能です(今回のClearDBでは出来ませんが)
  6. 他のスクリプトからでも容易にデータベースへアクセスが可能です。
  7. スプレッドシートのようなセル数での制限はなく、データベース容量での制限があります(後者のほうが遥かに広いです)。
  8. ベンダーロックインじゃないですが、Googleサービスに完全依存しない上でも利点があります。
  9. スマートフォンアプリ等への横展開がし易いです。

アカウントの作成

Azure ClearDBはMySQLそのものなので、操作や管理はMySQLのテクニックやリファレンスがそのまま利用する事が可能です。しかし、AzureでClearDBを使う為の最初のアカウント作成時にはクレジットカードが必要です(とはいえ、勝手に課金もされませんし、勝手に有償プランにも移行されたりしません。本人確認の為の手段としてカードを利用してるようです)。

また、当然、Microsoftアカウントが必要なのでMac使いだからといってもMicrosoftアカウントを事前に作って置かなければなりません。以下はAzureのトップページより、アカウントの作成までを記述しています。

  1. トップページの無料アカウント作成をクリックします。そして、無料で始めるをクリックします。
  2. 指示に従って氏名などを入力、本人確認の為のSMS受信、そしてクレジットカードを登録して次に進みます。
  3. 最終的に登録が完了すると、ポータルのダッシュボードが表示されるようになります。

図:ポータルで色々作ったり管理します。

新しいMySQLデータベースを作る

MySQLなのですが、今日の時点では、右上のマークからだと、作れません。リストに出てこないのです。なので、以下の手順で作成します。注意点があり、ここでは選択を失敗してホイホイすすめると、課金されかねない項目があるので、慎重に作業を行いましょう。

  1. 右上のマークをとりあえずクリックする
  2. Marketplaceを検索するのテキストボックスにmysqlと入れて検索を実行します。
  3. MySQLデータベース(ClearDB)というものが出てくるので、それを選択します。
  4. 続けて、作成ボタンを押します。
  5. ここ要注意!デフォルトでは課金タイプのTitanというものが選択されてるので、これを水星に変えます(水星は無料です)。
  6. データベース名(後で使用しますので、覚えておきましょう)を入力
  7. リソースグループはMySQLとでも入れて、場所は東日本を選択。
  8. 法律条項をクリックして、進み承認しましょう。
  9. 最後に作成ボタンを押します。ピン留めしておくとポータルトップから入れるので便利です。
  10. これでDBがデプロイされるまで数分コーヒーでも飲みながら待ちます。
  11. デプロイ完了の通知を受けたら、テーブルの作成やMySQL Workbenchで接続が可能です。

図:Titanでも月額3ドル程度ですけれどね。

MySQLのDBデータを取得する

データベース接続に必要な情報を取得します。この情報をGoogle Apps Scriptで使いますので控えておきましょう。

  1. 右のすべてのリソースをクリックし、先程つくったDBが出てくるので入ります。
  2. 左側のパネルのプロパティをクリックします。
  3. すると、ホスト名、ポート、ユーザ名、パスワードが出て来るので控えておきます。
  4. これらの情報は今回は、Google Apps Scriptのスクリプトプロパティに格納しておきます。実際に使う場合にはここから情報を取り出して使うスタイルです。

図:プロパティから必要な情報を取得します

MySQL Workbenchで接続・作業

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

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

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

図:MySQL Server 5.5が使われています。

Google Apps Scriptから接続

JDBCを使うに当たって

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

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

データベース接続用URL

JDBCでMySQLへ接続する為には、ClearDBサービスを作った後にプロパティから得られる情報を元に接続用のURLを作らなければなりません。必要な情報は

  1. データベースサーバのURL
  2. データベースサーバの接続先Port番号
  3. ユーザ名
  4. パスワード
  5. 作成したDB名

今回はgas_dbという名前でサービスを作っておりこれがDB名になります。すると以下のようなデータベース接続用URLを組み立てることができます。

これを今回スクリプトの中で利用します。各パラメータはGoogle Apps Scriptのスクリプトプロパティに格納してあります。

作成したテーブルデータ

今回、Azure上のMySQLにMySQL Workbenchで接続し、gas_db内に「jinji」というテーブルを作りました。テーブルレイアウトは以下の図の通りです。

図:jinjiテーブルを用意してみた

また、このテーブルに数件のデータをあらかじめ入れておきました。以下のような感じになります。

図:人員情報をテーブルにいれておいた

ソースコード

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

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

実際には、「SELECT * FROM jinji」なんて大雑把なやり方ではなく、様々な抽出条件を加えて必要なテーブルの必要なカラムだけ絞って取得を行いますが、今回は大したデータ量でもないためこのような書き方をしています。また、サービスによってはDBではなくキャッシュからであれば安価にデータを取得できるといったようなものもあるため、自分で使用するDBaaSサービスの内容をよく把握してから、クエリは投げましょう。

また、接続する部分で変数connにデータベース接続用URLを構築し、stmt.executeQueryにてクエリ発行をしています。これでデータの塊が取得できたので、配列に1つずつ収めて、スプレッドシートに追記するといった形を取っています。最後にかならずデータベースはクローズしましょう。時間課金の場合余計な時間消費の原因になったりします。使ったらさっさと閉じるというのがお約束です。(いちおうGASでは関数が実行終了と共に自動クローズされるようになってはいるみたいですが)。

図:割りと簡単に取得ができました。

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

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

HTMLサービスのダイアログの値を取得して、query文のInsert intoに続けて値をはめ込むコードです。ハマりどころは必ずDB名を指定しなければならないので、テーブル名だけでInsert Intoを実行するとエラーになります。また、IDを入れていませんが、DB側でAuto Increment指定をしていないと、やはりエラーになるので、主キーになるIDはAuto Incrementするか?IDは独自に重複しないように生成する必要があります。

setStringで1個目〜4個目のパラメータにはめ込んでゆきます。そして、executeでインサート実行です。insert intoはprepareStatementというものを使用する点も注意して下さい。

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

データベースの値を更新する

スプレッドシートのスクリプトには含めていますが、メニューは登録していないのですが、更新をする事例を記述してあります。今回はIDが1の人間の役職名を変更するというシーンを想定してコードを記述してあります。よって、UPDATE文だけでなくWHERE句も出てきます。

INSERT INTOとほぼおなじスタイルですが、SQL文が異なります。UPDATE文を使用します。SET POSITIONにて役職フィールドに値をセットしています。WHRER句でIDを限定しています。それぞれ、をsetStringとsetIntで値を入れると、IDが1の人間の役職が平社員から職員へと変わりました。WHEREで制限をしなければ全員変わることになります。

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

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

実行すると、IDが7の専務が消えてくれます。このようにSELECT, INSERT INTO, UPDATE, DELETEの4つが主なデータベースの操作になりますが、他にもテーブルの作成や削除などといったものもGoogle Apps ScriptのJDBC Serviceには用意されているので、使いやすいようにラッピングした関数などを用意しておくと良いでしょう。

ポイント

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

関連リンク

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