SQL Gateway 経由で Google Apps Script から LDAP のデータに接続

Jerod Johnson
Jerod Johnson
Senior Technology Evangelist
ODBC Driver for LDAP と SQL Gateway を使用して、Google Apps Script からLDAP objectsにアクセスする方法を紹介します。

Google Apps Script を使用すると、Google Sheets、Google Docs など、Google ドキュメント内でカスタム機能を作成できます。CData SQL Gateway を使用すると、LDAP 用の ODBC ドライバーを含む CData の 250種類以上のドライバーに対して MySQL インターフェースを作成できます。MySQL プロトコルは Google Apps Script の JDBC サービスでネイティブにサポートされているため、SQL Gateway を活用することで、Google ドキュメント内からリアルタイムのLDAP objectsにアクセスできるようになります。

本記事では、Google Apps Script から LDAP 用の ODBC Driver に接続する方法を説明し、SQL Gateway の設定手順と Google Spreadsheet でLDAP objectsを処理するためのサンプルスクリプトを紹介します。

このスクリプトでは指定されたテーブルからのデータ読み取りのみを行いますが、更新機能を組み込むように簡単に拡張できます。

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

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

LDAP のデータに接続

まだ行っていない場合は、データソース名（DSN）で必要な接続プロパティの値を指定してください。組み込みの Microsoft ODBC データソースアドミニストレーターを使用して DSN を設定できます。これはドライバーインストールの最後のステップでもあります。Microsoft ODBC データソースアドミニストレーターを使用して DSN を作成・設定する方法については、ヘルプドキュメントの「はじめに」の章を参照してください。

リクエストを認証するには、User およびPassword プロパティを有効なLDAP クレデンシャル（例えば、User を"Domain\BobF" または"cn=Bob F,ou=Employees,dc=Domain"）に設定します。 CData 製品は、デフォルトでプレーンテキスト認証を使用します。これは、CData 製品がサーバーとTLS/SSL のネゴシエーションを試みるためです。 AuthMechanism を使って別の認証方法を指定できます。 TLS/SSL コンフィギュレーションについて詳しくは、ヘルプドキュメントの「高度な設定」を参照してください。

    基本接続には、Server およびPort を設定します。さらに、次のように接続を微調整できます。
  • FollowReferrals：設定すると、CData 製品は参照サーバーのデータもビューとして表示します。参照サーバー上のデータを変更するには、このサーバーをServer およびPort で指定する必要があります。
  • LDAPVersion：サーバーが実装するプロトコルのバージョンに設定します。デフォルトでは、CData 製品はversion 2 を使用します。
  • BaseDN は、LDAP 検索の範囲を指定された識別名の高さに限定します。BaseDN の範囲を絞ることはパフォーマンスを劇的に向上させます。例えば、"cn=users,dc=domain" の値は、"cn=users" およびその子に含まれる結果のみを返します。
  • Scope：このプロパティを使用すると、サブツリーから返されるデータをより細かく制御できます。

LDAP のデータ用に MySQL リモーティングサービスを作成

SQL Gateway 概要を参照して、LDAP objects を仮想 MySQL データベースとして接続を設定してください。クライアントからの MySQL リクエストをリッスンする MySQL リモーティングサービスを設定します。サービスは SQL Gateway UI で設定できます。

リモートアクセスの設定

ODBC Driver とリモーティングサービスがオンプレミスにインストールされている（Google Apps Script からアクセスできない）場合は、リバース SSH トンネリング機能を使用してリモートアクセスを有効にできます。詳細な手順については、Knowledge Base の記事「SQL Gateway SSH トンネリング機能」を参照してください。

Apps Script で LDAP のデータに接続

ここまでで、LDAP objects 用に SQL Gateway を設定しました。あとは Google Apps Script を使用して MySQL リモーティングサービスにアクセスし、Google Sheets でLDAP objectsを操作するだけです。

このセクションでは、LDAP objects をスプレッドシートに入力するスクリプト（スクリプトを呼び出すメニューオプション付き）を作成します。サンプルスクリプトを作成し、各部分を説明しています。スクリプト全体は記事の最後で確認できます。

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

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

スクリプトエディタを開く

2. クラス変数を宣言

スクリプト内で作成する任意の関数から利用できるように、いくつかのクラス変数を作成します。

//replace the variables in this block with real values as needed
var address = 'my.server.address:port';
var user = 'SQL_GATEWAY_USER';
var userPwd = 'SQL_GATEWAY_PASSWORD';
var db = 'CData LDAP Sys';

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: 'connectToLDAPData'}
  ];
  spreadsheet.addMenu('LDAP Objects', menuItems);
}
新しく追加されたメニューオプション

4. ヘルパー関数を作成

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

/*
 * Finds the first empty row in a spreadsheet by scanning an array of columns
 * @return The row number of the first empty row.
 */
function getFirstEmptyRowByColumnArray(spreadSheet, column) {
  var column = spreadSheet.getRange(column + ":" + column);
  var values = column.getValues(); // get all data in one call
  var ct = 0;
  while ( values[ct] && values[ct][0] != "" ) {
    ct++;
  }
  return (ct+1);
}

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

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

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

シート選択用の入力ボックス

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

テーブル選択用の入力ボックス

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

/*
 * Reads data from a specified LDAP 'table' and writes it to the specified sheet.
 *    (If the specified sheet does not exist, it is created.)
 */
function connectToLDAPData() {
  var thisWorkbook = SpreadsheetApp.getActive();

  //select a sheet and create it if it does not exist
  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;

  //select a LDAP 'table'
  var table = Browser.inputBox('Which table would you like to pull data from?',Browser.Buttons.OK_CANCEL);
  if (table == 'cancel')
    return;

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

  //confirm that var table is a valid table/view
  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();

  //if the sheet is empty, populate the first row with the headers
  var firstEmptyRow = getFirstEmptyRowByColumnArray(resultSheet, "A");
  if (firstEmptyRow == 1) {
    //collect column names
    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;
  }

  //write rows ofLDAP objectsto the sheet
  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();
}

関数が完了すると、LDAP objects が入力されたスプレッドシートが作成され、インターネットにアクセスできる場所であれば、Google Sheets のすべての計算、グラフ作成、チャート機能を活用できるようになります。

完全な Google Apps Script

//replace the variables in this block with real values as needed
var address = 'my.server.address:port';
var user = 'SQL_GATEWAY_USER';
var userPwd = 'SQL_GATEWAY_PASSWORD';
var db = 'CData LDAP Sys';

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

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

/*
 * Finds the first empty row in a spreadsheet by scanning an array of columns
 * @return The row number of the first empty row.
 */
function getFirstEmptyRowByColumnArray(spreadSheet, column) {
  var column = spreadSheet.getRange(column + ":" + column);
  var values = column.getValues(); // get all data in one call
  var ct = 0;
  while ( values[ct] && values[ct][0] != "" ) {
    ct++;
  }
  return (ct+1);
}

/*
 * Reads data from a specified 'table' and writes it to the specified sheet.
 *    (If the specified sheet does not exist, it is created.)
 */
function connectToLDAPData() {
  var thisWorkbook = SpreadsheetApp.getActive();

  //select a sheet and create it if it does not exist
  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;

  //select a LDAP 'table'
  var table = Browser.inputBox('Which table would you like to pull data from?',Browser.Buttons.OK_CANCEL);
  if (table == 'cancel')
    return;

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

  //confirm that var table is a valid table/view
  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();

  //if the sheet is empty, populate the first row with the headers
  var firstEmptyRow = getFirstEmptyRowByColumnArray(resultSheet, "A");
  if (firstEmptyRow == 1) {
    //collect column names
    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;
  }

  //write rows ofLDAP objectsto the sheet
  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();
}

はじめる準備はできましたか？

LDAP ODBC Driver の無料トライアルをダウンロードしてお試しください：

 ダウンロード

詳細：

LDAP Icon LDAP ODBC Driver お問い合わせ

LDAP ODBC ドライバーは、ODBC 接続をサポートする任意のアプリケーションからリアルタイムなLDAP ディレクトリサービスに直接接続できるパワフルなツールです。標準のODBC ドライバーインタフェースを使用して、データベースのようにLDAP オブジェクトにアクセスし、任意のObjectClass のカスタムテーブルを定義し、SQL クエリーを実行できます。

この記事の内容

関連記事