Power QueryでJSON APIを使ってWebサービスのデータを取得する

外部システムやWebサービスから最新データを取得したいとき、JSON形式のAPIは非常に便利です。

ExcelのPower Queryを使えば、プログラミングを知らなくてもAPIデータを直接取得し、自動更新まで可能になります。

本記事では、JSON APIを使ってWebサービスのデータを取得する方法を解説します。

JSON APIとは
サンプルデータ
実際の手順
よくあるエラーと対処法
1. 接続できません

JSON APIとは

JSON（JavaScript Object Notation）は、Webでデータをやり取りするための軽量なフォーマットです。
「{ }」や「[ ]」を使って階層的にデータを表現するため、構造が分かりやすく、人にも機械にも扱いやすい形式です。

例：

{
  "user": {
    "name": "Taro",
    "age": 32
  }
}

このような構造をPower Queryは自動で認識し、テーブルに変換することができます。

JSON APIとは、Web上のサーバーからデータを取得するための仕組みで、多くのクラウドサービスが対応しています。

例えば次のサービスがあります。

YouTube Data API
Youtubeの動画リスト、再生回数、評価数、コメント数などが取得できます。
DeepL API
DeepLの機械翻訳結果を取得できます。
Rakuten API
楽天市場の商品情報など、楽天のサービスに関する情報が取得できます。

Power Queryを使えば、これらのAPIにアクセスしてJSON形式のデータを自動で取得し、Excel上でテーブルとして扱うことができます。

サンプルデータ

サンプルデータとしてJSON Placeholderを使います。テスト用のフェイクデータを提供しています。ユーザ一覧やTodo一覧などのフェイクデータが取得できます。

通常は、この手のWebサービスからデータを取得しようとするとユーザー登録してAPIキーを発行する必要があります。JSON Placeholderは登録等の必要ありませんので、Power QueryでJSON APIを試すだけであれば十分です。

試しに次のURLを開いてください。
https://jsonplaceholder.typicode.com/users/1

次のJSON形式のページが表示されると思います。
Power Queryでデータ取得・加工する方法をサンプルデータを使って解説します。

{
  "id": 1,
  "name": "Leanne Graham",
  "username": "Bret",
  "email": "Sincere@april.biz",
  "address": {
    "street": "Kulas Light",
    "suite": "Apt. 556",
    "city": "Gwenborough",
    "zipcode": "92998-3874",
    "geo": {
      "lat": "-37.3159",
      "lng": "81.1496"
    }
  },
  "phone": "1-770-736-8031 x56442",
  "website": "hildegard.org",
  "company": {
    "name": "Romaguera-Crona",
    "catchPhrase": "Multi-layered client-server neural-net",
    "bs": "harness real-time e-markets"
  }
}

実際の手順

JSON Placeholderからユーザー一覧のダミーデータを取得します。取得するには次のURLを使用します。

https://jsonplaceholder.typicode.com/users

手順①：データを取得する（Webからを選択）

「データ」タブー「Webから」をクリックします。

URLに「https://jsonplaceholder.typicode.com/users」を入力し、OKをクリックします。

Power QueryがJSONを読み込むと、リスト形式の結果が表示されます。

「Record」の中を見ると、JSONの構造が見れます。データとして扱えるようにテーブル形式に変換します。

手順②：JSONをテーブル形式に変換する

「変換」タブー「テーブルへ変換」をクリックします。

そのままOKをクリックします。

見た目はあまり変わりませんが、Column1という列があるテーブル形式に変換されています。1列の中にJSONの構造がすべて含まれているため、これらを展開していきます。

「Column1」列の⇔ボタンをクリックし、「元の列名をプレフィックスとして使用します」のチェックを外し、OKをクリックします。

「元の列名をプレフィックスとして使用します」のチェックがあると、展開した列にColumn1.id、Column1.nameなど、列名の全てにColumn1が付与されるため、この手順では不要です。

列が展開されました。

JSON構造の赤文字部分が展開されています。addressやcompanyはさらに階層があるため、「Record」と表示されています。

{
  "id": 1,
  "name": "Leanne Graham",
  "username": "Bret",
  "email": "Sincere@april.biz",
  "address": {
    "street": "Kulas Light",
    "suite": "Apt. 556",
    "city": "Gwenborough",
    "zipcode": "92998-3874",
    "geo": {
      "lat": "-37.3159",
      "lng": "81.1496"
    }
  },
  "phone": "1-770-736-8031 x56442",
  "website": "hildegard.org",
  "company": {
    "name": "Romaguera-Crona",
    "catchPhrase": "Multi-layered client-server neural-net",
    "bs": "harness real-time e-markets"
  }
}

手順③：JSONの構造をさらに展開する

addressやcompanyはさらに階層があるため、必要に応じて展開します。

「adress」列の⇔ボタンをクリックし、OKをクリックします。

列名の頭に「address.」とつくので、元の列名をプレフィックスとして使用した方が列名が分かりやすくなります。

address列が展開されました。

JSON構造の青文字部分が展開されています。必要に応じてcompanyも展開しましょう。

{
  "id": 1,
  "name": "Leanne Graham",
  "username": "Bret",
  "email": "Sincere@april.biz",
  "address": {
    "street": "Kulas Light",
    "suite": "Apt. 556",
    "city": "Gwenborough",
    "zipcode": "92998-3874",
    "geo": {
      "lat": "-37.3159",
      "lng": "81.1496"
    }
  },
  "phone": "1-770-736-8031 x56442",
  "website": "hildegard.org",
  "company": {
    "name": "Romaguera-Crona",
    "catchPhrase": "Multi-layered client-server neural-net",
    "bs": "harness real-time e-markets"
  }
}