Vytvorenie samoaktualizujúceho sa reportu dovoleniek v Google Sheets pomocou API Collabin

Financie chcú kvartálny prehľad absencií, manažér potrebuje kapacitný hárok, HR si vedie hlavnú tabuľku — skôr či neskôr sa údaje o dovolenkách ocitnú v Google Sheets. Namiesto kopírovania a vkladania môžete mať hárok, ktorý sa sám vyplní a obnoví z REST API Collabin pomocou Google Apps Script: bez serverov, bez nákladov, približne tridsať riadkov kódu, pripravených na kopírovanie.

Čo budete potrebovať

Účet Collabin v pláne Pro s API kľúčom iba na čítanie (ako ho vygenerovať, nájdete v návode API Quickstart — na report stačí kľúč iba na čítanie).
Účet Google a prázdny Google Sheet.

Krok 1: Otvorte editor skriptov a bezpečne uložte kľúč

Vo svojom hárku vyberte Rozšírenia → Apps Script. Skôr než napíšete kód, uložte API kľúč tam, kde nie je súčasťou kódu: v editore otvorte Nastavenia projektu → Vlastnosti skriptu a pridajte vlastnosť s názvom COLLABIN_API_KEY, ako hodnotu zadajte svoj kľúč. Vlastnosti skriptu nie sú súčasťou samotného skriptu, nie sú v histórii verzií a nevidí ich nikto, s kým hárok zdieľate.

Krok 2: Vložte skript

Nahraďte obsah editora týmto kódom:

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

function syncCollabinLeaves() {
  const apiKey = PropertiesService.getScriptProperties()
    .getProperty('COLLABIN_API_KEY');
  if (!apiKey) {
    throw new Error('Pridajte COLLABIN_API_KEY do Nastavenia projektu → Vlastnosti skriptu.');
  }

  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 ? 'áno' : 'nie',
      l.status
    ];
  });

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Dovolenky') || ss.insertSheet('Dovolenky');
  sheet.clearContents();
  sheet.getRange(1, 1, 1, 6)
    .setValues([['Meno', 'Typ', 'Od', 'Do', 'Pol dňa', 'Status']]);
  if (rows.length > 0) {
    sheet.getRange(2, 1, rows.length, 6).setValues(rows);
  }
  sheet.getRange('A1:F1').setFontWeight('bold');
}

// Prechádza zoznamový endpoint /v1 a vracia všetky položky.
function fetchAll(path, apiKey) {
  const sep = path.indexOf('?') === -1 ? '?' : '&';
  const limit = 500; // maximum API
  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;
  }
}

Niekoľko vecí, ktoré tento skript robí správne a ktoré ad-hoc riešenia bežne prehliadajú:

Stránkovanie. Zoznamové endpointy vracajú maximálne 500 položiek na jedno volanie; fetchAll prechádza parametrami limit/offset, kým nezíska všetko, takže report zostáva správny aj keď vaša história prekročí jednu stranu.
Mená sú už súčasťou odpovede. Odpoveď pre dovolenky obsahuje vnorené objekty user a leave_type, takže nie je potrebná druhá požiadavka ani funkcia VLOOKUP.
Filter dátumov je filter na základe prekrytia — dovolenka, ktorá začala v decembri a končí v januári, sa zobrazí v reportoch oboch rokov.

Krok 3: Spustite ho raz a autorizujte

Na paneli nástrojov vyberte syncCollabinLeaves a kliknite na Spustiť. Google vás vyzve na autorizáciu skriptu (potrebuje prístup k hárku a možnosť volať externú službu) — toto sa stane iba raz. O pár sekúnd neskôr karta Dovolenky obsahuje vaše údaje.

Krok 4: Nastavte automatické obnovovanie

V editore skriptov otvorte Spúšťače (ikona budíka) a pridajte spúšťač: funkcia syncCollabinLeaves, zdroj udalosti Časovo riadené, napríklad každé ráno medzi 6. a 7. hodinou. Odteraz je hárok jednoducho vždy aktuálny — kontingenčné tabuľky, grafy a vzorce QUERY() postavené na karte Dovolenky sa aktualizujú spolu s ním.

Nápady na rozšírenie

Kontingenčná tabuľka dní na osobu: vložte kontingenčnú tabuľku na karte Dovolenky — riadky: Meno, hodnoty: COUNTA stĺpca Od — pre rýchly počet žiadostí na osobu.
Čakajúce žiadosti: zmeňte status=APPROVED na status=PENDING a získate prehľad nevybavených schválení.
História jednej osoby: pridajte &user_id=7 do cesty a vytvorte individuálne reportové hárky.
Inkrementálna synchronizácia: pre veľmi veľké organizácie načítajte iba zmeny pomocou updated_since a zlúčte ich, namiesto prepisovania celého hárku.

Pozor na limit požiadaviek

API umožňuje 120 požiadaviek za minútu na kľúč. Vyššie uvedený skript použije jednu požiadavku na 500 záznamov, takže aj rozsiahla história predstavuje len zopár volaní — ale ak vytvoríte niečo „zhovorčivejšie", dávajte prednosť väčším stranám pred väčším počtom požiadaviek a rozložte spúšťače v čase, namiesto synchronizácie každú minútu.