Google Apps Script から kintone アプリのデータを SQL で操作してみた：CData Connect Server

こんにちは、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 でデータ操作していきます。

手順

最初に本記事で必要なものは下記の通りです。

• Google Apps Script  
• kintone  
• CData Connect Server ※30日間の無償トライアル可：CData Connect Server | ダウンロード

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 | ダウンロード

関連コンテンツ