Using Office Script, generate a default yearly report for all months sheet with columns: date, task, description, hours and issues.

generateYearlySheets.osts

function main(workbook: ExcelScript.Workbook) {
    const months: string[] = [
        "January", "February", "March", "April", "May", "June",
        "July", "August", "September", "October", "November", "December"
    ];

    const headers: string[] = ["Date", "Task", "Description", "Consumed Hours", "Any Issue", "Total Hours"];
    const columnWidths: number[] = [60, 300, 200, 100, 200, 100]; // Updated column sizes

    // Get all existing worksheets
    const sheets: ExcelScript.Worksheet[] = workbook.getWorksheets();

    // Delete all sheets except the first one
    for (let i = sheets.length - 1; i > 0; i--) {
        sheets[i].delete();
    }

    // Reset last sheet to default name
    sheets[0].setName("Sheet1");

    months.forEach((month, monthIndex) => {
        const sheet: ExcelScript.Worksheet = workbook.addWorksheet(month);

        // === HEADER ROW ===
        const range: ExcelScript.Range = sheet.getRange("A1:F1");
        range.setValues([headers]);
        range.getFormat().getFill().setColor("#145f82"); // Dark blue header
        range.getFormat().getFont().setColor("white");
        range.getFormat().getFont().setBold(true);

        // Apply filters to headers
        sheet.getAutoFilter()?.apply(range);

        // Set default column widths
        sheet.getRange("A:A").getFormat().setColumnWidth(columnWidths[0]);
        sheet.getRange("B:B").getFormat().setColumnWidth(columnWidths[1]);
        sheet.getRange("C:C").getFormat().setColumnWidth(columnWidths[2]);
        sheet.getRange("D:D").getFormat().setColumnWidth(columnWidths[3]);
        sheet.getRange("E:E").getFormat().setColumnWidth(columnWidths[4]);
        sheet.getRange("F:F").getFormat().setColumnWidth(columnWidths[5]);

        let date: Date = new Date(new Date().getFullYear(), monthIndex, 1);
        let rowIndex: number = 2; // Start filling from row 3 (after headers)

        while (date.getMonth() === monthIndex) {
            const formattedDate: string = date.toLocaleDateString("en-GB", { day: "numeric", month: "short", year: "numeric" });
            const isWeekend: boolean = date.getDay() === 0 || date.getDay() === 6;

            const rowRange: ExcelScript.Range = sheet.getRange(`A${rowIndex}:E${rowIndex}`);
            rowRange.setValues([[formattedDate, "", isWeekend ? "Weekend" : "", "", ""]]);

            if (isWeekend) {
                rowRange.getFormat().getFill().setColor("#FFCCCC"); // Light red for weekends
                sheet.getRange(`C${rowIndex}`).setValue("Weekend");
            } else {
                const backgroundColor = rowIndex % 2 === 0 ? "#82cbed" : "#bfe4f5"; // Alternating colors for weekdays
                rowRange.getFormat().getFill().setColor(backgroundColor);
            }

            date.setDate(date.getDate() + 1);
            rowIndex++;
        }

        // === APPLY WHITE BORDERS TO ALL ROWS AND COLUMNS (EXCEPT HEADERS) ===
        const dataRange: ExcelScript.Range = sheet.getRange(`A2:E${rowIndex - 1}`);
        const borderStyle: ExcelScript.BorderLineStyle = ExcelScript.BorderLineStyle.continuous;
        const borderWeight: ExcelScript.BorderWeight = ExcelScript.BorderWeight.thin
        const borderColor: string = "white";
        let format = dataRange.getFormat();

        // Get a RangeBorder object for each edge of the range and set the border properties.
        let edgeTop = format.getRangeBorder(ExcelScript.BorderIndex.edgeTop);
        edgeTop.setStyle(borderStyle);
        edgeTop.setWeight(borderWeight);
        edgeTop.setColor(borderColor);

        let edgeBottom = format.getRangeBorder(ExcelScript.BorderIndex.edgeBottom);
        edgeBottom.setStyle(borderStyle);
        edgeBottom.setWeight(borderWeight);
        edgeBottom.setColor(borderColor);

        let edgeLeft = format.getRangeBorder(ExcelScript.BorderIndex.edgeLeft);
        edgeLeft.setStyle(borderStyle);
        edgeLeft.setWeight(borderWeight);
        edgeLeft.setColor(borderColor);

        let edgeRight = format.getRangeBorder(ExcelScript.BorderIndex.edgeRight);
        edgeRight.setStyle(borderStyle);
        edgeRight.setWeight(borderWeight);
        edgeRight.setColor(borderColor);

        // Apply borders to inside vertical and horizontal lines
        let insideVertical = format.getRangeBorder(ExcelScript.BorderIndex.insideVertical);
        insideVertical.setStyle(borderStyle);
        insideVertical.setWeight(borderWeight);
        insideVertical.setColor(borderColor);

        let insideHorizontal = format.getRangeBorder(ExcelScript.BorderIndex.insideHorizontal);
        insideHorizontal.setStyle(borderStyle);
        insideHorizontal.setWeight(borderWeight);
        insideHorizontal.setColor(borderColor);

        // Define the formula to sum all timestamps in column D and return "00:00:00" if no values
        let formula = '=IF(SUM(D2:D40)=0, "00:00:00", TEXT(SUM(D2:D40), "[hh]:mm:ss"))';
        let totalHoursCell = sheet.getRange("F2");

        // Set the formula in cell F2
        totalHoursCell.getFormat().getFill().setColor("#82cbed");
        totalHoursCell.setFormula(formula);
    });

    // Delete the default "Sheet1" if it exists
    try {
        workbook.getWorksheet("Sheet1")?.delete();
    } catch (error: unknown) {
        console.log("Sheet1 not found or already deleted.");
    }
}