5

I have a sheet with two filter views, which apply filter on one column and sort on another. The sheet is populated from the API nightly - it has all worked great but today I noticed things in the wrong views.

enter image description here

First issue - Labels column is filtered on containing "CPA" but you can see the one right under the popup is "CVP,feature".

enter image description here

Second issue... Priority is sorted on value but the two new ones at the bottom are not being counted here.

I've tried switching filter views, refreshing the page in my browser, even closing and reopening the document, but nothing. What is wrong and how do I fix this?

Improve this question

4 Answers 4

Reset to default
8

Apparently in Google Sheets, when you turn on the filter (say on the first row) and you don't create a named filter view, that filter on the first row is still set with a range that only goes to the maximum number of rows that you have set up at the moment you turn on filters. If you add rows (not data to existing rows, but physical rows) to the spreadsheet, the filter will not read them.

In order to fix this, the user has to "Turn off filter" and then "Turn on filter" to reset the range.

Improve this answer
1
  • This is works for me and should be marked as the correct answer. One small correction to it though. It is now "Create a filter" rather than "Turn on filter".
    – Mike Poole
    Commented Oct 12, 2020 at 14:11
2

Google Sheet's Filter Views only work on the range specified. This range does not update upon the adding of rows or columns.

The second issue, the number not being sorted, is because of the first one. Those cells are not in the range of the Filter View.

Improve this answer
2
  • I don't think I set the range when I created the filter views... The sheet is repopulated each night by a script. But if you show me how to reset this I'll be corrected
    – Mr. Boy
    Commented Nov 6, 2018 at 17:58
  • The only way to update the range of the fitler view programatically is by using the Sheets v4 REST API You can find exactly how to do that in the documentation. The range of a filter view is automatically set to whatever range was highlighted when it was created. Open up a filter view, the top should say Name: Filter 1 to left of that is the range, you can type of this to change it.
    – Mark Shamy
    Commented Nov 8, 2018 at 5:22
1

You can see the range defined - it is highlighted in green line

I just ran into this problem myself, and this is what I did to solve it (2 options)

Option 1 - Simply update the range

The range is specified in the bar on the top, above the columns. Just update it.

enter image description here

Option 2 - Select option in menu to update the range

If you cannot find the range, go to the menu

Selected Data -> Filter Views -> Filter view options -> Update Range

enter image description here

Improve this answer
1

Select the filter view, then create a selection encompassing all columns and all rows (usually, you have to press Ctrl+A twice). Then update the range. That should suffice.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.