情報科学屋さんを目指す人のメモ

方法・手順・解説を書き残すブログ。私と同じことを繰り返さずに済むように。

【Excel】「WEBSERVICE関数」で外部サービスAPIからデータを手軽に取得する連携方法

Excel (20) Excel 2013 (5) Excel 2016 (11) Excel-FILTERXML (1) Excel-WEBSERVICE (1)

Excel 2013から追加されている「WEBSERVICE」関数を紹介します。

引数で指定したURLにGETリクエストを投げて、データを取得してくれる、だいぶ特殊な関数で、VBA(マクロ)不要、つまり、コードを書かずにAPIを叩いて連携できるお手軽関数です(高度なことは難しいけれど)。

JSONを返すREST API相手には使いにくいのですが、XML形式で返してくれるAPIであれば、同時に導入された「FILTERXML」関数(XPathが使える)と組み合わせて、個別のデータを手軽に取得できます

※普通のテキストファイルやHTMLページでも、WEBSERVICE関数から取得できます。ただし、レスポンスが長すぎるとセルに入らず#VALUEエラーになるので注意してください。また、Excel OnlineとExcel 2016 for Macでは、使えないようです。

Excelと外部サービスを手軽に連携させる関数「WEBSERVICE」

WEBSERVICE関数は、次のように使います。

=WEBSERVICE("http://www.drk7.jp/weather/xml/13.xml")

このままセルの編集を確定すると、Excelは指定のURLにGETリクエスト(User-Agent: Excel/16.0)を飛ばし、取得したデータをセルに表示してくれます(※画像は、「折り返して全体を表示する」を使用)。

FILTERXML関数

これではあまりに扱いにくいのですが、FILTERXML関数を使うと、取得した結果から、XPathを使った抽出を実行できます。試しに、先ほどWEBSERVICE関数で取得したXML(A1)から、天気を取り出してみます(XPath適当すぎ)。

=FILTERXML(A1, "//weather[1]")

この通り、「くもり」という情報が取得できました。これがワークシート関数の記述のみでできるのが手軽です。

※直接WEBSERVICE関数を呼び出すと、そのまま多数のリクエスト発行しかねないので、一度1つのセルでWEBSERVICE関数を使ってデータを取得し、その1つのセルに対して、いろいろなデータを取得するFILTERXMLを複数利用する、という構造にするのが無難かと思います(加えて、セルの自動更新をオフにしたほうがいいかも)。

JSON形式の場合

正直、XMLではなくJSONが使いたい、という人も多いかと思います。

しかしながら、手軽さを支えてくれる、「FILTERJSON」のようなJSONをパースしてくれる便利関数(ワークシート関数)はExcel 2016でも導入されませんでした(Office 365版Excel 2016含めて)。

なので、JSONがよほどシンプルでない限り、自由かつ手軽なデータ利用は難しい状態です。

※かなり決まった形式になっていれば、MID関数とFIND関数の組合せで、切り出せないことはない、はずです(あとは、文字コードの問題に注意)。

クエリエディタを経由する方法

VBA(マクロ)を使わない場合、クエリエディタの解析機能を使うとJSON形式をパースしてくれます。しかし「手軽」とは言えませんし、ここまで来ると初めからクエリ機能を使ってAPIを叩く(*)のとほとんど同じで、せっかくのワークシート関数「WEBSERVICE」の手軽さがいまひとつなことになってしまいます。

(*)「データ」タブの「新しいクエリ」>「その他のデータソースから」>「Web から」を利用すると、読み込むことができます(※「データ」タブ直下の「Webクエリ」は、テーブルをスクレイピングする、的な、HTMLを相手とするものなので、こちらと似ているようで違います)

付録:「セキュリティの警告 Webサービス機能は無効になっています」エラーについて

一度WEBSERVICE関数を使ったブックを開くと、「セキュリティの警告 Webサービス機能は無効になっています」という警告が表示され、いきなりアクセスを飛ばしてしまわないようになっています。

WEBSERVICE関数を利用する際は、警告メッセージのとなりに表示されている「コンテンツの有効化」をクリックしてください。

ひとこと

JSONをパースしてくれるワークシート関数や、正規表現での検索/置換ができるワークシート関数が増えてくれると、「プログラミングはちょっと難しいけど、Excelなら」という人のできることの幅がぐんと広がりそう(ただその場合は、負荷かけ過ぎて怒られるケースの発生に注意したいところ。Excelがそのあたりをどこまでケアしているのかは不明)

参考

サンプル用に利用させていただいたJSON形式、XML形式でレスポンスを返す2つの天気予報APIは、以下の通りです。

その他の参考:

コメント(0)

新しいコメントを投稿