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.
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
}
}
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();
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With