Automate deselect blanks in a Filter on a worksheet

[coletteanderson]

I am trying to automate a worksheet and deselect blanks in the filter dropdown.  The issue I am having is the automation is using the other selected items as the way to filter out the blanks and is breaking the script if I use it on another dataset.  Any suggestions?

  // Apply values filter on selectedSheet

  selectedSheet.getAutoFilter().apply(selectedSheet.getAutoFilter().getRange(), 11, { filterOn: ExcelScript.FilterOn.values, values: ["INC000018898005", "INC000018898822", "INC000018907315"] });

[#2]

@coletteanderson 

If you want to automate deselecting blanks in a filter dropdown without explicitly listing the values, you can dynamically retrieve the current filter values and then exclude blanks from the list.

Here is an example of how you can modify your script:

// Get the current filter values
let currentFilterValues = selectedSheet.getAutoFilter().getColumnFilter(11).getDistinctValues();

// Remove blanks from the filter values
let filteredValues = currentFilterValues.filter(value => value !== "");

// Apply the updated filter
selectedSheet.getAutoFilter().apply(selectedSheet.getAutoFilter().getRange(), 11, { filterOn: ExcelScript.FilterOn.values, values: filteredValues });

In this example:

1. getDistinctValues() retrieves the distinct values currently present in the filtered column.
2. filter(value => value !== "") removes any blank values from the list.
3. Finally, the updated non-blank values are applied as the filter.

This way, the script dynamically adjusts to the current dataset, excluding blanks without explicitly specifying other values.

Additional Informations: Office Scripts samples and scenarios

NOTE: My knowledge of the topic is limited, but since no one has answered yet, even though it has been read many times, I posted the question in various AIs and found the above suggested solution for you. The proposed solution is untested. Maybe it will help you further in your project, if not please just ignore it.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

[#3]

@NikolinoDE 
When I attempt the script

  // Get the current filter values

  let currentFilterValues = rMA_to_INC.getAutoFilter().getColumnFilter(11).getDistinctValues();

  // Remove blanks from the filter values

  let filteredValues = currentFilterValues.filter(value => value !== "");

  // Apply the updated filter

  rMA_to_INC.getAutoFilter().apply(rMA_to_INC.getAutoFilter().getRange(), 11, { filterOn: ExcelScript.FilterOn.values, values: filteredValues });

*Line 49 is the first let statement and the variable it is erroring on first is currentFilterValues

I get an error stating:  

See line 49, column 6: Office Scripts cannot infer the data type of this variable. Please declare a 
type for the variable.

[#4]

@coletteanderson 

It seems like there might be an issue with Office Scripts inferring the data type of the variable currentFilterValues. To address this, you can explicitly declare the data type of the variable. Here's the modified script:

javascript

// Get the current filter values
let currentFilterValues: string[] = rMA_to_INC.getAutoFilter().getColumnFilter(11).getDistinctValues();

// Remove blanks from the filter values
let filteredValues: string[] = currentFilterValues.filter(value => value !== "");

// Apply the updated filter
rMA_to_INC.getAutoFilter().apply(rMA_to_INC.getAutoFilter().getRange(), 11, { filterOn: ExcelScript.FilterOn.values, values: filteredValues });

In this version of the script, I've added explicit type declarations for the variables currentFilterValues and filteredValues. By specifying that these variables are arrays of strings (string[]), you provide Office Scripts with the necessary information about the data type.

Try running the modified script, and it should help resolve the "cannot infer the data type" error.

[#5]

@NikolinoDE 

That looks much better in the editor, now the only issue I see is that it doesn't like the getColumnFilter.

Line 61: rMA_to_INC.getAutoFilter(...).getColumnFilter is not a function