How to copy a specific row from one sheet to another (Google Apps Script)

Question

this is my first post, so forgive me if the question is not worded quite the way it is for the platform.

I'm currently working on a Google Apps script that is supposed to search a sheet (name: "[Overview] All Cases") and its rows for a certain value in column Y. This particular value is "No". If a row has this value, this row should be copied to the last row of another sheet ("OPS_FUNNEL").The row should then exist in both sheets. Can anyone help?

I have been through countless threads and have not been able to gather a solution. My solution so far, which does not work, you can see here:

function copy_to_OPS_FUNNEL() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var srcSheet = ss.getSheetByName("[Overview] All_Cases");
  var tarSheet = ss.getSheetByName("OPS_FUNNEL_new");
  var lastRow = srcSheet.getLastRow();
  
  for (var i = 2; i <= lastRow; i++) {

    var cell = srcSheet.getRange("Y" + i);

    var val = cell.getValue();

    if (val == 'No') {
      
      var srcRange = srcSheet.getRange("A" + i + ":B" + i);
      var sourcevalues = srcRange.getValues();
      
      var tarRow = tarSheet.getLastRow();
      var tarRange = tarSheet.getRange("A" + (tarRow + i) + ":B" + (tarRow + i));
      
      tarRange.setValues(sourcevalues);

      srcSheet.getRange("Y"+(i+0).toString()).setValue("Yes");

  }
  else{

  } 
}
}

Marios · Accepted Answer · 2021-02-04T13:17:49.763

1

Explanation:

You have two goals:

Copy the rows in sheet [Overview] All_Cases that contain "No" in column Y to the OPS_FUNNEL_new sheet.
After the copy is done, change the "No" in sheet [Overview] All_Cases to "Yes".

Three major improvements:

It is not recommended to iteratively call getRange and setRange, read best practices for more info.
You don't need a for loop and if statements to find the data that contains "No" in column Y. Instead you filter this data with one line of code:
```
const data = srcSheet.getDataRange().getValues().filter(r=>r[24]=='No');
```
Then, copy it to the target sheet with one line of code:

tarSheet.getRange(tarSheet.getLastRow()+1,1,data.length,data[0].length).setValues(data);

Solution:

function copy_to_OPS_FUNNEL() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const srcSheet = ss.getSheetByName("[Overview] All_Cases");
  const tarSheet = ss.getSheetByName("OPS_FUNNEL_new");
  const data = srcSheet.getDataRange().getValues().filter(r=>r[24]=='No').map(r => [r[0]]);
  if (data.length>0){
    tarSheet.getRange(tarSheet.getLastRow()+1,1,data.length,1).setValues(data);
    srcSheet.getRange("Y2:Y"+srcSheet.getLastRow()).setValue("Yes");
  }
}

edited Feb 04 '21 at 13:17

answered Feb 04 '21 at 12:25

Marios

26,333
8
32
52

Thanks for your solution! It works almost perfectly. Is there another way to copy only the value from column A? Or from a specific range (for example: "A:T"). Thanks in advance! – user14523871 Feb 04 '21 at 13:05
@user14523871 my question is what do you want? column "A" or range "A:T" ? – Marios Feb 04 '21 at 13:06
I want to copy only the value from column "A" from "Overview All_Cases" to "OPS_FUNNEL". – user14523871 Feb 04 '21 at 13:12
Could you please help me with one more little thing? How do I change your above solution if I want to copy the complete row with its values and formatting and paste it into my targetsheet? After copying I want to delete exactly the row from the sourceSheet. The condition for this is again that there is a 'No' in a column (for example T). Thank you very much! – user14523871 Feb 09 '21 at 08:50
@user14523871 Hi unfortunately stackoverflow does not allow for follow up questions. Could you please post a new question regarding that modification. Sorry for the inconvenience. Feel free to provide the url of the new question here so I can find it faster. – Marios Feb 09 '21 at 09:01

How to copy a specific row from one sheet to another (Google Apps Script)

1 Answers1

Explanation:

Solution:

Linked