Export training data to Excel via the Jam API

Before you start

• An Owner role in your Jam organization

• An API key — create one at https://auth.wejam.ai/org/api_keys/

1. Go to https://auth.wejam.ai/org/api_keys/ and sign in as an Owner.

2. Click Create key, give it a name, and copy it somewhere safe.

3. Keep it private — it grants read access to your organization's data. If it's ever exposed, delete it and create a new one from the same page.

💡 You only need to create the API key once. You'll paste it into Excel during setup, and it stays there for all future refreshes.

What data you can pull

💡 A session is a single role-play round played by one learner on one mission. It's the core performance record — it carries an overall score (0–100) and a per-task scorecard breakdown.

Set up Excel (one time)

1. In Excel, go to Data → Get Data → From Other Sources → Blank Query.

2. In the editor that opens, choose Home → Advanced Editor.

3. Delete whatever is in the box, paste one of the code examples from the section below, and replace YOUR_API_KEY with your real key.

4. Click Done.

5. If Excel asks how to connect to api.wejam.ai, choose Anonymous and click Connect.

6. When the table preview looks right, click Home → Close & Load.

⚠️ Always choose Anonymous when Excel asks for credentials — not "Web API" or "Organizational account." Your API key is already included in the code as a header, so no other auth is needed.

Code examples (Power Query)

Method A — one query per entity

let
    BaseUrl = "https://api.wejam.ai/api/v1/data-exports/missions",
    ApiKey  = "YOUR_API_KEY",

    GetPage = (page as number) as record =>
        Json.Document(
            Web.Contents(
                BaseUrl,
                [
                    Query   = [ page = Text.From(page), limit = "100" ],
                    Headers = [ #"X-API-KEY" = ApiKey, #"accept" = "application/json" ]
                ]
            )
        ),

    Pages = List.Generate(
        () => [ p = 1, resp = GetPage(1) ],
        each [resp] <> null,
        each if [resp][meta][hasNext] = true
             then [ p = [p] + 1, resp = GetPage([p] + 1) ]
             else [ p = [p] + 1, resp = null ],
        each [resp][data]
    ),

    Rows     = List.Combine(Pages),
    AsTable  = Table.FromList(Rows, Splitter.SplitByNothing(), {"Record"}),
    Expanded = if List.IsEmpty(Rows)
               then AsTable
               else Table.ExpandRecordColumn(AsTable, "Record", Record.FieldNames(Rows{0}))
in
    Expanded

Method B — one reusable function (recommended)

(entity as text) as table =>
let
    BaseUrl = "https://api.wejam.ai/api/v1/data-exports/",

    GetPage = (page as number) as record =>
        Json.Document(
            Web.Contents(
                BaseUrl & entity,
                [
                    Query   = [ page = Text.From(page), limit = "100" ],
                    Headers = [ #"X-API-KEY" = ApiKey, #"accept" = "application/json" ]
                ]
            )
        ),

    Pages = List.Generate(
        () => [ p = 1, resp = GetPage(1) ],
        each [resp] <> null,
        each if [resp][meta][hasNext] = true
             then [ p = [p] + 1, resp = GetPage([p] + 1) ]
             else [ p = [p] + 1, resp = null ],
        each [resp][data]
    ),

    Rows     = List.Combine(Pages),
    AsTable  = Table.FromList(Rows, Splitter.SplitByNothing(), {"Record"}),
    Expanded = if List.IsEmpty(Rows)
               then AsTable
               else Table.ExpandRecordColumn(AsTable, "Record", Record.FieldNames(Rows{0}))
in
    Expanded

= fnGetJamData("users")

= fnGetJamData("sessions")

= fnGetJamData("tracks")

= fnGetJamData("missions")

Bonus: a ready-to-read sessions report

let
    Sessions = fnGetJamData("sessions"),
    Users    = fnGetJamData("users"),
    Missions = fnGetJamData("missions"),

    // Pull the score out of the nested analysis record
    WithScore = Table.AddColumn(
        Sessions, "Score",
        each if [analysis] <> null then Record.FieldOrDefault([analysis], "score", null) else null,
        Int64.Type
    ),

    // Add learner names
    WithUsers = Table.NestedJoin(
        WithScore, {"learnerUserId"}, Users, {"id"}, "u", JoinKind.LeftOuter
    ),
    AddName = Table.AddColumn(
        WithUsers, "UserName",
        each Text.Combine({ [u]{0}?[firstName]?, [u]{0}?[lastName]? }, " ")
    ),

    // Add mission titles
    WithMissions = Table.NestedJoin(
        AddName, {"missionId"}, Missions, {"id"}, "m", JoinKind.LeftOuter
    ),
    AddTitle = Table.AddColumn(
        WithMissions, "MissionTitle", each [m]{0}?[title]?
    ),

    // Keep just the report columns
    Final = Table.SelectColumns(
        AddTitle, {"UserName", "MissionTitle", "createdAt", "Score", "completed"}
    ),
    Sorted = Table.Sort(Final, {{"createdAt", Order.Descending}})
in
    Sorted

Key fields by entity

• analysis.score — overall score 0–100 (the headline metric)

• completed — whether the role-play was finished (true/false)

• createdAt — when the session was played

• learnerUserId — links to id in Users

• missionId — links to id in Missions

• Scorecard: each task has an itemScore (0–6), mapped to solved / partly solved / not yet solved

Troubleshooting