function formatDailyWork() {
const ss = [Link]();
const formSheet = [Link]("Form Responses 1");
// Get the current month and year for sheet naming
const currentDate = new Date();
const month = [Link]('default', { month: 'long' }); // Full
month name (e.g., January)
const year = [Link](); // Current year (e.g., 2025)
const sheetName = `${month} ${year}`; // e.g., "January 2025"
// Check if the sheet for the current month exists
let formattedSheet = [Link](sheetName);
if (!formattedSheet) {
// If the sheet doesn't exist, create it
formattedSheet = [Link](sheetName);
} else {
// If it already exists, clear the existing data
[Link]();
}
// Get form data
const responses = [Link]().getValues();
const headers = ["Name", "Start Date", "Project", "Task", "Status",
"Comments"]; // Custom headers (ignoring timestamp)
const data = [Link](1).map(row => [Link](1)); // Skip the first
column (timestamp)
// Add headers to the new sheet
[Link](headers);
// Style headers
const headerRange = [Link](1, 1, 1, [Link]);
[Link]("bold")
.setFontSize(12)
.setHorizontalAlignment("center")
.setVerticalAlignment("middle")
.setBackground("#4CAF50") // Green header background
.setFontColor("#FFFFFF"); // White font color
// Add data rows to the new sheet
[Link]((row) => {
[Link](row);
});
// Style data rows
const lastRow = [Link]();
const dataRange = [Link](2, 1, lastRow - 1, [Link]);
[Link](11)
.setHorizontalAlignment("left")
.setVerticalAlignment("middle");
// Set alternating row colors
const alternatingRange = [Link](2, 1, lastRow - 1,
[Link]);
const rule = [Link]()
.whenFormulaSatisfied(`=ISEVEN(ROW())`)
.setBackground("#f9f9f9") // Light gray for even rows
.setRanges([alternatingRange])
.build();
[Link]([rule]);
// Apply Conditional Formatting for "Status" column
const statusColumn = [Link]("Status") + 1;
const statusRange = [Link](2, statusColumn, lastRow - 1);
const statusRules = [
[Link]()
.whenTextContains("Completed")
.setBackground("#b6d7a8") // Light green for Completed
.setFontColor("#000000")
.setRanges([statusRange])
.build(),
[Link]()
.whenTextContains("On Progress")
.setBackground("#ffe599") // Yellow for On Progress
.setFontColor("#000000")
.setRanges([statusRange])
.build(),
];
[Link]([...statusRules, rule]);
// Adjust column widths for neat display
[Link](1, 1, 120); // Name
[Link](2, 1, 110); // Start Date
[Link](3, 1, 140); // Project
[Link](4, 1, 200); // Task
[Link](5, 1, 120); // Status
[Link](6, 1, 200); // Comments
// Check and remove existing filter if any
if ([Link]()) {
[Link]().remove();
}
// Add filters for easy sorting and filtering
[Link](1, 1, 1, [Link]).createFilter();
// Apply dd-MM-yyyy format to Start Date column
const startDateColumn = [Link]("Start Date") + 1;
const startDateRange = [Link](2, startDateColumn, lastRow - 1);
[Link]("dd-MM-yyyy");
}
