Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Enter current date into a cell when another cell is changed

I have a Google spreadsheet that I use to track the status of configuration information requested for an app, from a client. E.g. Text to go on a button. I have a column with 'Status' at the top and cells with 'List from Range' Data Validation in the rows beneath. The range used has 'With client', 'With Me', 'Completed'. I also have a column to the right which I use to manually add the date the last time the status changed. What I would like is for the date to be automatically changed to today's date when I change the status. I believe it can be done with a script but I have never used one before.

like image 910
alicooke Avatar asked Aug 13 '14 11:08

alicooke


2 Answers

The other answer started with a good idea but unfortunately the suggested code does not work because it has many basic errors (obviously it wasn't tested ...)

So here is a version that simply works :

function onEdit(e) {
  var sheet      = e.source.getActiveSheet();
  var activeCell = sheet.getActiveCell();
  var col        = activeCell.getColumn();
  var row        = activeCell.getRow();
  if (col == 2 ) { // assuming status is in column 2, adapt if needed
    sheet.getRange(row, col+1).setValue(new Date()).setNumberFormat('MMM dd yyyy');// change the display format to your preference here
  }
}
like image 173
Serge insas Avatar answered Sep 30 '22 11:09

Serge insas


     column 'Status'    column 'date'
row 'List from Range'   12-08-20014
row 'Data Validation'   -----------

range {'With client', 'With Me', 'Completed'}

ok if you open your script editor and add the code below, replace the BOLD TERMS with relevant values. onEdit() should not need a trigger as it is a 'special' function, unless you are sharing the sheet and need otehrs to run it with developer authority.

onEdit() {
  var sheet      = e.source.getActiveSheet();
  var col        = activecell.getColumn();     //  numeric value of column, ColA = 1
  var row        = activecell.getRow();        //  numeric value of row
  if (col == PUT IN THE NUMBER OF YOUR COLUMN 'Status') {
    sheet.getRange(row, col+1) = Date();
}
like image 31
Niccolo Avatar answered Sep 30 '22 11:09

Niccolo