仅将过滤范围复制到其他工作表 [英] Copy only filtered range to other sheet

问题描述

大家好，我尝试制作用于复制过滤数据的脚本，但我没有这样做.

我想将过滤的数据自动复制到另一张纸上

  function RejectSave(){var ss = SpreadsheetApp.getActive();var DataSheet = ss.getSheetByName('导入')ss.setActiveSheet(DataSheet，true);var sheet = ss.getActiveSheet();sheet.getRange(1，1，sheet.getMaxRows()，sheet.getMaxColumns()).activate();sheet.getRange(1，1，sheet.getMaxRows()，sheet.getMaxColumns()).createFilter();var条件= SpreadsheetApp.newFilterCriteria().setHiddenValues([''，'A'，'B'，'C']).建造();ss.getActiveSheet().getFilter().setColumnFilterCriteria(20，条件);var SaveSheet = ss.getSheetByName('RAW')ss.setActiveSheet(SaveSheet，true);ss.getActiveSheet().insertRowsAfter(ss.getActiveSheet().getMaxRows()，5);var SaveRange = SaveSheet.getRange(SaveSheet.getLastRow()+ 2，1)DataSheet.getRange(2，1，DataSheet.getLastRow()，26).copyTo(SaveRange，SpreadsheetApp.CopyPasteType.PASTE_VALUES，false);DataSheet.getFilter().remove();}; 

但是脚本不能仅带过滤的数据，它带该范围内的所有数据，无论是否满足过滤条件.

我只想复制经过过滤的数据！

解决方案

如果在编写脚本之前已知过滤条件，则您可能应该使用

Hello Everybody I tried to make scripts for copying filtered data but I did not make it.

I want to copy filtered data to another sheet automatically

function RejectSave() {
  var ss = SpreadsheetApp.getActive();
  var DataSheet = ss.getSheetByName('Import')

  ss.setActiveSheet(DataSheet, true);
  var sheet = ss.getActiveSheet();

  sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).activate();
  sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).createFilter();

  var criteria = SpreadsheetApp.newFilterCriteria()
    .setHiddenValues(['', 'A', 'B', 'C'])
    .build();

  ss.getActiveSheet().getFilter().setColumnFilterCriteria(20, criteria);

  var SaveSheet = ss.getSheetByName('RAW')
  ss.setActiveSheet(SaveSheet, true);
  ss.getActiveSheet().insertRowsAfter(ss.getActiveSheet().getMaxRows(), 5);

  var SaveRange = SaveSheet.getRange(SaveSheet.getLastRow()+2, 1)

  DataSheet.getRange(2, 1, DataSheet.getLastRow(), 26)
    .copyTo(SaveRange, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  DataSheet.getFilter().remove();
};

but the script cannot bring just filtered data, it brings all data in that range, whether it meets the filter criteria or not.

I want to copy just filtered data!!

解决方案

If the filter criteria is known before writing a script, then you probably should use JS array filter() method instead of Google sheet getFilter(). For example, the following function filters the source range A1:C10, excluding the rows with special values in column C. As a result it sets new (filtered) data array at start cell E1 (see also image).

function filtered() {
  var sheet = SpreadsheetApp.getActiveSheet();
  // Get full (non-filtered) data
  var values = sheet.getRange('A1:C10').getValues();

  // Apply filter criteria here
  var hiddenValues = ['', 'A', 'B', 'C'];
  values = values.filter(function(v) {
    return hiddenValues.indexOf(v[2]) == -1;
  });

  // Set filtered data on the target sheet
  sheet.getRange(1, 5, values.length, 3).setValues(values);
}

JS filtering is flexible and rather quick.

这篇关于仅将过滤范围复制到其他工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！