• Excel vs JSON

JSON（ジェイソン。JavaScript Object Notation）は、テキストベースのデータ表現方法の1つ。クラウドサービスなどで使われることが多いJavaScriptで、オブジェクトのシリアライズなどに使われるデータ形式だ。プログラム間のデータの受け渡し以外にも、ファイルへの保存形式などに使われることが増えてきた。簡易で柔軟性があるためJSONに対応する言語実装も少なくない。

表計算ソフトウェアであるExcelもJSONの読み込みに対応している。しかし、表計算であるExcelの宿命として、データは表構造である必要があり、自由な構造を表現できるJSONとは相性が悪い。「ExcelでJSON読めるじゃないですか」とやってみるとたいていエラーになりガッカリする。

ExcelのJSON対応は、純粋なJSONに限られ、データ構造が均質な配列になっていないと、ちゃんと読み込めないからだ。しかし、Excelにデータを読み込むことができれば、あとの処理がラクなので、できれば、なんとかしたい。なので、できるだけ簡単にJSONファイルを前処理してExcelにも読み込めるようにする方法を考える。

サンプルデータとして、Windows Terminalが出力するJSON設定ファイルから、キー割り当てに関するデータを取り出して表にすることにしよう。これは、Windows Terminalの記事作成のために行った作業が元になっている。Windows TerminalのJSONファイルの仕様などについては以下の記事を参考にされたい。

・Windows Terminalのキーボードカスタマイズ 導入編
https://news.mynavi.jp/article/20211025-2168960/

この記事に掲載されている表は、Excelで作成したが、これは、JSONの設定ファイル（default.jsonおよびsettings.json）を処理したものだ。これらのJSONは、構造が複雑で、キー割り当てだけでも70弱ほどあり、いちいち手で編集などしていられない。

JSONファイルの前処理だが、PowerShell（以前PowerShell Coreと呼ばれていたもの。以下PowerShellと表記）を使う。Windowsに標準で搭載されているWindows PowerShell（Ver.5.1）を使うこともできるが、JSONの処理に関しては、後継となるPowerShell（Ver.7.1）を使うほうが便利だ。たとえば、Windows TerminalのJSONファイルには、JSONCと呼ばれる“//”を使う注釈（Microsoftが提唱）が使われているが、ExcelやWindows PowerShell（Ver.5.1）はこれに対応していないがPowerShellはこれをエラーにせず無視してくれる。

PowerShellは、Microsoftストアからインストールするのが最も簡単だが、ストアアプリとしての制限があり、勝手にアップデートするので、本格利用するコンピュータ言語としてはちょっと使いづらい（お試し導入にはお勧めだが……）。制限もなく簡単なのはwingetを使う方法だ。Windows Terminalをインストールしてあればすでにwingetがインストールされている（インストールに使われるアプリインストーラーに含まれている）。wingetがあるなら、“winget install --id Microsoft.PowerShell --exact”でインストールが可能だ。そのほか、msiやzipファイルをダウンロードしてインストールする方法もある。

PowerShellには、JSONとPowerShellオブジェクト（PSCustomObjectと呼ばれる）の間で、相互変換するためのコマンド“ConvertFrom-Json”と“ConvertTo-Json”がある。これを使いJSONをPSCustomObjectにしてしまえば、扱いが簡単になる。たとえば、


$defaultJson=Get-Content .\defaults.json | ConvertFrom-Json
$settingsJson=Get-Content .\settings.json | ConvertFrom-Json

として、変数にJSONから変換したPSCustomObjectを保存したとき、その中の"actions"オブジェクトだけを取り出すには“$defaultJson.actions”とすればよい（写真01）。2つのPowerShellオブジェクトからactionsオブジェクトだけを取り出して、合わせて1つのJSONファイルにするには


$defaultJson.actions,$settingsJson.actions | ConvertTo-Json -Depth 2 >key1.json

とする。最後の“-Depth 2”がExcelでJSONを処理するときの「キモ」だ。

  • 写真01: Excelで読み込めるようにJSONファイルをPowerShellで前処理する。ConvertFrom-JSONでJSONデータをPowerShellのオブジェクトに変換すれば、以後の操作はたやすい。最後にConvertTo-JSONでまたJSONに戻してファイルにする

Windows Terminalの個々のキー割り当ては、以下のような単純なアクション（command）と引数を持つ複雑なアクションののどちらかになる。


{"command": "openNewTabDropdown","keys": "ctrl+shift+space"}
{"command": {"action":"openSettings","target":"settingsUI"},"keys":"ctrl+," }

単純なアクションは、“command”と“keys”の2つのオブジェクトしかないため、これを複数Excelに読み込んだときには、2つの列を持つ行になるが、複雑なアクションの場合には、さらに"action"と"target"の2つの列を持ってしまう。行により列構造が違っていてはExcelに読み込むことができない。ConvertTo-Jsonの“-Depth”オプションを使うと、深さ方向のJSONへの変換を制限できる。内側の波括弧部分は、深さでは3となる。“-Depth 2”を指定すると、複雑なアクションは、


{ "command": "@{action=openSettings; target=settingsUI}", "keys": "ctrl+," }

となり、内側のオブジェクト（波括弧でくくられた部分）は、ダブルクオートでくくられた文字列となる。@が付いているのは、ここが元のPSCustomObjectのままで、そのまま文字列化したからだ（@はPowerShellの連想配列の表記）。これで、すべてのキー割り当てオブジェクトは、“command”と“keys”の2つのオブジェクトだけになる。

これをExcelに読むのはたやすい。Excelの「データタブ→データの取得→ファイルから→JSONから」で、PowerShellで作成したJSON（key1.json）を読み込むとPowerQueryが開く。JSONが展開されていないため、Listが2つ縦にならんだ状態となるが、「テーブルへの変換」で2列に展開したあと、Column1のヘッダ右側のボタンで列を2回展開すれば、“command”と“keys”からなる列が右側に出てくる（図01）。

  • 図01: PowerShellで処理したJSONデータをExcelで読み込むとPowerQueryが開く。メニューやボタン操作でJSONデータを展開し、不要な列を削除、置換処理でデータを成形する。最後に「閉じて読み込み」を押せばワークシートにJSONデータを元にした表が読み込まれる

その後、変換されなかった複雑なアクションの部分を元のJSONのように見える文字列に戻す。これは単なる文字列の置換を必要なだけ繰り返せばよい。この作業は、Excel側の置換機能でも行えるが、PowerQuery内で行っておけば、Windows Terminalのバージョンアップ時に同じ作業を繰り返すことができるため、できるだけPowerQuery内で行っておく。これでExcelにキー割り当ての表ができた。

矩形構造のデータに変換することができるなら、JSONもExcelに読み込みは可能だ。今回はPowerShellを使ったが、JSONと言語のオブジェクトを相互に変換できる言語ならば、他の言語でも応用は可能だろう。