【Googleスプレッドシート】MySQLデータの定期取り込み!認証情報の安全な管理

MySQLデータベースの情報をGoogleスプレッドシートに定期的に取り込みたいとお考えではありませんか。手動でエクスポート・インポートするのは手間がかかり、認証情報をシート内に直接書くのはセキュリティ上のリスクがあります。本記事では、Apps ScriptとJDBCサービスを利用して、認証情報を安全に管理しながらMySQLデータをスプレッドシートに定期的に取り込む方法を詳しく解説します。この方法を学べば、データ更新の自動化とセキュリティ確保を両立できます。

MySQLデータをスプレッドシートに取り込む仕組み

GoogleスプレッドシートからMySQLデータベースにアクセスするには、Apps Scriptが提供するJDBCサービスを使用します。JDBCサービスは、データベースのURL、ユーザー名、パスワードを用いて接続を確立します。これらの認証情報はスクリプトプロパティに保存することで、ソースコード内に平文で記述することを防げます。また、定期的なデータ取り込みは時間主導型トリガーを設定することで自動化します。スプレッドシート上でデータを更新するには、SQLのSELECT文を実行し、結果をシートに書き込む関数を作成します。

認証情報を安全に管理する手順

認証情報を安全に管理するためには、スクリプトプロパティを利用します。以下に具体的な手順を説明します。

1. スクリプトプロパティを開く
   Apps Scriptエディタを開き、「ファイル」メニューから「プロジェクトのプロパティ」を選択します。「スクリプトのプロパティ」タブをクリックし、キーと値のペアを追加します。例えば、キー「DB_USER」にデータベースのユーザー名、キー「DB_PASSWORD」にパスワード、キー「DB_URL」に接続URLを設定します。
2. スクリプトからプロパティを読み出すコードを記述する
   スクリプト内で PropertiesService.getScriptProperties().getProperty('DB_USER') のように記述して値を取得します。これにより、認証情報がソースコードに直接書かれることを防ぎます。パスワードなど機密性の高い情報は、特にこの方法で管理してください。
3. JDBC接続を確立する関数を作成する
   var conn = Jdbc.getConnection(url, username, password); と記述し、引数にはスクリプトプロパティから取得した値を渡します。接続が成功したら、conn.createStatement() でステートメントを作成し、SQLを実行します。

定期的なデータ取り込みを自動化する手順

データの定期取り込みを実現するには、Apps Scriptのトリガー機能を使います。以下の手順で設定します。

1. データ取り込み用の関数を作成する
   例えば function importMySQLData() という関数を作成し、その中でMySQLに接続してデータを取得し、スプレッドシートの特定のシートに書き込む処理を記述します。実際のコード例は後述します。
2. トリガーを設定する
   Apps Scriptエディタで「編集」→「現在のプロジェクトのトリガー」を開きます。「トリガーを追加」をクリックし、実行する関数を選択します。イベントのソースを「時間主導型」に設定し、時間の間隔を「日タイマー」など好みに合わせて選択します。例えば、毎日午前9時に実行するように設定できます。
3. トリガーを保存して動作を確認する
   トリガーを保存したら、一度手動で関数を実行して正しくデータが取り込まれるか確認します。ログを出力するなどしてエラーがないかチェックしてください。問題がなければ、次回以降自動で実行されます。

データ取り込み関数のコード例

以下は、MySQLからデータを取得してスプレッドシートに書き込む関数の例です。認証情報はスクリプトプロパティから取得します。

function importMySQLData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('データ');
  var url = PropertiesService.getScriptProperties().getProperty('DB_URL');
  var user = PropertiesService.getScriptProperties().getProperty('DB_USER');
  var password = PropertiesService.getScriptProperties().getProperty('DB_PASSWORD');
  var conn = Jdbc.getConnection(url, user, password);
  var stmt = conn.createStatement();
  var results = stmt.executeQuery('SELECT * FROM テーブル名');
  var rows = [];
  var numCols = results.getMetaData().getColumnCount();
  while (results.next()) {
    var row = [];
    for (var col = 0; col < numCols; col++) {
      row.push(results.getString(col + 1));
    }
    rows.push(row);
  }
  results.close();
  stmt.close();
  conn.close();
  sheet.clear();
  sheet.getRange(1, 1, rows.length, numCols).setValues(rows);
}

注意点とよくあるトラブル

MySQLサーバーへの接続が拒否される

Apps ScriptからMySQLに接続する際、ファイアウォールやアクセス制限によって接続が拒否される場合があります。その場合、MySQLサーバーの設定でリモート接続を許可し、GoogleのIPアドレス範囲からのアクセスを許可する必要があります。また、SSL接続が求められる場合もあります。JDBC接続URLに ?useSSL=true などのパラメータを追加してください。

認証情報がスクリプトプロパティから正しく取得できない

プロパティのキー名を間違えると値が取得できません。キー名は大文字小文字を区別するため、設定時とコード内で完全に一致させてください。また、プロパティの値を更新した後は、スクリプトを再保存する必要があります。

トリガーが正しく動作しない

トリガーの実行時間が短すぎる場合や、他のトリガーと競合する場合があります。トリガーの間隔を適切に設定し、実行時間が制限(6分)を超えないように関数を最適化してください。大量のデータを処理する場合は、ページング処理を導入することを検討します。

各方法の比較:手動取り込みと自動取り込み

まとめ

本記事では、Apps ScriptのJDBCサービスを使い、スクリプトプロパティで認証情報を安全に管理しながらMySQLデータをGoogleスプレッドシートに定期取り込みする方法を解説しました。これにより、手動操作の手間を省きつつ、セキュリティリスクを低減できます。次のステップとして、トリガーの実行間隔を調整したり、複数のテーブルを扱う関数を作成したりすることで、さらに効率的なデータ管理が可能になります。また、エラーハンドリングを追加して、障害発生時にメール通知を送る仕組みも検討してみてください。まずはサンプルコードを試し、ご自身の環境に合わせてカスタマイズしてみましょう。