Bad Script Error when I run my Office Script with Power Automate

Here is my office script which I run from Power Automate, I get error saying Bad Gateway but when I run the same script in excel it runs fine

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);
  });
  // 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() === "";
  });
  if (allCommentsEmpty) {
    // Try to find the table object if present
    const tables = sheet.getTables();
    const targetTable = tables.find(table => {
      const headerNames = table.getHeaderRowRange().getValues()[0];
      return headerNames.includes("Comment");
    });
    if (targetTable) {
      // Convert table to range before deleting column
      targetTable.convertToRange();
    }
    // Delete the Comment column from the entire worksheet
    sheet.getRangeByIndexes(0, commentColIndex, sheet.getUsedRange().getRowCount(), 1).delete(ExcelScript.DeleteShiftDirection.left);
    // Fix right border for the new last column after deleting "Comment"
    const updatedUsedRange = sheet.getUsedRange();
    const newLastColIndex = updatedUsedRange.getColumnCount() - 1;
    const rowCount = updatedUsedRange.getRowCount();
    // Apply right border to each cell in the new last column
    for (let i = 0; i < rowCount; i++) {
      const cell: ExcelScript.Range = sheet.getCell(i, newLastColIndex);
      const borders: ExcelScript.RangeBorder[] = cell.getFormat().getBorders();
      for (let j = 0; j < borders.length; j++) {
        const border: ExcelScript.RangeBorder = borders[j];
        if (border.getSideIndex() === ExcelScript.BorderIndex.edgeRight) {
          border.setStyle(ExcelScript.BorderLineStyle.continuous);
          border.setWeight(ExcelScript.BorderWeight.thick);
          border.setColor("black");
        }
      }
    }
  }
  // === STEP 1: Convert the table to a range (required before merging cells) ===
  const allTables = sheet.getTables();
  if (allTables.length > 0) {
    allTables[0].convertToRange();
  }
  // === STEP 2: Merge Activity and Duration columns for 'Weekend' rows ===
  const updatedRange = sheet.getUsedRange();
  const updatedValues = updatedRange.getValues();
  // Find new indexes after possible Comment column deletion
  const updatedHeaders = updatedValues[startRow] as string[];
  const updatedProjectColIndex = updatedHeaders.indexOf("Project");
  const updatedActivityColIndex = updatedHeaders.indexOf("Activity");
  const updatedDurationColIndex = updatedHeaders.indexOf("Duration (Hours)");
  // Loop through rows below the header and apply merging
  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;
      const rangeToMerge = sheet.getRangeByIndexes(rowIndex, updatedActivityColIndex, 1, 2);
      rangeToMerge.merge(true); // Merge and center
      rangeToMerge.getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center); // Center align
      const value = row[updatedActivityColIndex];
      sheet.getCell(rowIndex, updatedActivityColIndex).setValue(value);
    }
  }
  // === STEP 3: Merge Project + Activity + Duration columns for 'Leave' and 'Public Holiday' rows ===
for (let i = startRow + 1; i < updatedValues.length; i++) {
  const row = updatedValues[i];
  const project = row[updatedProjectColIndex]?.toString().trim();
  if (project === "Leave" || project === "Public Holiday") {
    const rowIndex = i;
    const rangeToMerge = sheet.getRangeByIndexes(rowIndex, updatedProjectColIndex, 1, 3);
    rangeToMerge.merge(true); // Merge and center
    rangeToMerge.getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center); // Center align
    const value = row[updatedActivityColIndex];
    sheet.getCell(rowIndex, updatedProjectColIndex).setValue(value);
  }
}
}
// 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()}`;
}