[GOOGLE SHEETS] Date Stamp when Sheet is Updated

[Mightytimbo]

I need a way to date stamp a sheet when a sheet is updated. I fill in a spreadsheet every week reporting sales transactions. I am supposed to fill in the date manually, but I often forget to update the date, or accidentally fill it out wrong. It would serve me much better if a formula could be entered that automatically entered that updated every time the sheet was updated.


Referencing the sheet attached, I'm looking for an automatic update on A2 anytime any cell on the sheet is updated.

As a caveat, I am working in google sheets, not excel. Most formulas translate across both platforms, but if you know that your solution is excel specific I just wanted to make you aware.

Is this possible?

pizzA.JPG

[mehmetcik]

This is very easy to do [ In Excel, Never Use Google so try it and see ].

Right click on the sheet name at the bottom of excel

Select view code

Paste this into the module that opens.

Close the module and save the Excel Spreadsheet as file type .xlsm.

Please Login or Register  to view this content.

[janmorris]

this is done using Apps Script.

Warning: The onEdit() trigger will mess with your undo/redo history.
The script is fired every time you edit any cell on the sheet, so the action from the trigger also gets added to the undo history, which means two things:

1. To undo the last thing you did, you have to undo twice in quick succession (once for onEdit(), once for your edit).
2. Since onEdit() also fires each time you undo something, it effectively gets rid of your redo history by immediately overwriting it.

heres the script:

Please Login or Register  to view this content.

[Haluk]

I would suggest to run the "onEdit" script only if the edited cell is in the range A3:H to prevent running unnecesarily.

.

[janmorris]

@Haluk,

The onEdit(e) trigger runs automatically when a user changes the value of any cell in a spreadsheet.

source: https://developers.google.com/apps-s...iggers#onedite

[Haluk]

@janmorris;

I know what the events, simple/installable triggers are and use them in my applications effectively .... don't worry about that.

What i tried to say in my message #4 is; if the table is in range say A3:H10 then, if you do not define this range in your "onEdit" function, then it will run also when any cell is edited out of this range as well. This will effect the performance and also may yield unwanted results.

So, it would be better if the working range is defined and after checking the edited cell is in this range, you allow the "onEdit" function to run. If not, you don't allow it to run..... and, this is to be coded in the "onEdit" function.

Hope, i am clear....

.

[janmorris]

@Haluk,
as mentioned in #5, the script onEdit will run every time an edit is done by the user, onEdit does not discriminate running when a range is defined because the script must run to check if the range is included.

however, do you have a working script that demonstrates what you have suggested, so that the date updates only when data is updated in a specific range?

[Haluk]

Of course "onEdit" function will run whether if the edited cell is in the range or not, but will not perform the necessary actions if it's not in the defined range .


The following script is for range C2:E10

Please Login or Register  to view this content.