function main(workbook: ExcelScript.Workbook) {
const sheet = workbook.getActiveWorksheet();
const startRow = 10; // Row 11 in Excel (0-based index)
const usedRange = sheet.getUsedRange();
const values = usedRange.getValues();
// Get the header row at row 11
const headers = values[startRow] as string[];
const dateColIndex = headers.indexOf("Date");
const durationColIndex = headers.indexOf("Duration (Hours)");
const commentColIndex = headers.indexOf("Comment");
const projectColIndex = headers.indexOf("Project");
const activityColIndex = headers.indexOf("Activity");
const nameColIndex = headers.indexOf("Name");
const employeeIdColIndex = headers.indexOf("Employee ID");
if (dateColIndex === -1) throw new Error("No 'Date' column found in header row.");
if (durationColIndex === -1) throw new Error("No 'Duration (Hours)' column found in header row.");
if (projectColIndex === -1) throw new Error("No 'Project' column found in header row.");
// Extract data rows (rows after the header)
const dataRows = values.slice(startRow + 1);
// Process data rows
dataRows.forEach(row => {
const duration = parseFloat(row[durationColIndex] as string);
const project = row[projectColIndex]?.toString().trim();
const activity = row[activityColIndex]?.toString().trim();
if (!isNaN(duration) && duration > 9) {
row[durationColIndex] = 9;
}
if (!isNaN(duration as number) && (duration as number) > 7) {
row[durationColIndex] = 9;
}
// Remove duration for Leave, Weekend, or Public Holiday
if (["Leave", "Weekend", "Public Holiday"].includes(project)) {
row[durationColIndex] = "";
}
if (activity && ["Week-Off", "Week Off"].includes(activity)) {
row[durationColIndex] = "";
row[commentColIndex] = "";
}
if (["Weekend", "Public Holiday"].includes(project)) {
row[commentColIndex] = "";
}
if (["Leave"].includes(project)) {
if (!["Half day"].includes(activity)) {
row[commentColIndex] = "";
}
}
});
// Parse all dates and determine month/year
const allDates: Date[] = [];
dataRows.forEach(row => {
const dateStr = row[dateColIndex]?.toString();
if (dateStr) {
const date = parseDateString(dateStr);
if (!isNaN(date.getTime())) {
allDates.push(date);
}
}
});
if (allDates.length === 0) throw new Error("No valid dates found in data.");
// Get the month and year from the first date
const firstDate = allDates[0];
const month = firstDate.getMonth();
const year = firstDate.getFullYear();
// Determine days in month
const daysInMonth = new Date(year, month + 1, 0).getDate();
// Create a Set of existing dates in DD-MM-YYYY format
const existingDates = new Set<string>();
allDates.forEach(date => {
const dateStr = formatDate(date);
existingDates.add(dateStr);
});
// Pre-calculate all weekends in the month
const weekends: { dateStr: string, dayName: string }[] = [];
for (let day = 1; day <= daysInMonth; day++) {
const date = new Date(year, month, day);
const dayOfWeek = date.getDay();
if (dayOfWeek === 0 || dayOfWeek === 6) { // Sunday or Saturday
weekends.push({
dateStr: formatDate(date),
dayName: dayOfWeek === 0 ? "Sunday" : "Saturday"
});
}
}
// Find missing dates
const missingDates: { dateStr: string, isWeekend: boolean, dayName?: string }[] = [];
for (let day = 1; day <= daysInMonth; day++) {
const date = new Date(year, month, day);
const dateStr = formatDate(date);
if (!existingDates.has(dateStr)) {
const dayOfWeek = date.getDay();
const isWeekend = dayOfWeek === 0 || dayOfWeek === 6;
missingDates.push({
dateStr: dateStr,
isWeekend: isWeekend,
dayName: isWeekend ? (dayOfWeek === 0 ? "Sunday" : "Saturday") : undefined
});
}
}
// Get sample data for employee info (from first row)
const sampleRow = dataRows[0];
const employeeId = sampleRow[employeeIdColIndex];
const name = sampleRow[nameColIndex];
// Create rows for missing dates
const missingRows = missingDates.map(missing => {
const newRow = [...sampleRow] as (string | number)[];
newRow[dateColIndex] = missing.dateStr;
if (missing.isWeekend) {
newRow[projectColIndex] = "Weekend";
if (activityColIndex !== -1) newRow[activityColIndex] = missing.dayName;
newRow[durationColIndex] = "";
} else {
newRow[projectColIndex] = "";
if (activityColIndex !== -1) newRow[activityColIndex] = "";
newRow[durationColIndex] = "";
}
return newRow;
});
// Combine existing and missing rows
const allDataRows = [...dataRows, ...missingRows];
// Sort by Date (proper date comparison)
allDataRows.sort((a, b) => {
const dateA = parseDateString(a[dateColIndex]?.toString());
const dateB = parseDateString(b[dateColIndex]?.toString());
return dateA.getTime() - dateB.getTime();
});
// Sort by Name (alphabetical)
allDataRows.sort((a, b) => {
const nameA = (a[nameColIndex] || '').toString().toLowerCase();
const nameB = (b[nameColIndex] || '').toString().toLowerCase();
return nameA.localeCompare(nameB);
});
allDataRows.forEach(row => {
const project = row[projectColIndex]?.toString().trim();
const activity = row[activityColIndex]?.toString().trim();
if (activity && ["Week-Off", "Week Off"].includes(activity)) {
row[durationColIndex] = "";
row[commentColIndex] = "";
}
if (["Weekend", "Public Holiday"].includes(project)) {
row[commentColIndex] = "";
}
if (["Leave"].includes(project)) {
if (!["Half day"].includes(activity)) {
row[commentColIndex] = "";
}
}
});
// Recombine: everything above row 11 + header + sorted, processed data
const finalValues = [
...values.slice(0, startRow), // rows above header
headers,
...allDataRows
];
// Resize and write back to sheet
const newRange = sheet.getRangeByIndexes(0, 0, finalValues.length, finalValues[0].length);
newRange.setValues(finalValues);
// Center align the Duration (Hours) column
const durationColumnRange = newRange.getColumn(durationColIndex);
durationColumnRange.getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);
// Apply font styling to all cells
const entireRange = sheet.getUsedRange();
entireRange.getFormat().getFont().setName("Aptos");
entireRange.getFormat().getFont().setSize(10);
// Check if all entries in "Comment" column are empty
const allCommentsEmpty = allDataRows.every(row => {
const val = row[commentColIndex];
return val === null || val === undefined || val.toString().trim() === "";
});
const tableName = "Table1";
let targetTable: ExcelScript.Table | undefined;
try {
targetTable = workbook.getTable(tableName);
} catch {
targetTable = undefined;
}
const headerNames = targetTable.getHeaderRowRange().getValues()[0] as string[];
if (targetTable) {
targetTable.convertToRange();
}
if (allCommentsEmpty) {
if (targetTable) {
if (headerNames.includes("Comment")) {
// You can proceed with your logic here
// e.g., convertToRange or delete column logic
const colToDelete = commentColIndex;
const usedRange = sheet.getUsedRange();
sheet.getRangeByIndexes(0, colToDelete, usedRange.getRowCount(), 1).delete(ExcelScript.DeleteShiftDirection.left);
}
}
}
//From here
const updatedUsedRange = sheet.getUsedRange();
const updatedValues = updatedUsedRange.getValues();
const updatedHeaders = updatedValues[startRow] as string[];
const updatedProjectColIndex = updatedHeaders.indexOf("Project");
const updatedActivityColIndex = updatedHeaders.indexOf("Activity");
const updatedDurationColIndex = updatedHeaders.indexOf("Duration (Hours)");
for (let i = startRow + 1; i < updatedValues.length; i++) {
const row = updatedValues[i];
const project = row[updatedProjectColIndex]?.toString().trim();
if (project === "Weekend") {
const rowIndex = i;
// Define range for Activity + Duration columns
const rangeToMerge = sheet.getRangeByIndexes(rowIndex, updatedActivityColIndex, 1, 2);
// Merge and format
rangeToMerge.merge(true); // true = merge and center
rangeToMerge.getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);
// Set merged value to the activity's original value
const value = row[updatedActivityColIndex];
sheet.getCell(rowIndex, updatedActivityColIndex).setValue(value);
}
}
const refreshedRange = sheet.getUsedRange();
const refreshedValues = refreshedRange.getValues();
// Recalculate column indexes after possible structural changes
const refreshedHeaders = refreshedValues[startRow] as string[];
const refreshedProjectColIndex = refreshedHeaders.indexOf("Project");
const refreshedActivityColIndex = refreshedHeaders.indexOf("Activity");
const refreshedDurationColIndex = refreshedHeaders.indexOf("Duration (Hours)");
for (let i = startRow + 1; i < refreshedValues.length; i++) {
const row = refreshedValues[i];
const activity = row[refreshedActivityColIndex]?.toString().trim();
if (activity === "Comp Off Leave") {
const projectCell = sheet.getCell(i, refreshedProjectColIndex);
projectCell.getFormat().getFill().setColor("yellow");
}
}
// Merge Project + Activity + Duration for 'Leave' and 'Public Holiday'
for (let i = startRow + 1; i < refreshedValues.length; i++) {
const row = refreshedValues[i];
const project = row[refreshedProjectColIndex]?.toString().trim();
if (project === "Leave" || project === "Public Holiday") {
const rowIndex = i;
// Merge the 3 columns starting from Project
const rangeToMerge = sheet.getRangeByIndexes(rowIndex, refreshedProjectColIndex, 1, 3);
rangeToMerge.merge(true); // Merge and center
rangeToMerge.getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);
// Set merged value to what's in the Activity column (or adjust if needed)
const value = row[refreshedActivityColIndex];
sheet.getCell(rowIndex, refreshedProjectColIndex).setValue(value);
}
}
// Refresh the used range after all changes (e.g., after deleting 'Comment' column and merging)
const finalUsedRange: ExcelScript.Range = sheet.getUsedRange();
const finalRowCount: number = finalUsedRange.getRowCount();
const finalLastColIndex: number = finalUsedRange.getColumnCount() - 1;
const lastColumn: ExcelScript.Range = finalUsedRange.getLastColumn();
// 2. Get the array of border objects for the entire last column range.
// The editor understands this as returning a `RangeBorder[]`.
const borders: ExcelScript.RangeBorder[] = lastColumn.getFormat().getBorders();
// 3. Loop through the borders in the array (this loop will only run a few times).
for (const border of borders) {
// 4. Check if the current border is the one for the right edge.
if (border.getSideIndex() === ExcelScript.BorderIndex.edgeRight) {
// 5. Apply the formatting.
border.setStyle(ExcelScript.BorderLineStyle.continuous);
border.setWeight(ExcelScript.BorderWeight.thick);
border.setColor("black");
// 6. Exit the loop since we've found and formatted the border we need.
break;
}
}
// Highlight rows with 'Half Day' in the Comment column as yellow (if the column exists)
// Highlight only the 'Comment' cell with 'Half Day' as yellow background (if the column exists)
if (refreshedHeaders.includes("Comment")) {
const refreshedCommentColIndex = refreshedHeaders.indexOf("Comment");
for (let i = startRow + 1; i < refreshedValues.length; i++) {
const row = refreshedValues[i];
const comment = row[refreshedCommentColIndex]?.toString().trim();
if (comment === "Half Day" || comment === "Half day") {
const commentCell = sheet.getCell(i, refreshedCommentColIndex);
commentCell.getFormat().getFill().setColor("yellow");
}
}
}
}
// Helper function to parse DD-MM-YYYY date strings
function parseDateString(dateStr: string | undefined): Date {
if (!dateStr) return new Date(NaN);
const parts = dateStr.split('-');
if (parts.length !== 3) return new Date(NaN);
const day = parseInt(parts[0], 10);
const month = parseInt(parts[1], 10) - 1; // JavaScript months are 0-based
const year = parseInt(parts[2], 10);
return new Date(year, month, day);
}
// Helper function to format date as DD-MM-YYYY
function formatDate(date: Date): string {
return `${date.getDate().toString().padStart(2, '0')}-${(date.getMonth() + 1).toString().padStart(2, '0')}-${date.getFullYear()}`;
}
