Teamleader Focus CSV Importer

Small .NET console tool that imports time entries from CSV into Teamleader Focus (NextGen projects API). It performs fuzzy resolution and optional creation of Companies → Projects → ProjectGroups (phases) → Tasks and then posts time tracking entries. Key features

• Supports both header-based CSV (ManicTime-style) and legacy semicolon CSV lines
• Fuzzy matching for companies, projects and groups; optional automatic creation
• Prefers ticket-id based task matching (extracted from tags like #12345)
• Extracts URLs from tag fields and collects them into each entry
• OAuth interactive flow with persisted auth_token.json and automatic refresh
• Uses projects-v2 and tasks-v2 endpoints where available

Build

dotnet build .\src\TeamleaderFocusAgent.csproj -c Debug

Examples

There are two sample CSV files in src/examples/:

• sample-header.csv — header-based CSV with Name,Start,End,Notes columns.
• sample-legacy.csv — legacy semicolon-separated lines using tags;start;end;notes.

Try the dry-run against the samples:

dotnet run --project .\src\TeamleaderFocusAgent.csproj -- --dry-run src\examples\sample-header.csv
dotnet run --project .\src\TeamleaderFocusAgent.csproj -- --dry-run src\examples\sample-legacy.csv

Run

Default behavior runs the import. You can also use helper commands described below.

# Example: create your own config (do not commit it)
Copy-Item .\src\appsettings.template.json .\src\appsettings.dev.json

# Example: run import with an explicit config file
dotnet run --project .\src\TeamleaderFocusAgent.csproj -- .\src\appsettings.dev.json input.csv

# Dry-run: parse CSV and print entries
dotnet run --project .\src\TeamleaderFocusAgent.csproj -- --dry-run input.csv

# Interactive OAuth test (manual browser step)
dotnet run --project .\src\TeamleaderFocusAgent.csproj -- --auth-test

# Exchange an authorization redirect URL you copied after consenting
dotnet run --project .\src\TeamleaderFocusAgent.csproj -- --exchange-code "https://your.redirect/?code=..."

# List helpers
dotnet run --project .\src\TeamleaderFocusAgent.csproj -- --list-companies
dotnet run --project .\src\TeamleaderFocusAgent.csproj -- --list-projects
dotnet run --project .\src\TeamleaderFocusAgent.csproj -- --list-projectgroups
dotnet run --project .\src\TeamleaderFocusAgent.csproj -- --list-tasks
dotnet run --project .\src\TeamleaderFocusAgent.csproj -- --list-timetracking

Configuration

Configuration is read from a JSON file you pass as the first argument (standard .NET configuration).

• Template file: src/appsettings.template.json (safe to commit)
• Recommended: create src/appsettings.dev.json and/or src/appsettings.prod.json locally (gitignored)

Important settings:

• BaseUrl: the Teamleader API base URL (e.g. https://api.focus.teamleader.eu/).
• Authentication: (optional) interactive OAuth parameters:
  • ClientId, ClientSecret — required for --auth-test / --exchange-code
  • RedirectUri — optional; must match your app registration
  • AuthorizeUrl / TokenUrl — override defaults if needed
• Import: import behaviour flags (createCompanies, createProjects, createGroups, createTasks, onlyOpen, etc.)
  • InputPathOverride — optional CSV path; when set it overrides the CLI input.csv argument.
  • DefaultWorkTypeId — default work type id used when creating tasks and when creating time entries if the task has no work type.
  • DefaultUserId — optional Teamleader user id used to attribute created time entries and to improve dedupe in multi-user environments.
  • AttachTimeEntriesToDefaultUser — when DefaultUserId is set, include it in timeTracking.add (as user_id).

Safe reruns (avoid duplicates)

When you re-run imports, the importer can skip rows if Focus already contains a time entry for the same company with the same start time.

• Config: Import.SkipIfTimeEntryExistsWithSameStartForCompany (default true)
• Lookup scope: scans timeTracking.list (limited by Import.TimeEntryDedupeMaxPages)
• Matching key:
  • If Import.DefaultUserId is empty: company + started_on (start time compared in UTC seconds)
  • If Import.DefaultUserId is set: user + company + started_on (prevents false positives between multiple users)

If you need to force a re-import for a specific company/start time (e.g., you deleted the entry in Focus and want it re-created), just re-run after deletion. If you need to bypass dedupe entirely for a run, set SkipIfTimeEntryExistsWithSameStartForCompany to false.

Future concern: multi-user imports

The current deduplication rule is intentionally simple: it skips if Focus already contains a time entry for the same company with the same start time.

In a multi-user environment this can become incorrect:

• If User A already has an entry for Company X starting at 09:00, then importing User B’s CSV (also starting at 09:00 for Company X) may be skipped even though it’s a different person.

Future improvement (not implemented):

• Include the user in the dedupe key (e.g., user + company + start) and/or query time tracking scoped to the importing user.
• Attach tasks (or at least time entries) to a specific user where applicable. Today tasks are created/resolved globally and are not user-scoped.

OAuth and token persistence

Use --auth-test to get a manual authorization URL. After consenting, paste the redirect URL into the CLI or run --exchange-code "<redirect_url>". The token is saved to auth_token.json in the same directory as your config file (the configDir) and refreshed automatically when expired.

auth_token.json shape

The interactive flow saves a small JSON object to auth_token.json in the configDir. Example shape:

{
  "access_token": "ey...",
  "refresh_token": "rt-...",
  "expires_in": 3600,
  "obtained_at": "2025-11-30T12:34:56Z",
  "token_type": "Bearer",
  "scope": "projects timeTracking"
}

The TokenManager/OAuthClient code will attempt to refresh the token using the refresh_token when the access_token is expired.

CSV format

The tool accepts two formats:

• Header-based CSV (preferred): A CSV with headers including Name, Start, End, Notes.
• Legacy semicolon lines: tags;start;end;notes where tags is a comma-separated hierarchy.

Tags format (comma-separated): Company,Project,Group,Task[,extra...]

Behavior:

• URLs in any of the first four tag fields are extracted (http/https/www) and stored with the entry.
• Ticket IDs like #12345 are extracted and used to prefer task-matching by ID; they are also prefixed to new task titles when tasks are created.
• Extra tags beyond the 4th are appended to the Notes field (separated by |).
• Notes are trimmed and surrounding single or double quotes are removed to avoid stray leading/trailing apostrophes.

Resolver logic

• Company → Projects → ProjectGroups (phases) → Tasks
• When a projectName isn't found, the code falls back to searching project groups across projects; when a matching project group is found, it is cached in the project-group (phase) cache (not the project cache) to avoid corrupting project lookups.

Troubleshooting

• If time entry creation (timeTracking.add) returns HTTP 400, enable logs and inspect the request/response body in logs/teamleader-.log. The API wrapper already throws HttpRequestException with the response body for non-successful requests; check the exception message and the Serilog file output.

• Reproducing and capturing failing requests

  1. Dry-run first: use --dry-run to verify the CSV parsing and resolver steps do not error.

  2. Reproduce a single failing time entry with a direct API call using the saved token (example PowerShell):

# Read access token from saved auth_token.json
$token = (Get-Content .\auth_token.json | ConvertFrom-Json).access_token

# Example request body (adjust subject id and timestamps)
$body = @{
    subject = @{ type = 'nextgenTask'; id = '<taskId>' }
    started_at = '2025-11-30T09:00:00+00:00'
    ended_at   = '2025-11-30T10:00:00+00:00'
    description = 'Test entry from CLI'
} | ConvertTo-Json -Depth 6

try {
    $resp = Invoke-RestMethod -Uri 'https://api.focus.teamleader.eu/timeTracking.add' -Method Post -Headers @{ Authorization = "Bearer $token"; 'Content-Type' = 'application/json' } -Body $body -ErrorAction Stop
    $resp | ConvertTo-Json
}
catch {
    # Try to extract response body for diagnostics
    if ($_.Exception.Response -ne $null) {
        $stream = $_.Exception.Response.GetResponseStream()
        $reader = New-Object System.IO.StreamReader($stream)
        $text = $reader.ReadToEnd()
        Write-Error "Request failed. HTTP response body:`n$text"
    } else {
        Write-Error "Request failed: $_"
    }
}

• Using a local proxy (mitmproxy / Fiddler)

  • Start a proxy (e.g., mitmproxy on 127.0.0.1:8080). Configure your system or the process to use the proxy so HTTPS requests can be inspected. On Windows you can set the HTTP_PROXY / HTTPS_PROXY environment variables in PowerShell:

$env:HTTP_PROXY = 'http://127.0.0.1:8080'
$env:HTTPS_PROXY = 'http://127.0.0.1:8080'
dotnet run --project .\src\TeamleaderFocusAgent.csproj -- .\src\appsettings.dev.json input.csv

(Use your own local config file, e.g. src\appsettings.dev.json.)

• Inspect the request JSON and the server response in the proxy UI. This helps identify field names or timestamp formats the server rejects.

Common payload issues to check

• subject vs task_id: the NextGen API expects a subject typed object (e.g., { "type": "nextgenTask", "id": "..." }) rather than a plain task_id in some endpoints.
• Timestamps: prefer ISO-8601 with timezone offset and avoid fractional seconds if the API rejects them (e.g., 2025-11-30T09:00:00+00:00).
• Duration vs started/ended: if using duration fields, ensure the API expects seconds or an ISO duration string — check the API docs and server response.

Projects filtering note

If projects are not found when filtering by company, the code uses the documented filter.customers shape in projects-v2/projects.list. If your account behaves differently, check the NextGen API docs and adjust TeamleaderApi.ListProjectsAsync accordingly.

Development notes

• .NET 8 console app. Uses HttpClient and System.Text.Json.
• Logs go to console and logs/teamleader-.log (Serilog).
• Key source files:
  • src/TeamleaderApi.cs — API wrappers and JSON parsing
  • src/Resolver.cs — fuzzy resolution and caching
  • src/CsvParser.cs — CSV parsing, URL/ticket extraction, note cleanup
  • src/CommandHandler.cs — CLI command handlers

Contributing & testing

Add small CSV samples under src/examples/ and use --dry-run to verify parsing. For live tests, use a throwaway account or confirm expected behavior with the Teamleader test environment if available.

If you want, I can add unit tests and small example CSVs to cover the parsing and resolver fallback paths.

License

MIT-style (no license file included by default). Modify as needed.