CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
J. Doe
J. Doe

Reputation: 273

Create a filterin GSheet per Scriptin

I try to create a simply script to filter a bigger list.

When I am using the command "ss.getRange('1:1904').createFilter();" the script runs perfect but when I try to work with a variable the script do not run.

function myScript() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var ss = spreadsheet.getSheetByName("MyUsers");
  var lr = ss.getLastRow()
  ss.getRange('1:1904').createFilter();   //<< the script runs perfect
  ss.getRange(1, lr).createFilter();      //<< the script to not run
  
  var criteria = SpreadsheetApp.newFilterCriteria().whenTextDoesNotContain('Peking').build();
  ss.getFilter().setColumnFilterCriteria(11, criteria);
  
  //ss.getRange('2:1904').activate();

  //ss.deleteRows(ss.getActiveRange().getRow(), ss.getActiveRange().getNumRows());
  ss.getFilter().remove();
};

I am sure the is only a simply bug, but I do not have any idea where is the fault.

Who can help?

Thanks

Upvotes: 1

Views: 42

Answers (1)

Tanaike
Tanaike

Reputation: 201378

Modification points:

  • In the case of ss.getRange('1:1904'), the rows from 1 to 1940 are used for all columns. On the other hand, in the case of ss.getRange(1, lr), only one cell is used. I thought that this might be the reason for your current issue.

If you want to set the basic filter from row 1 to the last row with all columns, how about the following modification?

From:

ss.getRange(1, lr).createFilter();

To:

ss.getRange(1, 1, lr, ss.getMaxColumns()).createFilter();
  • If you want to use the data range, you can use ss.getDataRange().createFilter();.

References:

Upvotes: 1

Related Questions