如何使用 Apps 脚本复制过滤后的电子表格数据 [英] How to copy filtered spreadsheet data with Apps Script
问题描述
我想将过滤后的数据从一个电子表格复制到另一个电子表格.
I want to copy filtered data from one spreadsheet to another.
我有一个电子表格,其中 A 列和 B 列中有一些数据:
I've got a spreadsheet with some data in column A and column B:
我有一个过滤数据的脚本:
And I have a script that filters the data:
function get_data(value){
value = 1
var sheet = SpreadsheetApp.getActiveSheet()
var range = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn())
var filter = range.getFilter() || range.createFilter();
var foo_index = 1; // column A
var filterValue = SpreadsheetApp.newFilterCriteria().whenTextEqualTo(value).build()
filter.setColumnFilterCriteria(foo_index, filterValue)
var data = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues()
Logger.log(data)
return data
}
生成此过滤器视图:
然而,Logger.log(data)
给了我这个结果:
However, Logger.log(data)
gives me this result:
[[foo, bar], [1.0, A], [1.0, B], [1.0, C], [3.0, D], [5.0, D], [7.0,E], [7.0, A]]
[[foo, bar], [1.0, A], [1.0, B], [1.0, C], [3.0, D], [5.0, D], [7.0, E], [7.0, A]]
我想得到的结果是:
[[foo, bar], [1.0, A], [1.0, B], [1.0, C]]
[[foo, bar], [1.0, A], [1.0, B], [1.0, C]]
我想要这个结果,所以我可以将这个过滤后的数据版本写入一个新的电子表格.
I want this result so I can write this filtered version of the data to a new spreadsheet.
推荐答案
我通过检查每一行并添加对 isRowHiddenByFilter
的检查来解决这个问题,但这看起来很不雅观.欢迎其他想法.
I got this to work by going through each row and adding a check for isRowHiddenByFilter
, but this seems pretty inelegant. Other ideas welcome.
function get_data(value){
value = 1
var sheet = SpreadsheetApp.getActiveSheet()
var range = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn())
var filter = range.getFilter() || range.createFilter();
var foo_index = 1; // column A
var filterValue = SpreadsheetApp.newFilterCriteria().whenTextEqualTo(value).build()
filter.setColumnFilterCriteria(foo_index, filterValue)
// new code
var data = []
for (var i = 1; i < sheet.getLastRow(); i++) {
if(!sheet.isRowHiddenByFilter(i)) {
var row_data = sheet.getRange(i, 1, 1, sheet.getLastColumn()).getValues()
data.push(row_data[0])
}
}
// end new code
Logger.log(data)
return data
}
这篇关于如何使用 Apps 脚本复制过滤后的电子表格数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!