こんにちは。CData Software Japan リードエンジニアの杉本です。
今回は CData Excel Add-in のあまり目立たないながらも強力な機能、CData Excel Formulas を紹介したいと思います。
CData Excel Formulas とは？
CData Driver で実行できる各種SQLクエリをExcel 関数のように呼び出すことができる機能です。
提供されている関数は全部で4種類。データを取得するクエリから、INSRET/UPDATE/DELETEおよびストアドプロシージャの実行まで、かなり柔軟に処理が行えます。
| 関数名 | 概要 | 備考 |
|---|---|---|
| CDATAQUERY | SELECT/INSERT/UPDATE/DLEETEもしくはストアドプロシージャを実行します。 | |
| CDATAINSERT | セルの値を元にINSERTを実行します。 | |
| CDATAUPDATE | セルの値を元にUPDATEを実行します。 | |
| CDATADELETE | セルの値を元にDELETEを実行します。 |
この機能のポイントはなんと言っても「Excel 関数であるがゆえに、セルの値が変わった瞬間に処理が実行される」という点でしょう。
これによりクラウドサービスのデータをセルの値を元に、動的に取得したり、更新したりすることができるExcel シートを簡単に作成することができます。
今回はこの4種類の機能の中から、データ取得：CDATAQUERYに絞って、使い方を簡単に解説したいと思います。
利用する CData Excel Add-in
今回はCData Excel Add-in for Salesforce を利用しました。
インストール完了後、予め接続情報を設定しておきます。
CDATAQUERY の使い方
今回は以下のようなSalesforceのAccount情報を検索して一覧取得する機能を作りたいと思います。
「A2」セルは検索ボックス扱いのセルにします。この値を元にAccountのNameを部分一致検索します。
A6セルにはCDATAQUERY 関数を指定します。
今回指定した関数は以下のようなものです。
=CDATAQUERY("SELECT Id, Name FROM Account WHERE Name like '%" & A2 & "%'","Salesforce.Connection1")
Salesforceの値を取得するSELECT文を1つ目のパラメータ、接続情報を2つ目のパラメータに指定しました。
SELECT文では、条件式として「WHERE Name Like '%HOGEHOGE%'」というようなNameの部分一致検索をかけます。
そして、このHOGEHOGE部分に対して「A2」のセルの値を指定するように記述しています。
このように指定することで、以下のようにA7:B7以降のセルに結果が表示されるという仕組みです。
ちなみに第3引数のParameters を利用することで、以下のような指定も可能です。
セルのヘッダーに当たる部分がパラメータ名となり、条件式を指定できます。
=CDATAQUERY("SELECT Id, Name FROM Account WHERE Name = @Name","Salesforce.Connection1",A1:A2)
第4引数のResultLocationでは結果を表示する先頭セルを指定できます。ちなみにここは文字列指定になるので、ダブルコーテーションをつけるのを忘れないようにしましょう。
例えば表示結果を「A8」のセルにすると
=CDATAQUERY("SELECT Id, Name FROM Account WHERE Name = @Name","Salesforce.Connection1",A1:A2,"A8")
以下のような結果になります。想定したデザインよりも1行ずれているのがわかりますね。
おわりに
このように、CData Excel Formulas を使うことで、かなり柔軟かつ動的なデータ取得が行なえます。
帳票系をExcel で作っていたりすると、こういった機能は便利そうですね。
今回はSalesfroceで紹介しましたが、KintoneやBacklogなど他のExcel Add-inでも同様に利用できます。
もし使い方・利用方法でわからない点があれば、テクニカルサポートまで問い合わせしてみてください。
