各製品の資料を入手。
詳細はこちら →こんにちは！プロダクトスペシャリストの宮本です。
Google Apps Script（GAS）を使用すると、Google スプレッドシートやGoogle Docs（Google ドキュメント）を含むGoogle アプリ内でカスタム機能を作成できます。CData Connect Server を使用すると、Business b-ridge を含むCData でサポートされている250を超えるデータソースにアクセスできます。Google Apps Script のネイティブサポートに対応したJDBC 機能を使って、Google スプレッドシート・Docs からリアルタイムBusiness b-ridge のデータにアクセスしてみましょう。
この記事では、Connect Server でBusiness b-ridge に接続する方法を説明して、Google スプレッドシートでBusiness b-ridge のデータを処理するためのサンプルスクリプトを提供します。
GAS からCData Connect Server に接続するには、利用するConnect Server インスタンスをネットワーク経由での接続が可能なサーバーにホスティングして、URL での接続を設定する必要があります。CData Connect がローカルでホスティングされており、localhost アドレス（localhost:8080 など）またはローカルネットワークのIP アドレス（192.168.1.x など）からしか接続できない場合、GAS はCData Connect Server に接続することができません。
クラウドホスティングでの利用をご希望の方は、AWS Marketplace やGCP Marketplace で設定済みのインスタンスを提供しています。
CData Connect Server は、シンプルなポイントアンドクリックインターフェースを使用してデータソースに接続し、データを取得します。まずは、右側のサイドバーのリンクからConnect Server をインストールしてください。
B b-ridge への接続には、Company Key、Project Key、Subscription Key が必要となります。それぞれWeb API 利用申請時に取得できます。取得したKey を接続プロパティに設定して接続します。
仮想データベースが作成されたら、Google Apps Script を含むお好みのクライアントからBusiness b-ridge に接続できるようになります。
この時点で、Connect Server でBusiness b-ridge の仮想データベースが構成できました。あとは、Google Apps Script を使ってConnect Server にアクセスし、Google スプレッドシートでサービスを操作するだけです。
まずは、接続に必要なTDS エンドポイントの情報を取得しておきます。「CLIENTS」→「View Endpoints」とクリックすると表示される、「SQL Server Hostname」と「Port」の情報が必要になります。
次に、スプレッドシートにBusiness b-ridge のデータを入力するためのスクリプト（スクリプトを呼び出すメニューオプション付き）を作成します。サンプルスクリプトを作成し、以下で各部分について説明を加えています。スクリプトの全体については、記事の最後に記載しています。
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;
この関数は、Google スプレッドシートにメニューオプションを追加し、UI を使用して関数を呼び出すことができるようにします。
function onOpen() {
var spreadsheet = SpreadsheetApp.getActive();
var menuItems = [
{name:'データをスプレッドシートに書き込む', functionName: 'selectBusiness b-ridgeData'}
];
spreadsheet.addMenu('Business b-ridge のデータを取得', menuItems);
}
以下の関数では、Google Apps Script のJDBC 機能を使用してBusiness b-ridge をConnect Server に接続し、SELECT でデータを取得してスプレッドシートに入力します。スクリプトを実行すると、以下の２つの入力ボックスが表示されます。
最初のボックスは、データを保持するシート名を入力するためのものです（該当するシートがない場合、新規に作成されます）。
次のボックス、読み込むBusiness b-ridge テーブルの名前を入力するためのものです。無効なテーブルを選択するとエラーメッセージが表示され、関数が終了します。
この関数は、メニューオプションからの使用を想定して設計されていますが、スプレッドシートの式として使用するようにカスタマイズすることもできます。
/*
* 指定したBusiness b-ridge のテーブルからデータを読み込み、指定したシートに書き込みます。
* シートが存在しない場合、新規に作成されます。
*/
function selectBusiness b-ridgeData() {
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 Business b-ridge '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();
}
処理が完了するとBusiness b-ridge のデータが入力されたスプレッドシートが作成され、インターネットにアクセスできるあらゆる場所でGoogle スプレッドシートの計算、グラフ化、チャート作成機能を利用できるようになります。
//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: 'selectBusiness b-ridgeData'}
];
spreadsheet.addMenu('Business b-ridge のデータを取得', menuItems);
}
/*
* 指定したBusiness b-ridge のテーブルからデータを読み込み、指定したシートに書き込みます。
* シートが存在しない場合、新規に作成されます。
*/
function selectBusiness b-ridgeData() {
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 Business b-ridge '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();
}
