JSONデータをExcelに取り込んでテーブル形式に変換する方法
何らかのシステムの情報を業務で扱う中で、構造化されたJSONデータをExcelのテーブルで管理したい時があります。
例えば、APIやシステムから出力されたJSONデータを、Excelで一覧管理したい場合や、各項目（例：氏名、住所、金額など）を列にして、行ごとにデータを見たい時、また数値データをグラフ化・ピボットテーブルで集計したい時など。
データを「一覧表」のように、見やすく整理したい時には、Excelのテーブル構造での管理が便利だったりします。
JSONデータをExcelに取り込むには、Power Query 機能を使用します。
Excelに標準搭載されている Power Query を使えば、JSONをテーブル形式に変換することができます。
JSON（JavaScript Object Notation）データは、下記のように階層化されたリスト構造を持つことが多いです。
階層化されたJSONの構造（サンプル）
{
"id": 1,
"name": "佐藤",
"contact": {
"email": "sato@example.com",
"phone": "090-1234-5678"
},
"skills": [
{ "name": "HTML", "level": "advanced" },
{ "name": "CSS", "level": "intermediate" }
]
}
JSONは主に2つのデータ構造を持ちます。
- オブジェクト（Object）
- { キー: 値 } の組み合わせ（辞書型）
- 配列（Array）
- [ 値1, 値2, 値3 ] のリスト構造
これらを入れ子（ネスト）して使うことで、階層化された構造になります。
もう少し複雑な構造となることもありますが、このような階層化されたデータは、Excelの2次元のテーブル（行と列）でフラット化させることで、人にとって見やすく、そして管理しやすくなります。
ここでは、JSONデータをExcelに取り込んでテーブルに変換する方法についてご紹介します。
JSONデータをExcelのテーブルに変換する
JSONデータをExcelのテーブルに変換する方法として、Windows版とmacOS版のExcelでは、少し操作に違いがありますので、各OSでの操作方法・流れをご紹介します。
サンプルとして、Google Chatの履歴をエクスポートしたJSONデータを例に見ていきます。ビジネスシーンでもよく利用されるGoogle Chatの履歴は、JSONデータでダウンロードされます。
ちなみに、Google Chatの履歴をエクスポートの方法は、下記の記事でご紹介しています。
それでは、Excelのテーブル形式への変換方法を見ていきましょう。
WindowsのExcelの場合
「データ」タブにて、「データの取得」から「ファイルから」と進み、「JSONから」を選択します。
そして、コンピュータ内にあるJSONファイルを選択してインストールします。
Power Query エディターが立ち上がり、こちらでJSONデータの展開を設定していきます。
リスト構造となっているJSONデータを、テーブル構造に変換します。
変換タブにて、「テーブルへの変換」を選択します。
変換が完了すると、Name と Value のようなカテゴリのテーブル構造となります。
また、クエリの設定にある「適用したステップ」の項目には、「テーブルに変換済み」のステップが追加されます。
次に、リスト構造の値を展開していきます。
Value の項目名の右にある、左右に開く矢印のアイコンから、「新しい行に展開する」を選択します。そうすると、リストの情報分のデータが展開されます。
今回のサンプルでは、messages のプロパティに対する Record が展開されました。
また、クエリの設定にある「適用したステップ」の項目には、「展開された Value」のステップが追加されます。
さらに、Record の情報を展開していきます。
Record が表示されたところで、Value の項目名の右にある、左右に開く矢印のアイコンを選択すると、Record 内にあるデータの項目が表示されます。
すべての項目を選択し、「OK」ボタンを選択します。
これで、階層化されたリスト構造のJSONデータを、テーブルとしてすべて展開することができました。
クエリの設定にある「適用したステップ」の項目には、「展開された Value1」として新たなステップが追加されたのが確認できます。
テーブルへと展開した後は、「ホーム」タブにて「閉じて読み込む」を選択することで、新しいシートにJSONデータをテーブルに変換（フラット化）したデータが作成されます。
ちなみに、クエリと接続の表示を閉じても、あとで「データ」タブから「クエリと接続」を選択することで再度表示することができます。
JSONデータの情報を変更した場合は、「データ」タブの「すべて更新」を選択することで、テーブルのデータも更新できます。
ただ、読み込んでいるJSONデータは、データを取得したディレクトリから移動させると、更新した際に読み込めなくなるので注意しましょう。
MacのExcelの場合
macOSもWindows版のExcelでの操作と流れは同じですが、少し表示が違う部分もあるので、その違いも含めて操作方法をご紹介します。
「データ」タブにて、「データ ファイル指定」から「データ ファイル指定 (Power Query)」を選択します。
次に、データ ソースの選択の画面にてJSONを選択します。
そして、データ ソースへの接続の画面にて、接続指定の項目の「参照」ボタンから、コンピュータ内のJSONファイルを選びます。
コンピュータ内のJSONファイルを選択し、「データ取り出し」ボタンを選択して接続指定の参照ファイルとします。
接続指定のファイルを設定しましたら、「次へ」ボタンを選択します。
Power Query エディターが立ち上がります。
ここから、JSONデータをテーブルへと展開していきます。
「レコード ツール」のタブにて、「テーブル表示」を選択します。
変換が完了すると、Name と Value のようなカテゴリのテーブル構造となります。
クエリの設定にある「適用されたステップ」の項目には、「テーブルに変換」のステップが追加されます。
次に、リスト構造の値を展開します。
Value の項目名の右にある、左右に開く矢印のアイコンを選択し、リストの情報分のデータを展開します。
サンプルでは、messages のプロパティに対する Record が展開されました。
また、クエリの設定にある「適用されたステップ」の項目には、「Value の展開」のステップが追加されます。
さらにリスト構造の情報を展開していきます。
Record が表示されたところで、Value の項目名の右にある、左右に開く矢印のアイコンを選択すると、Record 内にあるデータの項目が表示されます。
すべての項目を選択し、「OK」ボタンを選択します。
これで、階層化されたリスト構造のJSONデータを、テーブルとしてすべて展開することができます。
クエリの設定にある「適用されたステップ」の項目には、「Value の展開1」として新たなステップが追加されたのが確認できます。
テーブルへと展開した後は、「ホーム」タブにて「閉じて読み込む」を選択することで、新しいシートにJSONデータをテーブルに変換（フラット化）したデータが作成されます。
また、再度 Power Query エディター を立ち上げる際は、「データ」タブの「データ ファイル指定」から、「Power Query エディターの起動」を選択することで立ち上げることができます。
最後に
JSONデータが定期的に更新されるように管理しているのであれば、Power Queryで自動更新設定しておくと便利です。
クエリと接続の項目にある「プロパティ」を選択してクエリプロパティに進み、「ファイルを開くときにデータを更新する」にチェックを入れると、Excelファイルを開いたときにクエリを更新することができます。
他にも、「定期的に更新する」のチェックで更新頻度（分数）を設定することもできます。ただ、短い時間で更新するとPCの負荷が大きくなるため、あまりおすすめしません。
特に別のプログラムが関与していないのであれば、ファイルを開いたときに更新すればでいいでしょう。
システムからエクスポートしてきたJSONデータを、Excelで閲覧または管理していきたい場合は、Power Query機能を使ってデータをテーブルに変換してあげましょう。
ぜひ参考にしてください。
