How can I change the value of the cell A2 to "Today()" when I make any change in the cell A1?

Question

I am doing my project management in a Google Spreadsheet. A script that automates changes in my comment field to my change_date-field =TODAY() would help me to save some time and also use this function for other scripts I might need in the future.

When I make changes in a comment-field currently, I have to manually enter the change date in my change_date-field which is something that could be easily automated, I think.

Unfortunately I have no clue how to write the code for this.

When I am making any changes in my column named "comment" I want to return the value "=TODAY()" in the column named "change_date". Can you guys help me out with a script that does this job for me automatically?

Answer 1

It might be helpful if you could share a version of the spreadsheet you are referring to to help us answer more accurately.

Based on the information provided some ideas that might help you might be: 1. Use setFormula to change the requested cell to today(). Something like

function setToday() {
 var sheet = SpreadsheetApp.getActiveSheet();
 var range = sheet.getRange('B1'); //Set the cell here
 range.setFormula('=today()');
}

2. Add the date in using the script directly

    function newDate() {
     var sheet = SpreadsheetApp.getActiveSheet();
     var range = sheet.getRange('B1'); //Set the cell here
     var today = Utilities.formatDate(new Date(),Session.getScriptTimeZone(),'dd//MM/yy');
     range.setValue(today);
    }

You could use an onEdit trigger to update the cells when a specific cell is edited.

function onEdit(e){
 var sheet = SpreadsheetApp.getActiveSheet();
 var range = e.range;
 var column = range.getColumn();
 var row = range.getRow();
 if (column==2) { //Replace 2 with the column number of your comments cell//
   var newRange = sheet.getRange(row,column+1); //If the date is in the next column
 newRange.setFormula('=today()');
 }

Sorry I couldn't be more specific.