【Googleスプレッドシート】MySQLのテーブルをインポート!Apps ScriptとJDBCサービス

MySQLデータベースのテーブルをGoogleスプレッドシートに直接取り込めると、データ分析や共有が格段に楽になります。しかし、標準機能ではMySQLに直接接続する方法が用意されていません。そこで役立つのが、Apps ScriptとJDBCサービスです。この記事では、Apps ScriptのスクリプトからJDBCドライバを使ってMySQLテーブルをインポートする手順を詳しく解説します。スクリプトの書き方から注意点までを押さえて、スムーズなデータ連携を実現しましょう。

Apps ScriptとJDBCサービスでMySQLに接続する仕組み

Apps Scriptには、外部データベースに接続するためのJDBCサービスが用意されています。このサービスを使うと、GoogleのサーバーからMySQLやPostgreSQLなどのデータベースに直接アクセスできます。接続にはJDBC URL、ユーザー名、パスワードが必要です。MySQL用のJDBCドライバはGoogle側で既に組み込まれているため、特別なライブラリの追加は不要です。

ただし、MySQLサーバーが外部からの接続を許可している必要があります。Googleのサーバーから接続するため、ファイアウォールでGoogleのIPアドレス範囲を許可するか、Cloud SQLなどのマネージドサービスを使うと簡単です。また、Apps Scriptの実行時間は1回あたり最大6分、トリガーを使った場合も同様の制限があるため、大量データのインポートは分割処理を検討しましょう。

MySQLテーブルをインポートする具体的な手順

1. Apps Scriptエディタを開く

1. スプレッドシートを開く
   インポート先のGoogleスプレッドシートを開きます。アクティブなシートにデータが書き込まれます。
2. 拡張機能メニューからApps Scriptを開く
   メニューバーから「拡張機能」をクリックし、「Apps Script」を選択します。新しいタブでスクリプトエディタが開きます。

2. スクリプトコードを記述する

1. コード.gsに関数を追加する
   デフォルトのコード.gsファイルに、以下のような関数を記述します。

function importMySQLData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var url = "jdbc:mysql://ホスト名:3306/データベース名";
  var user = "ユーザー名";
  var password = "パスワード";
  var conn = Jdbc.getConnection(url, user, password);
  var stmt = conn.createStatement();
  var results = stmt.executeQuery("SELECT * FROM テーブル名");
  var metaData = results.getMetaData();
  var numCols = metaData.getColumnCount();
  var data = [];
  // ヘッダー行を追加
  var headers = [];
  for (var i = 1; i <= numCols; i++) {
    headers.push(metaData.getColumnLabel(i));
  }
  data.push(headers);
  // データ行を追加
  while (results.next()) {
    var row = [];
    for (var j = 1; j <= numCols; j++) {
      row.push(results.getString(j));
    }
    data.push(row);
  }
  results.close();
  stmt.close();
  conn.close();
  // シートに書き込み
  sheet.getRange(1, 1, data.length, numCols).setValues(data);
}

上記のコードでは、まずJdbc.getConnectionでMySQLに接続し、createStatementでステートメントを作成します。executeQueryでSELECT文を実行し、結果セットからメタデータを取得してヘッダー行を作成します。その後、ループで各行のデータを二次元配列に格納し、最後にsetValuesでシートに書き込みます。接続やステートメントは必ずcloseで解放してください。

3. スクリプトを実行する

1. 関数を選択して実行
   スクリプトエディタのツールバーで関数「importMySQLData」を選択し、実行ボタンをクリックします。
2. 権限を承認する
   初回実行時には、外部サービスへのアクセス許可を求められます。内容を確認して許可してください。

4. 結果を確認する

1. スプレッドシートに戻る
   スクリプトが正常に完了すると、アクティブなシートにMySQLのデータがインポートされています。
2. データを確認する
   1行目にカラム名、2行目以降にデータが表示されていることを確認します。文字化けや欠損がないかチェックしてください。

インポート時の注意点とよくあるトラブル

MySQLサーバーにアクセスできない場合

Googleのサーバーから接続するためには、MySQLサーバーのファイアウォールでGoogleのIPアドレス範囲を許可する必要があります。許可するIP範囲は、Googleの公開情報(SPFレコードなど)を参照してください。また、MySQLのユーザーがリモート接続を許可しているか確認しましょう。より簡単な方法として、Google Cloud SQLを使えば、同じGoogle Cloud環境内で安全に接続できます。

文字化けが発生する場合

日本語などのマルチバイト文字が文字化けする場合は、JDBC URLにエンコーディングパラメータを追加します。例えば、jdbc:mysql://ホスト名:3306/データベース名?useUnicode=true&characterEncoding=UTF-8と指定してください。これで文字コードがUTF-8で扱われ、文字化けを防止できます。

大量データでタイムアウトする場合

Apps Scriptの実行時間制限(6分)を超えるとエラーになります。数十万行以上のデータを扱う場合は、LIMITとOFFSETを使って分割して取得するか、スプレッドシートのインポート機能(CSVなど)を検討しましょう。また、スクリプトの処理を最適化して、ループ内での不要な操作を減らすことも有効です。

データ型の変換に関する注意

上記のサンプルコードでは、すべてのデータをgetString()で文字列として取得しています。数値や日付を元の型で保持したい場合は、getObject()を使用してから適切に変換してください。例えば、日付はJavaScriptのDateオブジェクトに変換し、数値はNumber()でキャストします。ただし、スプレッドシートに書き込む際にはsetValuesが二次元配列の値を受け付けるため、型を意識する必要があります。

JDBCインポートとCSVインポートの比較

まとめ

この記事では、Apps ScriptのJDBCサービスを使ってMySQLテーブルをGoogleスプレッドシートにインポートする方法を解説しました。スクリプトを一度作成すれば、スプレッドシート上でボタンやトリガーから定期的にデータを更新できます。さらに、インポート後にQUERY関数でデータを絞り込んだり、IMPORTRANGEで他のシートと連携したりするのも便利です。まずは小さなテーブルでテストし、徐々に実運用に適用してみてください。