Forum Discussion

coletteanderson's avatar
coletteanderson
Copper Contributor
Dec 21, 2023

Automate deselect blanks in a Filter on a worksheet

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"] });
Office Scripts
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Dec 22, 2023

    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.

    • coletteanderson's avatar
      coletteanderson
      Copper Contributor
      Dec 27, 2023

      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.
      • NikolinoDE's avatar
        NikolinoDE
        Gold Contributor
        Dec 28, 2023

        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.

Resources