Google Apps Scriptを使ってGoogleスプレッドシートとCloud SQLを連携

こんにちは、バックエンドエンジニアの、まるです。
みなさん、Google Apps Scriptをご存じでしょうか。
Google Apps Script(以下、GAS)は、Googleが開発・提供しているプログラミング言語です。JavaScriptをベースとしているので、JavaScriptを触ったことがある人であれば比較的簡単に読み書きすることができます。
GASを利用すれば、GCP（Google Cloud Platform）や、Googleドライブ・Google スプレッドシート・GmailなどのGoogle Workspaceのサービスを連携し、通知を飛ばしたり自動で内容を更新することができます。
今回は、GASを使って Cloud SQLとGoogleスプレッドシートを連携する方法をご紹介します。

構成
Cloud SQLを立ててレコードを登録する
GASを使ってCloud SQLからレコードを取得する
GASを使ってCloud SQLを更新する
スプレッドシート上にボタンを作る
Google Apps Scriptを自動実行する
おわりに

構成

構成は以下の通りです。

非常にシンプルですね。
このシステムでできることは

-Cloud SQLのレコードをGoogleスプレッドシートから読む
-Cloud SQLにGoogleスプレッドシートから書き込む

この二つです。

Cloud SQLを立ててレコードを登録する

まずはGCPでCloud SQLを立てましょう。
注意するべき点として、MySQLのバージョンは5.7を選択してください。 GASのJDBCサービスがMySQL8.0に対応していないらしく、MySQL8.0でインスタンスを立てるとCloud SQLとの連携ができなくなります。（2022年1月現在）

Cloud SQLインスタンスの準備が完了したら接続の設定を確認します。「パブリックIP」のチェックボックスにチェックを入れます。これでCloud SQLがインターネットからアクセス可能になります。
また、jdbc.getConnection(url)メソッドを使うので、JDBCが使用する可能性があるIPアドレスを一通り「承認済みネットワーク」に登録しておきます。
私の場合は下記の画像のように登録したら接続できるようになりましたが、公式ドキュメントによるとこのページに載っているIPアドレスからはアクセスされる可能性があるようです。心配な人は全て登録しておきましょう。

続いてCloud ShellからDBを作ります。DB名はsampleとしました。

> CREATE DATABASE sample
> USE sample

次にテーブルを作成します。今回はid, name, mailカラムを持つemployeeというテーブルを作りました。

> CREATE TABLE employee(
    id INT(11) AUTO_INCREMENT NOT NULL,
    name VARCHAR(30) NOT NULL,
    mail VARCHAR(256) NOT NULL,
    PRIMARY KEY (id));

INSERTでレコードを登録します。

> INSERT INTO employee (id, name, mail) VALUES
(1, '山田太郎', 'taro.yamada@example.com'),
(2, '鈴木花子', 'hanako.suzuki@example.com'),
(3, '高橋一郎', 'ichiro.takahashi@example.com');

正しくレコードが登録されていることをSELECTで確認しておきます。

これで準備は完了です。

GASを使ってCloud SQLからレコードを取得する

まずは空のGoogleスプレッドシートに表を作っておきます。この表にレコードが反映されるわけですね。

準備ができたら、メニューの「拡張機能」からApps Scriptを選択します。

これがGASのエディタ画面です。初めから用意されているfunction myFunction(){}はいったん消しておきましょう。

まずはCloud SQLの接続情報から書いていきます。
connectionNameとipはCloud SQLのインスタンス画面から確認できます。

/* Cloud SQL接続情報 */
var connectionName = '{connectionName}';
var user = '{user}';
var userPwd = '{password}';
var db = 'sample';
var ip = '{ip}'
var dbUrl = 'jdbc:mysql://' + ip + '/' + db;

次に、対象のスプレッドシートを指定します。スプレッドシートのIDはURLから取得できます。

getSheets()[0]は「1枚目のシート」という意味です。getSheetByName(name)とすれば「nameという名前のシート」が指定できますし、getActiveSheet()とすれば「現在アクティブになっているシート」が指定できます。

/* 対象のGoogleスプレッドシート情報 */
var sheet = SpreadsheetApp.openById('{SheetID}').getSheets()[0];

続いて、メイン部分の関数を書きます。やっていることは主に以下の4つです。

Cloud SQLからレコードを最大1000件取得する
対象シートのB3からD1002の範囲をクリアする
取得したレコードをarrayに格納する
arrayの内容をスプレッドシートの指定位置に反映する

クエリ結果をいったんarrayに格納しているのは、setValuesメソッドで全件を一度にスプレッドシートに反映するためです。ループを回してsetValueメソッドを使う方法もありますが、この方法は非常に実行時間がかかるのであまりお勧めしません。

function readRecords() {
  /* 実行時間計測開始 */
  var functionstart = new Date();
 
  /* JDBCを使って1000件取得 */
  var conn = Jdbc.getConnection(dbUrl, user, userPwd);
  var stmt = conn.createStatement();
  stmt.setMaxRows(1000);
  var results = stmt.executeQuery(createSelectQuery());
  var numCols = results.getMetaData().getColumnCount();
 
  /* B3からD1002の範囲をクリア */
  sheet.getRange('B3:D1002').clearContent()
 
  /* クエリの内容をarrayに成形 */
  const array = [];
 
  while(results.next()) {
    const cols = [];
    for (let col = 0; col < numCols; col++) {
    const cellData = results.getString(col + 1);
      cols.push(cellData);
    }
    array.push(cols);
  }
 
  /* arrayをGoogleスプレッドシートに反映 */
  sheet.getRange(3, 2, array.length, array[0].length).setValues(array);
 
  results.close();
  stmt.close();
 
  /* 実行時間計測終了 */
  var end = new Date();
  Logger.log('Time elapsed: %sms', end - functionstart);
}

変更しやすいようにクエリは別関数に分離しておきました。

/* クエリ */
function createQuery() {
  return `
    SELECT
        id,
        name,
        mail
    FROM
        employee
    ORDER BY
    id ASC
  `;
}

これで準備完了です。実行ボタンを押すとGASが実行され、スプレッドシートにCloud SQLのレコードが反映されます。

うまくいきました。
実行の履歴はGoogle Apps Scriptのメニューの「実行数」から閲覧できます。Logger.log()で出力したログもここから見られます。

GASを使ってCloud SQLを更新する

前段でCloud SQLのレコードを読み込むことができたので、今度はGoogleスプレッドシートの内容をCloud SQLに書き込んでみます。
/* 対象のGoogleスプレッドシート情報 */までは読み込みと同じなので、メイン関数以降を載せます。やっていることは以下の4つです。

employeeテーブルをTRUNCATEする
スプレッドシートの指定範囲の値を取得する
指定範囲の値を順にクエリに入れる
クエリをバッチ実行する

複数行のINSERTでも1回のクエリ実行で終わるように、addBatchメソッドを使っているところがポイントです。これで実行時間を短縮することができます。

function writeRecords() {
  /* 実行時間計測開始 */
  var functionstart = new Date();
 
  /* JDBCを使って接続 */
  var conn = Jdbc.getConnection(dbUrl, user, userPwd);
  conn.setAutoCommit(false);
 
  /* employeeテーブルをTRUNCATE */
  var truncatestmt = conn.createStatement();
  truncatestmt.executeUpdate('TRUNCATE TABLE employee');
 
  /* 指定範囲の値を取得 */
  var range = sheet.getRange('B3:D1002');
  values = range.getValues()
 
  /* クエリを準備 */
  var insertstmt = conn.createStatement();
  var insertstmt = conn.prepareStatement(createInsertQuery());
 
  /* 'id'が空文字の行が現れるまで、 指定範囲の値を読んでクエリに入れる */
  var roopflag = 0
  for (var row in values) {
    for (var col in values[row]) {
    if (col == 0 && values[row][col] == '') {
        roopflag = 1;
        break;
    }
    insertstmt.setString(Number(col)+1, values[row][col]);
    }
    if (roopflag == 1) {
    break;
    }
    insertstmt.addBatch();
  }
 
  /* バッチ実行 */
  var batch = insertstmt.executeBatch();
  conn.commit();
  conn.close();
 
  /* 実行時間計測終了 */
  var functionend = new Date();
  Logger.log('Time elapsed: %sms for %s rows.', functionend - functionstart, batch.length);
}
 
/* クエリ */
function createInsertQuery() {
  return `
    INSERT INTO employee
    (id, name, mail) values (?, ?, ?)`;

実行したらCloud SQLのレコードがちゃんと更新されているか確認しましょう。