# Integrating the jobdata API with Excel

Use Excel + Power Query to pull live job listings from the jobdata API, shape them into analysis-ready tables, and keep them refreshed for dashboards, market research, and reporting.

---

The jobdata API gives you structured job posting data from company ATS and career platforms. Excel's Power Query lets you connect to that API, transform nested JSON into tabular data, and refresh it repeatedly with minimal manual work.

This guide includes:
- A no-code UI method in Power Query
- A robust M-code method for repeatable setups
- Pagination patterns for larger pulls
- Refresh, security, and troubleshooting best practices

## Prerequisites

- **Excel Desktop** (Microsoft 365 or Excel 2016+ with Power Query/Get & Transform)
- **jobdata API key** from your [dashboard](/accounts/dashboard/)
- Optional:
  - OneDrive/SharePoint for cloud workbook workflows
  - Power Pivot for large models and PivotTables

## Important API Behavior Before You Start

- Base endpoint: `https://jobdataapi.com/api/jobs/` (trailing slash recommended)
- Auth header:
  - `Authorization: Api-Key YOUR_API_KEY`
- Jobs response envelope:
  - `count`, `next`, `previous`, `results`
- `/api/jobs/` defaults to `max_age=90` if no explicit age/slicing filter is provided
- Some filters/features are tier-gated and can return `403`
- Unknown query parameters return `400`

## Method A: Excel UI (No Code)

### Step 1: Open Power Query from Web

1. Open Excel.
2. Go to **Data** > **Get Data** > **From Other Sources** > **From Web**.
3. Choose **Advanced**.

### Step 2: Configure URL + header

- URL: `https://jobdataapi.com/api/jobs/`
- Header:
  - Name: `Authorization`
  - Value: `Api-Key YOUR_API_KEY`

Optional query example:
`https://jobdataapi.com/api/jobs/?title=python&max_age=7&has_remote=true`

If prompted for auth method, choose **Anonymous** (auth is passed in header).

### Step 3: Transform JSON in Power Query

1. Open top-level **Record**
2. Click into `results`
3. Convert list **To Table**
4. Expand records
5. Select desired fields (example):
   - `id`, `title`, `location`, `published`, `application_url`, `company`
6. Expand `company` into `company.id`, `company.name`
7. Set data types

### Step 4: Load data

Use **Close & Load**:
- To worksheet table, or
- Connection-only + Data Model (recommended for bigger workbooks)

### Step 5: Refresh

- Manual: **Data** > **Refresh All**
- Optional: enable refresh on open / periodic refresh / background refresh

## Method B: Parameterized Power Query (Recommended)

### Step 1: Parameterize API key

Create parameter:
- Name: `JOBDATA_API_KEY`
- Type: Text
- Value: your key (without `Api-Key `)

### Step 2: Use this M query (single page)

```powerquery
let
    ApiKey = "Api-Key " & JOBDATA_API_KEY,
    Source = Json.Document(
        Web.Contents(
            "https://jobdataapi.com",
            [
                RelativePath = "api/jobs/",
                Query = [
                    title = "python",
                    max_age = "7",
                    has_remote = "true"
                ],
                Headers = [
                    Authorization = ApiKey,
                    Accept = "application/json"
                ],
                Timeout = #duration(0, 0, 2, 0)
            ]
        )
    ),
    Results = Source[results],
    AsTable = Table.FromList(Results, Splitter.SplitByNothing(), {"job"}),
    Expanded = Table.ExpandRecordColumn(
        AsTable,
        "job",
        {"id", "title", "location", "published", "application_url", "company"},
        {"id", "title", "location", "published", "application_url", "company"}
    ),
    ExpandedCompany = Table.ExpandRecordColumn(
        Expanded,
        "company",
        {"id", "name"},
        {"company_id", "company_name"}
    )
in
    ExpandedCompany
```

## Method C: Multi-page Pagination (Advanced)

```powerquery
let
    ApiKey = "Api-Key " & JOBDATA_API_KEY,
    BaseUrl = "https://jobdataapi.com",
    PageSize = 200,
    MaxPages = 20,

    GetPage = (page as number) as record =>
        Json.Document(
            Web.Contents(
                BaseUrl,
                [
                    RelativePath = "api/jobs/",
                    Query = [
                        title = "python",
                        max_age = "7",
                        has_remote = "true",
                        page_size = Text.From(PageSize),
                        page = Text.From(page)
                    ],
                    Headers = [
                        Authorization = ApiKey,
                        Accept = "application/json"
                    ],
                    Timeout = #duration(0, 0, 2, 0)
                ]
            )
        ),

    FirstPage = GetPage(1),
    TotalPagesRaw = Number.RoundUp(FirstPage[count] / PageSize),
    TotalPages = Number.Min(TotalPagesRaw, MaxPages),
    PageNumbers = List.Numbers(1, TotalPages),

    PageResults = List.Transform(PageNumbers, each GetPage(_)[results]),
    AllRows = List.Combine(PageResults),

    AsTable = Table.FromList(AllRows, Splitter.SplitByNothing(), {"job"}),
    Expanded = Table.ExpandRecordColumn(
        AsTable,
        "job",
        {"id", "title", "location", "published", "application_url", "company"},
        {"id", "title", "location", "published", "application_url", "company"}
    ),
    ExpandedCompany = Table.ExpandRecordColumn(
        Expanded,
        "company",
        {"id", "name"},
        {"company_id", "company_name"}
    )
in
    ExpandedCompany
```

## Recommended Query Patterns for Excel

Use filters to keep pull size manageable:
- `max_age=7` (or tighter)
- `title=...`
- `country_code=...`
- `has_remote=true`
- `published_since=...` / `published_until=...` (if your plan supports it)

For large backfills, use date-window chunking and append results.

## Refresh and Automation Strategy

- Build one raw ingestion query
- Build reference queries for business-facing views
- Use connection-only raw query when modeling
- Use worksheet outputs for quick reporting

## Security and Governance Best Practices

- Do not expose API keys in visible cells
- Prefer query parameters and controlled sharing
- Review Data Source Settings + credentials periodically
- Set proper Power Query privacy levels
- Avoid "ignore privacy levels" unless fully intentional

## Common Errors and Fixes

- **301**: use `/api/jobs/` not `/api/jobs`
- **400**: invalid query parameter or malformed value
- **401**: invalid/missing auth header
- **403**: plan-gated feature/filter
- **429**: reduce refresh rate/page depth, tighten filters
- **500/timeouts**: retry + reduce payload complexity

## Performance Tips

- Expand only required fields
- Remove unused columns early
- Use smaller page sizes if transformations are heavy
- Use Data Model for larger datasets
- Split ingestion vs presentation queries

## Conclusion

Excel + Power Query is a solid, low-maintenance way to analyze jobdata API output. Start with a constrained single-page query, validate transformation quality, then scale into parameterized and paginated ingestion as reporting needs grow.