Excel Online (Business) Create Table Error

[djmitz6]

Greetings,

I'm trying to create a flow that copies an excel file from one sharepoint site to another sharepoint site. Then the flow will need to convert the worksheet to a table. My flow is running correctly except for the Create Table function. I keep getting the error: "The operation failed because it conflicts with a filtered range." I'm assuming this means that, because my excel online file contains filters it cannot convert the range to a table. Is there anyway to remove filters from an excel online workbook?

[rohentz]

Hi @djmitz6 how are you?

Regarding your question and If I understood correctly your needs, to remove a filter in a table at Excel maybe you can create a script and then call it on Power Automate.

My sample below clear applied filter on my table "Tabela1"

And then I call it from Power Automate using this action. Just change the script name ok?

The run script action still on preview but you can check it at https://docs.microsoft.com/en-us/office/dev/scripts/tutorials/excel-power-automate-manual

Please let me know if this information helped on your question.

Regards.

Rodrigo Hentz

Microsoft Support Engineer

[djmitz6]

Rohentz,

Thank you for the response, but I'm trying to create a table. Currently the workbook is just a filtered range without a table. So running a script that calls a table that doesn't exist won't work.

[djmitz6]

@rohentz 

Here is an example of a filtered non-table: 

[rohentz · Accepted solution]

Hi @djmitz6  thanks for the reply and the sample. I recorded a new script and the code to clear the filter is:

Please let me know if this information helped on your question.

Regards.

Rodrigo Hentz

Microsoft Support Engineer

[Anonymous]

Is there any way if I can't access the Excel Script Tab?

Thank you

[Boipelo]

Good Day. May I kindly ask if you could help me with a very similar issue? Attached below is a snap of my flow. I get the error: The operation failed because it conflicts with a filtered range. However I run 2 excel scripts to remove the filters as well as unfreeze panes. The scripts work just fine, I.E, I successfully remove all filters and successfully unfreeze panes, however I still get this error. Could you please help me figure out what I am missing? Kind regards

[Tangtang]

I'm also struggling to remove filters from a worksheet. 

I've incorporated your code:

let currentWorksheet = workbook.

      getWorksheet("Sheet 1");

    // Get the range containing all the cells with data or formatting.

    let usedRange = currentWorksheet.getUsedRange();

    // Log the range's address to the console.

    console.log(usedRange.getAddress());

    currentWorksheet.getAutoFilter().clearCriteria;

    currentWorksheet.getAutoFilter().remove;

//Add table

 let table1 = currentWorksheet.addTable(usedRange, true);

The Output:

 Worksheet addTable: The operation failed because it conflicts with a filtered range.

It's not removing the filters. Were you able to find the solution?

[djmitz6]

I tested using the following code and had no issues:

The only items that I changed were renaming "Sheet 1" to "Sheet1"

function main(workbook: ExcelScript.Workbook) {
	let currentWorksheet = workbook.getWorksheet("Sheet1");

	// Get the range containing all the cells with data or formatting.
	let usedRange = currentWorksheet.getUsedRange();

	// Log the range's address to the console.
	console.log(usedRange.getAddress());
	currentWorksheet.getAutoFilter().clearCriteria;
	currentWorksheet.getAutoFilter().remove;

	//Add table
	let table1 = currentWorksheet.addTable(usedRange, true);
}

 Image of Autofiltered Table that I used:

Image after running Script above:

Below is the console log output:

Sheet1!A1:C6

[Tangtang]

Thanks for testing it out. I've attached the excel that I'm having trouble with running this code on. Do you think the frozen panels affects the code? And if so, what would be the best course of action?

Thanks!

[djmitz6]

Tangtang,

I found the problem. You need to add the "()" after the .remove. See below code snippet.

Also on your example you have merged cells in row 5. You will need to delete this row or modify the code to exclude this, because it is part of your used range so it will create generic header columns when you go to add the table.

function main(workbook: ExcelScript.Workbook) {
	let currentWorksheet = workbook.getWorksheet("Sheet 1");

	// Get the range containing all the cells with data or formatting.
	let usedRange = currentWorksheet.getUsedRange();

	// Log the range's address to the console.
	console.log(usedRange.getAddress());
	currentWorksheet.getAutoFilter().clearCriteria();
	currentWorksheet.getAutoFilter().remove();

	//Add table
	let table1 = currentWorksheet.addTable(usedRange, true);
}

[Tangtang]

Ah, genius! Thank you so much!