Simulating get filter criteria- Error | Google Apps Script

Question

I have a function that one of the users suggested to get the criterion of the built-in google sheets filter. I dont know if I'm doing it right but when I paste the getVisibleValues() function to the cell I get an error:

TypeError: Cannot read property 'getColumnFilterCriteria' of undefined (row18)

function test_getVisibleValues() {
      const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Test1");
      const columnRange = sheet.getRange("C2:C");
      const filter = columnRange.getFilter();
      const visibleValues = getVisibleValues(filter, columnRange);
      console.log(visibleValues);
    }
    /**
     * Returns the values to show.
     * Simulates https://developers.google.com/apps-script/reference/spreadsheet/filter-criteria#getvisiblevalues
     * @param {Filter} filter
     * @param {Range} range
     * @returns {string[]}
     */
    function getVisibleValues(filter, range) {
      const filterCriteria = filter.getColumnFilterCriteria(range.getColumn());
      const hiddenValues = filterCriteria.getHiddenValues();
      const allNonBlankValues = range.getValues().filter(String);
      const uniqueValues = Array.from(new Set(allNonBlankValues));
          
      // filter unique values that aren't hidden
      return uniqueValues.flat().filter(value => !hiddenValues.includes(value));
    }

Assuming that you have a sheet with the name Test1 the issue might be that your selected range ( column C) does not have a filter and as a result getFilter() returns null. Can you confirm that ? Also try C1:C instead to get the full column. — soMario
@Marios The C column contains a filter. I tried C1: C and the same error. Am I doing this correctly by typing =getVisibleValues() without any parameters? — AdOne
which function are you executing first? test_getVisibleValues or getVisibleValues. If you execute the latter, then the arguments are not defined and hence the error. I tested your code by executing test_getVisibleValues and it didn't generate any error. I think it is straightforward to understand. The function getVisibleValues requires two arguments: filter and range. If you execute first the getVisibleValues then the arguments are not defined anywhere. But if you execute test_getVisibleValues first, then you pass these arguments to getVisibleValues and then it works. — soMario
@Marios hmm so how can i use the whole by typing a function into a cell to return a result ? — AdOne

soMario soMario · Accepted Answer · 2020-12-13T16:44:20

Explanation:

Your goal is to use your code as a custom function so it can be used as a formula in your sheet.

Solution:

Make sure column C contains a filter and that this formula applies to the sheet Test1. Change these to your needs or use:

const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()

if you want to get the active sheet instead.

function test_getVisibleValues(rng) {
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    const range = sheet.getRange(rng);
    const filter = range.getFilter();
    const filterCriteria = filter.getColumnFilterCriteria(range.getColumn());
    const hiddenValues = filterCriteria.getHiddenValues();
    const allNonBlankValues = range.getValues().filter(String);
    const uniqueValues = Array.from(new Set(allNonBlankValues));          
    // filter unique values that aren't hidden
    return uniqueValues.flat().filter(value => !hiddenValues.includes(value));
}

You can now apply test_getVisibleValues("C2:C") as a formula in your sheet but C2:C needs to be inside quotation:

Simulating get filter criteria- Error | Google Apps Script

1 Answers