1

I have a simple sheet that has two columns with tick boxes in them (columns 5 and 8) The goal is when the box is ticked the date and time are inserted in the column next to it. I have got it working correctly with one column with the following code:

function onEdit(e) {
  var aCell = e.source.getActiveCell(), col = aCell.getColumn(); 
  if(col == 5) {
    var adjacentCell = aCell.offset(0, 1);  
    var newDate = Utilities.formatDate(new Date(), 
      "GMT+1", "dd/MM/yyyy kk:mm:ss");
    adjacentCell.setValue(newDate);
  }
}

For the life of me, I can't work out how to modify that so that it works with column 8 as well. Any help will save me tearing my hair out!

0
  • you can simply just double it:

function onEdit(e) {
  var s = SpreadsheetApp.getActiveSheet();
  if( s.getName() == "Sheet1" ) { 
    var r = s.getActiveCell();
    if( r.getColumn() == 5 ) { 
      var nextCell = r.offset(0, 1);
      var newDate = Utilities.formatDate(new Date(), 
      "GMT+1", "dd/MM/yyyy hh:mm:ss");
      nextCell.setValue(newDate);
    }
    if( r.getColumn() == 8 ) { 
      var nextCell = r.offset(0, 1);
      var newDate1 = Utilities.formatDate(new Date(), 
      "GMT+1", "dd/MM/yyyy hh:mm:ss");
      nextCell.setValue(newDate1);
    }
  }
}

  • Hi there and thanks for the reply. I had actually tried that and what it does is ignore the first part and only works on column 8. Clicking column 5 does nothing. – ceolnamara Apr 28 at 21:36
  • @ceolnamara try now what it does – user0 Apr 28 at 21:53
  • good morning, thank you for taking the time to look at this. I tried the amended code and this has stopped putting the date in any of the adjacent columns :( – ceolnamara Apr 29 at 8:24
  • @ceolnamara answer updated (and tested) – user0 Apr 29 at 10:51
  • That worked perfectly. I just had to change the sheet name as i was using custom ones and your script run as it should. – ceolnamara Apr 29 at 12:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.