Integration of Trackline with Excel
Export documents and additional data from Trackline to Excel using API queries
Trackline is a functionality of the Code Contract platform that allows automating data collection without changing existing workflows or requiring manual records.

If you want to integrate Trackline with Excel to retrieve documents and process data, follow these steps using JWT Token authentication.

Step 1: Obtain the authentication token
Excel must authenticate with the Trackline API by sending a POST request to obtain a JWT token. This token will be used in subsequent requests to authenticate queries.

Request from Excel using Power Query (M Language)

let
    url = "https://api.codecontractplattform.com/api/login",
    body = "{""email"": ""myusername"", ""password"": ""mypassword""}",
    headers = [#"Content-Type"="application/json"],
    response = Json.Document(Web.Contents(url, [Content=Text.ToBinary(body), Headers=headers, ManualStatusHandling={400,404,500}])),
    token = response[token]
in
    token

Expected response

{
    "token": "eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJmcmV..."
}

The token value must be stored in Excel and used in every query, sent in the Authorization header as a Bearer Token.

Step 2: Query metadata of a process in Trackline from Excel
Query process information
Excel can retrieve information about a specific process using process_id or process_name.

Request from Excel using Power Query

let
    token = "eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJmcmV...", // Replace with dynamic token if retrieved from another query
    url = "https://api.codecontractplattform.com/api/track-line/ext/track-path/metadata?process_id=12345",
    headers = [Authorization="Bearer " & token],
    response = Json.Document(Web.Contents(url, [Headers=headers]))
in
    response

Expected response (JSON format)

{
    "process_id": "12345",
    "name": "Billing Process",
    "status": "In Progress",
    "created_at": "2024-02-09T10:30:00Z",
    "documents": [
        {
            "doc_id": "98765",
            "doc_name": "Invoice_001.pdf",
            "doc_type": "Invoice"
        }
    ]
}

This information will be imported directly into Excel, allowing automated management of process data.

Step 3: Download an attachment from Trackline in Excel
Once the process metadata is retrieved, Excel can download an attachment using its attachment_id.

Request from Excel using Power Query

let
    token = "eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJmcmV...", // Replace with dynamic token
    attachment_id = "98765",
    url = "https://api.codecontractplattform.com/api/track-line/ext/attachment/file?attachment_id=" & attachment_id,
    headers = [Authorization="Bearer " & token],
    response = Web.Contents(url, [Headers=headers])
in
    response

Expected response
The server will respond with a PDF, XML, or other file format, which Excel can download or open directly.

How to save the file in Excel:
The query will return the file as a binary.
To save it, use Power Query and select Transform → Load To and choose Table or File.

Integration Summary
✔️ Step 1: Excel obtains the JWT Token by authenticating with Trackline.
✔️ Step 2: Excel queries process metadata using process_id or process_name.
✔️ Step 3: Excel downloads attachments using attachment_id.
✔️ Step 4: Excel can automate these processes using Power Query, with no need for VBA.

If you need more information or support, contact the Code Contract team. 🚀