使用Google表格脚本移动过滤后的值(复制和粘贴值)，而忽略列标题/标题 [英] Moving filtered values (copy and paste values) using google sheet script while ignoring the column heading / title

问题描述

我只想使用Google脚本将VALUES从源工作表复制并粘贴到目标工作表.

I would like to copy and paste VALUES only from source sheet to target sheet using google scripts.

我在该列中应用了过滤器，所以我只希望复制存在/已过滤的单元格(不是所有值).

I have filter applied in the column so I am only looking to copy the cells that are present / filtered (not all values).

在我构建的示例中，我有:

In the example that I have built, I have:

源工作表(从中复制值):Sheet1 目标工作表(将值粘贴到):Sheet2

Source sheet (copy values from): Sheet1 Target sheet (paste values to): Sheet2

Sheet1屏幕截图:

过程/步骤:

当我在A列的"Fav_color"中选择任何特定的颜色时，请说黄色".

When I select any particular color in "Fav_color" in column A..let's say "yellow".

现在，对成员进行了相应的过滤.

Now, Members are filtered accordingly.

单击按钮后，我要运行我的Google脚本功能，该功能可以复制过滤的成员名称并将其粘贴到Sheet2中.

On the button click, I want my google script function to run that copies the filtered Member Names and paste it in Sheet2.

问题: 在工作表2中:我只需要成员名称(而不是实际的列名称)，就得到了列标题/标题以及成员名称".

Problem: In Sheet 2: I am getting column heading / title as well "Member Name" while I only want member names (not the actual column name).

我确实知道我的代码中存在的问题，我正在复制完整的B列，但不知道如何排除列标题/标题.

I do know the problem as in my code, I am copying complete B column but I do not know how to exclude column heading / title.

function transfer() {
 var sss = SpreadsheetApp.getActiveSpreadsheet();
 var ss = sss.getSheetByName('Sheet1'); 
 var range = ss.getRange('A1:A'); 
 var data = range.getValues();

 var tss = SpreadsheetApp.getActiveSpreadsheet();
 var ts = tss.getSheetByName('Sheet2');
 ts.getRange('A:A').clearContent();

  ts.getRange('A:A').clearContent();
  ss.getRange("B:B").copyTo(ts.getRange("A2"), {contentsOnly:true});

}

推荐答案

问题:

如果您使用

ss.getRange("B2:B").copyTo(ts.getRange("A2"), {contentsOnly:true});

它总是从B2单元格中获取值，即使它在过滤结果中不可见

it always bring the value from B2 cell even if it is not visible in filtered results

解决方案:

您可以使用Sheet#isRowHiddenByFilter

function copycolB() {
  var ss = SpreadsheetApp.getActive();
  var sourceSheet = ss.getSheetByName('Sheet1');
  var targetSheet = ss.getSheetByName('Sheet2');
  for(var row = 2; sourceSheet.isRowHiddenByFilter(row); ++row);//get first  visible row from row2
  var sourceRange = sourceSheet.getRange('B' + row + ':B');
  sourceRange.copyTo(
    targetSheet.getRange('A2'),
    {contentsOnly:true});
}

参考文献:

• Sheet#isRowHiddenByFilter

References:

• Sheet#isRowHiddenByFilter

这篇关于使用Google表格脚本移动过滤后的值(复制和粘贴值)，而忽略列标题/标题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！