# Looker Studio

> Report on TimeTracking hours in Looker Studio by loading GET /days and master data into Google Sheets with Apps Script and a read-only key.

URL: https://timetracking.tech/developers/integrations/bi/looker-studio
Last updated: 2026-10-24

## 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:

```js
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"));
}
```

4. Run `refreshAll` once (authorise), then add a daily time-driven trigger.
5. 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

```text
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.
```