いよいよ 2017年も終わりですね。今回は、過去にボツにしようとしたネタを見直して公開します。
Excel 2016 には「取得と変換」という名前で新しいデータアクセス機能が装備されています。今回はこの機能を使用して kintone の REST API にアクセスしてみます。
（2018.1.22追記: 500件以上の kintone レコードを取得できる方法を別記事に記載しましたので、あわせてご覧ください）
「データ」メニューの「新しいクエリ」- 「その他のデータ ソースから」- 「Web から」を選びます。
URLを指定する画面が表示されますが、「詳細設定」のラジオボタンをクリックします。
HTTPヘッダーを指定できる画面が現れます。HTTPヘッダーを指定できるというのだから、kintone アプリの APIトークンを指定してみましょう。
「OK」をクリックすると、以下のように表示されます。
レコードの一括取得API を使用されたことのある方ならピンと来るかもしれません。records の右に表示されている List というリンクをクリックします。
なんだかよく分からない表示になりましたが、ヘッダ部の「一覧」を右クリックして「テーブルへの変換」を選択します。
「OK」をクリックすると、下記の表示になります。
ヘッダが「Column1」に変わり、その右に謎のアイコンが表示されるのでクリックします。
「展開する列」を指定する画面が出てきました。
アプリのフィールドコードが表示されています。取り込みたい列を選んでチェックボックスをチェックし「OK」をクリックします。
まだダメな感じですね。。。
先ほどクリックしたのと同じ、各列ヘッダーの右アイコンをクリックすると、以下のようになります。
全ての列に対して「type」のチェックを外し「value」だけにすると、、
やっと目的のデータが表示されました。
左上の「閉じて読み込む ▼」をクリックします。
Excel に 100行のレコードを返すことができました。
この機能はもともと Power BI に搭載されていた Power Query という機能が Excel でも使えるようになったものです。
クエリー エディター画面の「詳細エディター」ボタンをクリックすると、以下のように Power Query Formula Language 、通称 M言語 が表示されます。
Power Query Formula Language を記述すれば、 Power BI でも全く同じようにデータを取得できるわけです。早速以下の内容を Power BI で指定してみましょう。
let
ソース = Json.Document(Web.Contents(“https://{subdomain}.cybozu.com/k/v1/records.json?app={appId}”, [Headers=[#”X-Cybozu-API-Token”=”{apiToken}”]])),
records1 = ソース[records],
テーブルに変換済み = Table.FromList(records1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#”展開された Column1″ = Table.ExpandRecordColumn(テーブルに変換済み, “Column1”, {“郵便番号”, “市区町村名”, “町域名”, “都道府県名”}, {“Column1.郵便番号”, “Column1.市区町村名”, “Column1.町域名”, “Column1.都道府県名”}),
並べ替えられた列 = Table.ReorderColumns(#”展開された Column1″,{“Column1.郵便番号”, “Column1.都道府県名”, “Column1.市区町村名”, “Column1.町域名”}),
#”展開された Column1.郵便番号” = Table.ExpandRecordColumn(並べ替えられた列, “Column1.郵便番号”, {“value”}, {“Column1.郵便番号.value”}),
#”展開された Column1.都道府県名” = Table.ExpandRecordColumn(#”展開された Column1.郵便番号”, “Column1.都道府県名”, {“value”}, {“Column1.都道府県名.value”}),
#”展開された Column1.市区町村名” = Table.ExpandRecordColumn(#”展開された Column1.都道府県名”, “Column1.市区町村名”, {“value”}, {“Column1.市区町村名.value”}),
#”展開された Column1.町域名” = Table.ExpandRecordColumn(#”展開された Column1.市区町村名”, “Column1.町域名”, {“value”}, {“Column1.町域名.value”})
in
#”展開された Column1.町域名”
Power BI Desktop を起動し、「データを取得」-「Web」から Excelで指定したのと同じように「詳細設定」します。
Excelの場合と同様の表示になるので「詳細エディター」ボタンをクリックすれば Power Query Formula Language (M言語) を入力することができ、Excelで設定したのと同じデータを取得できます。
Power BI ですから 標準で ゼンリンの地図も使えますし、ジオロケーションも出来ますし、カスタム ビジュアルを使ったり色々なことが出来ます。Windows を使ってて良かったあ・・ という感じではないでしょうか。
と、ここまで書いておいてなんですが、今回ご紹介した方法には一点大きな問題があります。
kintone のレコードを 100件 しか取得できないということです。
（2018.1.22追記: 別の方法を使って 500件以上の kintone レコードを取得できる方法を記載しましたので、あわせてご覧ください）
レコードの一括取得API をご存知の方であればお分かりかと思いますが、app={appId} に続けて &query=limit%20500 と書けば 500件まで取得できます。でもせっかくの BIツールで 500行までのレコードしか扱えないというのは残念過ぎますよね。。
Google Data Studio をご紹介した際の例 のように カスタムデータコネクタを作成する、、というのはちょっと困難なようなので、Google Apps Script (GAS) や AWS API Gateway & Lambda Function などを使用して kintone アプリのレコードを全件取得する Web API を作成し利用するといった方法も考えられます。
いかがだったでしょうか。
2018年も kintone を活用していきたいものですね。良いお年をお迎えください。
投稿者プロフィール
