- 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:
If I misunderstood your question and this was not the result you want, I apologize.