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);
    }
  }
}

5
  • 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 '19 at 21:36
  • @ceolnamara try now what it does
    – user0
    Apr 28 '19 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 '19 at 8:24
  • @ceolnamara answer updated (and tested)
    – user0
    Apr 29 '19 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 '19 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.