IMPORT.JSON Function

Description

Import structured JSON data directly into Excel without using Power Query or manual parsing steps.

NOTES

• This function is especially useful for:
  • Importing machine-generated or programmatic data sources.
  • Flattening structured JSON into rows and columns.
  • Automating data refresh workflows.
  • Avoiding Power Query for lightweight or repeatable JSON imports.
  • Working with configuration files, logs, or structured exports.

Syntax

Copied!=IMPORT.XML(data, [target_name], [table_index], [header_mode], [field_headers])
=IMPORT.XML(data, [target_name], [table_index], [header_mode], [field_headers])

SAMPLE-1

{
  "equipmentTypes": [
    {
      "id": "1",
      "key": "slot_torso",
      "label": "Jacket"
    },
    {
      "id": "2",
      "key": "slot_offhand",
      "label": "Shield"
    },
    {
      "id": "3",
      "key": "slot_head",
      "label": "Helmet"
    },
    {
      "id": "4",
      "key": "slot_hand",
      "label": "Gloves"
    },
    {
      "id": "5",
      "key": "slot_foot",
      "label": "Boots"
    }
  ]
}

{
  "equipmentTypes": [
    {
      "id": "1",
      "key": "slot_torso",
      "label": "Jacket"
    },
    {
      "id": "2",
      "key": "slot_offhand",
      "label": "Shield"
    },
    {
      "id": "3",
      "key": "slot_head",
      "label": "Helmet"
    },
    {
      "id": "4",
      "key": "slot_hand",
      "label": "Gloves"
    },
    {
      "id": "5",
      "key": "slot_foot",
      "label": "Boots"
    }
  ]
}

EXAMPLE 1.1

Copied!//𝘍𝘦𝘵𝘤𝘩 𝘑𝘚𝘖𝘕 𝘥𝘢𝘵𝘢 𝘧𝘳𝘰𝘮 𝘢 𝘧𝘪𝘭𝘦.
=IMPORT.JSON(B2)
//𝘍𝘦𝘵𝘤𝘩 𝘑𝘚𝘖𝘕 𝘥𝘢𝘵𝘢 𝘧𝘳𝘰𝘮 𝘢 𝘧𝘪𝘭𝘦.
=IMPORT.JSON(B2)

SAMPLE-2

EXAMPLE 2.1

Copied!//𝘍𝘦𝘵𝘤𝘩 𝘑𝘚𝘖𝘕 𝘥𝘢𝘵𝘢 𝘧𝘳𝘰𝘮 𝘢 𝘜𝘙𝘓.
//𝘩𝘵𝘵𝘱𝘴://𝘮𝘪𝘤𝘳𝘰𝘴𝘰𝘧𝘵𝘦𝘥𝘨𝘦.𝘨𝘪𝘵𝘩𝘶𝘣.𝘪𝘰/𝘋𝘦𝘮𝘰𝘴/𝘫𝘴𝘰𝘯-𝘥𝘶𝘮𝘮𝘺-𝘥𝘢𝘵𝘢/64𝘒𝘉.𝘫𝘴𝘰𝘯
=IMPORT.JSON(IMPORTTEXT(B2))
//𝘍𝘦𝘵𝘤𝘩 𝘑𝘚𝘖𝘕 𝘥𝘢𝘵𝘢 𝘧𝘳𝘰𝘮 𝘢 𝘜𝘙𝘓.
//𝘩𝘵𝘵𝘱𝘴://𝘮𝘪𝘤𝘳𝘰𝘴𝘰𝘧𝘵𝘦𝘥𝘨𝘦.𝘨𝘪𝘵𝘩𝘶𝘣.𝘪𝘰/𝘋𝘦𝘮𝘰𝘴/𝘫𝘴𝘰𝘯-𝘥𝘶𝘮𝘮𝘺-𝘥𝘢𝘵𝘢/64𝘒𝘉.𝘫𝘴𝘰𝘯
=IMPORT.JSON(IMPORTTEXT(B2))

RELATED POST