google apps script - 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

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.