Google Apps Script から Rakuraku Meisai のデータに接続

Dibyendu Datta
Lead Technology Evangelist

CData Connect Server を使用して、Google Apps Script からRakuraku Meisai のデータにアクセスする方法を解説します。

Google Apps Script を使えば、Google スプレッドシート、Google ドキュメントなど、Google ドキュメント内でカスタム機能を作成できます。CData Connect Server を使用すると、Rakuraku Meisai を含む250以上のCData がサポートするデータソースに対してSQL Server インターフェースを提供します。Google Apps Script ではJDBC サービスを通じてSQL Server プロトコルがネイティブにサポートされているため、Connect Server を利用することで、Google ドキュメント内からリアルタイムのRakuraku Meisai のデータにアクセスできます。

この記事では、Connect Server で Rakuraku Meisai 用の仮想データベースを作成し、Google スプレッドシートでRakuraku Meisai のデータを処理するためのサンプルスクリプトを紹介します。

CData Connect Server は、Rakuraku Meisai への純粋なSQL Server インターフェースを提供し、データをネイティブサポートのデータベースにレプリケートすることなく、Apps Script からリアルタイムのRakuraku Meisai のデータを使ったレポートを簡単に作成できます。ビジュアライゼーションを作成する際、Apps Script はデータを収集するためにSQL クエリを生成します。最適化されたデータ処理を標準で使用することで、CData Connect Server はサポートされているすべてのSQL 操作（フィルタ、JOIN など）をRakuraku Meisaiに直接プッシュし、サーバーサイド処理を活用して要求されたRakuraku Meisai のデータを迅速に返します。

Rakuraku Meisai のデータ用の仮想SQL Server データベースを作成

CData Connect Server は、シンプルなポイントアンドクリックのインターフェースでデータソースに接続し、API を生成できます。

Connect Server にログインし、Connections をクリックします。
Available Data Sources から「Rakuraku Meisai」を選択します。
Rakuraku Meisai に接続するために必要な認証プロパティを入力します。
「楽楽明細API キー」、「ドメイン」、「アカウント」を入力することで、楽楽明細に接続できます。API キーは、楽楽明細のユーザー設定ページで生成できます。ドメインとアカウントは、ご利用の環境のURL から見つけることができます。これらのプロパティを取得したら、ProfileSettings 接続プロパティに設定してください。

次に、プロファイルをダウンロードしてドライバーがアクセス可能な場所に配置します。こちらからプロファイルをダウンロードして、「C:/profiles/」などに設置してください。
Save Changes をクリックします。
Privileges -> Add をクリックし、適切な権限を持つ新しいユーザー（または既存のユーザー）を追加します。

仮想データベースが作成されたら、Apps Script からRakuraku Meisai のデータに接続する準備が整いました。

Apps Script で Rakuraku Meisai のデータに接続

この時点で、Connect Server で Rakuraku Meisai 用の仮想データベースを構成できているはずです。あとは Google Apps Script を使用して Connect Server にアクセスし、Google スプレッドシートでRakuraku Meisai のデータを扱うだけです。

このセクションでは、スプレッドシートにRakuraku Meisai のデータを入力するスクリプト（およびスクリプトを呼び出すメニューオプション）を作成します。サンプルスクリプトを作成し、各部分を説明しています。完全なスクリプトは記事の最後で確認できます。

1. 空のスクリプトを作成

Google スプレッドシート用のスクリプトを作成するには、Google スプレッドシートのメニューからツールスクリプトエディタをクリックします。

スクリプトエディタを開く

2. クラス変数の宣言

スクリプトで作成するすべての関数から利用できるように、いくつかのクラス変数を作成します。

//このブロックの変数を実際の値に置き換えてください
var address = 'CONNECT_SERVER_URL:port';
var user = 'CONNECT_USER';
var userPwd = 'CONNECT_PASSWORD';
var db = 'rakurakumeisaidb';
var serverSslCert = '-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----';
var clientSslCert = '-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----';
var clientSslKey  = '-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----';

var dbUrl = 'jdbc:sqlserver://' + address + '/' + db + '?useSSL=true';

3. メニューオプションの追加

この関数は、Google スプレッドシートにメニューオプションを追加し、UI から関数を呼び出せるようにします。

function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var menuItems = [
    {name: 'Write data to a sheet', functionName: 'connectToAPIData'}
  ];
  spreadsheet.addMenu('Rakuraku Meisai のデータ', menuItems);
}

4. ヘルパー関数の作成

この関数は、スプレッドシートの最初の空行を見つけるために使用します。

/*
 * カラムの配列をスキャンして、スプレッドシートの最初の空行を見つけます
 * @return 最初の空行の行番号
 */
function getFirstEmptyRowByColumnArray(spreadSheet, column) {
  var column = spreadSheet.getRange(column + ":" + column);
  var values = column.getValues(); // 1回の呼び出しですべてのデータを取得
  var ct = 0;
  while ( values[ct] && values[ct][0] != "" ) {
    ct++;
  }
  return (ct+1);
}

5. Rakuraku Meisai のデータをスプレッドシートに書き込む関数の作成

以下の関数は、Google Apps Script のJDBC 機能を使用してConnect Server に接続し、データをSELECT してスプレッドシートに入力することで、Rakuraku Meisai のデータを書き込みます。スクリプトを実行すると、2つの入力ボックスが表示されます。

最初のボックスでは、データを保持するシートの名前を入力するよう求められます（スプレッドシートが存在しない場合、関数はそれを作成します）。

2番目のボックスでは、読み取る Rakuraku Meisai テーブルの名前を入力するよう求められます。無効なテーブルを選択すると、エラーメッセージが表示され、関数は終了します。

この関数はメニューオプションとして使用するために設計されていますが、スプレッドシートの数式として使用できるように拡張することも可能です。

/*
 * 指定された Rakuraku Meisai 'テーブル'からデータを読み取り、指定されたシートに書き込みます。
 *    （指定されたシートが存在しない場合は作成されます。）
 */
function connectToAPIData() {
  var thisWorkbook = SpreadsheetApp.getActive();

  //シートを選択し、存在しない場合は作成
  var selectedSheet = Browser.inputBox('Which sheet would you like the data to post to?',Browser.Buttons.OK_CANCEL);
  if (selectedSheet == 'cancel')
    return;

  if (thisWorkbook.getSheetByName(selectedSheet) == null)
    thisWorkbook.insertSheet(selectedSheet);
  var resultSheet = thisWorkbook.getSheetByName(selectedSheet);
  var rowNum = 2;

  //Rakuraku Meisai 'テーブル'を選択
  var table = Browser.inputBox('Which table would you like to pull data from?',Browser.Buttons.OK_CANCEL);
  if (table == 'cancel')
    return;

  var conn = Jdbc.getConnection(dbUrl, {
    user: user,
    password: userPwd,
    _serverSslCertificate: serverSslCert,
    _clientSslCertificate: clientSslCert,
    _clientSslKey:         clientSslKey
  );

  //var table が有効なテーブル/ビューであることを確認
  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("Invalid table name: " + table, Browser.Buttons.OK);
    return;
  }

  var stmt = conn.createStatement();

  var results = stmt.executeQuery('SELECT * FROM ' + table);
  var rsmd = results.getMetaData();
  var numCols = rsmd.getColumnCount();

  //シートが空の場合、最初の行にヘッダーを入力
  var firstEmptyRow = getFirstEmptyRowByColumnArray(resultSheet, "A");
  if (firstEmptyRow == 1) {
    //カラム名を収集
    var headers = new Array(new Array(numCols));
    for (var col = 0; col < numCols; col++){
      headers[0][col] = rsmd.getColumnName(col+1);
    }
    resultSheet.getRange(1, 1, headers.length, headers[0].length).setValues(headers);
  } else {
    rowNum = firstEmptyRow;
  }

  //Rakuraku Meisai のデータの行をシートに書き込み
  var values = new Array(new Array(numCols));
  while (results.next()) {
    for (var col = 0; col < numCols; col++) {
      values[0][col] = results.getString(col + 1);
    }
    resultSheet.getRange(rowNum, 1, 1, numCols).setValues(values);
    rowNum++;
  }

  results.close();
  stmt.close();
}

関数が完了すると、Rakuraku Meisai のデータが入力されたスプレッドシートが完成し、インターネットにアクセスできる場所であれば、Google スプレッドシートのすべての計算、グラフ、チャート機能を活用できます。

完全な Google Apps Script

//このブロックの変数を実際の値に置き換えてください
var address = 'CONNECT_SERVER_URL:port';
var user = 'CONNECT_USER';
var userPwd = 'CONNECT_PASSWORD';
var db = 'rakurakumeisaidb';
var serverSslCert = '-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----';
var clientSslCert = '-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----';
var clientSslKey  = '-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----';

var dbUrl = 'jdbc:sqlserver://' + address + '/' + db + '?useSSL=true';

function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var menuItems = [
    {name: 'Write table data to a sheet', functionName: 'connectToAPIData'}
  ];
  spreadsheet.addMenu('Rakuraku Meisai のデータ', menuItems);
}

/*
 * カラムの配列をスキャンして、スプレッドシートの最初の空行を見つけます
 * @return 最初の空行の行番号
 */
function getFirstEmptyRowByColumnArray(spreadSheet, column) {
  var column = spreadSheet.getRange(column + ":" + column);
  var values = column.getValues(); // 1回の呼び出しですべてのデータを取得
  var ct = 0;
  while ( values[ct] && values[ct][0] != "" ) {
    ct++;
  }
  return (ct+1);
}

/*
 * 指定された 'テーブル'からデータを読み取り、指定されたシートに書き込みます。
 *    （指定されたシートが存在しない場合は作成されます。）
 */
function connectToAPIData() {
  var thisWorkbook = SpreadsheetApp.getActive();

  //シートを選択し、存在しない場合は作成
  var selectedSheet = Browser.inputBox('Which sheet would you like the data to post to?',Browser.Buttons.OK_CANCEL);
  if (selectedSheet == 'cancel')
    return;

  if (thisWorkbook.getSheetByName(selectedSheet) == null)
    thisWorkbook.insertSheet(selectedSheet);
  var resultSheet = thisWorkbook.getSheetByName(selectedSheet);
  var rowNum = 2;

  //Rakuraku Meisai 'テーブル'を選択
  var table = Browser.inputBox('Which table would you like to pull data from?',Browser.Buttons.OK_CANCEL);
  if (table == 'cancel')
    return;

  var conn = Jdbc.getConnection(dbUrl, {
    user: user,
    password: userPwd,
    _serverSslCertificate: serverSslCert,
    _clientSslCertificate: clientSslCert,
    _clientSslKey:         clientSslKey
  );

  //var table が有効なテーブル/ビューであることを確認
  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("Invalid table name: " + table, Browser.Buttons.OK);
    return;
  }

  var stmt = conn.createStatement();

  var results = stmt.executeQuery('SELECT * FROM ' + table);
  var rsmd = results.getMetaData();
  var numCols = rsmd.getColumnCount();

  //シートが空の場合、最初の行にヘッダーを入力
  var firstEmptyRow = getFirstEmptyRowByColumnArray(resultSheet, "A");
  if (firstEmptyRow == 1) {
    //カラム名を収集
    var headers = new Array(new Array(numCols));
    for (var col = 0; col < numCols; col++){
      headers[0][col] = rsmd.getColumnName(col+1);
    }
    resultSheet.getRange(1, 1, headers.length, headers[0].length).setValues(headers);
  } else {
    rowNum = firstEmptyRow;
  }

  //Rakuraku Meisai のデータの行をシートに書き込み
  var values = new Array(new Array(numCols));
  while (results.next()) {
    for (var col = 0; col < numCols; col++) {
      values[0][col] = results.getString(col + 1);
    }
    resultSheet.getRange(rowNum, 1, 1, numCols).setValues(values);
    rowNum++;
  }

  results.close();
  stmt.close();
}

はじめる準備はできましたか？

詳細はこちら、または無料トライアルにお申し込みください：

CData Connect Server

CData は、AI を本番環境で機能させるデータレイヤーです。350 以上のソースへのライブコネクティビティとレプリケーション、セマンティックコンテキスト、組み込みガバナンスを提供。Databricks、Microsoft、Google、Palantir をはじめ、世界中の 10,000 以上のお客様の AI を支えています。

お問い合わせ