CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsfilteringspreadsheet
maria
maria

Reputation: 159

Google Sheet check for filter always goes to true

why does this always go to true? How can i check if its acually something filtered or not?

 var list = SpreadsheetApp.openByUrl(url);
var SS = list.getSheetByName("Sheet 1");

if (SS.getFilter() !== null && SS.getFilter() != {}) {
  console.log("have populated filter..", SS.getFilter());

}

current output: have populated filter.. {}

Upvotes: 0

Views: 347

Answers (2)

Cooper
Cooper

Reputation: 64032

Thanks for the question. I played around with this. It actually takes quite a long time to run getFilter:

Here's what I did:

function messinwithmysheetsfilter() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1');
  let f = sh.getFilter();
  console.log(typeof f)
  if (f != null && typeof f == 'object') {
    console.log('Frozen: '+Object.isFrozen(f));
    console.log('Sealed: '+Object.isSealed(f));
    console.log('Extensible: '+Object.isExtensible(f));
    console.log('OwnProperies: '+Object.getOwnPropertyNames(f));
    console.log('toString(): '+ f.toString());
    console.log('getRange: ' + f.getRange().getA1Notation());
  }
}

Console Log Results:

11:54:32 AM Notice  Execution started
11:54:33 AM Info    Frozen: false
11:54:33 AM Info    Sealed: false
11:54:33 AM Info    Extensible: true
11:54:33 AM Info    OwnProperies: toString,remove,sort,getRange,getColumnFilterCriteria,getColumnSortSpec,setColumnFilterCriteria,removeColumnFilterCriteria
11:54:33 AM Info    toString(): Filter
11:54:33 AM Info    getRange: A1:Z63
11:54:33 AM Notice  Execution completed

Perhaps you can learn something from it I did.

You can actually rewrite your conditional as I did below:

function messinwithmysheetsfilter() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1');
  let f = sh.getFilter();
  //console.log(typeof f)
  if (f != null && f.toString()=='Filter') {//use the results of the filters toString() method which should  be 'Filter'
    console.log('Frozen: '+Object.isFrozen(f));
    console.log('Sealed: '+Object.isSealed(f));
    console.log('Extensible: '+Object.isExtensible(f));
    console.log('OwnProperies: '+Object.getOwnPropertyNames(f));
    console.log('toString(): '+ f.toString());
    console.log('getRange: ' + f.getRange().getA1Notation());
  }
}

Upvotes: 1

Nikko J.
Nikko J.

Reputation: 5533

Based on the documents:

getFilter() - Returns the filter in this sheet, or null if there is no filter.

This will always have value if the sheet has 1 or more filter regardless the range.

I also tried to replicate your code and it only set to false if the whole sheet has no filter.

Example:

With filter:

wfilter

Output:

enter image description here

Without filter:

woutfilter

Output:

enter image description here

Reference:

Upvotes: 1

Related Questions