使用 setColumnFilterCriteria 后如何从过滤器中获取过滤值? [英] How to get filtered values from Filter after using setColumnFilterCriteria?

问题描述

我在 appscript 中使用某些过滤器时遇到了麻烦.我可以看到电子表格正在过滤，但在编程上我没有看到任何变化.

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.

你能帮忙吗?

谢谢

    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);

记录结果:

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

推荐答案

• 您想从电子表格中过滤后的工作表中检索值.
• 您希望使用 Google Apps 脚本来实现这一目标.

如果我的理解是正确的，这个答案怎么样?

If my understanding is correct, how about this answer?

不幸的是，无法使用 getValues() 从过滤的工作表中检索值.TheMaster 的评论已经提到了这一点.作为变通方法，我想提出以下 2 种模式.

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.

发布电子表格后，可以看到过滤后的工作表.在此模式中，使用 this，并使用查询语言检索值.不过别担心.在此脚本中，使用了访问令牌.所以过滤后的工作表可以直接检索，无需发布电子表格.我认为这是最简单的方法.

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.

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);

模式 2:

在此模式中，使用了 Sheets API.在使用此脚本之前，请在高级 Google 服务中启用 Sheets API.

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);

参考:

• 查询语言
• 方法:电子表格.get莉>
• 高级 Google 服务

如果我误解了您的问题并且这不是您想要的结果，我深表歉意.

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

这篇关于使用 setColumnFilterCriteria 后如何从过滤器中获取过滤值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！