Google-sheets – Auto date added to adjacent cells when data is entered

dategoogle sheetsgoogle-apps-script

I am trying to create a spreadsheet to track student loan outs that will automatically put the date in an adjacent cell. The spreadsheet would be set up like this:

  • Column 1 would be "name"
  • Column 2 would be a drop down of what was loaned out
  • Column 3 would be the date
  • Column 4 would be a drop down menu again
  • Column 5 would be the date
  • Column 6 would be the drop down menu again
  • Column 7 would be the date

I Would like it to work so that each time i select something from the drop down menus, the current date would automatically be fill the adjacent cell so that I can track what was loaned out and the date each loan out was given. I am not sure if this is possible. I saw a script from OnenOnlyWalter that would add the date to column 2 and I tried to modify it so that it would work for multiple columns but no such luck.

Best Answer

Use this to add the dates:

Code

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

Example

I've created an example file for you: Adjacent Cell
Add the code to the script editor (Tools > Script editor...), press the save button and you're on the go !!

Related Topic