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

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

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

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

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

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

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

Bullhorn CRM のデータに接続

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

Bullhorn CRM 接続プロパティの取得・設定方法

Bullhorn CRM に接続するには、Bullhorn CRM アカウントの資格情報を入力します。また、DataCenterCode プロパティをデータセンターに対応するデータセンターコードに設定してください。詳しくは、こちら を参照してください。

CLS2、CLS21 などのコードはクラスタID で、ログインした際のブラウザのURL（アドレスバー）に含まれます。

例えば、

https://cls21.bullhornstaffing.com/BullhornSTAFFING/MainFrame.jsp?#no-ba

こちらのURL は、ログインしたユーザーがCLS21 クラスタに存在することを示しています。

ちなみに、コールバックURL の末尾に"/" を含む値、例えば http://localhost:33333/ を指定する場合は、アプリケーション設定で指定したコールバックURL と厳密に同じ値を指定する必要があります。このパラメータの文字が一致しない場合、エラーとなります。

Bullhorn CRM への認証（OAuth）

Bullhorn CRM ではOAuth 2.0 認証標準を利用できます。 OAuth を使用して認証するには、すべてのシナリオでカスタムOAuth アプリケーションを作成して設定する必要があります。詳しい認証方法については、ヘルプドキュメントの「OAuth」セクションを参照してください。

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

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

リモートアクセスの設定

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

Apps Script で Bullhorn CRM のデータに接続

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

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

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 BullhornCRM 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: 'connectToBullhornCRMData'}
  ];
  spreadsheet.addMenu('Bullhorn CRM のデータ', 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. Bullhorn CRM のデータをスプレッドシートに書き込む関数を作成

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

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

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

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

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

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

/*
 * Reads data from a specified Bullhorn CRM 'table' and writes it to the specified sheet.
 *    (If the specified sheet does not exist, it is created.)
 */
function connectToBullhornCRMData() {
  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 Bullhorn CRM '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 ofBullhorn CRM のデータto 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();
}

関数が完了すると、Bullhorn CRM のデータ が入力されたスプレッドシートが作成され、インターネットにアクセスできる場所であれば、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 BullhornCRM Sys';

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

function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var menuItems = [
    {name: 'Write table data to a sheet', functionName: 'connectToBullhornCRMData'}
  ];
  spreadsheet.addMenu('Bullhorn CRM のデータ', 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 connectToBullhornCRMData() {
  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 Bullhorn CRM '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 ofBullhorn CRM のデータto 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();
}

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

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

 ダウンロード

詳細：

Bullhorn CRM Icon Bullhorn CRM ODBC Driver お問い合わせ

Bullhorn CRM ODBC Driver を使って、ODBC 接続をサポートするあらゆるアプリケーション・ツールからBullhorn CRM にデータ連携。

Bullhorn CRM データにデータベースと同感覚でアクセスして、Bullhorn CRM のCandidates、Resumes、Searches データに使い慣れたODBC インターフェースで双方向連携。

この記事の内容

関連記事