SQL Gateway 経由で Google Apps Script から Dynamics 365 Business Central のデータに接続

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

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

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

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

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

Dynamics 365 Business Central のデータに接続

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

Dynamics 365 Business Central 接続プロパティの取得・設定方法

Dynamics 365 Business Central への認証には、User およびAccessKey プロパティが必要です。

データに接続するには、OrganizationUrl を指定します。OrganizationUrl は、 http：//businesscentral.dynamics.com/abc123/ などのBusiness Central アカウントへのエンドポインであるか、Web サービスのルートを参照する必要があります。OrganizationUrl を指定する方法 および利用可能なエンドポイントについての詳細は、Business Central エンドポイント を参照してください。組織内に複数の会社がある場合は、どの会社に接続するかを特定するためにCompany を指定する必要があります。 会社が1つだけの場合は、Company を指定する必要はありません。

Dynamics 365 Business Central に認証するには、User およびAccessKey 接続プロパティを指定します。Microsoft では、これらをテストおよび開発目的で推奨します。ただし、運用環境での使用は推奨していません。User およびAccessKey の値を取得するには、Dynamics 365 Business Central の「ユーザー」ページに移動して「編集」をクリックします。User Name および Web Service Access Key の値は、User およびPassword 接続文字列プロパティとして入力する値です。User Name はE メールアドレス ではありません。短縮されたユーザー名です。

Microsoft では、OAuth 認証を使用する本番ユースケースを推奨します。詳細については、ヘルプドキュメントの「OAuth 認証の使用」を参照してください。

Dynamics 365 Business Central のデータ用に MySQL リモーティングサービスを作成

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

リモートアクセスの設定

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

Apps Script で Dynamics 365 Business Central のデータに接続

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

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

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 D365BusinessCentral 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: 'connectToD365BusinessCentralData'}
  ];
  spreadsheet.addMenu('Dynamics 365 Business Central のデータ', 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. Dynamics 365 Business Central のデータをスプレッドシートに書き込む関数を作成

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

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

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

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

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

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

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

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