SQL Gateway 経由で Google Apps Script から BigCommerce のデータに接続

Jerod Johnson
Jerod Johnson
Senior Technology Evangelist
ODBC Driver for BigCommerce と SQL Gateway を使用して、Google Apps Script からBigCommerce のデータにアクセスする方法を紹介します。

Google Apps Script を使用すると、Google Sheets、Google Docs など、Google ドキュメント内でカスタム機能を作成できます。CData SQL Gateway を使用すると、BigCommerce 用の ODBC ドライバーを含む CData の 250種類以上のドライバーに対して MySQL インターフェースを作成できます。MySQL プロトコルは Google Apps Script の JDBC サービスでネイティブにサポートされているため、SQL Gateway を活用することで、Google ドキュメント内からリアルタイムのBigCommerce のデータにアクセスできるようになります。

本記事では、Google Apps Script から BigCommerce 用の ODBC Driver に接続する方法を説明し、SQL Gateway の設定手順と Google Spreadsheet でBigCommerce のデータを処理するためのサンプルスクリプトを紹介します。

このスクリプトでは指定されたテーブルからのデータ読み取りのみを行いますが、更新機能を組み込むように簡単に拡張できます。

SQL Gateway によるリアルタイム接続

SQL Gateway を使用すると、ローカルの ODBC データソースを標準的な MySQL データベースのように見せて動作させることができます。SQL Gateway で BigCommerce 用の ODBC Driver 用に新しい MySQL リモーティングサービスを作成し、SQL Gateway がインターネット接続可能なマシンにインストールされている（またはホストされた SSH サーバーに接続できる）ことを確認してください。

BigCommerce のデータに接続

まだ行っていない場合は、データソース名（DSN）で必要な接続プロパティの値を指定してください。組み込みの Microsoft ODBC データソースアドミニストレーターを使用して DSN を設定できます。これはドライバーインストールの最後のステップでもあります。Microsoft ODBC データソースアドミニストレーターを使用して DSN を作成・設定する方法については、ヘルプドキュメントの「はじめに」の章を参照してください。

BigCommerce 認証は標準のOAuth フローに基づいています。

Store ID の取得

BigCommerce Store に接続するには、StoreId が必要です。Store Id を確認するには、以下の手順に従ってください。

  1. BigCommerce アカウントにログインします。
  2. ホームページから「Advanced Settings」->「API Accounts」 を選択します。
  3. 「Create API Account」->「Create V2/V3 API Token」をクリックします。
  4. 画面にAPI Path という名前のテキストボックスが表示されます。
  5. テキストボックス内に、次の構造のURL が表示されます：https://api.bigcommerce.com/stores/{Store Id}/v3。
  6. 上記で示したように、Store Id は'stores/' と'/v3' パスパラメータの間にあります。
  7. Store Id を取得したら、「キャンセル」 をクリックするか、まだ持っていない場合はAPI Account の作成に進むことができます。

パーソナルアクセストークンの取得

加えて、自分のデータをテストおよびアクセスするには、個人用トークンを取得する必要があります。個人用トークンを取得する方法は次のとおりです。

  1. BigCommerce アカウントにログインします。
  2. ホームページから「Advanced Settings」->「API Accounts」 を選択します。
  3. 「Create API Account」->「Create V2/V3 API Token」をクリックします。
  4. アカウント名を入力します。
  5. 作成するAPI Account の「OAuth Scopes」を選択します。CData 製品 は"None" とマークされたデータにアクセスできません。また、"read-only" とマークされたデータを変更できません。
  6. 「保存」をクリックします。

BigCommerce への認証

次に、以下を設定してデータに接続できます。
  • StoreId：API Path テキストボックスから取得したStore ID に設定。
  • OAuthAccessToken：生成したトークンに設定。
  • InitiateOAuth：OFF に設定。

BigCommerce のデータ用に MySQL リモーティングサービスを作成

SQL Gateway 概要を参照して、BigCommerce のデータ を仮想 MySQL データベースとして接続を設定してください。クライアントからの MySQL リクエストをリッスンする MySQL リモーティングサービスを設定します。サービスは SQL Gateway UI で設定できます。

リモートアクセスの設定

ODBC Driver とリモーティングサービスがオンプレミスにインストールされている（Google Apps Script からアクセスできない）場合は、リバース SSH トンネリング機能を使用してリモートアクセスを有効にできます。詳細な手順については、Knowledge Base の記事「SQL Gateway SSH トンネリング機能」を参照してください。

Apps Script で BigCommerce のデータに接続

ここまでで、BigCommerce のデータ 用に SQL Gateway を設定しました。あとは Google Apps Script を使用して MySQL リモーティングサービスにアクセスし、Google Sheets でBigCommerce のデータを操作するだけです。

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

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

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

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

2. クラス変数を宣言

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

//replace the variables in this block with real values as needed
var address = 'my.server.address:port';
var user = 'SQL_GATEWAY_USER';
var userPwd = 'SQL_GATEWAY_PASSWORD';
var db = 'CData BigCommerce Sys';

var dbUrl = 'jdbc:mysql://' + address + '/' + db;

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

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

function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var menuItems = [
    {name: 'Write data to a sheet', functionName: 'connectToBigCommerceData'}
  ];
  spreadsheet.addMenu('BigCommerce のデータ', menuItems);
}
新しく追加されたメニューオプション

4. ヘルパー関数を作成

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

/*
 * Finds the first empty row in a spreadsheet by scanning an array of columns
 * @return The row number of the first empty row.
 */
function getFirstEmptyRowByColumnArray(spreadSheet, column) {
  var column = spreadSheet.getRange(column + ":" + column);
  var values = column.getValues(); // get all data in one call
  var ct = 0;
  while ( values[ct] && values[ct][0] != "" ) {
    ct++;
  }
  return (ct+1);
}

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

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

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

シート選択用の入力ボックス

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

テーブル選択用の入力ボックス

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

/*
 * Reads data from a specified BigCommerce 'table' and writes it to the specified sheet.
 *    (If the specified sheet does not exist, it is created.)
 */
function connectToBigCommerceData() {
  var thisWorkbook = SpreadsheetApp.getActive();

  //select a sheet and create it if it does not exist
  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;

  //select a BigCommerce 'table'
  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, userPwd);

  //confirm that var table is a valid table/view
  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();

  //if the sheet is empty, populate the first row with the headers
  var firstEmptyRow = getFirstEmptyRowByColumnArray(resultSheet, "A");
  if (firstEmptyRow == 1) {
    //collect column names
    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;
  }

  //write rows ofBigCommerce のデータto the sheet
  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();
}

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

完全な Google Apps Script

//replace the variables in this block with real values as needed
var address = 'my.server.address:port';
var user = 'SQL_GATEWAY_USER';
var userPwd = 'SQL_GATEWAY_PASSWORD';
var db = 'CData BigCommerce Sys';

var dbUrl = 'jdbc:mysql://' + address + '/' + db;

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

/*
 * Finds the first empty row in a spreadsheet by scanning an array of columns
 * @return The row number of the first empty row.
 */
function getFirstEmptyRowByColumnArray(spreadSheet, column) {
  var column = spreadSheet.getRange(column + ":" + column);
  var values = column.getValues(); // get all data in one call
  var ct = 0;
  while ( values[ct] && values[ct][0] != "" ) {
    ct++;
  }
  return (ct+1);
}

/*
 * Reads data from a specified 'table' and writes it to the specified sheet.
 *    (If the specified sheet does not exist, it is created.)
 */
function connectToBigCommerceData() {
  var thisWorkbook = SpreadsheetApp.getActive();

  //select a sheet and create it if it does not exist
  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;

  //select a BigCommerce 'table'
  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, userPwd);

  //confirm that var table is a valid table/view
  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();

  //if the sheet is empty, populate the first row with the headers
  var firstEmptyRow = getFirstEmptyRowByColumnArray(resultSheet, "A");
  if (firstEmptyRow == 1) {
    //collect column names
    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;
  }

  //write rows ofBigCommerce のデータto the sheet
  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();
}

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

BigCommerce ODBC Driver の無料トライアルをダウンロードしてお試しください：

 ダウンロード

詳細：

BigCommerce Icon BigCommerce ODBC Driver お問い合わせ

BigCommerce ODBC Driver を使って、ODBC 接続をサポートするあらゆるアプリケーション・ツールからBigCommerce にデータ連携。

BigCommerce データにデータベースと同感覚でアクセスして、BigCommerce のCustomers、Products、Orders、Transactions データに使い慣れたODBC インターフェースで双方向連携。

この記事の内容

関連記事