Moving filtered values (copy and paste values) using google sheet script while ignoring the column heading / title

Question

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

}

Possible duplicate of How to auto select range without having to manually entering it? — TheMaster
Hi @TheMaster you have tagged my question as duplicate but the other question do not have any answer that I am looking for. It says simply select range from next row but that does not work. That solution always bring value from that cell even if it is not in filtered values. I have tested that already. Second approach proposed in that other topic is about delete row, it is not applicable in my case since I will have some values in other columns. Deleting row with delete everything. My question is more focused on how to copy just the values (not title). I would appreciate any help. — kuml
The first comment: .. just to copy the result of the filtered range. – JMR should give you a little more faith. Edit your code as your code is a bit messed up in lines 11-14. Although getRange().getValues() gets all the values filtered or not, getRange().copyTo copies only the filtered range values. That's why I marked it as a duplicate. — TheMaster
Hi @TheMaster I have updated my title and fixed the code. My code is already doing copying and pasting of filtered values. As I mentioned before, my question is basically a request for help in how to exclude column heading / title. Hence, I do not believe it is a duplicate. Looking at the other thread, I still do not have a clear answer on approach that copy and paste filtered values without column heading / title. I would appreciate any help and removal of duplicate tag. — kuml

TheMaster TheMaster · Accepted Answer · 2019-09-06T11:07:19

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