Build a self-updating leave report in Google Sheets with the Collabin API

Finance wants a quarterly absence overview, a manager wants a capacity sheet, HR keeps a master spreadsheet — sooner or later, leave data ends up in Google Sheets. Instead of copy-pasting, you can have a sheet that fills and refreshes itself from the Collabin REST API using Google Apps Script: no servers, no cost, about thirty lines of code, copy-paste ready.

What you'll need

• A Collabin account on the Pro plan with a read-only API key (see the API quickstart for how to generate one — read-only is all a report needs).
• A Google account and an empty Google Sheet.

Step 1: Open the script editor and store the key safely

In your sheet, choose Extensions → Apps Script. Before writing code, store the API key where it doesn't belong to the code: in the editor open Project Settings → Script Properties, and add a property named COLLABIN_API_KEY with your key as the value. Script Properties stay out of the script itself, out of version history, and out of sight of anyone you share the sheet with.

Step 2: Paste the script

Replace the editor's content with this:

const BASE_URL = 'https://api.collabin.eu';

function syncCollabinLeaves() {
  const apiKey = PropertiesService.getScriptProperties()
    .getProperty('COLLABIN_API_KEY');
  if (!apiKey) {
    throw new Error('Add COLLABIN_API_KEY under Project Settings → Script Properties.');
  }

  const year = new Date().getFullYear();
  const leaves = fetchAll(
    '/v1/leaves?status=APPROVED&start=' + year + '-01-01&end=' + year + '-12-31',
    apiKey
  );

  const rows = leaves.map(function (l) {
    return [
      l.user ? l.user.name : '#' + l.user_id,
      l.leave_type ? l.leave_type.name : '',
      l.start_date.slice(0, 10),
      l.end_date.slice(0, 10),
      l.is_half_day ? 'yes' : 'no',
      l.status
    ];
  });

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Leaves') || ss.insertSheet('Leaves');
  sheet.clearContents();
  sheet.getRange(1, 1, 1, 6)
    .setValues([['Name', 'Type', 'From', 'To', 'Half day', 'Status']]);
  if (rows.length > 0) {
    sheet.getRange(2, 1, rows.length, 6).setValues(rows);
  }
  sheet.getRange('A1:F1').setFontWeight('bold');
}

// Pages through a /v1 list endpoint and returns every item.
function fetchAll(path, apiKey) {
  const sep = path.indexOf('?') === -1 ? '?' : '&';
  const limit = 500; // the API maximum
  let offset = 0;
  let all = [];
  for (;;) {
    const url = BASE_URL + path + sep + 'limit=' + limit + '&offset=' + offset;
    const res = UrlFetchApp.fetch(url, {
      headers: { 'X-API-Key': apiKey }
    });
    const body = JSON.parse(res.getContentText());
    all = all.concat(body.data);
    if (body.data.length === 0 || all.length >= body.total) {
      return all;
    }
    offset += limit;
  }
}

A few things the script gets right that ad-hoc versions usually miss:

• Pagination. List endpoints return at most 500 items per call; fetchAll walks limit/offset until it has everything, so the report stays correct when your history outgrows one page.
• Names come included. The leaves response embeds the user and leave_type objects, so there's no second request or VLOOKUP needed.
• The date filter is an overlap filter — a leave that started in December and ends in January shows up in both years' reports.

Step 3: Run it once and authorize

Select syncCollabinLeaves in the toolbar and hit Run. Google asks you to authorize the script (it needs spreadsheet access and the ability to call an external service) — this happens only once. A few seconds later the Leaves tab holds your data.

Step 4: Make it refresh itself

In the script editor, open Triggers (the alarm-clock icon) and add a trigger: function syncCollabinLeaves, event source Time-driven, e.g. every morning between 6 and 7. From now on the sheet is simply always current — pivot tables, charts, and QUERY() formulas built on the Leaves tab update with it.

Ideas to build on top

• Days-per-person pivot: insert a pivot table on the Leaves tab — rows: Name, values: COUNTA of From — for a quick request count per person.
• Pending requests instead: change status=APPROVED to status=PENDING for an approval-backlog dashboard.
• One person's history: add &user_id=7 to the path to build individual report sheets.
• Incremental syncs: for very large organizations, fetch only what changed using updated_since and merge, instead of rewriting the full sheet.

Mind the rate limit

The API allows 120 requests per minute per key. The script above uses one request per 500 records, so even a sizable history is a handful of calls — but if you build something chattier, prefer larger pages over more requests, and space out triggers rather than syncing every minute.