Stwórz samoaktualizujący się raport urlopów w Google Sheets za pomocą API Collabin

Finanse chcą kwartalny przegląd nieobecności, menedżer potrzebuje arkusza obciążenia zespołu, HR prowadzi główny arkusz — wcześniej czy później dane o urlopach trafiają do Google Sheets. Zamiast kopiować i wklejać, możesz mieć arkusz, który sam się wypełnia i odświeża danymi z REST API Collabin za pomocą Google Apps Script: bez serwerów, bez kosztów, około trzydziestu linii kodu, gotowych do skopiowania.

Czego będziesz potrzebować

• Konta Collabin w planie Pro z kluczem API tylko do odczytu (jak go wygenerować, opisuje przewodnik API quickstart — do raportu wystarczy klucz tylko do odczytu).
• Konta Google i pustego arkusza Google Sheets.

Krok 1: Otwórz edytor skryptów i bezpiecznie zapisz klucz

W arkuszu wybierz Rozszerzenia → Apps Script. Przed napisaniem kodu zapisz klucz API tam, gdzie nie jest częścią kodu: w edytorze otwórz Ustawienia projektu → Właściwości skryptu i dodaj właściwość o nazwie COLLABIN_API_KEY, jako wartość podając swój klucz. Właściwości skryptu nie znajdują się w samym kodzie, w historii wersji, ani nie są widoczne dla osób, z którymi udostępnisz arkusz.

Krok 2: Wklej skrypt

Zamień zawartość edytora na poniższy kod:

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

function syncCollabinLeaves() {
  const apiKey = PropertiesService.getScriptProperties()
    .getProperty('COLLABIN_API_KEY');
  if (!apiKey) {
    throw new Error('Dodaj COLLABIN_API_KEY w Ustawienia projektu → Właściwości skryptu.');
  }

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

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Urlopy') || ss.insertSheet('Urlopy');
  sheet.clearContents();
  sheet.getRange(1, 1, 1, 6)
    .setValues([['Imię i nazwisko', 'Typ', 'Od', 'Do', 'Pół dnia', 'Status']]);
  if (rows.length > 0) {
    sheet.getRange(2, 1, rows.length, 6).setValues(rows);
  }
  sheet.getRange('A1:F1').setFontWeight('bold');
}

// Przechodzi przez endpoint listy /v1 i zwraca wszystkie elementy.
function fetchAll(path, apiKey) {
  const sep = path.indexOf('?') === -1 ? '?' : '&';
  const limit = 500; // maksimum 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;
  }
}

Kilka rzeczy, które ten skrypt robi dobrze, a które rozwiązania ad hoc zwykle przeoczają:

• Paginacja. Endpointy list zwracają maksymalnie 500 elementów na wywołanie; fetchAll przechodzi przez parametry limit/offset, aż pobierze wszystko, dzięki czemu raport pozostaje poprawny, gdy historia przekroczy jedną stronę.
• Nazwy są już dołączone. Odpowiedź dla urlopów zawiera zagnieżdżone obiekty user i leave_type, więc nie jest potrzebne drugie zapytanie ani funkcja WYSZUKAJ.PIONOWO.
• Filtr dat działa na zasadzie nakładania się okresów — urlop, który zaczął się w grudniu, a kończy w styczniu, pojawi się w raportach obu lat.

Krok 3: Uruchom raz i zatwierdź uprawnienia

Wybierz syncCollabinLeaves na pasku narzędzi i kliknij Uruchom. Google poprosi o autoryzację skryptu (potrzebuje dostępu do arkusza i możliwości wywołania zewnętrznej usługi) — to dzieje się tylko raz. Po kilku sekundach zakładka Urlopy będzie zawierać dane.

Krok 4: Skonfiguruj automatyczne odświeżanie

W edytorze skryptów otwórz Wyzwalacze (ikona budzika) i dodaj wyzwalacz: funkcja syncCollabinLeaves, źródło zdarzenia Czasowe, np. każdego ranka między 6 a 7. Od teraz arkusz jest zawsze aktualny — tabele przestawne, wykresy i formuły QUERY() oparte na zakładce Urlopy aktualizują się wraz z nim.

Co można dobudować

• Tabela przestawna dni na osobę: wstaw tabelę przestawną w zakładce Urlopy — wiersze: Imię i nazwisko, wartości: ILE.NIEPUSTYCH z kolumny Od — szybka liczba wniosków na osobę.
• Wnioski oczekujące: zmień status=APPROVED na status=PENDING, aby uzyskać panel zaległości w zatwierdzaniu.
• Historia jednej osoby: dodaj &user_id=7 do ścieżki, aby tworzyć indywidualne arkusze raportów.
• Synchronizacja przyrostowa: w bardzo dużych organizacjach pobieraj tylko zmiany za pomocą updated_since i scalaj je, zamiast przepisywać cały arkusz.

Pamiętaj o limicie zapytań

API pozwala na 120 zapytań na minutę na klucz. Powyższy skrypt wykonuje jedno zapytanie na 500 rekordów, więc nawet duża historia to tylko kilka wywołań — ale jeśli budujesz coś bardziej „rozmowne", wybieraj większe strony zamiast większej liczby zapytań i rozkładaj wyzwalacze w czasie, a nie synchronizuj co minutę.