Transfer google sheet rows to another sheet based on cell

Question

Thanks for taking the time to look at my question.
I am looking at transferring rows of data into a new spreadsheet based on a cells value.
I have found some examples myself, but not entirely able to make it work for my exact needs. If I have overlooked any previously answered questions, please point me in the right direction. The closest I have been able to achieve is my current code.

I am looking at copying the data in columns A-E when E = "Y" from COPY sheet into PASTE sheet - examples of the sheets are below. My current issue is that, its copying all the data, even if D isn't "Y", and copying it for every instance of "Y".
So if I have 3 "Y"s in column E then it is copied over 3 times.
Copy From this Sheet
Paste to this Sheet

function transfer() {
 var sss = SpreadsheetApp.getActiveSpreadsheet();
 var ss = sss.getSheetByName('COPY'); 
 var range = ss.getRange('A2:E'); 
 var data = range.getValues();
  
  for (var i = 0; i <data.length; ++i){
   var row = data[i];
   var check = row[4];
 
   var tss = SpreadsheetApp.openById("1qxR-L2-Tj7slZQ0NMbQl0-JXZ8iVO59410ccVNZk7Ys");
   var ts = tss.getSheetByName('PASTE');
   
   if (check == "Y") {
     ts.getRange(ts.getLastRow()+1, 1, data.length, data[0].length).setValues(data);
   }
 }
}

As pointed out in the answer try replacing ts.getRange(ts.getLastRow()+1, 1, data.length, data[0].length).setValues(data); with ts.appendRow(row) — Umair Mohammad
@Umair - Thank you for the help with this answer. Can you see how the script can be improved as it takes awhile to run — Paul Marvell
Can you post a new question for that ? I had love to answer then. — Umair Mohammad
@umair Hey - I have asked a new question if you wanted to answer. — Paul Marvell

Anton Dementiev Anton Dementiev · Accepted Answer · 2018-04-13T10:10:47

Your 'data' variable stores all values from the source sheet, so calling setValues(data) will transfer everything to the target sheet. For simplicity, you can substitute that with:

ts.appendRow(row);

Another issue is how you are calling the 'getRange()' method:

sheet.getRange(startRow, startColumn, numberOfRows, numberOfColumns);

In your case, the number of rows is equal to data.length whereas it must be 1 as you only need to copy one row.

Transfer google sheet rows to another sheet based on cell

1 Answers