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のレコードを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つです。

1. Cloud SQLからレコードを最大1000件取得する
2. 対象シートのB3からD1002の範囲をクリアする
3. 取得したレコードをarrayに格納する
4. 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つです。

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

複数行の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のレコードがちゃんと更新されているか確認しましょう。

問題なさそうです。

余談ですが、GASは別ファイルの関数を呼ぶことができます。したがって、全てのファイルにおいて関数名は一意である必要があります。

スプレッドシート上にボタンを作る

ここまででCloud SQLとGoogleスプレッドシートを連携できるようになりましたが、実行のたびにGASの画面を開いて実行ボタンを押すのは面倒なので、Googleスプレッドシート上に実行ボタンを作ります。
メニューの「挿入」から「図形描画」を選択します。

適当な図形を描いて文字を入れたら「保存して終了」を押します。今回はReadとWriteというボタンを作りました。

図形を右クリックして「スクリプトを割り当て」から実行したい関数名を入力します。ファイル名ではなく関数名を入力することに注意してください。

これで図形と関数が紐づけられたので、クリックすると関数が実行されます。

Google Apps Scriptを自動実行する

GASはトリガーに応じて自動実行することもできます。
GASのメニューから「トリガー」のページを開き、「トリガーを追加」を押します。

例えば

• 実行する関数: readRecords
• 実行するデプロイ: Head
• イベントのソース: スプレッドシートから
• イベントの種類: 起動時

とすれば、スプレッドシートを開いたときに自動でCloud SQLからレコードを読み込みます。

他にも、

• 実行する関数: writeRecords
• 実行するデプロイ: Head
• イベントのソース: 時間主導型
• 時間ベースのトリガーのタイプ: 日付ベースのタイマー
• 時刻を選択: 午前0時～1時

とすれば、毎日午前0時から1時の間にCloud SQLへの書き込みが自動実行されます。

おわりに

今回はCloud SQLとGoogleスプレッドシートを連携する手段としてGASを紹介しました。
Googleが提供している各種サービスとの相性が良いのでみなさんもGASを使ってみてください。

■ デジタルハーツは一緒に働くメンバーを募集しています！ hrmos.co

hrmos.co

hrmos.co