Unanswered

Excel Online (Business) Create Table Error

(0) Share

Report

Posted on by 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?

Categories:

Building flows

I have the same question (0)

All responses (11)

Answers (1)

Rodrigo Hentz on at

Like (0)

Report

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

Was this reply helpful? Yes No
djmitz6 32 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
djmitz6 32 on at

Like (0)

Report

@rohentz

Here is an example of a filtered non-table:

Was this reply helpful? Yes No
Verified answer

Rodrigo Hentz on at

Like (4)

Report

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

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

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

Was this reply helpful? Yes No
Boipelo 7 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report
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?

Was this reply helpful? Yes No
djmitz6 32 on at

Like (1)

Report
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

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

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!

Excel_Example.xlsx

Was this reply helpful? Yes No
djmitz6 32 on at

Like (1)

Report
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); }

Was this reply helpful? Yes No