There are two ways for this.
1) Get the table names dynamically from the script (OR)
2) If you know the table names, use them directly.
1st approach:
Office Scripts can create worksheets and tables, but Power Automate cannot directly “see” script objects unless you expose them explicitly.
You must return table information from the script itself, then use that output in Power Automate.
How it works (high level)
1) Update the Office Script to:
Get the tables from the newly created worksheet
Return table names (or IDs) as output
2) In Power Automate:
Use the Run script action
Read the returned table names
Use standard Excel actions like List rows present in a table
Recommended Script Change (Example)
function main(workbook: ExcelScript.Workbook) {
const template = workbook.getWorksheet('TemplateDoNotDelete');
if (!template) {
throw new Error('Template not found');
}
const copiedSheet = template.copy(
ExcelScript.WorksheetPositionType.after,
template
);
copiedSheet.setName('123');
const tables = copiedSheet.getTables();
const tableNames = tables.map(t => t.getName());
return {
worksheetName: copiedSheet.getName(),
tableNames: tableNames
};
}
Power Automate receives:
New worksheet name
List of table names created or copied
Power Automate Flow Steps (Simple)
- Use Run script (Excel Online Business)
- Capture output from the script
- Loop over tableNames
- Use List rows present in a table
- Pass table name dynamically
This is the only supported and reliable method.
2nd approach
Step-by-Step Implementation in Power Automate
- Run script: Connect your script.
- Initialize Variable (Optional): Or just use the output directly.
- Excel Online (Business) Actions:
- In the Table field of any Excel action, do not use the dropdown.
- Select Enter custom value.
- Select the tableNames (or the specific string) from the Dynamic Content of the "Run script" step.
✅ 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.
