# Google Sheets export

Sync a safe read-only view of HollyHR people and time-off data into a Google
Sheet. This is useful for lightweight reporting, operations trackers, or a
spreadsheet-native team that wants HollyHR data in one place.

This recipe exports data from HollyHR into a sheet. It does not import people or
time off into HollyHR.

## What it reads

- `GET /people`
- `GET /time-off`

You can also read reference-data endpoints if your own sheet needs lookup
tables for custom reporting, but this starter example only needs people and
time-off records.

Optional reference endpoints:

- `GET /reference/time-off-categories`
- `GET /reference/time-off-statuses`

## Apps Script setup

Create a Google Sheet, then open **Extensions > Apps Script**. Add script
properties for:

- `HOLLYHR_API_BASE_URL`
- `HOLLYHR_API_TOKEN`

Use your organisation's HollyHR app origin, for example:

```text
https://{workspace}.hollyhr.com/api/v1
```

## Apps Script example

```js
function holly(path) {
  const props = PropertiesService.getScriptProperties();
  const baseUrl = props.getProperty("HOLLYHR_API_BASE_URL");
  const token = props.getProperty("HOLLYHR_API_TOKEN");

  if (!baseUrl || !token) {
    throw new Error("Missing HOLLYHR_API_BASE_URL or HOLLYHR_API_TOKEN script property");
  }

  const response = UrlFetchApp.fetch(baseUrl + path, {
    headers: {
      Authorization: "Bearer " + token,
      Accept: "application/json",
    },
    muteHttpExceptions: true,
  });

  if (response.getResponseCode() >= 400) {
    throw new Error("HollyHR API request failed: " + response.getContentText());
  }

  return JSON.parse(response.getContentText());
}

function listAll(path) {
  const items = [];
  let cursor = null;

  do {
    const separator = path.indexOf("?") === -1 ? "?" : "&";
    const page = holly(path + (cursor ? separator + "cursor=" + encodeURIComponent(cursor) : ""));
    items.push.apply(items, page.data);
    cursor = page.pagination && page.pagination.next_cursor;
  } while (cursor);

  return items;
}

function writeRows(sheetName, headers, rows) {
  const spreadsheet = SpreadsheetApp.getActive();
  const sheet = spreadsheet.getSheetByName(sheetName) || spreadsheet.insertSheet(sheetName);

  sheet.clearContents();
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);

  if (rows.length > 0) {
    sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);
  }

  sheet.autoResizeColumns(1, headers.length);
}

function syncHollyHr() {
  const people = listAll("/people?limit=100");
  const timeOff = listAll("/time-off?limit=100");
  const peopleById = {};

  people.forEach(function (person) {
    peopleById[person.id] = person;
  });

  writeRows(
    "People",
    ["Display name", "Work email", "Department", "Job title", "Status", "Start date", "Updated at"],
    people.map(function (person) {
      return [
        person.display_name,
        person.work_email,
        person.department ? person.department.name : "",
        person.job_title || "",
        person.status || "",
        person.start_date || "",
        person.updated_at,
      ];
    }),
  );

  writeRows(
    "Time off",
    ["Person", "Type", "Status", "Start date", "End date", "Length", "Updated at"],
    timeOff.map(function (entry) {
      const person = peopleById[entry.person_id];

      return [
        person ? person.display_name : entry.person_id,
        entry.type,
        entry.status || "",
        entry.start_date,
        entry.end_date,
        entry.length,
        entry.updated_at,
      ];
    }),
  );
}
```

## Schedule it

In Apps Script, add a time-driven trigger for `syncHollyHr`. Hourly or daily is
usually enough for spreadsheet reporting. Use `updated_since` later if you need
to reduce polling volume.

## Privacy notes

This recipe uses the default `people:read` directory projection, which excludes
dates of birth and home contact details. Elevated personal profile fields need
`people:personal:read` and a separate detail call per person. Compensation, bank
details, tax identifiers, and document contents are not exposed. Keep the sheet
permissions at least as strict as the HollyHR data it contains.