Add filter to PivotTable

Question

I have created pivot table in my script. Suppose this pivot table is named pivotTable. I would like to add a filter to this table, so I use FilterCriteria class to achieve this.

I can do something like this:

pivotTable.addFilter(colNumber, SpreadsheetApp.newFilterCriteria().setVisibleValues(["dog", "cat"]).build());

After executing this function, in pivotTable remain only rows which in the column with index colNumber have the value "cat" or "dog". Now I want to do something like that:

pivotTable.addFilter(anotherColNumber, SpreadsheetApp.newFilterCriteria().whenCellNotEmpty().build());

...and then I get exception:

Exception: The pivot table filter criteria should only contain visible values.

My problem is that:

• I do not understand the exception I receive (what exactly means "visible values"?).
• I don't know what I'm doing wrong.
• I can't find the answer to this or similar problem.
• Most of answers, which I find, relate to javascript, so they don't help me.
• There is no examples in documentation https://developers.google.com/apps-script/reference/spreadsheet.

Thank you for your help!

Answer 1

Unfortunately, At present, It seems "Filter By Condition" in the pivot table cannot be manipulated programmatically. Only "Filter By Values" can be manipulated using setVisibleValues().Consider creating a issue in the issuetracker adding a star (★ on top left) to this issue created by Fi Teach