How to return getVisibleValues() with Google Apps Script?

user_13507810 0
Viewed 1044

Do you know why the getVisibleValues() function doesn't return any values? I would like to get a filtering criterion, for example in this case (screenshot) I'd like to get the 'A' value. I tried getCriteriaValues() , getValues()and it doesn't work too. It doesn't not return any values. Only the getHiddenValues() function works and returns "B, C, D" correctly.

screenshot

function myFunction() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Test1');
var filter = sheet.getRange('A1:C').getFilter();
  var values = filter.getColumnFilterCriteria(3).getVisibleValues();
  
  Logger.log(values);
  
}
google-apps-script google-sheets filtering
3 Answers

This seems to be a bug. You can report in the Issue Tracker.

This method is not currently supported:

See issue 159051708:

Status: Won't Fix (Intended Behavior) Hello,

Unfortunately those methods are not currently supported. As you try to use them in your script you will see an Exception message:

Visible values are not currently supported. As an alternative specify a list of hidden values that excludes the values that should be visible.

Hidden values are supported.

You could create a separate function to simulate this:

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

I thought to do it this way:

  • Get all the values ​​first.
  • Then filter out the empty and not unique.
  • And then filter out hidden values ​​from them.

Do you know how to do it?

function getcriteria() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Test1")
var range = sheet.getRange('C2:C');

//get hidden values
var filter = range.getFilter();
var hiddenValues = filter.getColumnFilterCriteria(3).getHiddenValues();

//get all values, filter blank
var all = range.getValues().filter(String);

//filter unique 
var allUnique = 

//filter allUnique - hiddenValues
var filtered = 
      }

user_135078100 

function getFilterValues(filter, range) {
  const filterCriteria = filter.getColumnFilterCriteria(range.getColumn());
  const hiddenValues = filterCriteria.getHiddenValues();
  const allValues = range.getValues().slice(1).flat().map(value => value.toString());
  const uniqueValues = Array.from(new Set(allValues));  
  var visibleValues = uniqueValues.filter(value => !hiddenValues.includes(value));
  return {
    'visibleValues':visibleValues,
    'hiddenValues':hiddenValues,
    'uniqueValues':uniqueValues
  }
}

user_26563970
Related