Einen sich selbst aktualisierenden Urlaubsbericht in Google Sheets mit der Collabin API erstellen

Die Finanzabteilung will eine quartalsweise Abwesenheitsübersicht, ein Manager braucht ein Kapazitätsblatt, HR führt eine Master-Tabelle — früher oder später landen Urlaubsdaten in Google Sheets. Statt Copy-Paste können Sie ein Tabellenblatt haben, das sich selbst befüllt und aktualisiert — über die Collabin REST-API mit Google Apps Script: keine Server, keine Kosten, etwa dreißig Zeilen Code, kopierfertig.

Was Sie benötigen

• Ein Collabin-Konto im Plan Pro mit einem API-Schlüssel mit Lesezugriff (wie Sie einen erstellen, erfahren Sie im API-Schnellstart — für einen Bericht reicht Lesezugriff aus).
• Ein Google-Konto und ein leeres Google Sheet.

Schritt 1: Skripteditor öffnen und den Schlüssel sicher speichern

Wählen Sie in Ihrem Tabellenblatt Erweiterungen → Apps Script. Bevor Sie Code schreiben, speichern Sie den API-Schlüssel an einer Stelle, die nicht zum Code gehört: Öffnen Sie im Editor Projekteinstellungen → Skripteigenschaften und fügen Sie eine Eigenschaft namens COLLABIN_API_KEY mit Ihrem Schlüssel als Wert hinzu. Skripteigenschaften gehören nicht zum Skript selbst, erscheinen nicht im Versionsverlauf und sind für niemanden sichtbar, mit dem Sie das Tabellenblatt teilen.

Schritt 2: Skript einfügen

Ersetzen Sie den Inhalt des Editors durch Folgendes:

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

function syncCollabinLeaves() {
  const apiKey = PropertiesService.getScriptProperties()
    .getProperty('COLLABIN_API_KEY');
  if (!apiKey) {
    throw new Error('Fügen Sie COLLABIN_API_KEY unter Projekteinstellungen → Skripteigenschaften hinzu.');
  }

  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 ? 'ja' : 'nein',
      l.status
    ];
  });

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Urlaube') || ss.insertSheet('Urlaube');
  sheet.clearContents();
  sheet.getRange(1, 1, 1, 6)
    .setValues([['Name', 'Typ', 'Von', 'Bis', 'Halber Tag', 'Status']]);
  if (rows.length > 0) {
    sheet.getRange(2, 1, rows.length, 6).setValues(rows);
  }
  sheet.getRange('A1:F1').setFontWeight('bold');
}

// Durchläuft einen /v1-Listen-Endpunkt und gibt alle Einträge zurück.
function fetchAll(path, apiKey) {
  const sep = path.indexOf('?') === -1 ? '?' : '&';
  const limit = 500; // das 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;
  }
}

Ein paar Dinge macht das Skript richtig, die Ad-hoc-Lösungen oft übersehen:

• Paginierung. Listen-Endpunkte liefern pro Aufruf maximal 500 Einträge zurück; fetchAll durchläuft limit/offset, bis alles vorliegt, sodass der Bericht korrekt bleibt, wenn Ihre Historie über eine Seite hinauswächst.
• Namen sind bereits enthalten. Die Antwort für Urlaube enthält die eingebetteten Objekte user und leave_type, sodass keine zweite Anfrage oder ein SVERWEIS nötig ist.
• Der Datumsfilter ist ein Überlappungsfilter — ein Urlaub, der im Dezember beginnt und im Januar endet, erscheint in den Berichten beider Jahre.

Schritt 3: Einmal ausführen und autorisieren

Wählen Sie in der Symbolleiste syncCollabinLeaves aus und klicken Sie auf Ausführen. Google fordert Sie auf, das Skript zu autorisieren (es benötigt Zugriff auf das Tabellenblatt und die Möglichkeit, einen externen Dienst aufzurufen) — das passiert nur einmal. Wenige Sekunden später enthält die Registerkarte Urlaube Ihre Daten.

Schritt 4: Automatische Aktualisierung einrichten

Öffnen Sie im Skripteditor Trigger (das Wecker-Symbol) und fügen Sie einen Trigger hinzu: Funktion syncCollabinLeaves, Ereignisquelle Zeitgesteuert, z. B. jeden Morgen zwischen 6 und 7 Uhr. Ab sofort ist das Tabellenblatt einfach immer aktuell — Pivot-Tabellen, Diagramme und QUERY()-Formeln, die auf der Registerkarte Urlaube basieren, aktualisieren sich mit.

Ideen zum Ausbau

• Pivot-Tabelle Tage pro Person: Fügen Sie auf der Registerkarte Urlaube eine Pivot-Tabelle ein — Zeilen: Name, Werte: COUNTA von Von — für eine schnelle Anzahl der Anträge pro Person.
• Stattdessen ausstehende Anträge: Ändern Sie status=APPROVED zu status=PENDING für ein Dashboard mit dem Genehmigungsrückstand.
• Historie einer einzelnen Person: Fügen Sie &user_id=7 zum Pfad hinzu, um individuelle Berichtsblätter zu erstellen.
• Inkrementelle Synchronisierung: Bei sehr großen Organisationen rufen Sie nur Änderungen mit updated_since ab und führen sie zusammen, statt das gesamte Tabellenblatt neu zu schreiben.

Ratenlimit beachten

Die API erlaubt 120 Anfragen pro Minute pro Schlüssel. Das obige Skript verwendet eine Anfrage pro 500 Datensätze, sodass auch eine umfangreiche Historie nur eine Handvoll Aufrufe benötigt — bauen Sie jedoch etwas „gesprächigeres", bevorzugen Sie größere Seiten gegenüber mehr Anfragen und verteilen Sie Trigger zeitlich, statt jede Minute zu synchronisieren.