Power Platform Community Forum Thread Details

I have this Excel script which get called from power automate:-

function main(workbook: ExcelScript.Workbook) {
const templateName = 'TemplateDoNotDelete';
const template = workbook.getWorksheet(templateName);

if (!template) {
throw new Error(`Template sheet was not found.`);
}

// Get current date in Pacific Time
const pacificDate = new Date(
new Date().toLocaleString("en-US", { timeZone: "America/Los_Angeles" })
);

const day = pacificDate.getDate().toString().padStart(2, '0');
const month = (pacificDate.getMonth() + 1).toString().padStart(2, '0');
const year = pacificDate.getFullYear().toString();
const newSheetName = `${day}.${month}.${year}`;

// Delete existing sheet with same name, if found
const existingSheet = workbook.getWorksheet(newSheetName);
if (existingSheet) {
existingSheet.delete();
}

// Copy template
const copiedSheet = template.copy(
ExcelScript.WorksheetPositionType.after,
template
);

// Rename copied sheet
copiedSheet.setName(newSheetName);

// Get tables from new sheet
const tables = copiedSheet.getTables();
const tableNames = tables.map(t => t.getName());

return {
worksheetName: copiedSheet.getName(),
tableNames: tableNames
};
}

the original sheet named "TemplateDoNotDelete" has 3 tables with columns been sorted, but when the script creates the new sheet and i added some data using powerautomate flow, the column will not be sorted dynamically as the data get added,, instead i need to manually click on sort.. can this be fixed? to allow the new sheet to respect the sort setting and apply dynamic sorting?

Thanks

The issue is that Excel's AutoFilter copies the configuration but doesn't actively apply sort rules to newly added data. You need to reapply the sort settings after copying the template sheet.

Try adding this code to your script after renaming the copied sheet:

----------------------*****----------------------

// Reapply sort settings from template to copied tables
const templateTables = template.getTables();
templateTables.forEach((templateTable, index) => {
const copiedTable = tables[index];
if (copiedTable && templateTable) {
const templateSort = templateTable.getSort();
const copiedSort = copiedTable.getSort();
const sortFields = templateSort.getFields();
if (sortFields.length > 0) {
copiedSort.apply(sortFields);
}
}
});
----------------------*****----------------------

function main(workbook: ExcelScript.Workbook) {
const templateName = 'TemplateDoNotDelete';
const template = workbook.getWorksheet(templateName);
if (!template) {
throw new Error(`Template sheet was not found.`);
}
// Get current date in Pacific Time
const pacificDate = new Date(
new Date().toLocaleString("en-US", { timeZone: "America/Los_Angeles" })
);
const day = pacificDate.getDate().toString().padStart(2, '0');
const month = (pacificDate.getMonth() + 1).toString().padStart(2, '0');
const year = pacificDate.getFullYear().toString();
const newSheetName = `${day}.${month}.${year}`;
// Delete existing sheet with same name, if found
const existingSheet = workbook.getWorksheet(newSheetName);
if (existingSheet) {
existingSheet.delete();
}
// Copy template
const copiedSheet = template.copy(
ExcelScript.WorksheetPositionType.after,
template
);
// Rename copied sheet
copiedSheet.setName(newSheetName);
// Get tables from new sheet
const tables = copiedSheet.getTables();
const tableNames = tables.map(t => t.getName());
// ⭐ NEW CODE ADDED BELOW ⭐
// Reapply sort settings from template to copied tables
const templateTables = template.getTables();
templateTables.forEach((templateTable, index) => {
const copiedTable = tables[index];
if (copiedTable && templateTable) {
const templateSort = templateTable.getSort();
const copiedSort = copiedTable.getSort();
 
// Copy sort fields from template
const sortFields = templateSort.getFields();
if (sortFields.length > 0) {
copiedSort.apply(sortFields);
}
}
});
// ⭐ NEW CODE ADDED ABOVE ⭐
return {
worksheetName: copiedSheet.getName(),
tableNames: tableNames
};
}

Note :
If your Power Automate flow adds data to the tables, create a second script that runs after data insertion and reapply the sorts again using the same code above. This ensures new rows respect the sort rules dynamically

Vishnu WR

LinkedIn - My Tech Space

Please ✅ Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider answering Yes to Was this reply helpful? or give it a Like ♥