Automatic timestamp when a cell is filled out

user_1521662 0
Viewed 85032

I have an excel formula that is very simple and it works because I can restrict the recursive iterations. I am not very script savvy, but this is what it is and it works.

=IF(D24="P",IF(E24="",DateStamp,E24),IF(D24="F",IF(E24="",DateStamp,E24),""))

Its a pass/fail testing sheet and it adds a timestamp when someone passes or fails the test. We've added a few more people and I want to move the document to google apps to allow more than 1 person to work on it at the same time.

The only issue i've come in is the circular reference that this causes. In excel I can limit the # of iterations in the options, I dont have this ability anymore. Any help would be great.

EDIT: What I've tried. I've tried to find a way to input a VBA Script that a coworker created that would work for me. I'm not good with scripting so I'm unable to make this into a google apps script:

VBA SCRIPT:

    Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 4 Then
If Cells(Target.Row, 5).Value = "" Then

Cells(Target.Row, 5).Value = Now
End If
Else
End If


End Sub

In theory I tried to create a script that will copy a cell that has a timestamp on it and then try to 'paste special' and just paste the value into the cell needed. This would work except I was unable to find a way to paste special with the google apps scripting.

Thanks for any help /edit

google-apps-script google-sheets timestamp
7 Answers

You just need to use Apps Script. I'll explain using an example:

function onEdit(e) {
var row = e.range.getRow();
if (row > 1 && e.source.getActiveSheet().getName() === "Sheet1") {
    e.source.getActiveSheet().getRange(row, 14).setValue(new Date());
} else {
    if ((row > 1 && e.source.getActiveSheet().getName() === "Sheet2") || (row > 1 && e.source.getActiveSheet().getName() === "Sheet3")) {
        e.source.getActiveSheet().getRange(row, 6).setValue(new Date());
    }}}

This first of all check which sheet is being edited. If sheet1, then it takes the 14th column of all rows (getRange(row,14)) in that sheet & whenever anything is edited (edit(e)), it adds timestamp (setValue(new Date())) in that 14th column. Similarly, if it's a different sheet,i.e., Sheet2 or Sheet3 or any other name, we can select different columns for timestamp as per requirement. Also, if( (row > 1) condition has been added so that timestamp does NOT get added in the first row upon edit as it's usually headings.

Now you select to get a range of cells & use them as per requirement. See this question for a better idea on getRange().

user_143879740

I set the timestamp to include HH:MM:SS but upon testing the stamp 4 times in under a minute I get: 03,14,11,07 fluctuate as the MM in my timestamp.

user_77380900
Related