Week_Num4

 

function main(workbook: ExcelScript.Workbook) {

  // Get the table by name

  let table = workbook.getTable("tbl_Auto");

  // Add a new column called Week_Num

  let weekCol = table.addColumn();

  weekCol.setName("Week_Num");

  // Get the rows of the table

  let rows = table.getRange().getValues();

  // Find the index of the Date column

  let headers = table.getHeaderRowRange().getValues()[0];

  let dateColIndex = headers.indexOf("Date");

  let weekColIndex = headers.indexOf("Week_Num");

  // Loop through rows and calculate week number

  for (let i = 1; i < rows.length; i++) {

    let dateValue = rows[i][dateColIndex] as Date;

    if (dateValue) {

      let jsDate = new Date(dateValue.toString());

      let weekNum = getWeekNumber(jsDate);

      rows[i][weekColIndex] = weekNum;

    }

  }

  // Write back updated values

  table.getRange().setValues(rows);

}

// Helper function to calculate ISO week number

function getWeekNumber(date: Date): number {

  // Copy date so we don’t modify original

  let d = new Date(Date.UTC(date.getFullYear(), date.getMonth(), date.getDate()));

  // Set to nearest Thursday: current date + 4 - current day number

  let dayNum = d.getUTCDay() || 7;

  d.setUTCDate(d.getUTCDate() + 4 - dayNum);

  // Get first day of year

  let yearStart = new Date(Date.UTC(d.getUTCFullYear(), 0, 1));

  // Calculate full weeks to nearest Thursday

  let weekNo = Math.ceil((((d.getTime() - yearStart.getTime()) / 86400000) + 1) / 7);

  return weekNo;

}