Scripting and Macros

This scripts gets the workbook properties

function main(workbook: ExcelScript.Workbook) { 

   const _properties = workbook.getProperties(); 
   const _title = properties.getTitle(); 
   const _subject = properties.getSubject(); 
   const _author = properties.getAuthor(); 
   const _comments = properties.getComments(); 

   const _dataProperties : i_WorkbookMetaData = { 
      title : _title || "(Not Set)" 
      sibject : _subject || "(Not Set)" 
      author : _author || "(Not Set)" 
      comments : _comments || "(Not Set)" 
   }; 

   displayPropertiesInSheet(workbook, _dataProperties); 
} 

interface i_WorkbookMetaData{ 
   title : string; 
   subject : string; 
   author : string; 
   comments : string; 
} 

function displayPropertiesInSheet( 
   workbook: ExcelScript.Workbook, 
   properties: i_WorkbookMetaData) { 

   const _sheet = workbook.getActiveWorksheet(); 
   const _headerRange = _sheet.getRange("A1:B1"); 
   _headerRange.setValues([["Property","Value"]]); 
   const _dataRange = _sheet.getRange("A2:B5"); 
   const _propertiesArray = [ 
      ["Title", properties.title], 
      ["Title", properties.title], 
      ["Title", properties.title], 
      ["Title", properties.title], 
   ]; 
   _dataRange.setValues(_propertiesArray); 
} 

This script can generate monthly worksheets from a template

function main( 
  workbook: ExcelScript.Workbook, 
  templateSheetName: string = "Template_Month", 
  startMonth: string = "2026-01",   // format: YYYY-MM 
  endMonth: string = "2026-12",     // format: YYYY-MM 
  nameFormat: "YYYY-MM" | "MMM YYYY" | "MMMM YYYY" = "YYYY-MM", 
  overwriteExisting: boolean = false, 
  monthDateCellAddress: string = "" // e.g., "B2" (optional) 
) { 
  const templateSheet = workbook.getWorksheet(templateSheetName); 
  if (!templateSheet) { 
    throw new Error(`Template sheet not found: "${templateSheetName}"`); 
  } 

  const start = parseYearMonth(startMonth); 
  const end = parseYearMonth(endMonth); 

  if (start.year > end.year || (start.year === end.year && start.month > end.month)) { 
    throw new Error(`Invalid range: startMonth (${startMonth}) must be <= endMonth (${endMonth}).`); 
  } 

  let currentYear = start.year; 
  let currentMonth = start.month; 

  while (currentYear < end.year || (currentYear === end.year && currentMonth <= end.month)) { 
    const sheetName = formatMonthSheetName(currentYear, currentMonth, nameFormat); 

    const existing = workbook.getWorksheet(sheetName); 
    if (existing) { 
      if (!overwriteExisting) { 
        // Skip existing 
        incrementMonth(); 
        continue; 
      } 
      // Delete existing to recreate 
      existing.delete(); 
    } 

    const newSheet = templateSheet.copy(ExcelScript.WorksheetPositionType.after, templateSheet); 
    newSheet.setName(sheetName); 

    if (monthDateCellAddress && monthDateCellAddress.trim()) { 
      const firstOfMonth = new Date(Date.UTC(currentYear, currentMonth - 1, 1)); 
      // Excel stores dates as serials; Office Scripts accepts JS Date objects in setValue. 
      newSheet.getRange(monthDateCellAddress).setValue(firstOfMonth); 
    } 

    incrementMonth(); 
  } 

  function incrementMonth() { 
    currentMonth++; 
    if (currentMonth > 12) { 
      currentMonth = 1; 
      currentYear++; 
    } 
  } 
} 

function parseYearMonth(input: string): { year: number; month: number } { 
  const m = input.trim().match(/^(\d{4})-(\d{2})$/); 
  if (!m) throw new Error(`Invalid YYYY-MM format: "${input}". Example: "2026-03"`); 

  const year = Number(m[1]); 
  const month = Number(m[2]); 

  if (!Number.isFinite(year) || !Number.isFinite(month) || month < 1 || month > 12) { 
    throw new Error(`Invalid month/year: "${input}"`); 
  } 

  return { year, month }; 
} 

function formatMonthSheetName( 
  year: number, 
  month: number, 
  nameFormat: "YYYY-MM" | "MMM YYYY" | "MMMM YYYY" 
): string { 
  if (nameFormat === "YYYY-MM") { 
    return `${year}-${String(month).padStart(2, "0")}`; 
  } 

  const monthNamesShort = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"]; 
  const monthNamesLong = ["January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"]; 

  if (nameFormat === "MMM YYYY") { 
    return `${monthNamesShort[month - 1]} ${year}`; 
  } 

  return `${monthNamesLong[month - 1]} ${year}`; 
}