FriTTo + Excel: Zeitdaten auf Knopfdruck mit Office Scripts exportieren

/**
 * Fritto Export — Office Script (TypeScript) — Deutsche Version
 *
 * Liest Einstellungen vom Blatt "Einstellungen", ruft die TimeTracker-API auf
 * und füllt das Blatt "Daten" mit den exportierten Zeiteinträgen.
 *
 * Verwendung:
 *   1. Öffnen Sie die Excel-Datei in Excel für das Web oder Excel Desktop (Microsoft 365)
 *   2. Gehen Sie zum Reiter "Automatisieren"
 *   3. Klicken Sie auf "Neues Skript", fügen Sie diesen Code ein und speichern Sie
 *   4. Füllen Sie das Blatt "Einstellungen" aus und klicken Sie im Skript-Editor auf "Ausführen"
 */

async function main(workbook: ExcelScript.Workbook): Promise<void> {
    const settingsSheet = workbook.getWorksheet("Einstellungen");
    if (!settingsSheet) {
        throw new Error("Blatt 'Einstellungen' nicht gefunden.");
    }

    // Eingaben lesen
    const apiUrl = toString(settingsSheet.getRange("B1").getValue()).trim();
    const fromDate = formatDateValue(settingsSheet.getRange("B2").getValue());
    const toDate = formatDateValue(settingsSheet.getRange("B3").getValue());
    const apiKey = toString(settingsSheet.getRange("B4").getValue()).trim();

    // Validierung
    if (!apiUrl) throw new Error("API-Basis-URL ist erforderlich.");
    if (!fromDate) throw new Error("VON Datum ist erforderlich.");
    if (!toDate) throw new Error("BIS Datum ist erforderlich.");
    if (fromDate > toDate) throw new Error("VON Datum muss vor oder gleich dem BIS Datum liegen.");
    if (!apiKey) throw new Error("API-Schlüssel fehlt.");

    // Anfrage-URL erstellen
    const baseUrl = apiUrl.replace(/\/+$/, "");
    const requestUrl = `${baseUrl}/api/app/export-to-excel?From=${fromDate}&To=${toDate}`;

    // Daten von API abrufen
    console.log(`Daten werden abgerufen von: ${requestUrl}`);

    const response = await fetch(requestUrl, {
        method: "GET",
        headers: {
            "X-Api-Key": apiKey,
            "Accept": "application/json",
        },
    });

    if (!response.ok) {
        const body = await response.text();
        switch (response.status) {
            case 400:
                throw new Error(`Ungültige Anfrage (HTTP 400). Details: ${body.substring(0, 300)}`);
            case 401:
                throw new Error("Authentifizierung fehlgeschlagen. Bitte überprüfen Sie Ihren API-Schlüssel.");
            case 403:
                throw new Error("Zugriff verweigert. Ihr API-Schlüssel hat keine Berechtigung für diese Daten.");
            case 404:
                throw new Error("API-Endpunkt nicht gefunden. Bitte überprüfen Sie die API-Basis-URL.");
            default:
                throw new Error(`Serverfehler (HTTP ${response.status}). Bitte versuchen Sie es später erneut.`);
        }
    }

    const result: { items: ExportEntry[] } = await response.json();
    const items = result.items;

    // Daten-Blatt befüllen
    const dataSheet = workbook.getWorksheet("Daten");
    if (!dataSheet) {
        throw new Error("Blatt 'Daten' nicht gefunden.");
    }

    // Vorhandene Daten löschen
    const usedRange = dataSheet.getUsedRange();
    if (usedRange) {
        usedRange.clear();
    }

    // Vorhandenen Auto-Filter entfernen
    const existingFilter = dataSheet.getAutoFilter();
    if (existingFilter.getIsDataFiltered()) {
        existingFilter.remove();
    }

    if (items.length === 0) {
        console.log("Keine Daten für den angegebenen Zeitraum gefunden.");
        return;
    }

    // Spaltenüberschriften
    const headers = [
        "Datum", "Kunde", "Projekt", "Beschreibung", "Aufgaben-URL",
        "Erfasste Minuten", "Abrechenbar?", "Genehmigt?",
        "Vorname", "Nachname", "Mitarbeitergruppen", "Kostensatz", "Währung",
    ];

    // Überschriften schreiben
    const headerRange = dataSheet.getRangeByIndexes(0, 0, 1, headers.length);
    headerRange.setValues([headers]);
    headerRange.getFormat().getFont().setBold(true);
    headerRange.getFormat().getFill().setColor("#4472C4");
    headerRange.getFormat().getFont().setColor("#FFFFFF");
    headerRange.getFormat().setRowHeight(30);

    // Datenzeilen erstellen
    const rows: (string | number)[][] = items.map((item) => [
        item.date ?? "",
        item.client ?? "",
        item.project ?? "",
        item.description ?? "",
        item.taskUrl ?? "",
        item.trackedMinutes ?? 0,
        item.isBillable ? "Ja" : "Nein",
        item.isApproved ? "Ja" : "Nein",
        item.firstName ?? "",
        item.lastName ?? "",
        item.employeeGroups ?? "",
        item.costRate ?? "",
        item.currency ?? "",
    ]);

    // Daten schreiben
    const dataRange = dataSheet.getRangeByIndexes(1, 0, rows.length, headers.length);
    dataRange.setValues(rows);

    // Spaltenbreite automatisch anpassen
    dataSheet.getUsedRange()?.getFormat().autofitColumns();

    // Auto-Filter anwenden
    dataSheet.getAutoFilter().apply(dataSheet.getUsedRange());

    console.log(`${items.length} Einträge erfolgreich exportiert.`);
}

/** Excel-Zellwert (String, Zahl oder Datumsseriennummer) in yyyy-mm-dd String umwandeln. */
function formatDateValue(value: string | number | boolean): string {
    if (value === null || value === undefined || value === "") return "";

    if (typeof value === "number") {
        // Excel-Datumsseriennummer -> yyyy-mm-dd
        const date = new Date(Math.round((value - 25569) * 86400 * 1000));
        const y = date.getUTCFullYear();
        const m = String(date.getUTCMonth() + 1).padStart(2, "0");
        const d = String(date.getUTCDate()).padStart(2, "0");
        return `${y}-${m}-${d}`;
    }

    return value.toString().trim();
}

/** Zellwert sicher in String umwandeln. */
function toString(value: string | number | boolean): string {
    if (value === null || value === undefined) return "";
    return value.toString();
}

interface ExportEntry {
    date: string;
    client: string;
    project: string;
    description: string;
    taskUrl: string;
    trackedMinutes: number;
    isBillable: boolean;
    isApproved: boolean;
    userId: string;
    firstName: string;
    lastName: string;
    employeeGroups: string;
    costRate: number | null;
    currency: string | null;
}