为什么方法setColumnFilterCriteria()给出“无法调用方法"setColumnFilterCriteria""?无效"? [英] Why method setColumnFilterCriteria() gives 'Cannot call method "setColumnFilterCriteria" of null'?

问题描述

问题似乎很容易解决，但是我已经花了很多时间来寻找答案，而且我会发疯...

Problem seems to be very easy to solve, but I've spent a lot of time finding the answer and I'm going crazy...

我使用非常简单的代码将过滤器添加到工作表中，并且不断出现相同的错误:

I use very simple code to add the filetering to the sheet and I keep getting the same error:

TypeError:无法调用null的方法"setColumnFilterCriteria".

TypeError: Cannot call method "setColumnFilterCriteria" of null.

范围从第5行开始.在第50列中，我有不同的字母，包括"r".我也尝试过使用数字.

The range starts from row 5. In column 50 I have different letters, including "r". I've also tried with the numbers.

很抱歉，这种情况太简单了，但我想我不会自己解决...

Sorry if the case is too easy, but I think that I won't solve it on my own...

脚本:

function filtering() {

var sheet = SpreadsheetApp.getActiveSheet();

//remove filter and flush
if(sheet.getFilter() != null){sheet.getFilter().remove();}
SpreadsheetApp.flush();

var criteria = SpreadsheetApp.newFilterCriteria().whenNumberGreaterThan(10).build();  
// I've also tried commented lines
// var criteria = SpreadsheetApp.newFilterCriteria().whenTextContains("r").build();  
// var criteria = SpreadsheetApp.newFilterCriteria().whenTextEqualTo('r').build();  
// var criteria = SpreadsheetApp.newFilterCriteria().setHiddenValues(["r"]).build();  
// var criteria = SpreadsheetApp.newFilterCriteria().setVisibleValues(["r"]).build();

sheet.getFilter().setColumnFilterCriteria(50, criteria);

//also I've tried with the range
/*
var range = sheet.getRange(5, 1, 100, 50);
range.getFilter().setColumnFilterCriteria(50, criteria);
*/
}

推荐答案

问题在此行

if(sheet.getFilter() != null) {
     sheet.getFilter().remove();
}

如果存在任何过滤器，我们将其清除.然后我们在做

If there exists any filter, we clearing them. Then we're doing

sheet.getFilter().setColumnFilterCriteria(50, criteria);

此处 getFilter()将始终返回 null ，因为我们已在上一步中清除了过滤器.

Here getFilter() will always return null, as we have cleared the filter in previous step.

我们可以通过清除现有过滤器并创建新过滤器来解决此问题.

We can fix this by clearing existing filter and creating new filter.

类似这样的东西:

if(sheet.getFilter() != null) {
     sheet.getFilter().remove();
}
sheet.getDataRange().createFilter().setColumnFilterCriteria(50, criteria);

这篇关于为什么方法setColumnFilterCriteria()给出“无法调用方法"setColumnFilterCriteria""?无效"?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！