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

公開ツールJSON → Excel(XLSX)変換

目次
  1. JSONをExcelに変換したら特定の列が丸ごと空になる状況
  2. ツールを使わずに原因を切り分ける方法
  3. つまずきやすいポイント
  4. まとめ
  5. よくある質問

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 → Excel(XLSX)変換

無料・登録不要・JSONはサーバーに送信されず、ブラウザ内だけでxlsxに変換されます

つまずきやすいポイント

短い事実だけを表にまとめます。

状況実際に起きること
項目ごとにキーの構成が違うすべてのキーを合わせた1つの表になり、ないキーはその行だけ空欄(正常)
値がネストしたオブジェクト { }列見出しはできるが実際のセルには値が入らない(エラーメッセージなし)
値が配列 [ ]オブジェクトと同様にセルに反映されない
シート名に \ / ? * [ ] : を含む該当する文字だけが自動的に削除され、残りの名前で保存
ファイル名に " < > | を含むシート名とは異なる文字リストが適用され、この文字だけが削除
シート名が32文字以上先頭31文字まで保存(Excelのシート名自体の規格)

シート名とファイル名には別々のルールが適用されます。Excelファイルの規格上シート名に使えない文字と、OSがファイル名に禁止している文字が異なるからです。

まとめ

  • 項目ごとにキーの構成が違うためにできる空欄は正常です。すべてのキーを合わせた1つの表になり、ない値はその行だけ空きます。
  • 値がネストしたオブジェクト・配列の場合は、列見出しだけが残り、実際の値はExcelのセルに入りません。エラーメッセージも表示されません。
  • この2つのケースは、完成したExcelファイルだけを見ても区別できないため、変換前に元のJSONでコンソールを使って確認する必要があります。
  • シート名とファイル名で禁止されている文字は異なり、シート名は31文字で切り捨てられます。
JSON → Excel(XLSX)変換

無料・登録不要・JSONはサーバーに送信されず、ブラウザ内だけでxlsxに変換されます

よくある質問

ネストされた値をそのままExcelに残すにはどうすればよいですか?

変換前にJSON上でその値を文字列に変えておけば済みます。JSON.stringify(row.オプション) でネストしたオブジェクトを1つのテキストに変え、同じキーに入れておけば、文字列なのでセルにそのまま表示されます。

色や容量のように下位の値だけを取り出して、あらかじめ別のキーに分けておく方法もあります。

値がなかった行と、値が消えた行をExcelファイルだけで見分けられますか?

ファイルだけでは区別できません。どちらも単なる空欄に見えるからです。そのため、変換前に元のJSONで上のコンソールスクリプトを使って確認する必要があります。

シート名を空にするとどうなりますか?

空の値、または禁止文字だけがあって削除後に何も残らない場合は、自動的に "Sheet1" として保存されます。

入力したJSONや作成されたExcelファイルはサーバーに送信されますか?

いいえ。入力したJSONも変換後のExcelファイルも、すべてブラウザ内だけで処理され、サーバーに送信されたり保存されたりすることはありません。

この記事は韓国語の原文をもとに作成しています。 韓国語の原文を読む

関連記事

  1. JSON YAMLの違い|インデント規則とよくある失敗JSONは括弧とカンマ、YAMLはインデントでデータの境界を示します。両者の構文の違い、設定ファイルでYAMLが選ばれる理由、インデントのずれでパースが壊れる例を解説します。
  2. Markdownの改行が反映されない原因と3つの対処法MarkdownでEnterを1回押しても改行が消えて文がつながる理由を、CommonMarkの仕様から説明します。空行・行末の空白2つ・breaksオプションの3つの方法で確実に改行する方法も解説します。
  3. マークダウン表の中央揃えは区切り線のコロンで決まるマークダウン表の中央・右揃えが、セル内の空白ではなくヘッダー下の区切り線のコロン位置で決まる仕組みを解説します。表が崩れる原因と正しい書き方もわかります。
  4. マークダウン表をExcelで開けるCSVに変換する方法NotionやGitHubのマークダウン表をExcelに貼り付けると崩れる理由と、区切り線やエスケープされたパイプまで整理してCSVへ正確に変換する方法を説明します。
  5. 議事録の書き方|議題・決定事項・アクションアイテムの残し方議題だけの議事録は後で使えません。議題・決定事項・アクションアイテムを分け、担当者と期限を添える書き方と、自動で整えるツールの使い方をまとめました。
  6. プレスリリース 5W1Hの書き方|タイトルとリード文の順序プレスリリースのタイトルと最初の段落に、5W1Hをどの順番で置けば記者に読まれるかを解説。よくある失敗と、事実だけで書くためのコツが分かります。

関連ツール