【Googleスプレッドシート】PostgreSQL接続でリアルタイムデータ取得!Apps ScriptとJDBCの活用

GoogleスプレッドシートにPostgreSQLのデータをリアルタイムで取り込みたいとお考えではありませんか。業務データベースの最新情報をシート上で参照できれば、レポート作成やダッシュボード更新が格段に効率化します。この記事では、Apps ScriptのJDBCサービスを利用して、GoogleスプレッドシートからPostgreSQLに直接接続する方法を詳しく解説します。具体的なコード例と手順を理解すれば、すぐに実践できます。

Apps ScriptとJDBCでPostgreSQLに接続する仕組み

Apps Scriptには、Java Database Connectivity(JDBC)を介して外部データベースに接続するためのJdbcクラスが用意されています。このクラスを使用することで、Googleスプレッドシートのスクリプトから直接PostgreSQLのデータベースにクエリを発行できるようになります。接続には、PostgreSQL用のJDBCドライバが内部的に利用されます。スクリプトはGoogleのサーバー上で実行されるため、接続先のPostgreSQLサーバーがインターネット経由でアクセス可能である必要があります。また、GoogleのIPアドレス範囲からの接続を許可するよう、ファイアウォールやセキュリティグループの設定が必要です。

PostgreSQL接続のための事前準備

Apps ScriptからPostgreSQLに接続する前に、以下の準備を行います。

1. PostgreSQLサーバーのリモート接続設定を確認する
   サーバーのpostgresql.confファイルでlisten_addresses = ‘*’ に設定し、pg_hba.confで接続元IPアドレス(GoogleのIP範囲)からの接続を許可します。GoogleのIP範囲は公開ドキュメントで確認できます。
2. 接続情報をメモする
   ホスト名(IPアドレス)、ポート番号(デフォルト5432)、データベース名、ユーザー名、パスワードを用意します。
3. Googleスプレッドシートでスクリプトエディタを開く
   拡張機能メニューからApps Scriptエディタを開き、新しいプロジェクトを作成します。

Apps ScriptでPostgreSQLからデータを取得する手順

以下の手順で、PostgreSQLからデータを取得し、シートに出力する関数を作成します。

1. 関数を作成して接続文字列を設定する
   スクリプトエディタに以下の関数を記述します。接続情報はご自身の環境に合わせてください。
   function getPostgresData() {
var conn = Jdbc.getConnection('jdbc:postgresql://your-host:5432/your-database', 'username', 'password');
var stmt = conn.createStatement();
var results = stmt.executeQuery('SELECT * FROM your_table LIMIT 100');
var sheet = SpreadsheetApp.getActiveSheet();
var row = 1;
while (results.next()) {
var col = 1;
for (var i = 1; i <= results.getMetaData().getColumnCount(); i++) {
sheet.getRange(row, col).setValue(results.getString(i));
col++;
}
row++;
}
results.close();
stmt.close();
conn.close();
}
2. スクリプトを保存して実行する
   関数を保存し、実行ボタンをクリックします。初回は権限の承認が必要です。「このアプリは確認されていません」と表示された場合は、[詳細]から安全なページに移動して許可します。
3. データがシートに出力されたことを確認する
   実行後、アクティブなシートの1行目からデータが入力されます。クエリや出力先は必要に応じて変更してください。

接続時の注意点とよくあるトラブル

SSLが必要な場合の接続方法

セキュリティ要件でSSL接続が必須の場合は、接続文字列にssl=trueを追加します。例: jdbc:postgresql://host:5432/db?ssl=true。また、必要に応じてsslfactoryやsslmodeパラメータを指定します。

タイムアウトエラーが発生する

ネットワーク遅延やクエリの負荷でタイムアウトする場合は、接続時にタイムアウト値を設定します。Jdbc.getConnectionの前に以下のコードを追加します。
var connBuilder = Jdbc.getConnectionBuilder('jdbc:postgresql://host:5432/db', 'user', 'password');
connBuilder.setConnectionTimeout(30); // 秒単位
var conn = connBuilder.build();

IPアドレス制限に引っかかる

Googleのサーバーから接続するため、PostgreSQL側でGoogleのIPアドレス範囲を許可する必要があります。Googleは公開IPリストを提供していないため、代わりにすべてのIPを許可するか、クラウドプロバイダのIP範囲を調べて設定します。セキュリティを重視する場合は、VPNや専用線の利用も検討します。

JDBCドライバが見つからないエラー

正しい接続文字列を使用していてもドライバエラーが出る場合、URLの形式が間違っている可能性があります。必ず jdbc:postgresql:// で始め、ポート番号はデフォルト5432です。また、パスワードに特殊文字が含まれる場合はURLエンコードが必要です。

PostgreSQL接続を使ったリアルタイムデータ活用の応用例

定期的なデータ同期

時間主導型のトリガーを設定することで、1時間ごとや1日ごとに自動でデータを同期できます。スクリプトエディタのトリガーアイコンから、関数を定期実行するよう設定します。これにより、シートが常に最新のデータベース状態を反映します。

ダッシュボードの自動更新

取得したデータをシート上で集計・グラフ化しておけば、自動更新されるダッシュボードとして利用できます。QUERY関数やピボットテーブルと組み合わせると、動的なレポートが簡単に作成できます。

複数のテーブルを結合するクエリ

Apps Script内でJOINを使った複雑なSQLを実行し、結果を一つのシートにまとめることも可能です。パラメータ化されたクエリを使用する場合は、JdbcのpreparedStatementを利用してSQLインジェクションを防ぎます。

各接続方法の比較

この表から、Apps Script JDBCはリアルタイム性とカスタマイズ性のバランスに優れていることがわかります。特に無料で利用でき、トリガーを使えば完全自動化も可能です。

まとめ

Apps ScriptのJDBCサービスを活用することで、GoogleスプレッドシートからPostgreSQLに直接接続し、リアルタイムでデータを取得する仕組みを構築できました。接続文字列の指定や認証情報の管理、クエリの実行といった基本手順を押さえれば、さまざまな応用が可能です。まずは簡単なSELECT文から試し、トリガーによる定期同期や複数テーブルの結合に発展させてみてください。この方法を使えば、シートがデータベースのライブビューとして機能し、業務の効率化に大きく貢献します。