SpreadSheetからCloudSQL(MySQL)に接続する方法

メリット

サーバ側APIやクライアント側を作る必要がないので、とりあえず手っ取り早く管理画面を作りたい時に便利。

向いてない点

• あまりにもデータ量が多いと、GASやSpreadSheetが耐えられない。
• 処理速度の改善は難しい。

元ネタ

https://developers.google.com/apps-script/guides/jdbc
こちらを日本語にして、自分が躓いたところを補填しています。

準備

• 一度ぐらいGASを書いたことある。
• CloudSQLでDBを作成しておく。
• CloudSQLのユーザ名とパスワードとDB名は把握しておく。
• 何件かデータが入っている

IPアドレスを許可する

以下のアドレスをCloudSQLにアクセスするのを許可します。

64.18.0.0/20
64.233.160.0/19
66.102.0.0/20
66.249.80.0/20
72.14.192.0/18
74.125.0.0/16
173.194.0.0/16
207.126.144.0/20
209.85.128.0/17
216.239.32.0/19

GCPコンソールからだと一つずつしか出来なくて、とても面倒なので、gcloudコマンドを使います。

sql instances patch [インスタンスID] --authorized-networks=64.18.0.0/20,64.233.160.0/19,66.102.0.0/20,66.249.80.0/20,72.14.192.0/18,74.125.0.0/16,173.194.0.0/16,207.126.144.0/20,209.85.128.0/17,216.239.32.0/19 --project [プロジェクト名]

インスタンスIDはこれです。

プロジェクト名は、GCPのプロジェクト名になります。

うまく行けば、GCPコンソールのCloudSQLから「接続」を選べば、以下のような画面になります。

GASを書く

SpreadSheetからツール- スクリプトエディタから書きます。
詳しい内容は割愛します。

/**
 * Read up to 1000 rows of data from the table and log them.
 */
function readFromTable() {
  
  var connectionName = 'インスタンス接続名';
  var user = 'ユーザ名';
  var userPwd = 'パスワード';
  var db = 'DB名';
  
  var dbUrl = 'jdbc:google:mysql://' + connectionName + '/' + db;
  var conn = Jdbc.getCloudSqlConnection(dbUrl, user, userPwd);

  var start = new Date();
  var stmt = conn.createStatement();
  stmt.setMaxRows(1000);
  var results = stmt.executeQuery('SELECT * FROM テーブル名');
  var numCols = results.getMetaData().getColumnCount();

  while (results.next()) {
    var rowString = '';
    for (var col = 0; col < numCols; col++) {
      rowString += results.getString(col + 1) + '\t';
    }
    Logger.log(rowString);
  }

  results.close();
  stmt.close();

  var end = new Date();
  Logger.log('Time elapsed: %sms', end - start);
}

インスタンス接続名はこちらを参照する。

時間がないけどどうしても管理画面が必要！という時は向いてますね。