CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
weizer
weizer

Reputation: 1127

Why the the timestamp function will appear twice in two different cells in google sheet?

enter image description here

Hi everyone,

I'm very new to the google apps script. I have a code to print the timestamp in column S when the Action show "Transmit", else it will be blank for other type of Action. The timestamp did appear in Column S but it appear in Column O as well which is not what I want. I attached my code below:

function onEdit(e){

  if(e.range.columnStart == 15 && e.range.rowStart >= 12 && e.value == 'Update' || e.value == 'Transmit'){
     e.range.offset(0,2).setValue(new Date()).setNumberFormat("yyyy-MM-dd HH:mm:ss");
  } else {
    e.range.offset(0,2).setValue('')
  }

  if(e.range.columnStart == 13 && e.range.rowStart >= 22 && e.value == 'Transmit'){
     e.range.offset(0,6).setValue(new Date()).setNumberFormat("yyyy-MM-dd HH:mm:ss");
  } else {
    e.range.offset(0,6).setValue('')
  }
}

I guess the first IF statement print the TimeStamp by offset(0,2) and it brings the parameter of offset() to the second IF statement. I'm not sure how to make the two IF statement independent with each other. Hope to get some advice from you guys. Any help will be greatly appreciated.

Upvotes: 0

Views: 75

Answers (1)

Tanaike
Tanaike

Reputation: 201533

I believe your issue and your goal as follows.

  • For example, when the cell "M22" is set to the value of "Transmit", the date is put to the cells "O22" and "S22".
  • You want to know the reason of this.
  • From your script and question, you might want to achieve the following conditions.
    • When Transmit is put to the range of "M22:M", you want to put the date to the column "S" of the same row.
    • When the value except for Transmit is put to the range of "M22:M", you want to put the empty value to the column "S" of the same row.
    • When Update or Transmit is put to the range of "O12:O", you want to put the date to the column "Q" of the same row.
    • When the value except for Update and Transmit is put to the range of "O12:O", you want to put the empty value to the column "Q" of the same row.

Modification points:

  • When I saw your script, there are 2 if statements.
    • At 1st if statement, e.range.columnStart == 15 && e.range.rowStart >= 12 && e.value == 'Update' || e.value == 'Transmit' is used as the condition. In this case, when "M22" is set to "Transmit", this if statement becomes true by e.value == 'Transmit'.
    • At 2nd if statement, e.range.columnStart == 13 && e.range.rowStart >= 22 && e.value == 'Transmit' is used. In this case, when "M22" is set to "Transmit", this condition becomes true.
    • By these situation, e.range.offset(0,2).setValue(new Date()).setNumberFormat("yyyy-MM-dd HH:mm:ss") and e.range.offset(0,6).setValue(new Date()).setNumberFormat("yyyy-MM-dd HH:mm:ss") are run. I thought that this might be the reason of your issue.

When my guess, that I showed it above, is correct, how about the following modification?

Modified script:

function onEdit(e){
  if (e.range.columnStart == 15 && e.range.rowStart >= 12) {
    if (e.value == 'Update' || e.value == 'Transmit') {
      e.range.offset(0, 2).setValue(new Date()).setNumberFormat("yyyy-MM-dd HH:mm:ss");
    } else {
      e.range.offset(0, 2).setValue('');
    }
  } else if (e.range.columnStart == 13 && e.range.rowStart >= 22) {
    if (e.value == 'Transmit') {
      e.range.offset(0, 6).setValue(new Date()).setNumberFormat("yyyy-MM-dd HH:mm:ss");
    } else {
      e.range.offset(0, 6).setValue('');
    }
  }
}

Upvotes: 2

Related Questions