Set value of active cell in Google sheets script

Question

I want to create a formula which creates a timestamp on a certain cell change. The code below is okay for that. What I want to do now is anchor that timestamp to the sheet by converting the formula to plain text.

If you would do it manually you would select the timestamps, copy them and paste them as values.

I don't want to do it manually so I created the script below. It tries to overwrite the currenct active cell. Does anybody have an idea how to get this working?

Thanks

function cvTimestamp(value) {
  var timezone = "GMT+1";
  var timestamp_format = "dd-MM-yyyy HH:mm:ss"; // Timestamp Format. 


  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];


  var date = Utilities.formatDate(new Date(), timezone, timestamp_format);

  return date

  // Returns the active cell
  var cell = sheet.getActiveCell();
  // try to write date to the active cell as a value.
  cell.setValue(date);

}

Solution

How about following script? When the name of function is "onEdit()", this is executed. And an edited cell of spreadsheet is changed to "date".

function onEdit(e){
  var row = e.range.getRow();
  var col = e.range.getColumn();
  if (row == 3 && col == 3){ // You can freely define the range for putting DATE. 
    var timezone = "GMT+1";
    var timestamp_format = "dd-MM-yyyy HH:mm:ss";
    var date = Utilities.formatDate(new Date(), timezone, timestamp_format);
    var col = 5; // If you want to put a value to colmun 'E' of editing cell, col is 5.
    var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    ss.getRange(e.range.getRow(), col).setValue(date);
  }
}

Comments

[Christoph]

This does not work as expected since it puts a timestamp in every cell I try to edit. I could try to edit it to check if e is in the same row I want to write to. If so update te timestamp in that row in a particular column. (I do not know how to check this) Unless somebody has a more simple solution.

[Tanaike]

The active cell can be retrieved by "e.range". If you want to edit automatically other cell, you can get the cell position based on the active cell. For example, if you edit "a1" cell and want to change "b2" cell, "e.range" has "a1" position. So using "offset()", you can write "date" to a cell you want. I updated the sample script. Please check it.

[Christoph]

That works but the reason I started with a normal formula was so I could easily choose which field defines the timestamp. Now I have to edit the script code everytime I add a new field.

[Tanaike]

I'm sorry for my poor English skill. Please check the sample script I updated just now. The column position of timestamp is locked, while you edit various cells. In the sample, the timestamp is shown at column D. If I misunderstand what you want to do yet, please tell me. I want to modify again.

[Tanaike]

I'm sorry. col = 5 is column "E". And if you also want to lock the row, you can use "getRow()".

[Christoph]

Thanks for the edit, going to try that one. What I actually meant with being able to choose the column quickly is by typing =formulaname(B1) for example. The onedit function has you going to the scripting window every time.

[Tanaike]

Custom function outputs a value to same cell from input value. So it cannot put a value from the custom function cell to other cell. Although when multi dimensional array is used, values can be put other cell, it cannot overwrite to existing values. So when you want to put DATE to other cell from the cell of custom function, I think of the use of onEdit() which has the limited range. Please check updated sample script.