FriTTo + Excel: Export time data with one click using Office Scripts

Copy code

/**
 * Fritto Export — Office Script (TypeScript)
 *
 * Reads settings from the "Settings" sheet, calls the TimeTracker API,
 * and populates the "Data" sheet with exported time records.
 *
 * How to use:
 *   1. Open the Excel file in Excel for the web or Excel desktop (Microsoft 365)
 *   2. Go to the "Automate" tab
 *   3. Click "New Script", paste this code, and save
 *   4. Fill in the Settings sheet and click "Run" in the script editor
 */

async function main(workbook: ExcelScript.Workbook): Promise<void> {
    const settingsSheet = workbook.getWorksheet("Settings");
    if (!settingsSheet) {
        throw new Error("Settings sheet not found.");
    }

    // Read inputs
    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();

    // Validate
    if (!apiUrl) throw new Error("API Base URL is required.");
    if (!fromDate) throw new Error("FROM date is required.");
    if (!toDate) throw new Error("TO date is required.");
    if (fromDate > toDate) throw new Error("FROM date must be earlier than or equal to TO date.");
    if (!apiKey) throw new Error("API Key is missing.");

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

    // Fetch data from API
    console.log(`Fetching data from: ${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(`Bad request (HTTP 400). Details: ${body.substring(0, 300)}`);
            case 401:
                throw new Error("Authentication failed. Please check your API Key.");
            case 403:
                throw new Error("Access denied. Your API Key does not have permission to access this data.");
            case 404:
                throw new Error("API endpoint not found. Please check the API Base URL.");
            default:
                throw new Error(`Server error (HTTP ${response.status}). Please try again later.`);
        }
    }

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

    // Populate Data sheet
    const dataSheet = workbook.getWorksheet("Data");
    if (!dataSheet) {
        throw new Error("Data sheet not found.");
    }

    // Clear existing data
    const usedRange = dataSheet.getUsedRange();
    if (usedRange) {
        usedRange.clear();
    }

    // Remove existing auto-filter
    const existingFilter = dataSheet.getAutoFilter();
    if (existingFilter.getIsDataFiltered()) {
        existingFilter.remove();
    }

    if (items.length === 0) {
        console.log("No data found for the specified date range.");
        return;
    }

    // Headers
    const headers = [
        "Date", "Client", "Project", "Description", "Task URL",
        "Tracked Minutes", "Billable?", "Approved?",
        "First Name", "Last Name", "Employee Groups", "Cost Rate", "Currency",
    ];

    // Write headers
    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);

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

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

    // Auto-fit columns
    dataSheet.getUsedRange()?.getFormat().autofitColumns();

    // Apply auto-filter
    dataSheet.getAutoFilter().apply(dataSheet.getUsedRange());

    console.log(`${items.length} records exported successfully.`);
}

/** Convert Excel cell value (string, number, or Date serial) to yyyy-mm-dd string. */
function formatDateValue(value: string | number | boolean): string {
    if (value === null || value === undefined || value === "") return "";

    if (typeof value === "number") {
        // Excel date serial number -> 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();
}

/** Safely convert a cell value to string. */
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;
}