You’re offline. This is a read only version of the page.
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()}`;
}
Share
Report
All responses (
Answers (
1,057
