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

Jerod Johnson
Director, Technology Evangelism

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

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

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

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

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

AfterShip のデータに接続

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

まず、Profile 接続プロパティをディスク上の AfterShip プロファイルの場所に設定します（例：C:\profiles\Aftership.apip）。次に、ProfileSettings 接続プロパティを AfterShip の接続文字列に設定します（以下を参照）。

AfterShip API プロファイル設定

AfterShip のアカウント管理パネルで「My Account > Settings > API Keys」に移動して、API キーを取得します。

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

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

SQL Gateway で MySQL リモーティングサービスを作成（Salesforce の例）

リモートアクセスの設定

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

Apps Script で AfterShip のデータに接続

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

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

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

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

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

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

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

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

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

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

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

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

API Driver で AfterShip のライブデータに接続

AfterShip に接続

CData は、AI を本番環境で機能させるデータレイヤーです。数百の主要エンタープライズソースへのライブコネクティビティとレプリケーション、セマンティックコンテキスト、組み込みガバナンスを提供。Databricks、Microsoft、Google、Palantir をはじめ、世界中の 10,000 以上のお客様の AI を支えています。

お問い合わせ

CData Connect AI Gateway のご紹介

ユースケース別

プラットフォーム別

ユースケース別

ソース別

導入事例

デベロッパーセンター

5分で始めるクイックスタート

マネージド

セルフホスト

探索

参加する

サポート & サービス