最近Google App Script(GAS)にハマっている、せのりです。
皆さんはGAS使ってますか？
GASを使えば色んな事ができますよね！
例えば、
- 日々の作業の自動化
- Slack、Gmailと連携してリマインダーの作成
- スクレイピングツールの作成
- Webアプリケーションの作成
- データベース(DB)との連携
などなど・・・可能性無限大ですね。
Webサーバーとか用意しなくても簡単にWebアプリ公開できるんだもんなあ。
便利な時代になったなあ。
・・ということで、
今回はGASでDB(SQLServer)からデータ取得する方法を紹介いたします。
始めに完成版を紹介して、後から細部を解説していきます。
では早速いきまっしょい！
完成版！
で、こちらが完成版です。
データベースの接続情報はご自身の環境に合わせて修正してください。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 |
let results; let stmt; let connection; // データベース接続情報 const hostName = ホスト名を設定; const port = '1433'; const user = ユーザーを設定; const password = パスワードを設定; const database = DB名を設定; const connectionString = 'jdbc:sqlserver://' + host_name + ':' + port + ';databaseName=' + database; // 検索処理 function search() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); // SQL SELECT取得 var sql = ""; sql += "select "; sql += " * "; sql += "from table "; execute(sql); if (results == null) { return; } // スプレッドシートに取得結果を反映 var range = sheet.getRange(1,1); writeResult(range , results); dbConnectionClose(); } // 取得結果をスプレッドシートに書込み function writeResult(range, results) { var row = 0; // 取得結果の列数を取得する var columnCount = results.getMetaData().getColumnCount(); // 取得結果をスプレッドシートに書込み while (results.next()) { var rowString = ''; for (var col = 0; col < columnCount; col++) { rowString += results.getString(col + 1) + '\t'; range.offset(row, col).setValue(results.getString(col + 1)); } row++ } } // SQL発行(検索) function execute(connection, sql) { try { stmt = connection.createStatement(); results = stmt.execute(sql); } catch(e) { Logger.log('コネクション取得エラー: ' + e); dbConnectionClose(); } } // SQL発行(登録・更新) function executeUpdate(sql) { try { stmt = connection.createStatement(); stmt.executeUpdate(sql); } catch(e) { Logger.log('コネクション取得エラー: ' + e); dbConnectionClose(); } } // DB接続 function dbConnection(sql) { try { connection = Jdbc.getConnection(connectionString , user, password); } catch(e) { Logger.log('コネクション取得エラー: ' + e); dbConnectionClose(); } } // 解放処理 function dbConnectionClose() { try{ if(results != null) { results.close(); results = null; } if(stmt != null) { stmt.close(); stmt = null; } if(connection != null) { connection.close(); connection = null; } } catch (e) { Logger.log('コネクションClose処理でエラー: ' + e); } } |
DB接続！
ここから詳細を解説していきます。
まずは「DB接続」から。
関数dbConnectionのJdbc.getConnectionでデータベースと接続しています。
接続文字列はJDBC使用時のお決まりの構文です。
プログラムにデータベースの接続情報がハードコーディングされているのは、セキュリティ上あまりよろしくないかも。
非公開のスプレッドシートなどに記載しておくのが良いかもしれません。
関数dbConnectionCloseでコネクションを解放しています。
処理終了時やエラー発生時にこの関数を呼び出しています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
// DB接続 function dbConnection(sql) { try { connection = Jdbc.getConnection(connectionString , user, password); } catch(e) { Logger.log('コネクション取得エラー: ' + e); dbConnectionClose(); } } // 解放処理 function dbConnectionClose() { try{ if(results != null) { results.close(); results = null; } if(stmt != null) { stmt.close(); stmt = null; } if(connection != null) { connection.close(); connection = null; } } catch (e) { Logger.log('コネクションClose処理でエラー: ' + e); } } |
SQL発行！
次に「SQL発行」部分。
関数executeはデータベースからデータを取得する際に使います。
SQLで言うとSELECT文を発行するときに使います。
stmt.execute(sql);でSQL実行しています。
コネクションが取得できていなかったり、データベースに接続できなかった場合は、
catchでエラーログ出力とコネクション解放を行っています。
正常にSQL実行できた場合は、resultsに取得結果が格納されます。
関数executeUpdateはUPDATE・INSERT文を発行するときに使います。
stmt.executeUpdate(sql);でSQL実行しています。
※UPDATE・INSERTは今回扱っていません。
|
1 2 3 4 5 6 7 8 9 10 11 |
// SQL発行(検索) function execute(connection, sql) { try { stmt = connection.createStatement(); results = tmt.execute(sql); } catch(e) { Logger.log('コネクション取得エラー: ' + e); dbConnectionClose(); } } |
|
1 2 3 4 5 6 7 8 9 10 11 |
// SQL発行(登録・更新) function executeUpdate(sql) { try { stmt = connection.createStatement(); stmt.executeUpdate(sql); } catch(e) { Logger.log('コネクション取得エラー: ' + e); dbConnectionClose(); } } |
データをスプレッドシートに反映！
最後に検索処理です。
関数searchで、SQL文を構築して、前段の関数executeにSQLを渡しています。
select * from tableのtableはご自身がデータを取得したいテーブル名を記載してください。
SQLを実行して取得結果がある場合は、関数writeResultを呼び出しています。
writeResultでは取得結果をスプレッドシートに書き込む処理を記載しています。
取得件数×列数文のループ処理が行われるイメージです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
// 検索処理 function search() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); // SQL SELECT取得 var sql = ""; sql += "select "; sql += " * "; sql += "from table "; execute(sql); if (results == null) { return; } // スプレッドシートに取得結果を反映 var range = sheet.getRange(1,1); writeResult(range , results); dbConnectionClose(); } // 取得結果をスプレッドシートに書込み function writeResult(range, results) { var row = 0; // 取得結果の列数を取得する var columnCount = results.getMetaData().getColumnCount(); // 取得結果をスプレッドシートに書込み while (results.next()) { var rowString = ''; for (var col = 0; col < columnCount; col++) { rowString += results.getString(col + 1) + '\t'; range.offset(row, col).setValue(results.getString(col + 1)); } row++ } } |
まとめ
今回は以上となります！
GAS⇒SQLServerへの接続、検索処理について、解説しました。
処理のイメージは伝わりましたでしょうか。
今回は扱っていませんが、登録・更新処理についても記載していく予定です。
GASは様々なことができるので、うまく活用して日々の業務や作業を効率化しちゃいましょう！