JSONエクセル変換で空白になる原因と変換前の確認方法

APIやデータベースから取り出したJSON配列をExcelファイルに変換して開くと、値が入っていたはずの列なのに、セルが丸ごと空になっていることがあります。
これは変換に失敗したのではなく、その値がネストされたオブジェクトや配列だったために起きる、予測できる動作です。どの空欄が正常で、どの空欄が本当のデータ欠落なのか、変換前に見分ける方法をまとめます。
まとめ ① 項目ごとにキーが違うためにできる空欄は正常です — その項目に元からなかった値だからです。 ② 値がネストされたオブジェクト・配列の場合、列見出しは作られますが、実際のExcelファイルにはその値が入りません。 ③ ブラウザのコンソールで数行実行するだけで、どの列が空になるかを変換前に確認できます。
JSONをExcelに変換したら特定の列が丸ごと空になる状況
ネットショップの注文データをJSONで受け取り、Excelに変換して担当部署へ渡す場面を考えてみます。注文ごとに「オプション」というフィールドがあり、色や容量といった情報が { } の形で入っています。注文によっては、このフィールド自体がありません。
Excelに変換して開くと、「オプション」という列見出しは確かにできているのに、その下のセルはすべて空です。フィールドがなかった注文だけが空なら正常ですが、値があった注文まで空になっているので、変換がおかしいのではないかと戸惑います。
ツールを使わずに原因を切り分ける方法
2種類の空欄は原因が違うので、変換前に元のJSONで見分けておけば、結果を見て慌てずに済みます。ブラウザの開発者ツール(F12、MacはCmd+Option+I)のコンソールで確認します。
項目ごとにキーが違うか確認する
元の配列をコンソールで変数に入れ、次を実行すると、配列全体に登場するキーの一覧が出ます。
const data = JSON.parse(元のJSONテキスト);
const keys = new Set();
data.forEach(row => Object.keys(row).forEach(k => keys.add(k)));
console.log([...keys]);
ここに出たキーが、実際のExcelの列になります。特定の項目にだけこのキーがなければ、その行だけが空くのが正常で、ほかの項目は値がそのまま入ります。
値がネストされたオブジェクト・配列かどうかを確認する
反対に、値そのものが消える列は次のコードで見つけます。
data.forEach((row, i) => {
Object.entries(row).forEach(([k, v]) => {
if (v !== null && typeof v === "object") {
console.log(i + "番目の項目の \"" + k + "\" はネストされた値 — Excel変換時に空欄になります");
}
});
});
ここに出てくるキーは、値があってもExcelのセルには反映されません。事前に取り除いて JSON.stringify(v) で文字列に変えるか、必要な下位の値だけを別のキーとして取り出しておく必要があります。
この2つの確認を毎回コンソールに書き写す代わりに、貼り付けるだけで行数・列数をすぐ表示するページが下のツールです。
無料・登録不要・JSONはサーバーに送信されず、ブラウザ内だけでxlsxに変換されます

つまずきやすいポイント
短い事実だけを表にまとめます。
| 状況 | 実際に起きること |
|---|---|
| 項目ごとにキーの構成が違う | すべてのキーを合わせた1つの表になり、ないキーはその行だけ空欄(正常) |
値がネストしたオブジェクト { } | 列見出しはできるが実際のセルには値が入らない(エラーメッセージなし) |
値が配列 [ ] | オブジェクトと同様にセルに反映されない |
シート名に \ / ? * [ ] : を含む | 該当する文字だけが自動的に削除され、残りの名前で保存 |
ファイル名に " < > | を含む | シート名とは異なる文字リストが適用され、この文字だけが削除 |
| シート名が32文字以上 | 先頭31文字まで保存(Excelのシート名自体の規格) |
シート名とファイル名には別々のルールが適用されます。Excelファイルの規格上シート名に使えない文字と、OSがファイル名に禁止している文字が異なるからです。
まとめ
- 項目ごとにキーの構成が違うためにできる空欄は正常です。すべてのキーを合わせた1つの表になり、ない値はその行だけ空きます。
- 値がネストしたオブジェクト・配列の場合は、列見出しだけが残り、実際の値はExcelのセルに入りません。エラーメッセージも表示されません。
- この2つのケースは、完成したExcelファイルだけを見ても区別できないため、変換前に元のJSONでコンソールを使って確認する必要があります。
- シート名とファイル名で禁止されている文字は異なり、シート名は31文字で切り捨てられます。
無料・登録不要・JSONはサーバーに送信されず、ブラウザ内だけでxlsxに変換されます
よくある質問
ネストされた値をそのままExcelに残すにはどうすればよいですか?
変換前にJSON上でその値を文字列に変えておけば済みます。JSON.stringify(row.オプション) でネストしたオブジェクトを1つのテキストに変え、同じキーに入れておけば、文字列なのでセルにそのまま表示されます。
色や容量のように下位の値だけを取り出して、あらかじめ別のキーに分けておく方法もあります。
値がなかった行と、値が消えた行をExcelファイルだけで見分けられますか?
ファイルだけでは区別できません。どちらも単なる空欄に見えるからです。そのため、変換前に元のJSONで上のコンソールスクリプトを使って確認する必要があります。
シート名を空にするとどうなりますか?
空の値、または禁止文字だけがあって削除後に何も残らない場合は、自動的に "Sheet1" として保存されます。
入力したJSONや作成されたExcelファイルはサーバーに送信されますか?
いいえ。入力したJSONも変換後のExcelファイルも、すべてブラウザ内だけで処理され、サーバーに送信されたり保存されたりすることはありません。
この記事は韓国語の原文をもとに作成しています。 韓国語の原文を読む