Google Apps Script（GAS）からe-Sales Manager データに連携

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

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

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

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

e-Sales Manager データの仮想データベースを作成する

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

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

   e セールスマネージャー Remix に接続するには、User、Passowrd、URL、TenantId パラメータが必要です。

   • User：API を実行するためのログインユーザーのユーザーID。
   • Password：API を実行するためのログインユーザーのユーザーパスワード
   。
   • URL：e-Sales Manager Remix エンドポイントへのURL。例：https://XXX.softbrain.co.jp
   • TenantId：e-Sales Manager Remix テナント名のTenantd。例：cdata
4. 「 Test Connection」をクリックします。
5. 「Permission」->「 Add」とクリックし、適切な権限を持つ新しいユーザー(または既存のユーザー) を追加します。

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

Apps Script を使ってe-Sales Manager データに接続

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

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

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

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

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: 'selecte-Sales ManagerData'}
  ];
  spreadsheet.addMenu('e-Sales Manager データを取得', menuItems);
}

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

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

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

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

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

/*
 * 指定したe-Sales Manager のテーブルからデータを読み込み、指定したシートに書き込みます。
 *  シートが存在しない場合、新規に作成されます。
 */
function selecte-Sales ManagerData() {
  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 e-Sales Manager '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(); 
}
  

処理が完了するとe-Sales Manager データが入力されたスプレッドシートが作成され、インターネットにアクセスできるあらゆる場所で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: 'selecte-Sales ManagerData'}
 ];
  spreadsheet.addMenu('e-Sales Manager データを取得', menuItems);
}

/*
 * 指定したe-Sales Manager のテーブルからデータを読み込み、指定したシートに書き込みます。
 *  シートが存在しない場合、新規に作成されます。
 */
function selecte-Sales ManagerData() {
 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 e-Sales Manager '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(); 
}