How to get filtered values from Filter after using setColumnFilterCriteria?

Question

I m in trouble using some filter in appscript. I can see that the spreasheet is filtering, but programaticaly i don't see any changes.

Could you help ?

Thanks

    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("xxxx");
    var values = sheet.getDataRange().getValues();
    Logger.log("VALUES "+values.length);

    var newCriteria = SpreadsheetApp.newFilterCriteria().whenTextEqualTo('51').build();
    var range = sheet.getFilter().setColumnFilterCriteria(22, newCriteria).getRange(); //The 1-indexed position of the column.  
    values = range.getValues();

    Logger.log("VALUES "+values.length);

Results on logging :

19-08-28 19:27:33:272 CEST] VALUES 1379
[19-08-28 19:27:39:748 CEST] VALUES 1379

Setser

You can't getValues() only on the filtered range. May I know what you're going to do with the values? You can copyTo the filtered range or filter the range programmatically using pure JavaScript (without spreadsheet filter) – Yancey

Because the getDataRange().getValues i tried to do, take 4 or 5 seconds Too long because too many lines. (1379) I would like to reduce this time by reducing the range. – Setser

See this answer https://mcmap.net/q/2036173/-how-to-auto-select-range-from-a-filter-without-having-to-manually-enter-it using the copyTo method mentioned by @Yancey above. – Hippie

Answer 1

• You want to retrieve values from the filtered sheet in the Spreadsheet.
• You want to achieve this using Google Apps Script.

If my understanding is correct, how about this answer?

Issue and workaround:

Unfortunately, the values cannot be retrieved from the filtered sheet using getValues(). This has already been mentioned by TheMaster`s comment. As the workarounds, I would like to propose the following 2 patterns.

Pattern 1:

When the Spreadsheet is published, the filtered sheet can be seen. In this pattern, this is used, and the values are retrieved using the query Language. But don't worry. In this script, the access token is used. So the filtered sheet can be directly retrieved without publishing Spreadsheet. I think that this is the simple way.

Modified script:

var ss = SpreadsheetApp.getActiveSpreadsheet(); // Added
var sheet = ss.getSheetByName("xxxx"); // Modified
var values = sheet.getDataRange().getValues();
Logger.log("VALUES "+values.length);

var newCriteria = SpreadsheetApp.newFilterCriteria().whenTextEqualTo('51').build();
var range = sheet.getFilter().setColumnFilterCriteria(22, newCriteria).getRange(); //The 1-indexed position of the column.  
// values = range.getValues();

// I added below script.
var url = "https://docs.google.com/spreadsheets/d/" + ss.getId() + "/gviz/tq?tqx=out:csv&gid=" + sheet.getSheetId() + "&access_token=" + ScriptApp.getOAuthToken();
var res = UrlFetchApp.fetch(url);
var values = Utilities.parseCsv(res.getContentText());

Logger.log("VALUES "+values.length);

Pattern 2:

In this pattern, Sheets API is used. Before you use this script, please enable Sheets API at Advanced Google services.

Modified script:

var ss = SpreadsheetApp.getActiveSpreadsheet(); // Added
var sheet = ss.getSheetByName("xxxx"); // Modified
var values = sheet.getDataRange().getValues();
Logger.log("VALUES "+values.length);

var newCriteria = SpreadsheetApp.newFilterCriteria().whenTextEqualTo('51').build();
var range = sheet.getFilter().setColumnFilterCriteria(22, newCriteria).getRange(); //The 1-indexed position of the column.  
// values = range.getValues();

// I added below script.
var res = Sheets.Spreadsheets.get(ss.getId(), {
  ranges: ["xxxx"], // <--- Please set the sheet name.
  fields: "sheets/data"
});
var values = res.sheets[0].data[0].rowMetadata.reduce(function(ar, e, i) {
  if (!e.hiddenByFilter && res.sheets[0].data[0].rowData[i]) {
    ar.push(
      res.sheets[0].data[0].rowData[i].values.map(function(col) {
        return col.userEnteredValue[Object.keys(col.userEnteredValue)[0]];
      })
    );
  }
  return ar;
}, []);

Logger.log("VALUES "+values.length);

References:

• Query Language
• Method: spreadsheets.get
• Advanced Google services

If I misunderstood your question and this was not the result you want, I apologize.

Mcguigan

I tried the first pattern ! Seems to answer my needs. Thanks Tanaike :-) I will try pattern2. I dont understand why we can not use the range of the filtered data in a simple way or get only the visibile lines in a simple way. – Setser

@Sylvain ASSEMAT Thank you for replying. I could understand that the pattern 1 worked for your situation. Unfortunately, from your replying, I couldn't understand that the pattern 2 didn't work. If you want to use the pattern 2, can you provide the sample Spreadsheet for replicating your issue for the pattern 2? By this, I would like to confirm the issue. Of course, please remove your personal information. – Mcguigan

@Sylvain ASSEMAT Is there anything that I can do for your question? If my answer was not useful for your situation. I have to apologize and modify it. If you can cooperate to resolve your issue, I'm glad. I would like to think of about the solution. – Mcguigan

Pattern 2 just iterates through the retrieved data to keep the rows that are not hiddenByFilter . Since the Sheets API retrieving the data just gets the same data unfiltered by the filter that the buggy Range.getValues() would get, why use the Sheets API at all instead of just Range.getValues() , a far more performant and simple approach? – Holarctic

Pattern 1 can include in the GViz query URL QUERY_STRING a range=<A1> key=value to get just the filtered range by its A1 . Indeed the GViz query can include a SELECT query that is much more powerful and flexible than the GSheets GUI filter, and is not uselessly buggy for filtered data reading by GAS. As long as the code is using GViz with its duration and other costs, it might be best to just execute the filter in the GViz query along with reading the values, if not filtering for display (which is also slow when executed by GAS). – Holarctic

Answer 2

Probably can be done with sheet.isRowHiddenByFilter(rowNum) and isRowHiddenByUser(rowNum) (same for columns) but I haven't checked yet..

Bushing