この前の投稿でご紹介したように、Power Query が「データの取得と変換」となって Excel の標準機能となり、様々なデータの取り扱いが可能になりました。(2017年6月現在、Office 365 サブスクリプションの 最新の Excel が機能拡張の対象となります）
データの取得と変換である Power Query は、アドイン単体としての機能追加、さらに Power BI Desktop の登場によって、Power BI Desktop の ETL 機能 (Extract, Transform, Load)として拡張が行われてきました。Power Query は、Excel そして Power BI Desktop のデータの取り込み、変換・加工、ロードを受けもつ ETL 機能として今も進化し続けています。
JSON 形式のデータをプログラミングなしで取り込む
この進化し続ける「データの取得と変換」機能で、すぐにでも使ってほしいのが CSV データの取り込み機能ですが、人によっては JSON 形式データの取り込みのほうを重宝するかもしれません。
というのも、JSON 形式のデータ取り込みは、以前からも Power Query を使ってできていたのですが、現在は空のクエリから詳細エディターを開いて Power Query 関数を手で記述することなく、クエリ エディターのクリック操作のみで取り込みが可能になったからです。
また、JSON形式のデータを表形式に変換してワークシート上に読み込むためには、VBAを使う方法がこれまで多く紹介されていましたが、VBAでプログラムすることなく、JSON形式のデータをワークシートに展開することができるようになりました。
Web から JSON でデータを取り込む
実際のところ、JSON形式のデータによるテキスト ファイルをフォルダーから読み込むよりも、Web 上での検索条件設定の結果で、JSON形式のデータが表示されることが多いと思います。
サンプルとして、IT勉強会の告知・募集でお世話になることが多い connpass さんの API を使ってみたいと思います。
https://connpass.com/
connpass さんは、イベントサーチ API を提供していて、検索クエリの条件に応じた一覧を JSON 形式のデータとして取得することができます。
connpass API リファレンス
Web API や REST API でデータ提供サービスをしています、という場合、上記のような API リファレンスのページが必ずありますので、探してみてください。
たとえば "BI" というキーワードでイベントを検索するための URL は以下のようになります。
https://connpass.com/api/v1/event/?keyword=bi
この URL で、以下のような JSON 形式のデータによるレスポンスがブラウザに表示されます。
このデータを、Excel のワークシートに展開できるように2次元の表形式に「変換」して、私たちが読めるようにデータを加工することが、データの取得と変換のクエリ エディターだけでできます。上述のように VBA などでプログラミングをすること無しに可能です。
シンプルなデータの取り込み手順
JSON データの取り込みには2つの方法があります。ファイルから取り込む方法と、Web から取り込む方法です。ファイルから取り込む方法を使っても URL を指定することで Web からの取り込みが可能ですが、今回は「Web から」を使ってみます。
「Web から」のデータ取得は、これまでの Web クエリよりも高機能になっています。HTML ページの table だけではなく、今回のように JSON にも対応しているのです。
connpass の Web API 利用はサインインする必要がありません。以下のダイアログで "PowerBI" キーワードを含むイベントを検索する URL を入力し、OKをクリックします。(上記 JSON サンプルのキーワード "bi" の検索結果の数が多かったので、キーワードを PowerBI に変更しています。注意してください。）
接続が完了するとクエリ エディター ウィンドウが立ち上がり、以下の画面が表示されます。
注意点は、ここでファイルのアイコンの [connpass.com] をダブルクリックしてはいけません。アイコン上でマウスオーバーすると「開くにはダブルクリックします」というツールチップが表示されますが、ダブルクリックすると、現時点ではテキストファイルとして処理されてしまいます。リボンの [変換] タブの [形式を指定して開く] から [JSON] を選んでください。
Json 形式で開くと以下のデータが表示されます。
results_returned、events、results_start、results_available の意味は、上述の API リファレンスに解説がありますので、詳細については後で参照してほしいのですが、情報から「指定した（PowerBI）キーワードの検索結果の総数は 19 件で、このファイル（データ）に含まれるのは 10 件、検索開始位置は 1 件目からですよ」という意味です。
あらかじめだいたいの件数がわかっている、もしくは取得する件数に上限を付けることができるのであれば、取得件数を 20 件に指定して全件を一気に取得することができます。PowerBI のサンプルは件数が少ないので、以下の検索条件にしてみます。
条件を変更（URLの変更）のため、クエリの設定の [適用したステップ] の [ソース] の横にある歯車マークをクリックします。
ダイアログの URL を変更します。取り込む件数の上限を 20 とするパラメーターの &count=20 を追記した URL です。
[OK] を押すと、results_returned が 19 に更新されます。
今回は例をシンプルにするために、検索結果で全件を取り込むパラメータを追加しました。また、匿名アクセスで利用が可能なので、認証に関する設定はありません。これらへの設定・対応はもちろん可能です。あらためて別の機会にご紹介したいと思います。
今、この状態は、検索したいキーワードを設定した URL をサーバーに送り、その結果を JSON 形式のデータとして Web 経由で 19 件取得しています。
この表示されているリストの events の List のリンクに、JSON 形式で 19件の検索結果の格納されています。
List のリンクをクリックすると、以下のように List リンク内のリストが 19件のデータとして展開されます。
Record のリンクをクリックすると、クリックした1件分だけの内容を確認することができますが、今回はすべての Record（勉強会）の詳細を一気に展開したいので、ここでリボンにある [テーブルへの変換] をクリックして、19件のレコードを含むリストを、テーブル（表）形式に変換します。すでに1件1レコードとして認識されているので、テーブルへの変換のダイアログのオプションはそのままで、[OK] ボタンをクリックします。
リストだったデータがテーブル形式になりました。
ここで Record リンクをクリックすると1件分のみの展開をしますが、テーブル形式に変換したことによる「列名」の Column1 の横にある [展開] ボタンを押すと、Record に含まれるデータをテーブル形式1件分のデータのみなした場合の列名の一覧が表示されます。ここで取り出す列を絞り込むこともできます。今回はすべての列を選択し、かつ、列名が長くなるので、[元の列名をプレフィックスとして使用します] のチェックをはずして、[OK] ボタンをクリックします。
JSON形式の元データを、21列19行のテーブル形式のデータに変換することができました。クエリ エディターの操作のみで1行もコードを書かずにここまでできました。
テーブル形式になったデータを Excel 向けに加工する
今回は Excel の「データの取得と変換」を使って connpass から検索結果を JSON 形式のデータで取得しました。その後、ここまでの処理・操作で、データはテーブル形式になりました。それぞれの列名がどのような意味なのか、connpass の API リファレンスのレスポンス フィールドで確認することが可能です。
ここで、必要な列や行のみを残す、といった加工が可能です。ここからの処理は、JSON だから特別、というものはなく、普通のテーブル形式のデータのフィルター オプションを操作する感覚でできるのは、クエリ エディターを使ったことがあれば理解できるでしょう。まだ慣れていない方は Power Query によるデータの加工や絞り込みについて、もう少しだけ情報収集するといいでしょう。
この connpass のデータや、特にサーバーからデータを取得した際に、Excel ユーザーが一瞬「おや？」と思うのは、日付データの扱いです。この日付データのトピックだけで結構長いお話になってしまうので、ここで詳細は割愛しますが、1つだけ意識してほしいのは、サーバーの日付形式のデータは、そのまま Excel で扱うことができない場合がある、ということです。
上記はデータ変換をせずにテーブル形式に変換したJSONデータをワークシートに読み込み、列名 started_at の1行目のセルの書式を表示したものです。2017-05-20T13:00:00+09:00 は勉強会・イベントの開始日のデータですが、Excel は単なる文字列として認識し、日付として扱っていません。
多くの場合、Excel は日付「らしい」文字列のセルへの入力があると、日付データの「シリアル値」に変換し、表示形式によって人間が日付と認識できるデータに見た目上変換します。残念ながら、2017-05-20T13:00:00+09:00 という ISO-8061 形式の文字列は Excel によって日付データと認識されなかった、となります。このままではシリアル値として扱っていないため、日付関連の関数の利用や演算ができません。
そのため、クエリ エディターで Excel が日付として認識できるように加工します。
データをワークシートに読み込む前の、クエリ エディター上で、対象となる日付のデータの started_at の列データを変換します。
started_at の列名をクリックし列を選択した状態で [変換] タブの [データ型の検出] を使ってもいいですし、この日付データ型は [日付/時刻/タイムゾーン] と呼ばれるものなので、列名横のデータ型のボタンを押して、明示的に選択することで変換可能です。[ABC 123] のアイコンが地球儀と時計のアイコンに変わります。
この変換ステップを行うことで、[日付/時刻] に変換できるようになります。[日付/時刻/タイムゾーン] に一度変換しないで、いきなり [日付/時刻] を選択すると Error になるので注意してください。データ型ボタンで [日付/時刻] を選ぶと、列タイプの変更 ダイアログが表示されるので、[新規手順の追加] を選んでください。[現在のものを置換] はタイムゾーン付きのデータに変換したステップを置き換えてしまうのでエラーになります。
このように日付データを変換して Excel のワークシートに読み込むことで、シリアル値として扱うことができます。
日本語データも特に問題なく扱うことができています。イベント(勉強会)の概要データの description は HTMLタグを含むテキストデータとして取り込まれています。ここからまた何らかの判断をしたい場合は、クエリ エディターの詳細エディター上で M 言語を使ってやってもいいですし、ワークシートに取り込んだ後で、関数や VBA を使ってもいいでしょう。
実際は考慮すべき点がいっぱい
今回は JSON 形式のデータでも、取得と変換のクエリ エディターを使うことで、プログラミングすること無しに Excel ワークシートに取り込むことができることを紹介するのが目的でした。しかし、それ以外のところで考慮すべきことが出てくるのが実際でしょう。
たとえば、取得する件数。サービスによっては上限が決まっていて、それ以上のデータの取得は、今回のような件数の指定のほかに、ページ数指定や、オフセット指定を使うことが推奨されます。
Power Query / 取得と変換のクエリ エディターで、詳細エディターを使ってこれらへの対応が可能です。ページ数やオフセットの「繰り返し」の処理は、URLを組み立てる一連のステップを「関数化」して、ページ数やオフセットを引数として渡す、という方法を使います。
データの取得と変換である Power Query は、アドイン単体としての機能追加、さらに Power BI Desktop の登場によって、Power BI Desktop の ETL 機能 (Extract, Transform, Load)として拡張が行われてきました。Power Query は、Excel そして Power BI Desktop のデータの取り込み、変換・加工、ロードを受けもつ ETL 機能として今も進化し続けています。
JSON 形式のデータをプログラミングなしで取り込む
この進化し続ける「データの取得と変換」機能で、すぐにでも使ってほしいのが CSV データの取り込み機能ですが、人によっては JSON 形式データの取り込みのほうを重宝するかもしれません。
というのも、JSON 形式のデータ取り込みは、以前からも Power Query を使ってできていたのですが、現在は空のクエリから詳細エディターを開いて Power Query 関数を手で記述することなく、クエリ エディターのクリック操作のみで取り込みが可能になったからです。
また、JSON形式のデータを表形式に変換してワークシート上に読み込むためには、VBAを使う方法がこれまで多く紹介されていましたが、VBAでプログラムすることなく、JSON形式のデータをワークシートに展開することができるようになりました。
Web から JSON でデータを取り込む
実際のところ、JSON形式のデータによるテキスト ファイルをフォルダーから読み込むよりも、Web 上での検索条件設定の結果で、JSON形式のデータが表示されることが多いと思います。
サンプルとして、IT勉強会の告知・募集でお世話になることが多い connpass さんの API を使ってみたいと思います。
https://connpass.com/
connpass さんは、イベントサーチ API を提供していて、検索クエリの条件に応じた一覧を JSON 形式のデータとして取得することができます。
connpass API リファレンス
Web API や REST API でデータ提供サービスをしています、という場合、上記のような API リファレンスのページが必ずありますので、探してみてください。
たとえば "BI" というキーワードでイベントを検索するための URL は以下のようになります。
https://connpass.com/api/v1/event/?keyword=bi
この URL で、以下のような JSON 形式のデータによるレスポンスがブラウザに表示されます。
このデータを、Excel のワークシートに展開できるように2次元の表形式に「変換」して、私たちが読めるようにデータを加工することが、データの取得と変換のクエリ エディターだけでできます。上述のように VBA などでプログラミングをすること無しに可能です。
シンプルなデータの取り込み手順
JSON データの取り込みには2つの方法があります。ファイルから取り込む方法と、Web から取り込む方法です。ファイルから取り込む方法を使っても URL を指定することで Web からの取り込みが可能ですが、今回は「Web から」を使ってみます。
「Web から」のデータ取得は、これまでの Web クエリよりも高機能になっています。HTML ページの table だけではなく、今回のように JSON にも対応しているのです。
connpass の Web API 利用はサインインする必要がありません。以下のダイアログで "PowerBI" キーワードを含むイベントを検索する URL を入力し、OKをクリックします。(上記 JSON サンプルのキーワード "bi" の検索結果の数が多かったので、キーワードを PowerBI に変更しています。注意してください。）
接続が完了するとクエリ エディター ウィンドウが立ち上がり、以下の画面が表示されます。
注意点は、ここでファイルのアイコンの [connpass.com] をダブルクリックしてはいけません。アイコン上でマウスオーバーすると「開くにはダブルクリックします」というツールチップが表示されますが、ダブルクリックすると、現時点ではテキストファイルとして処理されてしまいます。リボンの [変換] タブの [形式を指定して開く] から [JSON] を選んでください。
Json 形式で開くと以下のデータが表示されます。
results_returned、events、results_start、results_available の意味は、上述の API リファレンスに解説がありますので、詳細については後で参照してほしいのですが、情報から「指定した（PowerBI）キーワードの検索結果の総数は 19 件で、このファイル（データ）に含まれるのは 10 件、検索開始位置は 1 件目からですよ」という意味です。
あらかじめだいたいの件数がわかっている、もしくは取得する件数に上限を付けることができるのであれば、取得件数を 20 件に指定して全件を一気に取得することができます。PowerBI のサンプルは件数が少ないので、以下の検索条件にしてみます。
条件を変更（URLの変更）のため、クエリの設定の [適用したステップ] の [ソース] の横にある歯車マークをクリックします。
ダイアログの URL を変更します。取り込む件数の上限を 20 とするパラメーターの &count=20 を追記した URL です。
[OK] を押すと、results_returned が 19 に更新されます。
今回は例をシンプルにするために、検索結果で全件を取り込むパラメータを追加しました。また、匿名アクセスで利用が可能なので、認証に関する設定はありません。これらへの設定・対応はもちろん可能です。あらためて別の機会にご紹介したいと思います。
今、この状態は、検索したいキーワードを設定した URL をサーバーに送り、その結果を JSON 形式のデータとして Web 経由で 19 件取得しています。
この表示されているリストの events の List のリンクに、JSON 形式で 19件の検索結果の格納されています。
List のリンクをクリックすると、以下のように List リンク内のリストが 19件のデータとして展開されます。
Record のリンクをクリックすると、クリックした1件分だけの内容を確認することができますが、今回はすべての Record（勉強会）の詳細を一気に展開したいので、ここでリボンにある [テーブルへの変換] をクリックして、19件のレコードを含むリストを、テーブル（表）形式に変換します。すでに1件1レコードとして認識されているので、テーブルへの変換のダイアログのオプションはそのままで、[OK] ボタンをクリックします。
リストだったデータがテーブル形式になりました。
ここで Record リンクをクリックすると1件分のみの展開をしますが、テーブル形式に変換したことによる「列名」の Column1 の横にある [展開] ボタンを押すと、Record に含まれるデータをテーブル形式1件分のデータのみなした場合の列名の一覧が表示されます。ここで取り出す列を絞り込むこともできます。今回はすべての列を選択し、かつ、列名が長くなるので、[元の列名をプレフィックスとして使用します] のチェックをはずして、[OK] ボタンをクリックします。
JSON形式の元データを、21列19行のテーブル形式のデータに変換することができました。クエリ エディターの操作のみで1行もコードを書かずにここまでできました。
テーブル形式になったデータを Excel 向けに加工する
今回は Excel の「データの取得と変換」を使って connpass から検索結果を JSON 形式のデータで取得しました。その後、ここまでの処理・操作で、データはテーブル形式になりました。それぞれの列名がどのような意味なのか、connpass の API リファレンスのレスポンス フィールドで確認することが可能です。
ここで、必要な列や行のみを残す、といった加工が可能です。ここからの処理は、JSON だから特別、というものはなく、普通のテーブル形式のデータのフィルター オプションを操作する感覚でできるのは、クエリ エディターを使ったことがあれば理解できるでしょう。まだ慣れていない方は Power Query によるデータの加工や絞り込みについて、もう少しだけ情報収集するといいでしょう。
この connpass のデータや、特にサーバーからデータを取得した際に、Excel ユーザーが一瞬「おや？」と思うのは、日付データの扱いです。この日付データのトピックだけで結構長いお話になってしまうので、ここで詳細は割愛しますが、1つだけ意識してほしいのは、サーバーの日付形式のデータは、そのまま Excel で扱うことができない場合がある、ということです。
上記はデータ変換をせずにテーブル形式に変換したJSONデータをワークシートに読み込み、列名 started_at の1行目のセルの書式を表示したものです。2017-05-20T13:00:00+09:00 は勉強会・イベントの開始日のデータですが、Excel は単なる文字列として認識し、日付として扱っていません。
多くの場合、Excel は日付「らしい」文字列のセルへの入力があると、日付データの「シリアル値」に変換し、表示形式によって人間が日付と認識できるデータに見た目上変換します。残念ながら、2017-05-20T13:00:00+09:00 という ISO-8061 形式の文字列は Excel によって日付データと認識されなかった、となります。このままではシリアル値として扱っていないため、日付関連の関数の利用や演算ができません。
そのため、クエリ エディターで Excel が日付として認識できるように加工します。
データをワークシートに読み込む前の、クエリ エディター上で、対象となる日付のデータの started_at の列データを変換します。
started_at の列名をクリックし列を選択した状態で [変換] タブの [データ型の検出] を使ってもいいですし、この日付データ型は [日付/時刻/タイムゾーン] と呼ばれるものなので、列名横のデータ型のボタンを押して、明示的に選択することで変換可能です。[ABC 123] のアイコンが地球儀と時計のアイコンに変わります。
この変換ステップを行うことで、[日付/時刻] に変換できるようになります。[日付/時刻/タイムゾーン] に一度変換しないで、いきなり [日付/時刻] を選択すると Error になるので注意してください。データ型ボタンで [日付/時刻] を選ぶと、列タイプの変更 ダイアログが表示されるので、[新規手順の追加] を選んでください。[現在のものを置換] はタイムゾーン付きのデータに変換したステップを置き換えてしまうのでエラーになります。
このように日付データを変換して Excel のワークシートに読み込むことで、シリアル値として扱うことができます。
日本語データも特に問題なく扱うことができています。イベント(勉強会)の概要データの description は HTMLタグを含むテキストデータとして取り込まれています。ここからまた何らかの判断をしたい場合は、クエリ エディターの詳細エディター上で M 言語を使ってやってもいいですし、ワークシートに取り込んだ後で、関数や VBA を使ってもいいでしょう。
実際は考慮すべき点がいっぱい
今回は JSON 形式のデータでも、取得と変換のクエリ エディターを使うことで、プログラミングすること無しに Excel ワークシートに取り込むことができることを紹介するのが目的でした。しかし、それ以外のところで考慮すべきことが出てくるのが実際でしょう。
たとえば、取得する件数。サービスによっては上限が決まっていて、それ以上のデータの取得は、今回のような件数の指定のほかに、ページ数指定や、オフセット指定を使うことが推奨されます。
Power Query / 取得と変換のクエリ エディターで、詳細エディターを使ってこれらへの対応が可能です。ページ数やオフセットの「繰り返し」の処理は、URLを組み立てる一連のステップを「関数化」して、ページ数やオフセットを引数として渡す、という方法を使います。
この件数の上限への対応は結構「頭の体操」的なアイディアが必要になります。場合によっては CData さんの ODBC ドライバーを使うと幸せになれることがあります。
サイボウズの kintone の API も1回のリクエストあたりのデータ取得件数の上限がありますが、CData さんのドライバーを使うことで、その上限を気にせずにデータの取得が可能になります。
CData ODBC ドライバー一覧
https://www.cdata.com/jp/download/?f=odbc
Power Query/Excel 取得と変換/Power BI Desktop も標準機能としてさまざまなデータソースに対応していますが、CDataさんのような専業メーカーさんの ODBC ドライバーを試してみると、意外な発見や、解決策を見つけることができるかもしれません。
また、匿名アクセスではなく、ユーザー名＋パスワード、アプリケーション登録による Web キーの利用など、サービスによって認証の方法はさまざまです。
connpass と同じくらい IT 勉強会の告知・募集・管理ツールで人気がある Doorkeeper の場合は、検索の URL の送信と一緒に Header データに認証情報を入れる必要があります。connpass は「Web から」の「基本」を使いましたが、Doorkeeper は「詳細設定」を使って、Header に認証情報をセットして、検索リクエストを送信する必要があります。Doorkeeper の API の解説には、Power BI Desktop や Excel クエリ エディターの具体的な設定方法や手順はないので、苦労するポイントでしょう。
 |
| 登録して、Public API Access Token を取得 |
 |
| HTTP要求ヘッダーに Bearer を使ってトークンを追加 |
長くなりましたが、Excel のデータの取得と変換という新しい外部データ取り込みの仕組みを使うことで様々なデータソースへ接続し、様々なデータ形式のデータを扱うことができます。まだまだ進化中ですが、その方向はなるべくコーディングさせない方向で、JSON形式のデータであっても、コーディングなしでテーブル形式に展開して、ワークシートに取り込むことが可能です。
食わず嫌いせずに、この新しい機能をぜひ使ってみてください。
[追記] 最後まで読んでいただいてありがとうございます。取得と変換の機能の一つの「ピボットの解除」もJSON処理同様これまでVBAでなければできないと言われていた処理です。こちらもおすすめ機能なので是非使ってみてください。
https://road2cloudoffice.blogspot.jp/2018/03/blog-post.html
[追記] REST APIで取れないデータをWebページスクレイピングでとる方法を追加しました。
https://road2cloudoffice.blogspot.com/2020/05/excel-web.html
