samedi 6 mars 2021

How to change the if statement to for statement without affecting the outcome in google apps script?

enter image description here

Hello,

I want to copy the orders from five customers to Order History table whenever the orders had been submitted or updated. This is my code:

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("I"+(ss.getLastRow()+1)).getNextDataCell(direction.UP).getRow()
  var destRange = ss.getRange(aLast+1,9);

  if (e.range.columnStart == 6 && e.range.rowStart >= 4) {
    if (e.value == 'Update' || e.value == 'Submit') {
      e.range.offset(0, 1).setValue(dt);
      
      if(e.range.rowStart = 4) {
        var order1 = ss.getRange ("A4:G4");
        order1.copyTo (destRange, {contentsOnly: true});
      } else if (e.range.rowStart = 5){
        var order2 = ss.getRange ("A5:G5");
        order2.copyTo (destRange, {contentsOnly: true});
      } else if (e.range.rowStart = 6){
        var order3 = ss.getRange ("A6:G6");
        order3.copyTo (destRange, {contentsOnly: true});
      } else if (e.range.rowStart = 7){
        var order4 = ss.getRange ("A7:G7");
        order4.copyTo (destRange, {contentsOnly: true});
      } else if (e.range.rowStart = 8){
        var order5 = ss.getRange ("A8:G8");
        order5.copyTo (destRange, {contentsOnly: true});
      }
      
      source.copyTo (destRange, {contentsOnly: true});
    } else {
      e.range.offset(0, 1).setValue('');
    }
  } 
}

However, when I submitted or updated the order from Customer 2, the order been copied into Order History table will still be the order from Customer 1. I think the reason is because of this line of code: if (e.range.columnStart == 6 && e.range.rowStart >= 4) where the >=4 is the main problem. However, I'm not sure how to change if statement to for loop because this line of code is used to assist in printing the current timestamp in column G whenever column F shows Update or Submit.

Hope to get some guidance from expert, really appreaciate for any help!

Aucun commentaire:

Enregistrer un commentaire