Advertisement
jakkyjack

Enter a timestamp in column M when column B says TRUE but only if column M is blank

Jul 9th, 2021
100
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. function onEdit(e) {
  2.   var s = e.source.getActiveSheet();
  3.   var row = e.range.getRow();
  4.   var col = e.range.getColumn();
  5.   var offsetCol = (col == 2) ? 11 : 12
  6.   var offsetValues = e.source.getActiveSheet().getRange(row, col, 1, 2).offset(0, offsetCol).getValues()
  7.   var curDate = Utilities.formatDate(new Date(), "GMT-5", "dd MMM yyyy")
  8.   var curTime = Utilities.formatDate(new Date(), "GMT-5", "HH:mm")
  9.  
  10.   console.log(offsetValues[0][0], offsetValues[0][1])
  11.   console.log(s.getRange(row, col, 1, 2).offset(0, offsetCol).getA1Notation())
  12.   console.log(offsetValues == ["", ""])
  13.   if (s.getName() == "Input" && row >= 2 && (col == 2 || col == 3) && e.value == "TRUE" && offsetValues[0][0] == "" && offsetValues[0][1] == "") {
  14.     s.getRange(row, col, 1, 2).offset(0, offsetCol).setValues([[curDate, curTime]]);
  15.   }
  16.   else return;
  17. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement