When we work with Excel in Power Automate, we might need to remove the filter on worksheet to be able to create a data table. If filter is enabled, Excel Online for Business connector might not be able to create a data table.
We can create Office Script within Excel Online for Business. To save the script so Power Automate can use it, we must open the file in Excel Online (the web version). Under the Automate tab, we can create scrit. The script created saved here is actually stored in the Microsoft 365 cloud profile, not inside the .xlsx file itself. Even if we delete the .xlsx file, the script will still be there. This allows the script to be “Global” and run on any .xlsx file in your cloud account, and run on any file your flow touches.
Here is exactly where to go:
- Open your Browser: Go to Office.com or your OneDrive/SharePoint folder.
- Open any Excel file: It doesn’t matter which one; any workbook will do just for the setup phase.
- Find the Tab: Look at the top ribbon for Automate. Note: If you don’t see it, ensure you are signed in with a Business/Enterprise account. It won’t appear in the free personal @outlook.com version.
- Create the Script: Click New Script, paste the code provided earlier, and save it with a meaningful name.
Script to simply remove the filter on a worksheet:
function main(workbook: ExcelScript.Workbook, sheetName: string) {
let sheet = workbook.getWorksheet(sheetName);
sheet.getAutoFilter().remove();
}
script to remove the filter and create a datatable:
function main(workbook: ExcelScript.Workbook, sheetName: string, tableName: string) {
let sheet = workbook.getWorksheet(sheetName);
// 1. Completely turn off the AutoFilter mode to clear all "memory" of filters
sheet.getAutoFilter().remove();
// 2. Get the used range
let usedRange = sheet.getUsedRange();
if (!usedRange) {
throw "No data found on the sheet.";
}
// 3. Create the table inside the script
// This bypasses the Power Automate "Create Table" action and its Graph API conflicts
try {
let newTable = workbook.addTable(usedRange, true);
newTable.setName(tableName);
return newTable.getId();
} catch (e) {
return "Table might already exist: " + e;
}
}
We can then use the Run a Script action from Excel Online for Business connector to choose the script to be run.