Google Apps Script からODBC データソースに接続

Google Apps Script を使えば、Google Sheets やGoogle Docs などに独自の機能を追加できます。CData SQL Gateway を使用すると、あらゆるCData ODBC データソースのMySQL インターフェースを作成できます。Google Apps Script のJDBC サービスではMySQL プロトコルがネイティブにサポートされているため、SQL Gateway を利用することでGoogle ドキュメント内からODBC データにアクセスできるようになります。

この記事では、Google Apps Script からODBC ドライバーに接続する方法を説明します。SQL Gateway の構成手順と、Google スプレッドシートでデータを処理するためのサンプルスクリプトを紹介します。

ここでは、指定したテーブルからデータを読み取るSELECT クエリのみを実行しますが、INSERT、UPDATE、DELETE を実行するように簡単に拡張できます。

SQL Gateway によるリアルタイム接続

SQL Gateway を使用すると、ローカルのODBC データソースを標準的なMySQL データベースのように扱うことができます。SQL Gateway でODBC ドライバー用の新しいMySQL リモーティングサービスを作成し、SQL Gateway がWeb 公開されたマシンにインストールされている（もしくはホストされたSSH サーバーに接続できる）ことを確認してください。

MySQL リモーティングサービスの作成

SQL Gateway のMySQL リモーティング機能を使用すると、ODBC データソースに仮想MySQL データベースとしてアクセスできます。SQL Gateway はクライアントからのMySQL リクエストをリッスンするサービスプロセスを実行します。サービスはSQL Gateway UI で構成できます。

Apps Script からODBC データソースに接続

ここまでで、ODBC ドライバー用にSQL Gateway を構成しました。あとはGoogle Apps Script を使用してSQL Gateway にアクセスし、Google Sheets でODBC データソースを操作するだけです。

このセクションでは、スプレッドシートにデータを読み込むスクリプト（スクリプトを呼び出すためのメニューオプション付き）を作成します。サンプルスクリプトを作成し、各パートを解説していきます。完全なスクリプトは記事の末尾で確認できます。

1. 空のスクリプトを作成

Google Sheet のスクリプトを作成するには、Google Sheets のメニューからツール スクリプトエディタをクリックします。

2. クラス変数を宣言

スクリプト内で作成する関数から利用できるクラス変数をいくつか作成します。

// このブロックの変数を実際の値に置き換えてください
var address = 'my.server.address:port';
var user = 'SQL_GATEWAY_USER';
var userPwd = 'SQL_GATEWAY_PASSWORD';
var db = 'DSN_HERE';

var dbUrl = 'jdbc:mysql://' + address + '/' + db;

3. メニューオプションを追加

この関数はGoogle Sheet にメニューオプションを追加し、UI から関数を呼び出せるようにします。

function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var menuItems = [
  {name: 'Write data to a sheet', functionName: 'connectToODBCData'}
  ];
  spreadsheet.addMenu('ODBC Data', menuItems);
}

4. ヘルパー関数を作成

この関数は、スプレッドシートの最初の空行を検索するために使用します。

/*
 * カラムの配列をスキャンして、スプレッドシートの最初の空行を検索
 * @return 最初の空行の行番号
 */
function getFirstEmptyRowByColumnArray(spreadSheet, column) {
  var column = spreadSheet.getRange(column + ":" + column);
  var values = column.getValues(); // すべてのデータを1回の呼び出しで取得
  var ct = 0;
  while ( values[ct] && values[ct][0] != "" ) {
    ct++;
  }
  return (ct+1);
}

5. ODBC データをスプレッドシートに書き込む関数を作成

以下の関数は、Google Apps Script のJDBC 機能を使用してMySQL リモーティングサービスに接続し、データをSELECT してスプレッドシートに書き込みます。スクリプトを実行すると、2つの入力ボックスが表示されます。

最初の入力ボックスでは、データを格納するシート名を入力するよう求められます（スプレッドシートが存在しない場合は関数が作成します）。

2番目の入力ボックスでは、読み取るODBC テーブル名を入力するよう求められます。無効なテーブルを選択した場合、エラーメッセージが表示され、関数は終了します。

なお、この関数はメニューオプションとして使用するように設計されていますが、スプレッドシートの数式として使用できるように拡張することも可能です。

/*
 * 指定されたODBC 'テーブル'からデータを読み取り、指定されたシートに書き込む
 *    （指定されたシートが存在しない場合は作成される）
 */
function connectToODBCData() {
  var thisWorkbook = SpreadsheetApp.getActive();

  // シートを選択（存在しない場合は作成）
  var selectedSheet = Browser.inputBox('Which sheet would you like the data to post to?',Browser.Buttons.OK_CANCEL);
  if (selectedSheet == 'cancel')
    return;

  if (thisWorkbook.getSheetByName(selectedSheet) == null)
    thisWorkbook.insertSheet(selectedSheet);
  var resultSheet = thisWorkbook.getSheetByName(selectedSheet);
  var rowNum = 2;

  // ODBC 'テーブル'を選択
  var table = Browser.inputBox('Which ODBC data table would you like to pull data from?',Browser.Buttons.OK_CANCEL);
  if (table == 'cancel')
    return;

  var conn = Jdbc.getConnection(dbUrl, user, userPwd);

  // var table が有効なテーブル/ビューであることを確認
  var dbMetaData = conn.getMetaData();
  var tableSet = dbMetaData.getTables(null, null, table, null);
  var validTable = false;
  while (tableSet.next()) {
    var tempTable = tableSet.getString(3);
    if (table.toUpperCase() == tempTable.toUpperCase()){
      table = tempTable;
      validTable = true;
      break;
    }
  }
  tableSet.close();
  if (!validTable) {
    Browser.msgBox("Invalid table name: " + table, Browser.Buttons.OK);
    return;
  }

  var stmt = conn.createStatement();

  var results = stmt.executeQuery('SELECT * FROM ' + table);
  var rsmd = results.getMetaData();
  var numCols = rsmd.getColumnCount();

  // シートが空の場合、最初の行にヘッダーを設定
  var firstEmptyRow = getFirstEmptyRowByColumnArray(resultSheet, "A");
  if (firstEmptyRow == 1) {
    // カラム名を収集
    var headers = new Array(new Array(numCols));
    for (var col = 0; col < numCols; col++){
      headers[0][col] = rsmd.getColumnName(col+1);
    }
    resultSheet.getRange(1, 1, headers.length, headers[0].length).setValues(headers);
  } else {
    rowNum = firstEmptyRow;
  }

  // データ行をシートに書き込む
  var values = new Array(new Array(numCols));
  while (results.next()) {
    for (var col = 0; col < numCols; col++) {
      values[0][col] = results.getString(col + 1);
    }
    resultSheet.getRange(rowNum, 1, 1, numCols).setValues(values);
    rowNum++;
  }

  results.close();
  stmt.close();
}

関数が完了すると、ODBC ドライバーからのデータでスプレッドシートが作成され、インターネットにアクセスできる場所ならどこからでもGoogle Sheets の計算、グラフ、チャート機能を活用できます。

完全なGoogle Apps Script

// このブロックの変数を実際の値に置き換えてください
var address = 'my.server.address:port';
var user = 'SQL_GATEWAY_USER';
var userPwd = 'SQL_GATEWAY_PASSWORD';
var db = 'DSN_HERE';

var dbUrl = 'jdbc:mysql://' + address + '/' + db;

function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var menuItems = [
    {name: 'Write table data to a sheet', functionName: 'connectToODBCData'}
  ];
  spreadsheet.addMenu('ODBC Data', menuItems);
}

/*
 * カラムの配列をスキャンして、スプレッドシートの最初の空行を検索
 * @return 最初の空行の行番号
 */
function getFirstEmptyRowByColumnArray(spreadSheet, column) {
  var column = spreadSheet.getRange(column + ":" + column);
  var values = column.getValues(); // すべてのデータを1回の呼び出しで取得
  var ct = 0;
  while ( values[ct] && values[ct][0] != "" ) {
    ct++;
  }
  return (ct+1);
}

/*
 * 指定された'テーブル'からデータを読み取り、指定されたシートに書き込む
 *    （指定されたシートが存在しない場合は作成される）
 */
function connectToODBCData() {
  var thisWorkbook = SpreadsheetApp.getActive();

  // シートを選択（存在しない場合は作成）
  var selectedSheet = Browser.inputBox('Which sheet would you like the data to post to?',Browser.Buttons.OK_CANCEL);
  if (selectedSheet == 'cancel')
    return;

  if (thisWorkbook.getSheetByName(selectedSheet) == null)
    thisWorkbook.insertSheet(selectedSheet);
  var resultSheet = thisWorkbook.getSheetByName(selectedSheet);
  var rowNum = 2;

  // ODBC 'テーブル'を選択
  var table = Browser.inputBox('Which ODBC data table would you like to pull data from?',Browser.Buttons.OK_CANCEL);
  if (table == 'cancel')
    return;

  var conn = Jdbc.getConnection(dbUrl, user, userPwd);

  // var table が有効なテーブル/ビューであることを確認
  var dbMetaData = conn.getMetaData();
  var tableSet = dbMetaData.getTables(null, null, table, null);
  var validTable = false;
  while (tableSet.next()) {
    var tempTable = tableSet.getString(3);
    if (table.toUpperCase() == tempTable.toUpperCase()){
      table = tempTable;
      validTable = true;
      break;
    }
  }
  tableSet.close();
  if (!validTable) {
    Browser.msgBox("Invalid table name: " + table, Browser.Buttons.OK);
    return;
  }

  var stmt = conn.createStatement();

  var results = stmt.executeQuery('SELECT * FROM ' + table);
  var rsmd = results.getMetaData();
  var numCols = rsmd.getColumnCount();

  // シートが空の場合、最初の行にヘッダーを設定
  var firstEmptyRow = getFirstEmptyRowByColumnArray(resultSheet, "A");
  if (firstEmptyRow == 1) {
    // カラム名を収集
    var headers = new Array(new Array(numCols));
    for (var col = 0; col < numCols; col++){
      headers[0][col] = rsmd.getColumnName(col+1);
    }
    resultSheet.getRange(1, 1, headers.length, headers[0].length).setValues(headers);
  } else {
    rowNum = firstEmptyRow;
  }

  // データ行をシートに書き込む
  var values = new Array(new Array(numCols));
  while (results.next()) {
    for (var col = 0; col < numCols; col++) {
      values[0][col] = results.getString(col + 1);
    }
    resultSheet.getRange(rowNum, 1, 1, numCols).setValues(values);
    rowNum++;
  }

  results.close();
  stmt.close();
}