【徹底解説】Google Apps Script とNetSuite SuiteAnalytics のデータを連携する方法 | コード付き
Google Apps Script を使えばGoogle スプレッドシート、Google ドキュメントといったGoogle のサービス内でカスタム機能を作成できます。CData Connect AI を使えば、NetSuite SuiteAnalytics を含むCData がサポートする100以上のすべてのデータソースのSQL Server インターフェースを利用できます。SQL Server プロトコルはGoogle Apps Script のJDBC サービスでネイティブにサポートされているので、Connect AI を活用すればご利用のGoogle サービスからNetSuite SuiteAnalytics のリアルタイムデータにアクセスできます。
この記事では、Connect AI でNetSuite SuiteAnalytics に接続する方法を説明し、Google スプレッドシートでNetSuite SuiteAnalytics のデータを扱うためのサンプルスクリプトを提供します。
Connect AI からNetSuite SuiteAnalytics への接続
CData Connect AI では、直感的なクリック操作ベースのインターフェースを使ってデータソースに接続できます。
- Connect AI にログインし、 Add Connection をクリックします。
- Add Connection パネルから「NetSuite SuiteAnalytics」を選択します。
-
必要な認証プロパティを入力し、NetSuite SuiteAnalytics に接続します。
接続する前に、NetSuite アカウントでSuiteAnalytics Connect を設定してください。Setup > Company > Enable Features > Analytics でSuiteAnalytics Connect 機能を有効にし、ロールにSuiteAnalytics Connect 権限を追加した上で、Set Up SuiteAnalytics Connect > Your Configuration からサービスホストとアカウントID を確認しておきます。
接続には次の必須プロパティを設定します。
- Server：SuiteAnalytics Connect の設定情報のService Host の値。
- AccountId：SuiteAnalytics Connect の設定情報のAccount ID の値。
- RoleId：SuiteAnalytics Connect 権限を付与したログインロールの内部ID。
次に認証方式を選択します。Basic 認証の場合はUser とPassword にNetSuite の認証情報を設定します。トークンベース認証の場合は、統合レコードに対して生成されたOAuthClientId とOAuthClientSecret（コンシューマーキーとシークレット）、およびOAuthAccessToken とOAuthAccessTokenSecret（トークンキーとシークレット）を設定します。
- Create & Test をクリックします。
- 「Add NetSuite SuiteAnalytics Connection」ページの「Permissions」タブに移動し、ユーザーベースのアクセス許可を更新します。
パーソナルアクセストークンの追加
OAuth 認証をサポートしていないサービス、アプリケーション、プラットフォーム、またはフレームワークから接続する場合は、認証に使用するパーソナルアクセストークン（PAT）を作成できます。きめ細かなアクセス管理を行うために、サービスごとに個別のPAT を作成するのがベストプラクティスです。
- Connect AI アプリの右上にあるユーザー名をクリックし、「Settings」をクリックします。
- 「Settings」ページで「Access Token」セクションにスクロールし、 Create PAT をクリックします。
- PAT の名前を入力して Create をクリックします。
- パーソナルアクセストークンは作成時にしか表示されないため、必ずコピーして安全に保存してください。
コネクションの設定が完了したら、Google Apps Script からNetSuite SuiteAnalytics のデータへの接続準備ができました。
Apps Script からNetSuite SuiteAnalytics のデータに接続
ここまでで、Connect AI でのNetSuite SuiteAnalytics のコネクション設定は完了していると思います。ここからは、Google Apps Script からConnect AI に接続して、Google スプレッドシートでNetSuite SuiteAnalytics のデータを扱います。
このセクションでは、スプレッドシートからNetSuite SuiteAnalytics のデータを取得する、スクリプトを呼び出すメニューオプション付きのスクリプトを作成していきます。本記事用に、各部分にコメントをつけたサンプルスクリプトを作成しました。サンプルスクリプトは、記事の最後に記載しています。
1.空のスクリプトを作成
Google スプレッドシート用のスクリプトを作るには、Google スプレッドシートのメニューから拡張機能 Apps Script をクリックします。
2.クラス変数の宣言
スクリプトで作成した関数で利用するためのクラス変数をいくつか作成します。
//replace the variables in this block with real values as needed var address = 'tds.cdata.com:14333'; var user = 'CONNECT_USER'; // [email protected] var userPwd = 'CONNECT_USER_PAT'; var db = 'SuiteAnalytics1'; var dbUrl = 'jdbc:sqlserver://' + address + ';databaseName=' + db;
3.メニューオプションを追加
この関数はGoogle スプレッドシートに、作成した関数を呼び出すためのメニューオプションを追加します。
function onOpen() {
var spreadsheet = SpreadsheetApp.getActive();
var menuItems = [
{name:'Export data to a sheet', functionName: 'connectToSuiteAnalyticsData'}
];
spreadsheet.addMenu('NetSuite SuiteAnalytics のデータ', 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.NetSuite SuiteAnalytics のデータをスプレッドシートに書き込む関数を作成
以下の関数はGoogle Apps Script のJDBC 機能を使ってConnect AI に接続し、NetSuite SuiteAnalytics のデータを書き込み、データをSELECT してスプレッドシートにデータを読み込みます。スクリプトを実行すると、2つの入力ボックスが表示されます。
最初のボックスはユーザーに、データを保持するためのシート名を入力するよう求めます。スプレッドシートが存在しなければ、関数側で作成します。
2つ目のボックスはユーザーに、読み込むNetSuite SuiteAnalytics テーブル名を入力するよう求めます。無効なテーブルを選択すると、エラーメッセージが表示され関数は終了します。
関数はメニューオプションとして使用するよう設計されていますが、スプレッドシートの式として使用するよう拡張することもできます。
/*
* Reads data from a specified NetSuite SuiteAnalytics 'table' and writes it to the specified sheet.
* (If the specified sheet does not exist, it is created.)
*/
function connectToSuiteAnalyticsData() {
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 NetSuite SuiteAnalytics 'table'
var table = Browser.inputBox('Which table would you like to pull data from?',Browser.Buttons.OK_CANCEL);
if (table == 'cancel')
return;
var name = Jdbc.getConnection(dbUrl, {
user: user,
password: userPwd
}
);
//confirm that var table is a valid table/view
var dbMetaData = name.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 = name.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 ofNetSuite SuiteAnalytics のデータ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();
}
関数の処理が完了すると、スプレッドシートにNetSuite SuiteAnalytics のデータが読み込まれ、Google スプレッドシートの表計算、グラフ作成機能をどこでも自在に活用できます。
完全なGoogle Apps Script
//replace the variables in this block with real values as needed var address = 'tds.cdata.com:14333'; var user = 'CONNECT_USER'; // [email protected] var userPwd = 'CONNECT_USER_PAT'; var db = 'SuiteAnalytics1'; var dbUrl = 'jdbc:sqlserver://' + address + ';databaseName=' + db; function onOpen() { var spreadsheet = SpreadsheetApp.getActive(); var menuItems = [ {name:'Write table data to a sheet', functionName: 'connectToSuiteAnalyticsData'} ]; spreadsheet.addMenu('NetSuite SuiteAnalytics のデータ', 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 connectToSuiteAnalyticsData() { 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 NetSuite SuiteAnalytics 'table' var table = Browser.inputBox('Which table would you like to pull data from?',Browser.Buttons.OK_CANCEL); if (table == 'cancel') return; var name = Jdbc.getConnection(dbUrl, { user: user, password: userPwd } ); //confirm that var table is a valid table/view var dbMetaData = name.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 = name.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 ofNetSuite SuiteAnalytics のデータ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(); }