如何使用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
}

哪个生成此过滤器视图:

Which generates this filter view:

但是，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屋！