JSON API to Table

Introduction

JSON API to Table is an Excel add-in that allows you to fetch data from APIs and import it into Excel tables. The add-in is designed to update your data without overriding existing entries, with automatic deduplication.

Getting Started

Basic Usage

1. Open the JSON API to Table add-in from the Home tab in Excel
2. Enter the API URL you want to fetch data from
3. Select the appropriate HTTP method (default is GET)
4. Add any required headers or request body
5. Click "Fetch Data" to retrieve and import the data

Data Path

If your API returns nested data, you can specify a data path to extract the specific array or object you need. For example, if your API returns:

{
  "results": {
    "items": [
      { "id": 1, "name": "Item 1" },
      { "id": 2, "name": "Item 2" }
    ]
  }
}

You would enter the data path as results.items to get just the array of items.

Example APIs to Try

Here are several public APIs you can use to test the JSON API to Table. These examples work without any authentication:

https://pokeapi.co/api/v2/pokemon?limit=20&offset=10

https://pokeapi.co/api/v2/pokemon/pikachu

https://jsonplaceholder.typicode.com/users

https://jsonplaceholder.typicode.com/posts

https://restcountries.com/v3.1/all

https://restcountries.com/v3.1/region/europe

https://api.nasa.gov/planetary/apod?api_key=DEMO_KEY

https://date.nager.at/api/v3/publicholidays/2023/US

https://randomuser.me/api/?results=10

Advanced Features

Headers and Authentication

For APIs that require authentication or specific headers, add them to the Headers field in JSON format:

{
  "Authorization": "Bearer your-token-here",
  "Content-Type": "application/json",
  "Accept": "application/json"
}

Request Body

For POST, PUT, or DELETE requests that require a body, add it to the Request Body field in JSON format:

{
  "query": "your search query",
  "filters": {
    "category": "example",
    "limit": 100
  }
}

Saving and Loading Configuration

Your API configuration is automatically saved at the top of the current worksheet. To reload a saved configuration, click the "Load Config" button when working on a sheet with a saved configuration.

Troubleshooting

Common Issues

• API Error Responses: Check your URL, headers, and request body for accuracy
• No Data Found: Ensure your data path is correct and the API is returning data
• Format Issues: Make sure your headers and request body are valid JSON
• CORS Errors: Some APIs don't allow requests from Excel. Try using a different API or consult the API provider.