API Integration Guide for Excel

Accessing Open API Endpoints

To import data from open API endpoints into Excel, follow these steps:

  1. Open Excel and create a new workbook.
  2. Go to the "Data" tab and click on "Get Data" > "From Other Sources" > "From Web".
  3. In the "From Web" dialog box, enter the URL of the JSON API and click "OK".
  4. The JSON data will load into the Power Query Editor. Select "Parse" > "JSON", then choose "To table" and click "OK".
  5. Apply any necessary transformations and click on "Close & Load" to import the data into Excel.

Accessing Secure API Endpoints with Authentication

For secure API endpoints requiring a token, use the following method:

  1. Open Excel and create a new workbook.
  2. Access Power Query by going to the "Data" tab, then "Get Data" > "From Other Sources" > "Blank Query".
  3. Enter the Power Query Editor and input the code to access your secure API, including the API token in the request headers. For example:
    4. let
    url = "your_api_endpoint",
    headers = [Authorization="Bearer your_token"],
    source = Json.Document(Web.Contents(url, [Headers=headers]))
in
    source
  4. Parse the JSON data, apply necessary transformations, and then load it into Excel by clicking "Close & Load".

Authentication for Secure Endpoints

To ensure secure access to the API, we employ token-based authentication. Follow these steps:

  1. Obtaining Your Access Token: Send a POST request to /api/token with your credentials.
  2. Using Your Access Token: Include the token in the Authorization header for API requests.
  3. Token Expiry: Tokens are valid for a limited time. After expiry, re-authenticate for a new token.
  4. Example of an Authenticated Request: Make authenticated requests like this:
    5. GET /api/resources
Authorization: Bearer YOUR_ACCESS_TOKEN
  5. Handling Authentication Errors: For 401 Unauthorized errors, check your credentials and token validity.