Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

google apps script - How to get filtered values from Filter after using setColumnFilterCriteria?

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
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)
  • 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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...