Google Sheet Script | Can I access a cell edit history via a script formula?

4
votes

I'm working with google sheets and I'm trying to figure out a way of how to get the time when a cell is filled in? I would like that access this information via the google sheet script. Is that even possible? Thank you for your time.

1
google-apps-scriptgoogle-sheets
Does this answer your question? Capture date & time when cell is edited (google spreadsheet) – xTwisteDx
I'll look into it thank you – Federico Minelli
The onEdit() function is often misunderstood. It only fires on 'user' edits not on changes due to other script or formula actions. reference – Cooper

1 Answers

0
votes

After reading your question, I assume the following:

  • You want to detect when a particular cell is modified.
  • When the modification is detected, you want to print a timestamp in another cell.

If my assumptions are correct, you can use the following example to achieve your requests:

CODE

function onEdit(e) {
  if (e.range.getA1Notation() == "C4") {
    var timestamp = new Date().toLocaleTimeString('it-IT');
    SpreadsheetApp.getActive().getActiveSheet().getRange(4, 4).setValue(
      timestamp);
  }
}

BEHAVIOUR

The code will run when an edit is made on the spreadsheet by a user. It will check if the modified cell is the desired one (C4 in this example), and if it is a timestamp will be dropped in the time-keeping cell (D4 in this case).

OBSERVATIONS

  • This code works for every sheet of the spreadsheet. To make it exclusive to a sheet, you should use getSheet methods [as getSheetByName()].
  • Due to onEdit trigger limitations, this code won't run if the modification is made by a script (in opposition to a user edit).
  • The timestamp will be printed in the italian timezone because your name looks Italian to me. Please, forgive me if I mistook your timezone.

ALLUSIONS

Please, don't hesitate to write me back any additional doubts or request me further clarifications.