Integrating the jobdata API with Excel

Table of contents

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
• 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)

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)

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.

Related Docs