Copy over a specific range not whole row to another google sheet based on cell value

Question

I would like to have select cells of a row copied from "Pending" sheet to "Live" sheet based on when a specific cell is edited to say "Yes".

So far I'm able to copy the whole row over when a specific cell in a column is edited to say "Yes".

I would like the specific cells in that row to copy and move onEdit, but not the whole row. For example copy, the contents of column 2,3,5,6 but not 4,7,8 etc. of the row edited in the "Pending" sheet over to the "Live" sheet.

Also though, when copying over to the "Live" sheet having column 2 of the "Pending" sheet line up with column 2 of the "Live" Sheet etc. But for column 5 and 6, they should both move over one column since column 4 wasn't copied over. So "Pending" 5 should take the place of "Live" 4 and "Pending" 6 should take the place of "Live" 5.

function onEdit(event) {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var s = event.source.getActiveSheet();
   var r = event.source.getActiveRange();

if(s.getName() == "Pending" && r.getColumn() == 1 && r.getValue() == "Yes") {
   var row = r.getRow();
   var numColumns = s.getLastColumn();
   var targetSheet = ss.getSheetByName("Live");
   var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
   s.getRange(row, 1, 1, numColumns).copyTo(target);
   }
}

Cooper Cooper · Accepted Answer · 2017-10-09T21:17:26

Try This:

function onEdit(e) 
{
   var ss=e.source;
   var sh=ss.getActiveSheet();
   var rg=sh.getActiveRange();
   var row=rg.getRow();
   var col=rg.getColumn();
   Logger.log(col);
  if(sh.getName()=="Pending" && col==1 && rg.getValue()=="Yes") 
  {
     var row = rg.getRow();
     var liveSht = ss.getSheetByName("Live");
     var vA=sh.getRange(row,col,1,6).getValues();
     var liveA=['',vA[0][1],vA[0][2],vA[0][4],vA[0][5]];
     liveSht.appendRow(liveA);
  }
}

Copy over a specific range not whole row to another google sheet based on cell value

1 Answers