CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
user14523871
user14523871

Reputation: 35

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

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{

  } 
}
}

Upvotes: 1

Views: 323

Answers (1)

Marios
Marios

Reputation: 27350

Explanation:

You have two goals:

  1. Copy the rows in sheet [Overview] All_Cases that contain "No" in column Y to the OPS_FUNNEL_new sheet.

  2. 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");
  }
}

Upvotes: 1

Related Questions