Integration of Tools/Filter with google apps script [36753410]

Fixed

Feature Request

Status Update

No update yet.

Description

dm...@gmail.com

created issue #1

Apr 4, 2011 09:08PM

Request Summary:

From the perspective of using spreadsheets to support simple database applications, Tools/Filter is a very valuable tool. Better integration with google apps would greatly benefit everyone involved in using this platform.

1. .getFilteredRows() - return a 1d array of row nbrs in the current filter. Return all row nbrs if no filter is set. Return -1 if filter is not enabled
2. .getFilteredValues() - return a 2d array of rows/cols in the current filter. Return entire range if no filter is set. Return -1 is filter is not enabled
3. sheet.enableFilter(true) - equivalent to clicking Tools/Filter to enable filtering (no toggle unless we can tell if filters are active or not).
4. sheet.enableFilter(false) - equivalent to clicking Tools/Filter to disable filtering.
5. sheet.enableFilter() - return true if filter is enabled, false otherwise.
6. sheet.clearFilter() - clear current filter (if any) ... ie. display all records.
7. sheet.setFilter(..,...,...) - programmatically set filter based on criteria collected elsewhere by the application.

#1 is far and away the most important of the enhancements. If #1 is difficult and #2 is easier, then that would be my choice. If you only did #1 or #2, that is 95% in terms of importance.

Notes:
Here is an example from an application that i'm developing for my ski club.

Suppose we have a membership file that is stored on a spreadsheet. Suppose we have two applications: a formatted screen that supports add/change/delete/prev rec/next rec and an email 'blaster' that sends the same email message to a list of members.

File Maintenance - suppose we as asked to change data associated with a specific member whose last name is Moritz. The user can filter Last_Name = Moritz. Say there are two of them. She can then use Next Rec / Prev Rec to browse to the current record, change it and submit the change.

Email Blast - send an email to all active members advising them of a deadline to signup for a future ski trip. The user can filter for all active members.
Email Blast - send an email to all active members that haven't paid dues for coming year yet.

Comments

sa...@google.com <sa...@google.com> Apr 6, 2011 02:25PM

Assigned to sa...@google.com.

ri...@gmail.com <ri...@gmail.com> #2Apr 9, 2011 06:43PM

A must!

jp...@gmail.com <jp...@gmail.com> #4Apr 15, 2011 07:52AM

Very interesting!

ki...@gmail.com <ki...@gmail.com> #5Apr 18, 2011 11:55AM

Excellent suggestion. Is this on the radar for implementation? +1 for #7 - I would definitely like to see programmatic controls of filters via script.

m....@gmail.com <m....@gmail.com> #6Dec 5, 2011 09:28AM

Given that these methods exist in the Google Visualisation API it would be good if the same method statements and functionality were used

http://code.google.com/apis/chart/interactive/docs/reference.html#DataView

su...@gmail.com <su...@gmail.com> #7Mar 13, 2012 10:05AM

Must thing.

[Deleted User] <[Deleted User]> #8May 31, 2012 01:41PM

I've tested it, and you can use the Array.filter() method in GAS so for some applications this is a better alternative than manipulating spreadsheet (which is a more expensive operation since it has to be pushed to the client).

https://developer.mozilla.org/en/JavaScript/Reference/Global_Objects/Array/filter

Also, search for "javascript array filter examples" for more info.

ri...@gmail.com <ri...@gmail.com> #9Aug 20, 2012 11:44AM

I have also used Array.filter(), Array.reduce(), Array.map() <= they work fine. However, they are not documented and cannot be found anywhere on GAS. It still works though.

[Deleted User] <[Deleted User]> #10Aug 20, 2012 04:11PM

Those aren't GAS functions, they're standard JavaScript functions. The GAS documentations suggests Mozilla's excellent JavaScript resources for more information (although there are alternatives).

See

https://developer.mozilla.org/en-US/docs/JavaScript/Reference/Global_Objects/Array#Iteration_methods for more info.

ri...@gmail.com <ri...@gmail.com> #11Aug 20, 2012 10:20PM

The difference here is; in the GAS editor the popups do list Array.push(), .pop() etc. which are Javascript functions. So I am comfortable in using them in the sense Google has acknowledged and supported those functions. But Array.map(), .reduce(), .filter() are not available as methods on the Array object in the GAS editor.

Hence, the hesitation and ambiguity.

But AFAIK, they do work.

pe...@pezius.com <pe...@pezius.com> #12Sep 15, 2012 12:35PM

I really need getFilteredRows()! Preferably it would return a range similar to getActiveSelection().

ne...@gmail.com <ne...@gmail.com> #13Sep 15, 2012 04:55PM

I would like similar functionality for hidden rows. I need to know if a row is hidden, so it can be skipped. Simply setting the state isn't useful, similar to background color. It is useful to get the current state as well as setting.

mg...@interpunkt.hr <mg...@interpunkt.hr> #14Oct 11, 2012 06:22PM

This functionality is also important for data driven spreadsheets (using jdbc, etc.) since a user applied filter can play havoc with applying data sets. The clearfilter function is the most important here..

in...@gmail.com <in...@gmail.com> #15Nov 30, 2012 03:36PM

Must thing.

re...@gmail.com <re...@gmail.com> #16Dec 14, 2012 03:11AM

I was migrating my App from HTML to Google Script, and I assumed that both implementations shared the same functionality, I hitted this wall when I was implementing the filters I need to generate my Charts (e.g. total of sales by employee out of the sales history).

I was trying to implement this:

var filterDateStoreSale = new google.visualization.data.group(
data,
[4,3], // Group by |Date|
[{'column': 5, 'aggregation': google.visualization.data.sum, 'type': 'number'}] //|Store|sum:|Sale|
);

In my opinnion, adding grouped values is extremely useful for bulding usefull Charts out of SpreadSheets while programming on Google Scripts. I hope the guys at Google fix this soon.

ma...@gmail.com <ma...@gmail.com> #17Apr 26, 2013 02:52PM

Agree a very useful functionality

[Deleted User] <[Deleted User]> #18May 17, 2013 08:33PM

Indded very useful

pj...@gmail.com <pj...@gmail.com> #19May 25, 2013 02:24PM

Any update on this feature request?

ge...@gmail.com <ge...@gmail.com> #20Jun 24, 2013 01:09AM

I am working on a spreadsheet where I extend the number of rows and paste in additional data. Unfortunately the filter capability does not follow for the added rows. I would like to extend the filter capability to the new rows using the script that creates the new rows. I need this capability to do that sort of thing.

al...@alucab.org <al...@alucab.org> #21Sep 3, 2013 01:17PM

Yes i totally support it.

From a user point of view is particularly important, otherwise the user is obliged to deactivate and reactivate the filter every time there is an update in the sheet, a row addition or deletion

ar...@trbls.com <ar...@trbls.com> #22Oct 22, 2013 03:26PM

when will that be implemented GOOGLE ?

je...@gmail.com <je...@gmail.com> #23Jan 7, 2014 12:18AM

+1 for #6. This seems like it should be reasonably easy to implement and of great use for documents with filters used by multiple people. That way when someone filters the data and then closes the document without clearing the filter criteria it will reset automatically.

an...@gmail.com <an...@gmail.com> #24Jan 7, 2014 07:17AM

Hopefully there is a plan to provide an apps script equivalent to the new Filter Views functionality just released. Seems logical that a filtering class would provide access to create and modify "traditional" filters and also create/modify/activate the new filter views. I'm hopeful!

[Deleted User] <[Deleted User]> #25Apr 3, 2014 04:16PM

Feature urgently needed to filter a Range of a spreadsheet, according to data collected in another spreadsheet, itself populated with data issued from a Google form!

ek...@google.com <ek...@google.com> #26Apr 10, 2014 04:23PM

Reassigned to ap...@google.com.

Unassigning

ad...@gmail.com <ad...@gmail.com> #27Apr 14, 2014 04:48PM

for me, the entire filtering class would be a huge help (like in Excel VBA). I am running into issues with range copies because filters are set and I cannot clear them through script...

ar...@trbls.com <ar...@trbls.com> #28May 30, 2014 08:29AM

please implement this. We need to clear all filter onOpen event. We use the spreadsheet as the register. To eliminate duplicates we need to disable filter onOpen.

an...@castbefore.com <an...@castbefore.com> #29Jun 5, 2014 11:33PM

This will bring a lot of business values! Especially the ScriptDB is now decommissioned and even though CloudSQL is there, new Sheet is actually the most reasonable small database for small business (division). The point is if there is no query language or tool to support searching content or records in sheet, an API to support creating filters, then get filtered rows would simply mimic a query language already.

This is not just +1 from me, it's +20! We can't wait, please implement this.

[Deleted User] <[Deleted User]> #30Jun 20, 2014 01:43PM

In my opinion, clearFilter() is the function that would bring greater value. It is not unusual to happen that someone is filtering a worksheet when a script runs and eventually errors occur, because of the filters. With the function, the script can remove filters before starting. With the others functions, it could even return to the state that the user is filtering left.

ar...@trbls.com <ar...@trbls.com> #31Jun 23, 2014 08:06AM

currently there is no way of establishing from API whether the filter is on or off. onOpen trigger we need to clear all filters which could be set by other person.

Please implement clearFilters() and isFilterOn(columnNo) functions.
Thanks,
Arthur

jo...@ascensoreslaplata.com.ar <jo...@ascensoreslaplata.com.ar> #32Jun 23, 2014 11:19AM

A way to handle filter view created filters will be useful also. Some of my users always create filters that don't reuse and the list of created filters (auto named filters) continuously grows and the only way to clear them is one by one.

ar...@trbls.com <ar...@trbls.com> #33Jun 23, 2014 11:47AM

If you use the spreadsheet to store unique numbers in each row and If someone forgets to clear filter before assigning new number then your numbers are not unique any more. That is not acceptable for our company and the reason why we still use EXCEL.

ar...@trbls.com <ar...@trbls.com> #34Jun 23, 2014 11:48AM

forgotten attachement

Capture.JPG

22 KB

View

Download

zi...@cloudware360.com.pe <zi...@cloudware360.com.pe> #35Jun 25, 2014 12:46AM

All the use cases i see here can be handled by writting scripts that do the filtering and ux (not spreadsheet) to display/enter data.
The only valid case i think is a way to clear the filter, but this was solved recently by the new sheets saved user-filters which do not modify other user's views.

ra...@gmail.com <ra...@gmail.com> #36Jun 25, 2014 12:48AM

[Comment deleted]

ra...@gmail.com <ra...@gmail.com> #37Jun 25, 2014 12:51AM

It would be great if there was a way of filtering/ hiding columns. I have written my own script which checks for values in row 1 and then filters or unhides a particular column based on results. Since my script takes long time to execute and hiding/unhiding affects other users, we are not using this feature.

ad...@gmail.com <ad...@gmail.com> #38Aug 1, 2014 02:19AM

I absolutely agree that ability to clear and set filters is really needed to be able to automate sheet app solutions. In many cases, if a filter has been set and you try to modify a cell (lets say to add a specific "=query" value into the cell, the script crashes unless you turn off filters. Would like to be able to:
1. determine if a filter has been set on a sheet
2. what the filter parameters are (so I could return the sheet the the original state)
3. clear a filter
4. set a filter with specific parameters

btw, I did this often in Excel VBA and really want to fully transition my applications to Sheets.

wa...@gmail.com <wa...@gmail.com> #39Aug 1, 2014 04:25AM

Since ScriptDb is deprecated, we need filter or query functionality for spreadsheets.

ar...@trbls.com <ar...@trbls.com> #40Aug 1, 2014 11:01AM

please implement this google. Thank you.

sk...@evergreens.com.ua <sk...@evergreens.com.ua> #41Sep 4, 2014 08:26PM

Very-very important feature! Please, implement!

ar...@trbls.com <ar...@trbls.com> #42Sep 5, 2014 08:11AM

still waiting. it is just the waste of the time.

ac...@gmail.com <ac...@gmail.com> #43Sep 30, 2014 06:11PM

yes, im trying to create a filter view in the main sheets as opposed to the Data > Filter > Filter1 drop down menu. I think that it cant be that hard to implement since the actual operation is do able via a few clicks

an...@gmail.com <an...@gmail.com> #44Oct 27, 2014 05:10PM

Filters are useless in a coded sheet if I can't AT LEAST apply a custom filter… Such a detail and yet so needed!

an...@gmail.com <an...@gmail.com> #45Oct 27, 2014 05:10PM

[Comment deleted]

[Deleted User] <[Deleted User]> #46Oct 27, 2014 05:19PM

ditto. Over two years waiting for this and also over two years waiting for google drive to implement an "inFolders:" search term. We definitely are wasting our time waiting. Next time I will actively oppose using google sheets or drive for any projects requiring more than BASIC storage/text editing.

dm...@gmail.com <dm...@gmail.com> #48Dec 3, 2014 01:34PM

I also had problems with working with filtered data. I found that using copyTo with option contentOnly, will copy only the filtered data. then you can work on the copy.

al...@gmail.com <al...@gmail.com> #49Jan 1, 2015 08:19PM

For me, setting a filter for a range in a spreadsheet via a script is a very important feature. Example: my wife doesn't have the time (or inclination) to learn how to filter on arbitrary search terms. In Excel, I just created a button called "Enter ..." which popped up a text entry box to enter the search string for a fixed column, and another button called "All" which cleared the filter and showed all rows. I would like to do the same in Sheets. I look forward to this requirement being met ;-)

c....@gmail.com <c....@gmail.com> #50Jan 11, 2015 10:23AM

We have spend days of trying sub-optimal workarounds because setting filters through script is not supported. That would have been minutes if it was. +n where n=hours wasted

t2...@woolworths.com.au <t2...@woolworths.com.au> #51Jan 13, 2015 04:27AM

I agree this is also a very important feature and would simplify coding work around requirements

t2...@woolworths.com.au <t2...@woolworths.com.au> #52Jan 13, 2015 04:27AM

[Comment deleted]

ar...@trbls.com <ar...@trbls.com> #53Jan 15, 2015 09:00AM

HI Google,

Just the simple reply. Are you going to implement methods in Google SCript to enable, disable the filter and to manipulate the filter views, like create the new one, change the active etc.

Are you working on this? Are there any plans to implement this?

jo...@ascensoreslaplata.com.ar <jo...@ascensoreslaplata.com.ar> #54Jan 15, 2015 11:14AM

"to manipulate the filter views, like create the new one, change the active etc."

Into etc. please consider delete stored filter views. Some users don't re use stored filter views and since the filters are stored if you give them a name or not, they accumulate into an hard to handle list. To manipulate them could allow to write a very useful FV manager.

[Deleted User] <[Deleted User]> #55Jan 20, 2015 07:31PM

If simple and common functions such as .getValues() and setValue() can throw the error "This operation is not supported on a range with a filtered out row" (and they do!), then you simply *must* give us tools to test for those states.

Without the ability to check for reasonable conditions which are known to result in failed execution, it's impossible to build a reliable data-processing application.

ca...@whiting-turner.com <ca...@whiting-turner.com> #56Jan 21, 2015 08:27PM

[Comment deleted]

ar...@trbls.com <ar...@trbls.com> #57Jan 22, 2015 08:41AM

the solution is to go back to Excel and use

Excel.ActiveWorkbook.Worksheets(1).ListObjects(1).Range.AutoFilter

to disable all filters.

The new Excel 2013 has the ability to save files on cloud, the same way as google doing it.

We have been waiting for implementation of this function from Google for years so we decided that Excel alternative is better.

Hope this helps.
Cheers

he...@google.com <he...@google.com> #58Jan 27, 2015 03:10PM

Definitely +1. It would be very critical feature.

he...@gmail.com <he...@gmail.com> #59Jan 27, 2015 10:42PM

まだかなーまだかなー

jo...@ascensoreslaplata.com.ar <jo...@ascensoreslaplata.com.ar> #60Feb 18, 2015 03:25PM

Please, don't forget about this :-(

Filter views on protected ranges (where I expected that users with limited rights were able to filter) doesn't work, and if the editor creates filter views to be used as fixed filters, the range doesn't grow with the rows and limited range users can't change it neither.
I did my catharsis; sorry for this.

[Deleted User] <[Deleted User]> #61Mar 4, 2015 09:22AM

Want to understand if the clearFilter(;)works or not. please help.

ar...@trbls.com <ar...@trbls.com> #62Mar 4, 2015 09:36AM

please implement this. we employ 100 people and use google spreadsheets everyday. If someone sets the filter and if other forgets to 'select all' then we end up with situation like:

- we can find something using CTRL+F
- we end up with duplicated item. Because some of them are hidden people are adding same thing again.

We need to onOpen event have the possibility to clear all filters (set them to select all)

Google, Looking for some hits...

ad...@gmail.com <ad...@gmail.com> #63Mar 30, 2015 02:05AM

I really struggle regularly with this issue. If a filter has been left on and I run code to find or hide rows in preparation for printing, I get the wrong result. I have to display a message box to remind to TURN OFF the filter, run the code, and then message box to turn filter back on. Very error prone, messy and inefficient. Would love a solution at least allowing to remove all filters!

an...@acronym.com <an...@acronym.com> #64Apr 8, 2015 08:05PM

[Comment deleted]

et...@gmail.com <et...@gmail.com> #65Apr 8, 2015 11:13PM

I am amazed that after more than four years, this important item still has a medium priority status. Google, please, listen to your customers!

[Deleted User] <[Deleted User]> #66Apr 9, 2015 12:56PM

Seriously...enough is enough! At a minimum, please provide methods to:

* check for active filters; and
* clear all filters

Without these basics, you can't build an application that will produce reliable, consistent results...in other words, you can only build toys.

Please, if we can't have .getActiveFilters() and associated tools to manipulate the returned filters, then start with the Boolean .filtersActive() and a simple .clearActiveFilters().

as...@gmail.com <as...@gmail.com> #67Apr 9, 2015 01:14PM

Perhaps it's not 100% clear, since this is a very old thread, that this problem is literally a breaking bug in the new Sheets owing to the non-compatibility of filters with Range methods .copyTo(), .copyValuesToRange(), and copyFormatToRange().

The copyTo() method in particular is needed in many cases where formulas, data validations, and formats need to be extended to a range that may or may not have had filters applied to it by end users.

The rather embarrassing best practice in my organization, which manages and bulk-updates data in arrays of Spreadsheets for a large number of schools, had been to pay someone with a Masters degree to spend dozens of hours a month manually inspecting and unsetting all filters on Sheets that are being updated.

Thanks to the Masters degree, we've since discovered a breathtaking innovation, which involves a hidden properties sheet that holds a formula like =if(counta('Heat Map'!A:A)<>subtotal(3,'Heat Map'!A:A),"Yes","No") where the subtotal formula only applies to the visible rows, whereas counta applies to all. When there is a discrepancy between these two values, we know that a filter has been applied to the sheet. We now perform a check to determine if a filter is set prior to running any batch process, at which point we pay someone to manually unset filters on all the offending sheets.

Whereas the .getFilters(), .unsetFilters() etc. functionality used to be understood as an enhancement, this thread should be recategorized as a high priority bug, since the lack of compatibility between filters and multiple other sheets methods renders them almost useless.

ev...@gmail.com <ev...@gmail.com> #68Apr 10, 2015 06:20PM

+1 #6 especially for me. This functionality would create a lot of value for me for such a simple task.

ev...@gmail.com <ev...@gmail.com> #69Apr 10, 2015 06:20PM

+1 #6 especially for me. This functionality would create a lot of value for me for such a simple task.

ar...@trbls.com <ar...@trbls.com> #70Apr 10, 2015 06:58PM

it is worth to mentioned that both filter and custom filter views need to have these method implemented. In our organization we use custom filter views, so the screen of other users doesn't 'blink'

Anyway, please integrate the functionality of Filters and Filters views with Api Script.

It would be very useful to have the functions called
.createNewFilterView(nameOfTheFilter)
.renameActiveFilterView(newName)
.activateFilterView(nameOfTheFilter)//not sure what to do with filters with same name
.updateRangeOfActiveFilterView()
.deleteActiveFilterView()
.filterViewDisable()
.filter(onOrOff)
.doFilter(columnNumber,filterText) //filter text =="" to select all

Please implement these method as they are critical for our organization.

thanks

ml...@gmail.com <ml...@gmail.com> #71Apr 18, 2015 03:56PM

I agree totaly:
.activateFilterView would be very appreciated !!!!!!

and others function around the filtered view

Thank you in advance

sf...@gmail.com <sf...@gmail.com> #72May 11, 2015 08:50AM

I agree, this is a must.
Any workaround for this for now?

ad...@gmail.com <ad...@gmail.com> #73Jun 10, 2015 05:21PM

YES PLEASE

mz...@gmail.com <mz...@gmail.com> #74Jun 17, 2015 02:48PM

.getFilteredRows() would enhance our add-on enormously!! Any temporary workarounds available?

be...@gmail.com <be...@gmail.com> #75Jun 22, 2015 06:17PM

Our add-on have lots of users who are requesting this feature.

Is there anything we can do except wait? A workaround?

ma...@vinodgubbala.com <ma...@vinodgubbala.com> #76Jun 23, 2015 02:17PM

I am also waiting for this for a long time.

ar...@trbls.com <ar...@trbls.com> #77Jun 23, 2015 03:50PM

you can wait forever and Google don't even tell you what they are after.

[Deleted User] <[Deleted User]> #78Jul 16, 2015 09:44AM

I have created a workaround. Take a look at

https://gist.github.com/carlbergman/1a1dd786425bc9180b29

The workaround abuses the fact that you can only change the font color on non-filtered cells.

If you use this workaround it would be wise to keep track of

issue 36761726

https://code.google.com/p/google-apps-script-issues/issues/detail?id=3981

da...@gmail.com <da...@gmail.com> #79Jul 16, 2015 01:16PM

@ #79 clever and nifty. Thanks.

ar...@trbls.com <ar...@trbls.com> #80Jul 16, 2015 01:21PM

so what the workaround actually does? Sets the font? I want functions enableFilter or disableFilter, isEnabled?

[Deleted User] <[Deleted User]> #81Jul 16, 2015 03:31PM

@ #81: The workaround does the following:

1. Gets the font colors of a range.
2. Changes the font colors of a range.
3. Gets the font colors of the same range.
4. Changes the font colors back to the initial colors.

In step 3 we can see which cells were filtered. Only non-filtered cells have changed font color. My method stores the values from non-filtered cells in an array, because it suits my use case.

It can probably be modified to OP's .getFilteredRows() and it's only a slight modification of OP's .getFilteredValues(). I'm afraid it can't be used for the rest of the use cases.

ju...@gmail.com <ju...@gmail.com> #82Aug 4, 2015 09:56PM

I think that this function would be greatly useful

tb...@gmail.com <tb...@gmail.com> #83Aug 8, 2015 09:48PM

Another useful method not mentioned in the request or in any of the comments is reapplyFilter(), to simply cause a re-filtering of the rows, so it can be invoked in an onEdit() method. The ability to change filters is great, but since editing doesn't re-apply the filter, there needs to be a method to allow you to do that.

ca...@whiting-turner.com <ca...@whiting-turner.com> #84Aug 14, 2015 08:08PM

[Comment deleted]

ch...@gmail.com <ch...@gmail.com> #85Aug 17, 2015 01:41PM

This functionality would be very useful.

c....@gmail.com <c....@gmail.com> #86Aug 18, 2015 06:45PM

Yes, just re-apply would be just enough, because now we have conditional filters. We just need a way of programmatically re-applying the existing filters.

ar...@trbls.com <ar...@trbls.com> #87Aug 18, 2015 07:42PM

YOU WILL WAIT ANOTHER 5 YEARS TO GET THIS IMPLEMENTED. Google doesn't even bother to update its release notes:

https://developers.google.com/apps-script/releases/

Last update took 3 months ago.

[Deleted User] <[Deleted User]> #88Sep 8, 2015 08:50PM

I would love this feature!

il...@newvisions.org <il...@newvisions.org> #89Sep 16, 2015 10:23PM

Please prioritize this issue Google! We have been waiting for 4 years!

[Deleted User] <[Deleted User]> #90Oct 7, 2015 06:25PM

I just can't figure out how Google didn't provide this feature yet!
It's so necessary, so obvious.

ar...@trbls.com <ar...@trbls.com> #91Oct 15, 2015 03:35PM

Hi google, can you at least tell us whether this will be implemented or not?

pr...@gmail.com <pr...@gmail.com> #92Oct 27, 2015 10:26AM

that's important also for me! many thanks developers!

[Deleted User] <[Deleted User]> #93Oct 31, 2015 07:33PM

GOOGLE DEVELOPERS this feature is being requested by many of your users, WILL WE GET AN ANSWER?

on...@gmail.com <on...@gmail.com> #94Nov 9, 2015 05:43AM

This is a must. Please add this feature.

[Deleted User] <[Deleted User]> #95Nov 11, 2015 09:13AM

Thus feature would make things easier please help

[Deleted User] <[Deleted User]> #96Nov 17, 2015 09:09PM

Please Add this feature

ad...@hopbrookcc.com <ad...@hopbrookcc.com> #97Nov 20, 2015 08:43PM

I want this feature as well, specifically setFilter()

si...@gmail.com <si...@gmail.com> #98Dec 14, 2015 07:14AM

I would even settle for sheet.refreshFilter() as values changed by a script will not be reflected in an existing filter unless manually refreshed by the user

fr...@westbeach.com <fr...@westbeach.com> #99Dec 15, 2015 11:09AM

Adding my voice to the crowd here. I just spent a while trying to figure out how to get user filter criteria, assuming that it would be possible... and ended up here. I'm surprised this has not been addressed given the demand! Any info please google?

[Deleted User] <[Deleted User]> #100Dec 17, 2015 07:44PM

It would be great to have sheet.enableFilter(). Set it off and on to clear the criteria for the filters.

Filter Dropdown -> Select all -> OK (Repeat for multiple criteria)

Doing this is very annoying.

he...@fuw.org.uk <he...@fuw.org.uk> #101Jan 8, 2016 03:37PM

Please get the filters available in scripts Google - I was very disappointed to find you can't do it yet.

[Deleted User] <[Deleted User]> #102Jan 14, 2016 01:08AM

Yes please

in...@gmail.com <in...@gmail.com> #103Jan 15, 2016 08:55PM

Yes please, make it ASAP.

br...@gmail.com <br...@gmail.com> #104Jan 18, 2016 08:46PM

Yes please

ky...@gmail.com <ky...@gmail.com> #105Jan 19, 2016 02:40AM

Please

da...@gmail.com <da...@gmail.com> #106Jan 22, 2016 04:37PM

We need to clear filters!!! The reason is obvious: filters bring us errors when writing on the sheet!

Don't you want to beat Excel?

Anyway, there is a possible workaround here:

http://stackoverflow.com/questions/26807618/how-can-i-remove-all-filters-in-a-google-spreadsheet-using-google-apps-script

in...@gmail.com <in...@gmail.com> #107Jan 30, 2016 03:23PM

Please fix :)

em...@gmail.com <em...@gmail.com> #108Feb 4, 2016 10:29AM

Really need this functionality, although I would like to see ability to turn on and off view-filters too.

ar...@gmail.com <ar...@gmail.com> #109Feb 12, 2016 05:43PM

Yes, sending emails to Filtered rows help us tremendously. Please help.

ts...@ciyh.co.uk <ts...@ciyh.co.uk> #110Feb 17, 2016 12:32PM

I really need the ability to update the view to remove recently added rows from the currently active filter.

ke...@barronltg.com <ke...@barronltg.com> #111Feb 23, 2016 10:39PM

this would be awesome!

xi...@bc.edu <xi...@bc.edu> #112Feb 26, 2016 09:07PM

Yes, this functionality would rock! Please implement!

st...@gmail.com <st...@gmail.com> #113Feb 29, 2016 02:31PM

Absolutely! Please! :)

in...@saidattanj.org <in...@saidattanj.org> #114Feb 29, 2016 02:33PM

Yes, sending emails to Filtered rows will help very much !

qu...@gmail.com <qu...@gmail.com> #115Mar 9, 2016 08:38AM

That's very useful! plase do it! many thanks,

Igor Fiorini

in...@morvanremorques.fr <in...@morvanremorques.fr> #116Mar 11, 2016 02:52AM

Yes, please add. At least ability to determine if filter active. Had an issue today caused by lack of this.

in...@morvanremorques.fr <in...@morvanremorques.fr> #117Mar 11, 2016 03:07AM

[Comment deleted]

se...@gmail.com <se...@gmail.com> #118Mar 13, 2016 04:01AM

I definitely need to filter rows for massive emailings.

he...@jasondainter.com <he...@jasondainter.com> #119Mar 13, 2016 01:31PM

Please add this!

rf...@gmail.com <rf...@gmail.com> #120Apr 1, 2016 07:18PM

Google,

At a minimum, even partial, non-controversial functionality would be immensely helpful. The most common use-case seems to be just to be able to de-activate all filters.
Please consider at least doing .DeactivateFilters()

za...@gmail.com <za...@gmail.com> #121Apr 2, 2016 05:03PM

Dear Google, Please add this feature.

al...@temet.ch <al...@temet.ch> #122Apr 3, 2016 06:36PM

I also need this feature, at least to deactivate the filter. I use the Sheets API to write to user accessible sheets, and if someone sets an autofilter, the writes are ignored. Which means a manual correction whenever somebody uses an autofilter-

[Deleted User] <[Deleted User]> #123Apr 8, 2016 02:43PM

This feature would be great to have

sa...@gmail.com <sa...@gmail.com> #124Apr 21, 2016 08:12AM

How long will this take to be available?

This is a must if you want to merge spreadsheet data into documents (i.e. print reports, labels, lists) and you need to filter the printed data

[Deleted User] <[Deleted User]> #125Apr 27, 2016 06:10PM

This is a must. Add and Remove Filters from Cells are a must!

ho...@gmail.com <ho...@gmail.com> #126Apr 28, 2016 09:32AM

[Comment deleted]

ho...@gmail.com <ho...@gmail.com> #127Apr 28, 2016 09:33AM

It would be a huge help if this is available

ma...@york.ac.uk <ma...@york.ac.uk> #128Apr 29, 2016 09:22AM

Dear Google Dev,

Please implement a set of basic filter functions!

sheet.setFilter()
sheet.removeFilter()
sheet.getFilterRange()

Thank you,
Matt Hodges

lu...@gmail.com <lu...@gmail.com> #129May 8, 2016 02:32AM

I would like to get data as seen in a filter view.

I.e. I have a filter view that has the data alphabetical, or a subset of data, and I want to easily work on that without having to re-implement the filtering in Apps Script.

ad...@centrallocal.org <ad...@centrallocal.org> #130May 9, 2016 02:57PM

This needs fixed asap. I can't even append a row to a spreadsheet with a script if the data filter is on. That means I can't automate my project. I need to be able to clear all filters before it runs.

Please create these functions, I can't believe the wait time on this project.

sheet.setFilter()
sheet.removeFilter()

al...@gmail.com <al...@gmail.com> #131May 12, 2016 08:34AM

We all need it!

vi...@gmail.com <vi...@gmail.com> #132May 20, 2016 07:10AM

It's a must to have this...

ri...@autovance.com <ri...@autovance.com> #133Jun 1, 2016 12:55AM

Any update on this? I'm really struggling to understand why this hasn't been implemented.

ph...@incisive.org <ph...@incisive.org> #134Jun 2, 2016 09:47PM

I also would like this function

ik...@jig.jp <ik...@jig.jp> #135Jun 20, 2016 02:11AM

[Comment deleted]

jo...@aanscharius.com <jo...@aanscharius.com> #136Jun 20, 2016 06:18PM

It is so simple to implement, why don't they?
Every menu action should have to be possible to handle by a separate button, which refers via a script to a certain menu.
Something like applymenu(6;8;2) which in this case would be applying a certain filterview. Imagine what you could do, this is SO MUCH MORE.

Of course, if versions change, it could be your reference to a menu doesn't work anymore. But I would not bother to update, if google sheets is updated.

ma...@neverland.com.ar <ma...@neverland.com.ar> #137Jun 23, 2016 01:50PM

5 years and not even an acknowledgment from google?

ro...@gmail.com <ro...@gmail.com> #138Jun 29, 2016 08:26PM

The new Sheets API (V4), presented at Google I/O 2016, provides support to get & set filters on a sheet (both basic filter & filtered views).

It's possible to call this API from Apps Script (via UrlFetch). Here's an example:

https://script.google.com/d/1MXxVLPUEQF9s9L3uzJ5dTn4W5ur8yUn15dR7Y_yzUzClHyAjnmdBDCvn/edit?usp=sharing
(note that it works like an Advanced Service, you need to activate the API in the Developers Console Project).

Bruce Mcpherson built an Apps Script library to get filtered values from a sheet:

http://ramblings.mcpher.com/Home/excelquirks/exceldocsintegration/filters

This library will be updated later on to support the ability to set or delete filters on a sheet.
Code source is available on GitHub -

https://github.com/brucemcpherson/SheetsMore

[Deleted User] <[Deleted User]> #139Jul 11, 2016 09:24AM

wow thank you. will have a pop at this ;-)

jo...@ascensoreslaplata.com.ar <jo...@ascensoreslaplata.com.ar> #140Sep 19, 2016 04:05PM

For those who may find a way to delete filter views and turn basic filter off useful, check the following stackoverflow entry

https://goo.gl/1EalGQ

[Deleted User] <[Deleted User]> #141Oct 20, 2016 04:03PM

These most recent solutions described in #139 and #141 do not work for me, as the sheet will not allow the deletion of rows that contain form questions. It would be great if google implemented a GAS native solution for this.

jo...@trans-plus.com <jo...@trans-plus.com> #142Dec 1, 2016 09:01PM

[Comment deleted]

jo...@trans-plus.com <jo...@trans-plus.com> #143Dec 1, 2016 09:29PM

update #139 proposed solution not working, returning error

Request failed for

https://sheets.googleapis.com/v4/spreadsheets/xx[MY SHEET SSID]xx?fields=sheets%2FbasicFilter returned code 400. Truncated server response: { "error": { "code": 400, "message": "Client project not found. Please pass a valid project.", "status": "INVALID_ARGUMENT", "det... (use muteHttpExceptions option to examine full response)

kr...@gmail.com <kr...@gmail.com> #144Dec 28, 2016 12:03AM

please implement this google. Thank you.

co...@gustinquon.com <co...@gustinquon.com> #145Jan 23, 2017 10:14PM

[Comment deleted]

sl...@gmail.com <sl...@gmail.com> #146Jan 29, 2017 03:50AM

Please implement

ev...@gmail.com <ev...@gmail.com> #147Feb 3, 2017 01:46PM

S'il vous plait, pretty please!!

[Deleted User] <[Deleted User]> #148Feb 16, 2017 05:26PM

This would be a powerful tool to let us programmatically edit/enable filter views. really still needed.

ro...@gmail.com <ro...@gmail.com> #149Feb 23, 2017 03:17PM

Yes, it would be very very interesting to have the possibility to set filters programmatically.

pe...@gmail.com <pe...@gmail.com> #150Feb 27, 2017 10:35AM

Please implement

ba...@cactusauto.com <ba...@cactusauto.com> #151Feb 27, 2017 10:45PM

I am in disbelief that this portion of the api is in the feature requests trash bin, and not on the apps script reference page.

j....@nubalia.com <j....@nubalia.com> #152Mar 9, 2017 01:02PM

I can hardly believe such a basic feature is still being ignored after so many years.

ja...@gmail.com <ja...@gmail.com> #153Mar 23, 2017 02:56PM

Please listen to us and add a way to clear a filter in a sheet before other code tries to run on it.

filtererror.png

24 KB

View

Download

jo...@gmail.com <jo...@gmail.com> #154Mar 24, 2017 11:52AM

I really need the function to clear all filters before manipulating the data.

[Deleted User] <[Deleted User]> #155Apr 8, 2017 05:59AM

incredible six years and nothing

ma...@neverland.com.ar <ma...@neverland.com.ar> #156Apr 10, 2017 10:59AM

Some 'Issue tracker'. We'd have better luck asking for this on 4chan.

ro...@gmail.com <ro...@gmail.com> #157Apr 13, 2017 08:49PM

You can use the new Google Sheets advanced service to get the filtered rows, clear all filters,...
See this blog post for more info:

https://gsuite-developers.googleblog.com/2017/04/using-google-sheets-filters-in-add-ons.html

function getIndexesOfFilteredRows(ssId, sheetId) {
var hiddenRows = [];

// limit what's returned from the API
var fields = "sheets(data(rowMetadata(hiddenByFilter)),properties/sheetId)";
var sheets = Sheets.Spreadsheets.get(ssId, {fields: fields}).sheets;

for (var i = 0; i < sheets.length; i++) {
if (sheets[i].properties.sheetId == sheetId) {
var data = sheets[i].data;
var rows = data[0].rowMetadata;
for (var j = 0; j < rows.length; j++) {
if (rows[j].hiddenByFilter) hiddenRows.push(j);
}
}
}
return hiddenRows;
}

ar...@trbls.com <ar...@trbls.com> #158Apr 18, 2017 08:04AM

great mate, That is what I wanted.

ry...@google.com <ry...@google.com> #159Apr 18, 2017 02:44PM

Accepted by ap...@google.com.

As mentioned in #157, it is possible to access and edit filters using the Sheets Advanced Service. The functionality does not exist in the regular Sheets service at this time, so we will keep this feature request open for now.

ki...@gmail.com <ki...@gmail.com> #160Apr 18, 2017 07:33PM

Could I please get taken off this list?

On Apr 8, 2017 5:59 PM, <buganizer-system@google.com> wrote:

- Show quoted text -

Kh...@tlpk.com <Kh...@tlpk.com> #161May 10, 2017 07:45AM

Yes, I want to add a code on sheet open i.e.

if (email == 'abc@gmail.com' )
sheet.setFilter('SalesView')

ki...@gmail.com <ki...@gmail.com> #162May 12, 2017 10:55PM

Unsubscribe me please

On May 10, 2017 7:45 PM, <buganizer-system@google.com> wrote:

- Show quoted text -

et...@gmail.com <et...@gmail.com> #163May 12, 2017 11:07PM

Man, (ki...@gmail.com)
Just read the emails you get through the end!

Two hint :
- you only need one second of thinking and two clicks to get peace of mind by yourself!!!
- Google must admit that the mouseover tip might be counterintuitive at first reading for some poeple.

Lets go : give it a try!

ki...@gmail.com <ki...@gmail.com> #164May 12, 2017 11:09PM

Unsubscribe please

On May 13, 2017 11:08 AM, <buganizer-system@google.com> wrote:

- Show quoted text -

et...@gmail.com <et...@gmail.com> #165May 12, 2017 11:12PM

Sorry, I forgot to Google, that this is still an urgent need!!!

jp...@gmail.com <jp...@gmail.com> #166May 12, 2017 11:14PM

ki... please go to

https://issuetracker.google.com/issues/36753410 and click the little up left star.
et... please be nice :-p

ki...@gmail.com <ki...@gmail.com> #167May 12, 2017 11:18PM

I have used the unstar function several times but still get updates....
when I search for started items, it doesn't even show up but I am still
receiving all this.
Maybe the team could remove my email from those conversations?

On May 13, 2017 11:14 AM, <buganizer-system@google.com> wrote:

- Show quoted text -

[Deleted User] <[Deleted User]> #168May 15, 2017 01:45AM

First of all, thank you Google Dev's for finally resolving this issue! :) As for all of you wishing to unsubscribe from this issue, try this: head over to the "Starred by me" section in the left panel, once there look for this issue in the mail panel and select it, finally tick "Un-Cc me" in the E-mail drop-down. Not sure if that will work, but it's worth a try.

ja...@gmail.com <ja...@gmail.com> #169May 17, 2017 10:53PM

I just want to clear any filter a user has left applied on a Google Sheet before Apps Script code pulls in data from a database. I see examples of how to set a filter, but how do you simply clear a filter?

da...@gmail.com <da...@gmail.com> #170May 18, 2017 05:15AM

Reply to #169 and others.

There is a workaround to "remove filters":

http://stackoverflow.com/questions/26807618/how-can-i-remove-all-filters-in-a-google-spreadsheet-using-google-apps-script/30494211#30494211

ja...@gmail.com <ja...@gmail.com> #171May 18, 2017 12:08PM

Thanks. Yes we've seen that workaround, but in our situation we have a lot
of reports with multiple sheets which sometimes have different numbers of
header rows due to total formulas being at the top so that when a user
filters, they can see the resulting total from the columns they filtered.

Our hope was that this new Sheets API filter function would enable us to
simply clear all filters on a sheet before the data was pulled in from our
database nightly.

On Thu, May 18, 2017 at 12:16 AM, <buganizer-system@google.com> wrote:

- Show quoted text -

ro...@gmail.com <ro...@gmail.com> #172May 18, 2017 12:18PM

Here's how to clear all filters on a given sheet, using the new Sheets Advanced Service.

function clearFilter() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ssId = ss.getId();
var sheetId = ss.getActiveSheet().getSheetId();
var requests = [{
"clearBasicFilter": {
"sheetId": sheetId
}
}];
Sheets.Spreadsheets.batchUpdate({'requests': requests}, ssId);
}

ja...@gmail.com <ja...@gmail.com> #173May 18, 2017 12:37PM

Thank You, thank you, thank you!

On Thu, May 18, 2017 at 7:18 AM, <buganizer-system@google.com> wrote:

- Show quoted text -

ar...@trbls.com <ar...@trbls.com> #174May 18, 2017 01:49PM

'clearFilter' function turns off filter. How can I leave the filters on but 'select all' items so there are none rows hidden?

ro...@gmail.com <ro...@gmail.com> #175May 18, 2017 02:13PM

Like that:

function resetFilter() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ssId = ss.getId();
var dataSheet = ss.getActiveSheet();
var lastRow = dataSheet.getLastRow();
var lastColumn = dataSheet.getLastColumn();
var sheetId = dataSheet.getSheetId();

var filterSettings = {
"range": {
"sheetId": sheetId,
"startRowIndex": 0,
"endRowIndex": lastRow,
"startColumnIndex": 0,
"endColumnIndex": lastColumn
}
};
var requests = [{
"setBasicFilter": {
"filter": filterSettings
}
}];
Sheets.Spreadsheets.batchUpdate({'requests': requests}, ssId);
}

ar...@trbls.com <ar...@trbls.com> #176May 18, 2017 03:19PM

you are the hero! thank you

do...@lawsonweb.org <do...@lawsonweb.org> #177May 26, 2017 04:10AM

#175 shows you how to reset a filter but can someone show how you can filter a particular column for a specific string?

ro...@gmail.com <ro...@gmail.com> #178May 26, 2017 08:23AM

Sure, here's an example, where "columnIndex" is your particular column and "hiddenValues" your specific string.
I've compiled all code snippets here:

https://sites.google.com/site/scriptsexamples/learn-by-example/google-sheets-api/filters

function setFilter() {
var ss = SpreadsheetApp.getActiveSpreadsheet();

var filterSettings = {};

// The range of data on which you want to apply the filter.
// optional arguments: startRowIndex, startColumnIndex, endRowIndex, endColumnIndex
filterSettings.range = {
sheetId: ss.getActiveSheet().getSheetId()
};

// Criteria for showing/hiding rows in a filter
//

https://developers.google.com/sheets/api/reference/rest/v4/FilterCriteria
filterSettings.criteria = {};
var columnIndex = 2;
filterSettings['criteria'][columnIndex] = {
'hiddenValues': ["England", "France"]
};

var request = {
"setBasicFilter": {
"filter": filterSettings
}
};
Sheets.Spreadsheets.batchUpdate({'requests': [request]}, ss.getId());
}

mj...@mtu.edu <mj...@mtu.edu> #179Aug 9, 2017 01:22PM

I have an application this would work wonderful on since filter views are not compatible with ANDROID/Iphone mobile apps!

da...@vli.lt <da...@vli.lt> #180Sep 23, 2017 12:58PM

I am trying to run a script and got this: ReferenceError: "Sheets" is not defined. (line 25, file "Code"). Could somebody tell me how to solve this?

[Deleted User] <[Deleted User]> #181Nov 2, 2017 08:45AM

Same error
ReferenceError: "Sheets" is not defined

ro...@diversey.com <ro...@diversey.com> #182Nov 12, 2017 05:20PM

ReferenceError: "Sheets" is not defined. (line 25, file "Code").

go to Resources, then "Advanced Google Services", locate "Google Sheets API and enable.
also, go to

https://console.cloud.google.com/apis/dashboard and enable "Sheets API"

ac...@gmail.com <ac...@gmail.com> #183Nov 29, 2017 09:36AM

Must thing!!!

[Deleted User] <[Deleted User]> #184Dec 7, 2017 02:25AM

<purged>

is...@gmail.com <is...@gmail.com> #185Dec 20, 2017 08:43AM

When is this going to be implemented for consumer users of sheets/apps script?

[Deleted User] <[Deleted User]> #186Jan 6, 2018 03:01AM

Steps to get the Advanced Google Services working, and avoid the ReferenceError: "Sheets" is not defined. (line 25, file "Code"). error:

1) you need to follow all of the steps to enable Advanced Google Services. I wrote a quick blog post to help illustrate all the steps, because it's really easy to overlook one or another.

https://www.questionabledecisionmaking.com/blog/2018/1/5/scripting-google-sheets-filters

sh...@gmail.com <sh...@gmail.com> #187Feb 5, 2018 04:10AM

#172 How do I do that on every sheet in the workbook? I want it to work on every sheet present in the workbook

tu...@gmail.com <tu...@gmail.com> #188Apr 6, 2018 03:10PM

I need reflesh filtered data automaticly

ek...@google.com <ek...@google.com> #189Apr 12, 2018 05:30PM

Yesterday we released some new functionality that makes it possible to manipulate filters with Apps Script:

-

https://developers.google.com/apps-script/reference/spreadsheet/range#createFilter()
-

https://developers.google.com/apps-script/reference/spreadsheet/sheet#getfilter
-

https://developers.google.com/apps-script/reference/spreadsheet/filter

Unfortunately Apps Script still doesn't have methods for determining if a given row or column is hidden by the filter. See

comment #157

for a workaround utilizing the Sheets Advanced Service.

[Deleted User] <[Deleted User]> #190Apr 12, 2018 05:45PM

Still a wonderful advance -- thank you!

mu...@gmail.com <mu...@gmail.com> #191Apr 12, 2018 07:46PM

How do I remove my e-mail from this?

[Deleted User] <[Deleted User]> #192Apr 13, 2018 02:08PM

First off: Thanks. This will be helpful.
What's the easiest method to simply refresh a current filter?
Do we have to iterate through each column, get it's filter criteria, and - if not null - set it's filter criteria to the same thing?
Is there a plan to add a filter.refresh() method in the future?

sc...@gmail.com <sc...@gmail.com> #193Nov 11, 2018 02:57PM

#175 posted the following:

function resetFilter() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ssId = ss.getId();
var dataSheet = ss.getActiveSheet();
var lastRow = dataSheet.getLastRow();
var lastColumn = dataSheet.getLastColumn();
var sheetId = dataSheet.getSheetId();

var filterSettings = {
"range": {
"sheetId": sheetId,
"startRowIndex": 0,
"endRowIndex": lastRow,
"startColumnIndex": 0,
"endColumnIndex": lastColumn
}
};
var requests = [{
"setBasicFilter": {
"filter": filterSettings
}
}];
Sheets.Spreadsheets.batchUpdate({'requests': requests}, ssId);
}

My questions is.... where do I enter the stuff specific to my spreadsheet. I'm just learning to create buttons on spreadsheets, but I've never learned coding. Can someone just highlight the spots I need to enter stuff specific to my sheets. Also, is there a good resource for learning more about this? I got my first button to work yesterday and I'm in love.

ba...@gmail.com <ba...@gmail.com> #194Dec 18, 2018 07:37PM

co...@gmail.com <co...@gmail.com> #195Feb 2, 2019 04:24AM

We need a clear filter option in the menu and with a keyboard shortcut ASAP!!!!!

da...@gmail.com <da...@gmail.com> #196Mar 2, 2019 12:49AM

Is this now possible or not?
Like this:

onOpen()
{
switchToFilterView('filterName');
}

jb...@google.com <jb...@google.com> #197Mar 13, 2019 07:56PM

You can determine if a row is hidden by the current filter using this method:

https://developers.google.com/apps-script/reference/spreadsheet/sheet#isrowhiddenbyfilterrowposition

That covers the shortcoming that was noted in

comment #189

ra...@pwc.com <ra...@pwc.com> #198Mar 14, 2019 02:29PM

Need a way to clear all filters, it is time consuming when working on large spreadsheet to go through and clear each filter to "Select All", unlike Excel where you can clear all filters with the click of a button.

jb...@google.com <jb...@google.com> #199Mar 14, 2019 03:28PM

#198 - Rachael: Here is some sample code that can do that:

// @OnlyCurrentDoc

function onOpen() {
SpreadsheetApp.getUi().createMenu("Filters").addItem("Clear all filters", "clearFilters").addToUi();
}

function clearFilters() {
var filter = SpreadsheetApp.getActiveSheet().getFilter();
if (filter) {
for (var column = filter.getRange().getColumn(); column <= filter.getRange().getLastColumn(); ++column) {
filter.removeColumnFilterCriteria(column);
}
}
}

jb...@google.com <jb...@google.com> #200Mar 19, 2019 05:33PM

Marked as fixed.

I believe that this request is done. New methods have been added for managing filters.

an...@cdp.net <an...@cdp.net> #201Mar 19, 2019 05:38PM

Actually, there's still no way to delete unused filters, right?
My problem is that over time, in a shared Google Sheet, we have accumulated hundreds of filter views that are unused. There is no way to delete them in bulk.
The new methods only allow you to add/remove filters that are actually applied to the sheet, if I understand correctly

jo...@ascensoreslaplata.com.ar <jo...@ascensoreslaplata.com.ar> #202Mar 19, 2019 06:58PM

an... Workaround: check #140

[Deleted User] <[Deleted User]> #203Mar 22, 2019 03:03PM

remove duplicates without installing an addon

Message last modified on Mar 22, 2019 03:03PM

rb...@salesforce.com <rb...@salesforce.com> #204Apr 21, 2019 01:54PM

In Macros, While creating a filter if I use setHiddenValues method if tomorrow any new value comes, then that filter not gonna filter out the new value. It would be great if we have a method to set the filter not to hide. or is it already there? Please help.

la...@gmail.com <la...@gmail.com> #205Sep 28, 2019 02:01AM

Please update the filter view everytime there new item added

lu...@gmail.com <lu...@gmail.com> #206Dec 16, 2019 10:01PM

Would be very handy to have automated filtering whe n new item is added

we...@gmail.com <we...@gmail.com> #207Mar 31, 2020 12:47PM

Oh yes please do this

el...@gmail.com <el...@gmail.com> #208May 22, 2020 01:05PM

i agree!

v....@informatica.aci.it <v....@informatica.aci.it> #209Jul 17, 2020 09:00AM

Hi this function is also important for me.

thank you

[Deleted User] <[Deleted User]> #210Aug 7, 2020 06:18AM

Please add this functionality!

sa...@gmail.com <sa...@gmail.com> #211Aug 11, 2020 06:44AM

Pl add this functionality.

ro...@one-line.com <ro...@one-line.com> #212Aug 12, 2020 02:52PM

I agree!

[Deleted User] <[Deleted User]> #213Oct 22, 2020 07:18PM

This would be great!

g....@leevingroup.com <g....@leevingroup.com> #214Nov 16, 2020 01:19PM

That would be so useful

it...@evesarts.com <it...@evesarts.com> #215Nov 17, 2020 08:22AM

Agree a very useful functionality

or...@gmail.com <or...@gmail.com> #216Jan 25, 2021 01:47PM

Please, add this functionality

ro...@gmail.com <ro...@gmail.com> #217Jan 25, 2021 03:41PM

Yes, it would be very useful!!!

ma...@verizon.com <ma...@verizon.com> #218Mar 15, 2021 05:27PM

Mainly it would be super!

jl...@accoes.com <jl...@accoes.com> #219Apr 30, 2021 05:25PM

Please add this feature. Thank you!

br...@gmail.com <br...@gmail.com> #220May 12, 2021 03:19PM

I'd love this feature too!

li...@gmail.com <li...@gmail.com> #221May 13, 2021 12:25AM

This shows as Status=Fixed.

Can someone point me to the documentation for the new functionality?

rl...@redhat.com <rl...@redhat.com> #222May 22, 2021 05:48PM

+1 -- would love this!

th...@gmail.com <th...@gmail.com> #223Jul 22, 2021 10:23AM

Fixed, but not yet in Prod, it seems.

er...@veolia.com <er...@veolia.com> #224Aug 31, 2021 05:16PM

please does th foncitonnality in place.
please implement this, basically it makes any sense to have filter which stay not automatically update? this is more a lack of dev than a nex fonctionnality required
.
thank you

yo...@gmail.com <yo...@gmail.com> #225Sep 13, 2021 12:03PM

function resetFilter() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssId = ss.getId();
  var dataSheet = ss.getActiveSheet();
  var lastRow = dataSheet.getLastRow();
  var lastColumn = dataSheet.getLastColumn();
  var sheetId = dataSheet.getSheetId();

  var filterSettings = {
    "range": {
      "sheetId": sheetId,
      "startRowIndex": 0,
      "endRowIndex": lastRow,
      "startColumnIndex": 0,
      "endColumnIndex": lastColumn
    }
  };

  var requests = [{
    "setBasicFilter": {
      "filter": filterSettings
    }

  }];

  Sheets.Spreadsheets.batchUpdate({'requests': requests}, ssId);
}

I'm interested in this script, it does reset Basic Filter, is there anyway to do the same with the current filter views that we open?

Like for example, I open Filter 1 and it has filter value on columns B and D, with the similar script above can we achieve do the same so basically we just need to remove the filtered column and update the range on the top left?

du...@vrijclbdehavens.be <du...@vrijclbdehavens.be> #226Oct 30, 2021 04:22AM

Please add this functionality. It would make it possible to create a user friendly sheet with multiple searchboxes, which each user can set without affecting the data viewed by another user and with the ability to edit the data on the same sheet. Above cannot be done with the query function of other functions with Google Sheets. We can use the slicer, but it cannot made as user friendly as filter views which, when made possible to code, could be used easily for users with the help of apps script.
Adding this functionality would be a big upgrade to Google Sheets, it would give the possibility to make a lot of workflows easier. Above can now only be done with a Webapp (but then lots of coding is required) or software like AppSheet, Airtable or MS Access.

Message last modified on Oct 30, 2021 04:35AM

du...@vrijclbdehavens.be <du...@vrijclbdehavens.be> #227Oct 30, 2021 04:23AM

Why does this has the status "Fixed"? Can you please follow up on this functionality or provide some feedback? Thank you in advance!

ja...@gmail.com <ja...@gmail.com> #228Dec 24, 2021 04:04PM

why does this say fixed?
It is not fixed at all.
Please fix the filters so they update automatically when you change the data.

er...@veolia.com <er...@veolia.com> #229Dec 30, 2021 10:14AM

I m back , no news?
do not agree how it is "fixed"?

please answer?

gl...@gmail.com <gl...@gmail.com> #230Feb 1, 2023 09:28PM

Please add automatic filter update

qq...@gmail.com <qq...@gmail.com> #231Apr 11, 2023 03:41PM

PLEASE ADD AUTOMATIC FILTER UPDATE

mo...@spektrum.de <mo...@spektrum.de> #232Jul 6, 2023 11:54AM

>PLEASE ADD AUTOMATIC FILTER UPDATE
Please also implement this for filter views!

li...@gmail.com <li...@gmail.com> #233Jul 6, 2023 05:52PM

Very confused about "Status = Fixed" on this one.

Anybody have any ideas?

jo...@gmail.com <jo...@gmail.com> #234Aug 18, 2023 04:50PM

same we need this pls google

li...@gmail.com <li...@gmail.com> #235Aug 18, 2023 05:35PM

Does anybody understand why this is listed as "FIXED"?

Did something change?

su...@gscrd.com.br <su...@gscrd.com.br> #236Jan 17, 2024 01:08PM

i dont know why the status its fix if nothing was made... I have created an macro and create an filterView, rename him, duplicate and delete. But in macro code only showed Spreadsheet().active() and an getRange with activated. i think this was not implemented. An way to create an filterView with app script will be very nice for who use app script and google sheets daily. Please google!

Message last modified on Jan 17, 2024 01:10PM

Issue 36753410

Description

Issue summary

Comments

sa...@google.com <sa...@google.com> Apr 6, 2011 02:25PM

ri...@gmail.com <ri...@gmail.com> #2Apr 9, 2011 06:43PM

jp...@gmail.com <jp...@gmail.com> #4Apr 15, 2011 07:52AM

ki...@gmail.com <ki...@gmail.com> #5Apr 18, 2011 11:55AM

m....@gmail.com <m....@gmail.com> #6Dec 5, 2011 09:28AM

su...@gmail.com <su...@gmail.com> #7Mar 13, 2012 10:05AM

[Deleted User] <[Deleted User]> #8May 31, 2012 01:41PM

ri...@gmail.com <ri...@gmail.com> #9Aug 20, 2012 11:44AM

[Deleted User] <[Deleted User]> #10Aug 20, 2012 04:11PM

ri...@gmail.com <ri...@gmail.com> #11Aug 20, 2012 10:20PM

pe...@pezius.com <pe...@pezius.com> #12Sep 15, 2012 12:35PM

ne...@gmail.com <ne...@gmail.com> #13Sep 15, 2012 04:55PM

mg...@interpunkt.hr <mg...@interpunkt.hr> #14Oct 11, 2012 06:22PM

in...@gmail.com <in...@gmail.com> #15Nov 30, 2012 03:36PM

re...@gmail.com <re...@gmail.com> #16Dec 14, 2012 03:11AM

ma...@gmail.com <ma...@gmail.com> #17Apr 26, 2013 02:52PM

[Deleted User] <[Deleted User]> #18May 17, 2013 08:33PM

pj...@gmail.com <pj...@gmail.com> #19May 25, 2013 02:24PM

ge...@gmail.com <ge...@gmail.com> #20Jun 24, 2013 01:09AM

al...@alucab.org <al...@alucab.org> #21Sep 3, 2013 01:17PM

ar...@trbls.com <ar...@trbls.com> #22Oct 22, 2013 03:26PM

je...@gmail.com <je...@gmail.com> #23Jan 7, 2014 12:18AM

an...@gmail.com <an...@gmail.com> #24Jan 7, 2014 07:17AM

[Deleted User] <[Deleted User]> #25Apr 3, 2014 04:16PM

ek...@google.com <ek...@google.com> #26Apr 10, 2014 04:23PM

ad...@gmail.com <ad...@gmail.com> #27Apr 14, 2014 04:48PM

ar...@trbls.com <ar...@trbls.com> #28May 30, 2014 08:29AM

an...@castbefore.com <an...@castbefore.com> #29Jun 5, 2014 11:33PM

[Deleted User] <[Deleted User]> #30Jun 20, 2014 01:43PM

ar...@trbls.com <ar...@trbls.com> #31Jun 23, 2014 08:06AM

jo...@ascensoreslaplata.com.ar <jo...@ascensoreslaplata.com.ar> #32Jun 23, 2014 11:19AM

ar...@trbls.com <ar...@trbls.com> #33Jun 23, 2014 11:47AM

ar...@trbls.com <ar...@trbls.com> #34Jun 23, 2014 11:48AM

zi...@cloudware360.com.pe <zi...@cloudware360.com.pe> #35Jun 25, 2014 12:46AM

ra...@gmail.com <ra...@gmail.com> #36Jun 25, 2014 12:48AM

ra...@gmail.com <ra...@gmail.com> #37Jun 25, 2014 12:51AM

ad...@gmail.com <ad...@gmail.com> #38Aug 1, 2014 02:19AM

wa...@gmail.com <wa...@gmail.com> #39Aug 1, 2014 04:25AM

ar...@trbls.com <ar...@trbls.com> #40Aug 1, 2014 11:01AM

sk...@evergreens.com.ua <sk...@evergreens.com.ua> #41Sep 4, 2014 08:26PM

ar...@trbls.com <ar...@trbls.com> #42Sep 5, 2014 08:11AM

ac...@gmail.com <ac...@gmail.com> #43Sep 30, 2014 06:11PM

an...@gmail.com <an...@gmail.com> #44Oct 27, 2014 05:10PM

an...@gmail.com <an...@gmail.com> #45Oct 27, 2014 05:10PM

[Deleted User] <[Deleted User]> #46Oct 27, 2014 05:19PM

dm...@gmail.com <dm...@gmail.com> #48Dec 3, 2014 01:34PM

al...@gmail.com <al...@gmail.com> #49Jan 1, 2015 08:19PM

c....@gmail.com <c....@gmail.com> #50Jan 11, 2015 10:23AM

t2...@woolworths.com.au <t2...@woolworths.com.au> #51Jan 13, 2015 04:27AM

t2...@woolworths.com.au <t2...@woolworths.com.au> #52Jan 13, 2015 04:27AM

ar...@trbls.com <ar...@trbls.com> #53Jan 15, 2015 09:00AM

jo...@ascensoreslaplata.com.ar <jo...@ascensoreslaplata.com.ar> #54Jan 15, 2015 11:14AM

[Deleted User] <[Deleted User]> #55Jan 20, 2015 07:31PM

ca...@whiting-turner.com <ca...@whiting-turner.com> #56Jan 21, 2015 08:27PM

ar...@trbls.com <ar...@trbls.com> #57Jan 22, 2015 08:41AM

he...@google.com <he...@google.com> #58Jan 27, 2015 03:10PM

he...@gmail.com <he...@gmail.com> #59Jan 27, 2015 10:42PM

jo...@ascensoreslaplata.com.ar <jo...@ascensoreslaplata.com.ar> #60Feb 18, 2015 03:25PM

[Deleted User] <[Deleted User]> #61Mar 4, 2015 09:22AM

ar...@trbls.com <ar...@trbls.com> #62Mar 4, 2015 09:36AM

ad...@gmail.com <ad...@gmail.com> #63Mar 30, 2015 02:05AM

an...@acronym.com <an...@acronym.com> #64Apr 8, 2015 08:05PM

et...@gmail.com <et...@gmail.com> #65Apr 8, 2015 11:13PM

[Deleted User] <[Deleted User]> #66Apr 9, 2015 12:56PM

as...@gmail.com <as...@gmail.com> #67Apr 9, 2015 01:14PM

ev...@gmail.com <ev...@gmail.com> #68Apr 10, 2015 06:20PM

ev...@gmail.com <ev...@gmail.com> #69Apr 10, 2015 06:20PM

ar...@trbls.com <ar...@trbls.com> #70Apr 10, 2015 06:58PM

ml...@gmail.com <ml...@gmail.com> #71Apr 18, 2015 03:56PM

sf...@gmail.com <sf...@gmail.com> #72May 11, 2015 08:50AM

ad...@gmail.com <ad...@gmail.com> #73Jun 10, 2015 05:21PM

mz...@gmail.com <mz...@gmail.com> #74Jun 17, 2015 02:48PM

be...@gmail.com <be...@gmail.com> #75Jun 22, 2015 06:17PM

ma...@vinodgubbala.com <ma...@vinodgubbala.com> #76Jun 23, 2015 02:17PM

ar...@trbls.com <ar...@trbls.com> #77Jun 23, 2015 03:50PM

[Deleted User] <[Deleted User]> #78Jul 16, 2015 09:44AM