【GAS】SpreadsheetからCloud SQLに移行する

はじめに

これまで無料だからということで各種WebサービスのデータをGoogle Spreadsheetで管理してやりくりしていたのですが、どうにもこうにも処理速度や同時編集の際のデータ管理に不安が残るので、ここはCloud SQLのMySQLを利用することにしました。

Cloud SQLインスタンスのセッティング

今回はそんな大規模なサービス用ではないので、アイオワ(us-central1)で共有コア、メモリ0.6GBのHA db-f1-microでインスタンスを作成することにしました。
MySQLのバージョンについては、GASのJDBCがMySQL 8.0に対応していないということで、5.7を選択しています。


マシンタイプのところで共有コア 1 vCPU、0.614 GBを選ぶといわゆるdb-f1-microというプランになります。

ストレージ容量は増やせても減らすことはできないので、最小の10GBを選択しておきます。
デフォルトが100GBになっているので注意です（自分はこれで作り直しました）。
最初は少なくても、自動増量のオプションがあるので10GBにしておいても問題はないかと思います。

Cloud SQLの料金

Google Cloud SQLで気になるのはやっぱり月額の費用です。ほかの人の記事にもありますが説明がわかりにくいので、「実際いくらかかるの？」というのは料金表を見てもぱっとしません。
料金計算ツールからすると、上記のセッティングで停止時間無しだと月額$20ぐらいのようです。

90日は試用期間で$300まで無料なので、料金が発生するのは3か月後になりそうです。

IAMでドメインにロールを設定する

Jdbc.getCloudSqlConnectionで接続する場合、IAMにWorkspaceのドメインとロールを追加しないとDBに接続することができません。
※IAMを利用せず接続したい場合はJdbc.getConnectionでパブリックIPに接続します。その場合はこちらを参考にしてください）

gcloud sql instances patch [インスタンス名] --authorized-networks=8.8.4.0/24,8.8.8.0/24,8.34.208.0/20,8.35.192.0/20,23.236.48.0/20,23.251.128.0/19,34.3.3.0/24,34.4.4.0/24,34.64.0.0/10,34.128.0.0/10,35.184.0.0/13,35.192.0.0/14,35.196.0.0/15,35.198.0.0/16,35.199.0.0/17,35.199.128.0/18,35.200.0.0/13,35.208.0.0/12,35.224.0.0/12,35.240.0.0/13,64.15.112.0/20,64.233.160.0/19,66.102.0.0/20,66.249.64.0/19,70.32.128.0/19,72.14.192.0/18,74.114.24.0/21,74.125.0.0/16,104.154.0.0/15,104.196.0.0/14,104.237.160.0/19,107.167.160.0/19,107.178.192.0/18,108.59.80.0/20,108.170.192.0/18,108.177.0.0/17,130.211.0.0/16,136.112.0.0/12,142.250.0.0/15,146.148.0.0/17,162.216.148.0/22,162.222.176.0/21,172.110.32.0/21,172.217.0.0/16,172.253.0.0/16,173.194.0.0/16,173.255.112.0/20,192.158.28.0/22,192.178.0.0/15,193.186.4.0/24,199.36.154.0/23,199.36.156.0/24,199.192.112.0/22,199.223.232.0/21,207.223.160.0/20,208.65.152.0/22,208.68.108.0/22,208.81.188.0/22,208.117.224.0/19,209.85.128.0/17,216.58.192.0/19,216.73.80.0/20,216.239.32.0/19

プリンシパルに会社のWorkspaceドメインを、ロールに「Cloud SQLクライアント」を設定します。

接続を試す

Spreadsheetでデータ取得していた処理をMySQL向けに書き換えていきます。
例えば「id,ユーザ名、件名、本文」といった形でスプレッドシートに保存している場合、概ね下記のような形で変更するとあまり修正の手間をかけなくて済みそうです。

変更前イメージ

var ws = ss.getSheetByName(TABLE_NAME);
var values = ws.getDataRange().getValues();

テーブルには「id,user_name,title,body」でフィールドが設定されている想定です。

変更後イメージ

var sql = "select * from " + TABLE_NAME + " order by id";
var stmt = con.createStatement();
var results = stmt.executeQuery(sql);
var values = [];

while(results.next()){
  values.push([
    results.getInt("id"),
    results.getString("user_name"),
    results.getString("title"),
    results.getString("body")
  ]);
}

Spreadsheetでのデータ処理はgetValuesの戻り値やsetValuesの引数である二次元配列で処理していることが多いので、MySQLで処理した後のデータを同じ形の二次元配列に整形してあげることで、割と簡単に移行できました。

速度でいうとSpreadsheetは開いてシート選択してgetValuesしてWebページに表示して…というところが1秒程度だったのですが、MySQLで同じ処理をすると0.1秒程度まで速くなりました。
Jdbc.getCloudSqlConnection()でGASからCloud SQLに接続する場合、自分の環境では平均して0.06秒というところです。

データ件数が増えていくとこの差はどんどん開いていくはずなので、移行しようか考えている人は試用期間を利用して色々試してみるのもいいかと思います。

Spreadsheetの方が便利なものはそのまま

Cloud SQLは処理においては便利ですが、設定ファイルを一般の人が書き換えたりしたい等が発生した場合、別途Webでの入力フォームなどを作ったりする必要があります。その場合はSpreadsheet+スクリプトキャッシュなどで対応したほうが便利かもしれません。