Automatic timestamp when a cell is filled out

Question

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

Pebbly

Answer 1

Stackoverflow is a place to ask questions related to programming, e.g. that you're actually working on. Not really asking for others to develop it for you, i.e. you didn't even started trying any Apps Script code yet. I recommend you reading its tutorials and guides. It's really easy to start.

Anyway, just to help you get started, I'll drop everything you said and stick to the question title: "automatic timestamp when a cell is filled out"

I advise you to do it all on apps script, and drop your formulas entirely, e.g.

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  if( s.getName() == "Sheet1" ) { //checks that we're on the correct sheet
    var r = s.getActiveCell();
    if( r.getColumn() == 4 ) { //checks the column
      var nextCell = r.offset(0, 1);
      if( nextCell.getValue() === '' ) //is empty?
        nextCell.setValue(new Date());
    }
  }
}

This code does what I understood from yours, which is: if something is edited on column D and column E is empty, add the current date to E.

Fortna

Thank you for your help. I'm extremely new to scripting at all, so all that I can do at this point is try to learn. I have read through the guides and while i've tried a lot of different things I dont know what works. ie. I've never seen r.offset() I had to google what that was. – Pebbly

Alright, I'm glad I helped. offset is just one way of doing it, in this case probably the easier one, but I rarely use it elsewhere. BTW, since you're new here, you should mark an answer as accepted when you feel you got a good one. Voting is also another nice feature. – Fortna

why you want to check the sheet name and the column number? – Item

I'm a little confused... Why r.getActiveCell() won't return the cell into which the function has been entered into? – Item

we check the sheet name and cell column because that is usually a requirement of such scripts. Normally one only want to add this "timestamp" feature to a specific sheet/tab, instead of the all sheets available in the file. Same goes for the column. The idea is to monitor changes in a particular important section of your sheet. But if you don't need those, just remove the ifs. – Fortna

To change which column is being "watched", change the 4 in if( r.getColumn() == 4 ) {, e.g. 2 would "watch" column B. If you want the timestamp to be inserted in the column before (rather than the column after), change the 1 in var nextCell = r.offset(0, 1); to -1. – Glia

Answer 2

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().

Thad

Answer 3

Just addition to above code FOR Multi Column AutoStamp in Same Sheet

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  if( s.getName() == "Sheet1" ) { //checks that we're on the correct sheet
    var r = s.getActiveCell();
    if( r.getColumn() == 5 ) { //checks the column
      var nextCell = r.offset(0, 1);
      //if( nextCell.getValue() !== '' ) //is empty?
      nextCell.setValue(new Date());
    }

    if( r.getColumn() == 7 ) { //checks the column
      var nextCell = r.offset(0, 1);
      //if( nextCell.getValue() !== '' ) //is empty?
      nextCell.setValue(new Date());
    }

    if( r.getColumn() == 9 ) { //checks the column
      var nextCell = r.offset(0, 1);
      //if( nextCell.getValue() !== '' ) //is empty?
      nextCell.setValue(new Date());
    }
  }
}

Farah

Answer 4

Based on Henrique G. Abreu's answer, thank you!

If a cell is edited in column B and the cell on the same row in column A is empty (or contains a number, see below), put the current time in A.

Ignore if the cell edited in column B wasn't empty before (e.oldValue !== undefined).

If column A contains a number, e.g. -5, use that number as an offset for the time, e.g. subtract 5 minutes.

Uses Intl.DateTimeFormat to format the time. Currently configured to insert the time only ({ timeStyle: "short" }), not the date, for ease of manual editing.

See also onEdit event object docs.

// https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Intl/DateTimeFormat
const timeFormatter = new Intl.DateTimeFormat("en-US", { timeStyle: "short" });

function onEdit(e) {
  // https://developers.google.com/apps-script/guides/triggers/events#edit
  // Return if cell was not empty before this edit
  // Return if this is a multi-cell edit
  if (e.oldValue !== undefined || e.value === undefined) return;

  const activeCell = e.range; // Or: SpreadsheetApp.getActiveSheet().getActiveCell();
  
  // Return if cell is not in column 2 (B)
  if (activeCell.getColumn() !== 2) return;

  // Cell  to put time into, -1 means to the left, so column 1 (A)
  const timeCell = activeCell.offset(0, -1);

  const timeCellValue = timeCell.getValue();
  const offset = typeof timeCellValue === "number" ? timeCellValue : 0;

  // Return if time cell contains anything other than an offset
  // (an empty cell will be treated as an offset of 0)
  // An offset example: `-5` will subtract 5 minutes from current time
  if (!offset && timeCellValue !== "") return;

  const date = new Date(Date.now() + 60 * 1000 * offset);
  const timeValue = timeFormatter.format(date).toLocaleLowerCase();
  timeCell.setValue(timeValue);
}

Glia

Answer 5

and if you want it to update if the cell is changed again just delete this line

if( nextCell.getValue() !== '' ) //is empty?

By the way, how can the date be formatted to ie. dd/mm/yyyy instead of the default dd/mm/yyyy hh:MM:ss format

Debutante

Answer 6

Actually, in this case you don't have to script anything. Google (or someone) has done it already. In your Google spreadsheet, go to "Insert -> Script" and search on "time". There are two ready-made scripts which will do what you want. I found "Cell Last Modified Date" works perfectly. Select it and click the "Install" button. You can reformat the column to show date, date+time, and so on. You can also hand code a date in the column, or move them from another column if you were tracking it before, and they will stay as you set them. But updating any cell in the row will update the timestamp.

Energetic

The insert script from Gallery feature isn't available anymore – Thigmotropism

Answer 7

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.

Dialectic

Answer 8

it's much easier than that! =now

or;

=today

Depending what you need

Frictional

The values returned by NOW() and TODAY() change every time that the spreadsheet is recalculated. – Thigmotropism