Power Platform Community Forum Thread Details

I have an excel worksheet which contain 3 tables. the tables contain formulas and conditional formatting for the cells. so i do not want to recreate all the logic inside power automate, instead i want to duplicate this worksheet and add values inside the new worksheet tables..can this be done inside power automate and how?

Regards

Hi @johnjohnPter,

Power Automate alone cannot duplicate an Excel worksheet. Use an Office Script to copy the worksheet with all tables, formulas, and formatting, then use Power Automate to insert data into the copied tables.

Recommended and supported way
Use Office Scripts called from Power Automate to:

Duplicate the worksheet
Keep formulas, tables, and formatting
Return table names
Then update table rows in Power Automate

This is the correct and scalable approach.

What will NOT work

Power Automate cannot clone a worksheet using Excel Online actions
Power Automate cannot copy formulas or conditional formatting
Excel actions only work with existing tables, not sheet structure

What you should do
Use an Office Script to duplicate the worksheet.

How it works

1) Create an Office Script that:

Copies the template worksheet
Renames it
Keeps all tables, formulas, and formatting
Returns table names

2) Call the script from Power Automate
3) Use Excel actions to insert values into the copied tables

Sample Office Script (Minimal and practical)

function main(workbook: ExcelScript.Workbook) {
  const template = workbook.getWorksheet("Template");
  const newSheet = template.copy(ExcelScript.WorksheetPositionType.end);
  
  const newName = `Run_${Date.now()}`;
  newSheet.setName(newName);
  // Grab the first table on the new sheet and give it a predictable name
  const table = newSheet.getTables()[0];
  const uniqueTableName = "Table_" + newName;
  table.setName(uniqueTableName);
  return {
    sheetName: newName,
    tableName: uniqueTableName // Return a single string instead of an array for easier Flow mapping
  };
}

Power Automate flow steps

Trigger the flow
Run Office Script (Excel Online Business)
Capture output table names
Loop through table names
Use Add a row into a table or Update a row
Formulas and formatting are preserved automatically

✅ If this answer helped resolve your issue, please mark it as Accepted so it can help others with the same problem.
👍 Feel free to Like the post if you found it useful.