function main(workbook: ExcelScript.
Workbook) {
try {
ensureTemplateExists(workbook);
createDailySheet(workbook);
lockOldSheets(workbook);
// protectWorkbookStructure(workbook); // REMOVED - this prevents sheet
management
enforceSheetNames(workbook);
[Link]("Script completed.");
} catch (e) {
[Link]("Error: " + (e instanceof Error ? [Link] : e));
[Link]("Full error details: " + [Link](e));
throw e;
}
}
// Ensure a sheet named exactly "Template" exists. If not, create one (copy first
sheet or make a bare template)
function ensureTemplateExists(workbook: [Link]) {
const templateName = "Template";
let template = [Link](templateName);
if (template) {
[Link](`Found existing "${templateName}" sheet.`);
return;
}
const sheets = [Link]();
if ([Link] > 0) {
// Copy the first worksheet to create a Template
const first = sheets[0];
const copy = [Link]([Link], first);
// Give it the Template name (ensure unique by removing conflicting names if
needed)
try {
[Link](templateName);
} catch {
// If naming fails (rare), append timestamp
[Link](templateName + "_" + new Date().getTime());
}
[Link](`No "${templateName}" found. Copied first sheet to create "$
{templateName}".`);
} else {
// No sheets exist - create a basic Template
const created = [Link](templateName);
// Put example headers in row 4 to match your earlier requirement (A4:F4)
[Link]("A4").setValue("Header1");
[Link]("B4").setValue("Header2");
[Link]("C4").setValue("Header3");
[Link]("D4").setValue("Header4");
[Link]("E4").setValue("Header5");
[Link]("F4").setValue("Header6");
[Link](`No sheets in workbook. Created a basic "${templateName}" sheet
with sample headers at A4:F4.`);
}
}
// CREATE DAILY SHEET
function createDailySheet(workbook: [Link]) {
const today = new Date();
const sheetName: string = formatDate(today);
if ([Link](sheetName)) {
[Link](`Sheet "${sheetName}" already exists. Skipping create.`);
return;
}
const template = [Link]("Template");
if (!template) throw new Error('Template sheet not found even after
ensureTemplateExists.');
// Save protection states
const templateProtection = [Link]();
const templateWasProtected = [Link]();
const workbookProtection = [Link]();
const workbookWasProtected = [Link]();
// Temporarily remove protections to allow copying
if (templateWasProtected) {
[Link]();
[Link]("Temporarily unprotected Template sheet for copying.");
}
if (workbookWasProtected) {
[Link]();
[Link]("Temporarily unprotected workbook structure for copying.");
}
try {
// Try to copy the template sheet
let newSheet: [Link];
try {
// First attempt: copy after template
newSheet = [Link]([Link],
template);
} catch (copyError) {
[Link]("First copy method failed, trying alternative method...");
// Second attempt: copy to end
newSheet = [Link]([Link], null);
}
// Name the new sheet
try {
[Link](sheetName);
} catch (nameError) {
// If name collision occurs, append timestamp
const timestamp = new Date().getTime();
[Link](sheetName + "_" + timestamp);
[Link](`Used fallback name: ${sheetName}_${timestamp}`);
}
// Move new sheet to the end
const sheets = [Link]();
const lastIndex = [Link] - 1;
[Link](lastIndex);
[Link](`Created sheet "${[Link]()}" from Template.`);
} finally {
// Always restore protections
if (templateWasProtected) {
[Link]({
allowAutoFilter: false,
allowDeleteColumns: false,
allowDeleteRows: false,
allowFormatCells: false,
allowFormatColumns: false,
allowFormatRows: false,
allowInsertColumns: false,
allowInsertRows: false,
allowPivotTables: false,
selectionMode: [Link]
});
[Link]("Re-protected Template sheet.");
}
if (workbookWasProtected) {
[Link]();
[Link]("Re-protected workbook structure.");
}
}
}
// LOCK OLD SHEETS (>24 HOURS) with PASSWORD PROTECTION
function lockOldSheets(workbook: [Link]) {
const now = new Date();
// IMPORTANT: Define a password that only the workbook owner knows
// This should be a secure password - in practice, you might want to:
// 1. Store it securely elsewhere (not in code)
// 2. Use environment variables
// 3. Use a vault service
const SHEET_PROTECTION_PASSWORD = "YourSecurePassword123!"; // CHANGE THIS!
[Link]().forEach((sheet) => {
const name = [Link]();
if (name === "Template") return;
const sheetDate = new Date(name);
if (isNaN([Link]())) return;
const ageHours = ([Link]() - [Link]()) / (1000 * 60 * 60);
if (ageHours > 24) {
try {
const protection = [Link]();
// First, ensure the sheet is not already protected in a way that
prevents re-protection
if ([Link]()) {
// If already protected, we need to unprotect first to change
settings
try {
[Link](SHEET_PROTECTION_PASSWORD);
[Link](`Unprotected existing protection on sheet "$
{name}" to update settings.`);
} catch {
// If we can't unprotect with our password, someone else
changed it
[Link](`Sheet "${name}" is already protected with a
different password. Skipping.`);
return;
}
}
// Apply protection with password and specific settings
[Link]({
allowAutoFilter: false,
allowDeleteColumns: false,
allowDeleteRows: false,
allowFormatCells: false,
allowFormatColumns: false,
allowFormatRows: false,
allowInsertColumns: false,
allowInsertRows: false,
allowPivotTables: false,
selectionMode: [Link]
}, SHEET_PROTECTION_PASSWORD);
[Link](`Password-protected sheet "${name}" (age $
{[Link](ageHours)} hours).`);
} catch (error) {
[Link](`Failed to protect sheet "${name}": ${error}`);
// Continue with other sheets instead of stopping
}
}
});
}
// PROTECT WORKBOOK STRUCTURE - UPDATED WITH ERROR HANDLING
function protectWorkbookStructure(workbook: [Link]) {
try {
const protection = [Link]();
if (![Link]()) {
[Link](); // NO arguments!
[Link]("Workbook structure protected.");
} else {
[Link]("Workbook structure already protected.");
}
} catch (error) {
[Link](`Failed to protect workbook structure: ${error}`);
}
}
// ENFORCE SHEET NAMES (YYYY-MM-DD) - UPDATED WITH ERROR HANDLING
function enforceSheetNames(workbook: [Link]) {
const sheets = [Link]();
const datePattern = /^\d{4}-\d{2}-\d{2}$/;
const today = new Date();
[Link]((sheet) => {
const name = [Link]();
if (name === "Template") return;
if () {
try {
const newName = formatDate(today);
// Check if the new name already exists (except for the current sheet)
const existingSheet = [Link](newName);
if (existingSheet && existingSheet !== sheet) {
// If duplicate exists, append timestamp
const unique = newName + "_" + new Date().getTime();
[Link](unique);
[Link](`Renamed "${name}" => "${unique}" (avoided
duplicate).`);
} else {
[Link](newName);
[Link](`Renamed "${name}" => "${newName}".`);
}
} catch (error) {
[Link](`Failed to rename sheet "${name}": ${error}`);
// Continue with other sheets
}
}
});
}
// FORMAT DATE (YYYY-MM-DD)
function formatDate(date: Date): string {
const yyyy = [Link]();
const mm = String([Link]() + 1).padStart(2, "0");
const dd = String([Link]()).padStart(2, "0");
return `${yyyy}-${mm}-${dd}`;
}