ノーコードでクラウド上のデータとの連携を実現。
詳細はこちら →こんにちは！プロダクトスペシャリストの宮本です。
Google Apps Script（GAS）を使用すると、Google スプレッドシートやGoogle Docs（Google ドキュメント）を含むGoogle アプリ内でカスタム機能を作成できます。CData Connect Server を使用すると、CSV を含むCData でサポートされている250を超えるデータソースにアクセスできます。Google Apps Script のネイティブサポートに対応したJDBC 機能を使って、Google スプレッドシート・Docs からリアルタイムCSV データにアクセスしてみましょう。
この記事では、Connect Server でCSV に接続する方法を説明して、Google スプレッドシートでCSV データを処理するためのサンプルスクリプトを提供します。
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 をインストールしてください。
DataSource プロパティにローカルフォルダ名を設定します。
.csv、.tab、.txt ではない拡張子のファイルを扱う場合には、IncludeFiles 使用する拡張子をカンマ区切りで設定します。Microsoft Jet OLE DB 4.0 driver 準拠の場合にはExtended Properties を設定することができます。別の方法として、Schema.ini ファイルにファイル形式を記述することも可能です。
CSV ファイルの削除や更新を行う場合には、UseRowNumbers をTRUE に設定します。RowNumber はテーブルKey として扱われます。
URI をバケットおよびフォルダに設定します。さらに、次のプロパティを設定して認証します。
URI をCSV ファイルを含むフォルダへのパスに設定します。Box へ認証するには、OAuth 認証標準を使います。 認証方法については、Box への接続 を参照してください。
URI をCSV ファイルを含むフォルダへのパスに設定します。Dropbox へ認証するには、OAuth 認証標準を使います。 認証方法については、Dropbox への接続 を参照してください。ユーザーアカウントまたはサービスアカウントで認証できます。ユーザーアカウントフローでは、以下の接続文字列で示すように、ユーザー資格情報の接続プロパティを設定する必要はありません。
URI をCSV ファイルを含むドキュメントライブラリに設定します。認証するには、User、Password、およびStorageBaseURL を設定します。
URI をCSV ファイルを含むドキュメントライブラリに設定します。StorageBaseURL は任意です。指定しない場合、ドライバーはルートドライブで動作します。 認証するには、OAuth 認証標準を使用します。
URI をルートフォルダとして使用されるフォルダへのパスが付いたサーバーのアドレスに設定します。認証するには、User およびPassword を設定します。
デスクトップアプリケーションからのGoogle への認証には、InitiateOAuth をGETANDREFRESH に設定して、接続してください。詳細はドキュメントの「Google Drive への接続」を参照してください。
仮想データベースが作成されたら、Google Apps Script を含むお好みのクライアントからCSV に接続できるようになります。
この時点で、Connect Server でCSV の仮想データベースが構成できました。あとは、Google Apps Script を使ってConnect Server にアクセスし、Google スプレッドシートでサービスを操作するだけです。
まずは、接続に必要なTDS エンドポイントの情報を取得しておきます。「CLIENTS」→「View Endpoints」とクリックすると表示される、「SQL Server Hostname」と「Port」の情報が必要になります。
次に、スプレッドシートにCSV データを入力するためのスクリプト（スクリプトを呼び出すメニューオプション付き）を作成します。サンプルスクリプトを作成し、以下で各部分について説明を加えています。スクリプトの全体については、記事の最後に記載しています。
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: 'selectCSVData'}
];
spreadsheet.addMenu('CSV データを取得', menuItems);
}
以下の関数では、Google Apps Script のJDBC 機能を使用してCSV をConnect Server に接続し、SELECT でデータを取得してスプレッドシートに入力します。スクリプトを実行すると、以下の２つの入力ボックスが表示されます。
最初のボックスは、データを保持するシート名を入力するためのものです（該当するシートがない場合、新規に作成されます）。
次のボックス、読み込むCSV テーブルの名前を入力するためのものです。無効なテーブルを選択するとエラーメッセージが表示され、関数が終了します。
この関数は、メニューオプションからの使用を想定して設計されていますが、スプレッドシートの式として使用するようにカスタマイズすることもできます。
/*
* 指定したCSV のテーブルからデータを読み込み、指定したシートに書き込みます。
* シートが存在しない場合、新規に作成されます。
*/
function selectCSVData() {
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 CSV '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();
}
処理が完了するとCSV データが入力されたスプレッドシートが作成され、インターネットにアクセスできるあらゆる場所で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: 'selectCSVData'}
];
spreadsheet.addMenu('CSV データを取得', menuItems);
}
/*
* 指定したCSV のテーブルからデータを読み込み、指定したシートに書き込みます。
* シートが存在しない場合、新規に作成されます。
*/
function selectCSVData() {
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 CSV '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();
}