Google Apps Script（GAS）からEnterpriseDB データに連携

こんにちは！プロダクトスペシャリストの宮本です。

Google Apps Script（GAS）を使用すると、Google スプレッドシートやGoogle Docs（Google ドキュメント）を含むGoogle アプリ内でカスタム機能を作成できます。CData Connect Server を使用すると、EnterpriseDB を含むCData でサポートされている250を超えるデータソースにアクセスできます。Google Apps Script のネイティブサポートに対応したJDBC 機能を使って、Google スプレッドシート・Docs からリアルタイムEnterpriseDB データにアクセスしてみましょう。

この記事では、Connect Server でEnterpriseDB に接続する方法を説明して、Google スプレッドシートでEnterpriseDB データを処理するためのサンプルスクリプトを提供します。

こちらのスクリプトは指定されたテーブルからデータを読み取るだけですが、スクリプトを簡単に拡張して更新機能を組み込むことができます。

EnterpriseDB データの仮想データベースを作成する

CData Connect Server は、シンプルなポイントアンドクリックインターフェースを使用してデータソースに接続し、データを取得します。まずは、右側のサイドバーのリンクからConnect Server をインストールしてください。

1. Connect Server にログインし、「CONNECTIONS」をクリックします。
2. 一覧から「EnterpriseDB」を選択します。
3. EnterpriseDB に接続するために必要な認証プロパティを入力します。

   データに接続するには、以下の接続プロパティが必要です。

   • Server: EnterpriseDB データベースをホスティングしているサーバーのホスト名またはIP アドレス。
   • Port: EnterpriseDB データベースをホスティングしているサーバーのポート。

   オプションで、以下を設定することもできます。

   • Database: EnterpriseDB サーバーに接続する場合のデフォルトのデータベース。設定されていない場合は、ユーザーのデフォルトデータベースが使用されます。

   Basic 認証による接続

   Basic 認証を使って認証するには、以下を設定します。

   • User：EnterpriseDB サーバーに認証する際に使われるユーザー。
   • Password：EnterpriseDB サーバーに認証する際に使われるパスワード。

   SSL 認証による接続

   SSL 認証を利用して、セキュアなセッションを介してEnterpriseDB データに接続できます。以下の接続プロパティを設定して、データに接続します。

   • SSLClientCert：クライアント証明書のための証明書ストア名に設定します。クライアントとサーバーの両方のマシンでトラストストアとキーストアが保持される2-way SSL の場合に使用されます。
   • SSLClientCertPassword：クライアント証明書ストアがパスワードで保護されている場合、この値をストアのパスワードに設定します。
   • SSLClientCertSubject：TLS/SSL クライアント証明書のSubject。ストア内の証明書を検索するために使用されます。
   • SSLClientCertType：クライアントストアの証明書タイプ。
   • SSLServerCert：サーバーが受け入れ可能な証明書。
4. 「 Test Connection」をクリックします。
5. 「Permission」->「 Add」とクリックし、適切な権限を持つ新しいユーザー(または既存のユーザー) を追加します。

仮想データベースが作成されたら、Google Apps Script を含むお好みのクライアントからEnterpriseDB に接続できるようになります。

Apps Script を使ってEnterpriseDB データに接続

この時点で、Connect Server でEnterpriseDB の仮想データベースが構成できました。あとは、Google Apps Script を使ってConnect Server にアクセスし、Google スプレッドシートでサービスを操作するだけです。

CData Connect Server のTDS エンドポイントを確認

まずは、接続に必要なTDS エンドポイントの情報を取得しておきます。「CLIENTS」→「View Endpoints」とクリックすると表示される、「SQL Server Hostname」と「Port」の情報が必要になります。

次に、スプレッドシートにEnterpriseDB データを入力するためのスクリプト（スクリプトを呼び出すメニューオプション付き）を作成します。サンプルスクリプトを作成し、以下で各部分について説明を加えています。スクリプトの全体については、記事の最後に記載しています。

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

Google スプレッドシートのスクリプトを作成するには、Google スプレッドシートメニューから「拡張機能」→「Apps Script」をクリックします。

2．クラス変数を宣言

スクリプトで作成された関数で使用できるようにいくつかのクラス変数を作成します。

  //CData Connect ServerのIP およびポートを指定
  var connectionName = 'xxxxxxx:1433;';
  //CData Connect Serverで作成したユーザー
  var user = 'admin';
  //CData Connect Serverで設定したパスワード
  var userPwd = 'xxxxxx';
  //接続先DB名（CData Connect Serverのコネクション名）
  var db = 'Connect_1';
   
  var instanceUrl = 'jdbc:sqlserver://' + connectionName + 'databaseName=' + db;

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

この関数は、Google スプレッドシートにメニューオプションを追加し、UI を使用して関数を呼び出すことができるようにします。

function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var menuItems = [
  {name:'データをスプレッドシートに書き込む', functionName: 'selectEnterpriseDBData'}
  ];
  spreadsheet.addMenu('EnterpriseDB データを取得', menuItems);
}

4．EnterpriseDB データをスプレッドシートに書き込む関数を記述

以下の関数では、Google Apps Script のJDBC 機能を使用してEnterpriseDB をConnect Server に接続し、SELECT でデータを取得してスプレッドシートに入力します。スクリプトを実行すると、以下の２つの入力ボックスが表示されます。

最初のボックスは、データを保持するシート名を入力するためのものです（該当するシートがない場合、新規に作成されます）。

次のボックス、読み込むEnterpriseDB テーブルの名前を入力するためのものです。無効なテーブルを選択するとエラーメッセージが表示され、関数が終了します。

この関数は、メニューオプションからの使用を想定して設計されていますが、スプレッドシートの式として使用するようにカスタマイズすることもできます。

/*
 * 指定したEnterpriseDB のテーブルからデータを読み込み、指定したシートに書き込みます。
 *  シートが存在しない場合、新規に作成されます。
 */
function selectEnterpriseDBData() {
  var thisWorkbook = SpreadsheetApp.getActive();

  //select a sheet and create it if it does not exist
  var selectedSheet = Browser.inputBox('データを書き込みたいシートを指定してください',Browser.Buttons.OK_CANCEL);
  if (selectedSheet == 'cancel')
    return;

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

  //select a EnterpriseDB 'table'
  var table = Browser.inputBox('データを取得したいテーブルを指定してください',Browser.Buttons.OK_CANCEL);
  if (table == 'cancel')
    return;

  // JDBCでデータベースへのコネクション確立
  var conn = Jdbc.getConnection(instanceUrl , user, userPwd);
  var stmt = conn.createStatement();

  //入力したテーブルが利用可能か検証します
  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("テーブル名が不正です：" + table, Browser.Buttons.OK);
    return;
  }

   
  // 実行したいSQL
  var results = stmt.executeQuery('SELECT * FROM [Connect_1].[Account];');
   
  var numCols = results.getMetaData();
   
  const sheet = SpreadsheetApp.getActiveSheet();
  const lastRow = sheet.getLastRow();
   
  let i = 1;
  while (results.next()) {
   
    var clmString = '';
    for (var col = 0; col < numCols.getColumnCount(); col++) {
        if (col==0){
          for(var j=1; j<=numCols.getColumnCount(); j++) {
            sheet.getRange(1, j).setValue(numCols.getColumnName(j))
          }
        }
    
      clmString = results.getString(col + 1);
      Logger.log(clmString);
      sheet.getRange(i+1, col+1).setValue(clmString);
    }
    i++;
  }
   
  results.close();
  stmt.close(); 
}
  

処理が完了するとEnterpriseDB データが入力されたスプレッドシートが作成され、インターネットにアクセスできるあらゆる場所でGoogle スプレッドシートの計算、グラフ化、チャート作成機能を利用できるようになります。

Google Apps Script 用サンプルスクリプトの全体

//CData Connect ServerのIP およびポートを指定
var connectionName = 'xxxxxxx:1433;';
//CData Connect Serverで作成したユーザー
var user = 'admin';
//CData Connect Serverで設定したパスワード
var userPwd = 'xxxxxx';
//接続先DB名（CData Connect Serverのコネクション名）
var db = 'Connect_1';
  
var instanceUrl = 'jdbc:sqlserver://' + connectionName + 'databaseName=' + db;

function onOpen() {
 var spreadsheet = SpreadsheetApp.getActive();
 var menuItems = [
 {name:'データをスプレッドシートに書き込む', functionName: 'selectEnterpriseDBData'}
 ];
  spreadsheet.addMenu('EnterpriseDB データを取得', menuItems);
}

/*
 * 指定したEnterpriseDB のテーブルからデータを読み込み、指定したシートに書き込みます。
 *  シートが存在しない場合、新規に作成されます。
 */
function selectEnterpriseDBData() {
 var thisWorkbook = SpreadsheetApp.getActive();
 
 //select a sheet and create it if it does not exist
 var selectedSheet = Browser.inputBox('データを書き込みたいシートを指定してください',Browser.Buttons.OK_CANCEL);
 if (selectedSheet == 'cancel')
   return;
 
 if (thisWorkbook.getSheetByName(selectedSheet) == null)
  thisWorkbook.insertSheet(selectedSheet);
 var resultSheet = thisWorkbook.getSheetByName(selectedSheet);
 var rowNum = 2;
 
 //select a EnterpriseDB 'table'
 var table = Browser.inputBox('データを取得したいテーブルを指定してください',Browser.Buttons.OK_CANCEL);
 if (table == 'cancel')
   return;
 
 // JDBCでデータベースへのコネクション確立
 var conn = Jdbc.getConnection(instanceUrl , user, userPwd);
 var stmt = conn.createStatement();
 
 //入力したテーブルが利用可能か検証します
 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("テーブル名が不正です：" + table, Browser.Buttons.OK);
   return;
 }
     
 // 実行したいSQL
 var results = stmt.executeQuery('SELECT * FROM [Connect_1].[Account];');
    
 var numCols = results.getMetaData();
    
 const sheet = SpreadsheetApp.getActiveSheet();
 const lastRow = sheet.getLastRow();
    
 let i = 1;
 while (results.next()) {
    
   var clmString = '';
   for (var col = 0; col < numCols.getColumnCount(); col++) {
       if (col==0){
         for(var j=1; j<=numCols.getColumnCount(); j++) {
           sheet.getRange(1, j).setValue(numCols.getColumnName(j))
         }
       }
     
     clmString = results.getString(col + 1);
     Logger.log(clmString);
     sheet.getRange(i+1, col+1).setValue(clmString);
   }
   i++;
 }
    
 results.close();
 stmt.close(); 
}