Looker Studio

1. Summary

Status: Recipe. A bulk export feed for BI is Planned.

Direction: TimeTracking to Looker Studio (read-only).

2. Mechanism

Looker Studio has no generic authenticated REST connector. The simplest reliable path:

Apps Script (time trigger) → GET /days (cursor-paginated) + GET /placements, /clients → Google Sheet → Looker Studio (Google Sheets connector)

A custom community connector (Apps Script getData) is possible but more work.

3. Prerequisites

• Google account with Sheets, Apps Script and Looker Studio.
• TimeTracking API key with scope read (enough for /days, /claims, /people, /clients, /placements, /exports/runs).

4. Entity mapping

TimeTracking	Sheet tab	Columns
GET /days items	days	id, work_date, placement_id, person_id, minutes, hours, status, has_conflict, resolution, export_run_id, updated_at
GET /placements?limit=500 items	placements	id, ref, role_title, person_id, client_id, active
GET /clients?limit=500 items	clients	id, name, external_ref, approval_mode
GET /people?limit=500 items	people	id, employee_number, first_name, last_name, active

Blend days with placements on placement_id = id, and placements with clients on client_id = id, in Looker Studio.

5. Step-by-step setup

1. Create a Google Sheet with tabs days, placements, clients, people.
2. Extensions → Apps Script; Project Settings → Script properties TT_API_KEY = tt_live_… (read key).
3. Paste:

const TT = "https://app.timetracking.tech/api/v1";
const DAYS_BACK = 120;

function ttGet(path) {
  const res = UrlFetchApp.fetch(TT + path, {
    headers: { Authorization: "Bearer " + PropertiesService.getScriptProperties().getProperty("TT_API_KEY") },
    muteHttpExceptions: true,
  });
  if (res.getResponseCode() === 429) { Utilities.sleep(60000); return ttGet(path); }
  const json = JSON.parse(res.getContentText());
  if (res.getResponseCode() >= 300) throw new Error(path + " " + res.getResponseCode() + " " + JSON.stringify(json.error));
  return json.data;
}

function ttAll(path) {               // path already has a "?"
  let items = [], cursor = null;
  do {
    const page = ttGet(path + (cursor ? "&cursor=" + encodeURIComponent(cursor) : ""));
    items = items.concat(page.items);
    cursor = page.next_cursor;
  } while (cursor);
  return items;
}

function write(tab, cols, items, extra) {
  const rows = [cols.concat(extra ? Object.keys(extra) : [])];
  for (const it of items) rows.push(cols.map((c) => it[c] ?? "").concat(extra ? Object.values(extra).map((f) => f(it)) : []));
  const sh = SpreadsheetApp.getActive().getSheetByName(tab);
  sh.clearContents();
  sh.getRange(1, 1, rows.length, rows[0].length).setValues(rows);
}

function refreshAll() {
  const iso = (d) => Utilities.formatDate(d, "UTC", "yyyy-MM-dd");
  const to = new Date(), from = new Date(Date.now() - DAYS_BACK * 86400000);
  write("days", ["id","work_date","placement_id","person_id","minutes","status","has_conflict","resolution","export_run_id","updated_at"],
    ttAll(`/days?from=${iso(from)}&to=${iso(to)}&limit=500`), { hours: (it) => (it.minutes == null ? "" : it.minutes / 60) });
  write("placements", ["id","ref","role_title","person_id","client_id","active"], ttAll("/placements?limit=500"));
  write("clients", ["id","name","external_ref","approval_mode"], ttAll("/clients?limit=500"));
  write("people", ["id","employee_number","first_name","last_name","active"], ttAll("/people?limit=500"));
}

1. Run refreshAll once (authorise), then add a daily time-driven trigger.
2. Looker Studio: Create → Data source → Google Sheets for each tab; set work_date as Date and hours as Number (SUM); blend as in section 4.

6. Sync schedule and triggering events

Daily or hourly trigger. Webhooks are not needed. For long histories, keep older months in a separate tab and refresh only recent days (or use updated_since and merge on id).

7. Corrections, conflicts and locked periods

The script rewrites the window each run, so corrections, approvals and client changes are picked up. Conflicted days have empty minutes (has_conflict = TRUE). Exported/locked days no longer change. GET /days includes days of inactive placements.

8. Testing checklist

• Sheet totals equal GET /days sums for a sample week.
• Paging terminates and row counts match.
• Trigger stays within Apps Script quotas (6 min/run, UrlFetch daily quota).
• Key stored in Script properties with read scope only.

9. Troubleshooting

Problem	Fix
401	Wrong or revoked key.
403 "API key is read-only"	Only export preview (POST /exports) needs a write key.
422 "Invalid cursor"	Do not reuse cursors across endpoints.
422 on /days	from and to are required.
Script timeout	Lower DAYS_BACK or split by month.

10. Limits and known gaps

Leave (GET /leave) and expenses (GET /expenses) can be added with the same read key; no hour-type split outside committed export runs (GET /exports/runs/{id} → pay_lines); a bulk BI feed is Planned.

11. Build it with Claude

Write a Google Apps Script that loads TimeTracking data into Google Sheets for Looker Studio. API base
https://app.timetracking.tech/api/v1, Bearer read key from Script properties. Paginate {data:{items, next_cursor}}
with cursor=. Tabs: days from GET /days?from&to&limit=500 (last 120 days), placements, clients, people from
GET /…?limit=500. Handle 429 by sleeping 60 s, stay under 6 minutes, add a daily trigger installer, and describe the
Looker Studio blends (days.placement_id = placements.id, placements.client_id = clients.id) plus three starter charts.