How to copy the data to destination range when there is any update in google sheet?

Question

Hi everyone,

My goal is to copy the entire row of data from source data to destination range whenever the users select Transfer or Update in column E. Based on my script, it only works perfectly for Employee No 1 (Peter).

For example, when I select transfer in cell E4, the entire row of data from cell A4 to cell F4 will be copied to destination range (Row 1 in destination range as shown in the screenshot above). When I update certain cell, say cell C4, and select Update in cell E4, the entire row will be copied again to the 2nd row of destination range as shown in the screenshot above. However, when I select Transfer of Update in cell E5 or cell E6 the data been copied to destination range will still be the data from Peter (Row 3 in destination range as shown in the screenshot above).

I'm not sure how to fix this in my script and really hope to get some helps from you guys. This is the access to my google sheet for easier reference: https://docs.google.com/spreadsheets/d/1vfJYOAJT1Lqoj7uYQYftQ39Rl22FR9RWz04d1qYmRkg/edit?usp=sharing

This is my script:

function onEdit(e){
  const dt = Utilities.formatDate(new Date(), 'Asia/Singapore', 'HH:mm:ss');
  var ss = SpreadsheetApp.getActiveSheet ();
  var direction = SpreadsheetApp.Direction
  var aLast = ss.getRange("H"+(ss.getLastRow()+1)).getNextDataCell(direction.UP).getRow()
  var destRange = ss.getRange(aLast+1,8);
  if (e.range.columnStart == 5 && e.range.rowStart >= 4) {
    if (e.value == 'Update' || e.value == 'Transfer') {
      e.range.offset(0, 1).setValue(dt);
      
      if(e.range.rowStart = 4) {
        var order1 = ss.getRange ("A4:F4");
        order1.copyTo (destRange, {contentsOnly: true});
      } else if (e.range.rowStart = 5){
        var order2 = ss.getRange ("A5:F5");
        order2.copyTo (destRange, {contentsOnly: true});
      } else if (e.range.rowStart = 6){
        var order3 = ss.getRange ("A6:F6");
        order3.copyTo (destRange, {contentsOnly: true});
      } 
      
      source.copyTo (destRange, {contentsOnly: true});
    } else {
      e.range.offset(0, 1).setValue('');
    }
  } 
}

Really appreciate for any helps and advice.

Cooper Cooper · Accepted Answer · 2021-03-06T15:55:21

Try something like this:

function onEdit(e){
  e.source.toast('entry');
  const sh=e.range.getSheet();
  const hr=1;
  if(sh.getName()=='Your sheet name' && e.range.columnStart==5 && e.range.rowStart>hr && (e.value=='Update' || e.value=='Transfer')){
    const dt=Utilities.formatDate(new Date(), 'Asia/Singapore', 'HH:mm:ss');
    let src=sh.getRange(e.range.rowStart,1,1,6).getValues();
    src[0][5]=dt;
    const desrow=getColHeight(8,sh,e.source)+1;
    sh.getRange(desrow,8,1,6).setValues(src);
    sh.getRange(e.range.rowStart,6).setValue(dt);
  }
 
}

function getColHeight(col,sh,ss){
  var ss=ss||SpreadsheetApp.getActive();
  var sh=sh||ss.getActiveSheet();
  var col=col||sh.getActiveCell().getColumn();
  const rcA=sh.getRange(1,col,sh.getLastRow(),1).getValues().reverse()
  let s=0;
  for(let i=0;i<rcA.length;i++) {
    if(rcA[i][0].toString().length==0) {
      s++;
    }else{
      break;
    }
  }
  return rcA.length-s;
}

How to copy the data to destination range when there is any update in google sheet?

1 Answers