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

问题描述

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

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

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

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

Sheet1 屏幕截图:

流程/步骤:

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

现在，相应地过滤成员.

在单击按钮时，我希望运行我的 google 脚本函数，该函数复制过滤后的成员名称并将其粘贴到 Sheet2 中.

问题:在工作表 2 中:我正在获取列标题/标题以及成员名称"，而我只想要成员名称(而不是实际的列名称).

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

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 单元格中获取值，即使它在过滤结果中不可见

解决方案:

您可以使用 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);//从第2行获取第一个可见行var sourceRange = sourceSheet.getRange('B' + row + ':B');sourceRange.copyTo(targetSheet.getRange('A2'),{contentsOnly:true});}

参考:

• Sheet#isRowHiddenByFilter

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:

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

Sheet1 screenshot:

Process / Steps:

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

Now, Members are filtered accordingly.

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

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

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

}

解决方案

Issue:

If you use,

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

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

Solution:

You can get the first non-filtered row number using Sheet#isRowHiddenByFilter

Snippet:

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

References:

• Sheet#isRowHiddenByFilter

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