こんにちは、CData Software Japan プロダクトチームの宮本です！
昨年リリースしたCData Connect Cloud というクラウド版データハブサービスのセルフホスティング型として、CData Connect Server という製品があるのはご存知でしょうか？
今回はCData Connect Server を 使って、kintone のデータを Google Apps Script からSQL で操作する手順をご紹介したいと思います。
CData Connect Server とは
冒頭にもお伝えしましたが、CData Connect Server はCData Connect Cloud というデータコネクティビティに特化した SaaS のアプリケーション版になります。
CData Connect Server | セルフホスティング可能なデータ連携プラットフォーム
CData Connect Server から各種SaaS 、DB データへの接続をしつつ、外部からは OData、REST API、TDS インタフェースで接続が行えるようデータ仮想化を行っています。
今回はOData、REST API、TDS とあるうちの TDS インタフェースを使って、GAS からSQLServer へ接続する内容でコードを書いてSQL でデータ操作していきます。
手順
最初に本記事で必要なものは下記の通りです。
CData Connect Server のセットアップ
CData Connect Server は .NET 版と クロスプラットフォーム版に加え、AMI 版もあります。
セットアップ方法については今回は省略しますが、特に大層なことはしていなく、インストールとアプリケーションの起動だけです。
以前にクロスプラットフォーム版をGCE 上でホスティングさせる手順を書いていましたので、クロスプラットフォーム版をご検討の場合は一度見ていただけるとイメージが付くかと思います。
GCE の CentOS上で CData Connect Cross-Platform 版をホスティングする方法 | CData Software Blog
もしくは下記のヘルプをご参照ください。
CData Connect - Installation and Configuration | 22.0.8340
CData Connect Server から kintone への接続設定
CData Connect Server を起動してましたら、「CONNECTIONS」→「+ Add Connectsion」の順でクリックし新規接続情報を作成します。
一覧からKintone を選択します。
接続設定したら右下の接続テストをクリックし、成功しましたら自動で保存されるようになります。
これで CData Connect Server から kintone への接続が完了です。
CData Connect Server のTDSエンドポイント確認
「CLIENTS」→「View Endpoints」をクリックします。
確認したらコピーしてどこかで保持しておきます。
Google Apps Script から kintone にSELECT
それではkintone のデータを取得するために SQL のSELECT を使ってデータを取得し、
スプレッドシート上に設定していきたいと思います。
使うコードはこちらになります。設定値はコメントを見ていただければわかるかと思います。
//CData Connect ServerのIP＋ポートを指定
var connectionName = 'xxxxxxx:1433;';
//CData Connect Serverで作成したユーザー(adminでも可)
var user = 'miyamotok';
//CData Connect Serverで設定したパスワード
var userPwd = 'xxxxxx';
//接続先DB名（CData Connect Serverのコネクション名）
var db = 'Connect_1';
var instanceUrl = 'jdbc:sqlserver://' + connectionName + 'databaseName=' + db;
function selectKintoneData(){
// JDBCでデータベースへのコネクション確立
var conn = Jdbc.getConnection(instanceUrl , user, userPwd);
var stmt = conn.createStatement();
// 実行したいSQL
var results = stmt.executeQuery('SELECT [RecordId],[部署名],[担当者名],[メールアドレス],[会社名],[更新日時] 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();
}
これを実行してみると、初回であれば以下のようなアクセス許可を承認するようメッセージが表示されますので許可を行います。
そうしますと、スプレッドシート上に kintone の指定したアプリのレコードがそのまま取得できます。
Google Apps Script から kintone にINSERT
では続いてデータの登録を行ってみます。
22行目に新規データをスプレッドシート上に追加します。
その後、下記関数でInsert を行うとkintone にデータが登録されます。
function insertToKintone(){
try{
var conn = Jdbc.getConnection(instanceUrl, user, userPwd);
var stmt = conn.createStatement();
var stmt = conn.prepareStatement('insert into [Connect_1].[Account] (部署名, 担当者名, メールアドレス, 会社名) values(?, ?, ?, ?);');
//現在のシートを取得
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getActiveSheet();
//設定値を取得
var busyo = sheet.getRange("B22").getValue();
var tantou = sheet.getRange("C22").getValue();
var mail = sheet.getRange("D22").getValue();
var company = sheet.getRange("E22").getValue();
stmt.setString(1, busyo);
stmt.setString(2, tantou);
stmt.setString(3, mail);
stmt.setString(4, company);
stmt.execute();
}
catch(e){
Logger.log(e);
}
finally{
stmt.close();
}
}
Google Apps Script から kintone にUPDATE
次はレコードの担当者名とメールアドレスを変更してみます。
変更したらあとは下記関数を実行するだけです。
function updateToKintone(){
try{
var conn = Jdbc.getConnection(instanceUrl, user, userPwd);
var stmt = conn.createStatement();
var stmt = conn.prepareStatement('UPDATE [Connect_1].[Account] SET 担当者名=? , メールアドレス=? WHERE RECORDID=?;');
//現在のシートを取得
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getActiveSheet();
//値を取得
var id = sheet.getRange("A2").getValue();
var tantou = sheet.getRange("C2").getValue();
var mail = sheet.getRange("D2").getValue();
stmt.setString(1, tantou);
stmt.setString(2, mail);
stmt.setString(3, Number(id));
stmt.executeUpdate();
}
catch(e){
Logger.log(e);
}
finally{
stmt.close();
}
}
Google Apps Script から kintone にDELETE
では最後に登録したレコードを削除してみます。
function deleteToKintone(){
try{
var conn = Jdbc.getConnection(instanceUrl, user, userPwd);
var stmt = conn.createStatement();
var stmt = conn.prepareStatement('DELETE FROM [Connect_1].[Account] WHERE RECORDID=?;');
stmt.setString(1, 21);
stmt.executeUpdate();
}
catch(e){
Logger.log(e);
}
finally{
stmt.close();
}
}
Delete後、確認としてSelect を実行してみると、このようにレコードが削除されているのが確認できました。
おわりに
いかがでしたでしょうか。CData Connect Server 側は一度設定してしまえば、あとは同じ手法で他のデータソースでもすぐにGoogle Apps Script からアクセスできるようになりますので、ぜひ SQL で各種SaaS のデータを Google Apps Script からアクセスしてみてください。
なお、今回ご紹介した CData Connect Server は30 日間は無償トライアルとテクニカルサポートへの問い合わせが可能です。この機会にお試しください。
CData Connect Server | ダウンロード
関連コンテンツ
