I got a good Google Sheet script for pasting a time stamp at the designated column of the same row on editing any cells of another particular column. Here it is On Edit of column 11: timestamp is to be pasted at column 13, i.e., 2 columns adjacent.
function onEdit(e) {
if (e.range.getColumn() == 11) {
e.range.offset(0, 2).setValue(new Date());
}
}
But the problem is that it is being updated only when edited by myself. Can it be made applicable for all users' edits? Also, I want it to be applicable for only particular sheet.
Again repeating the issue: need to apply for all user edits, only to a particular sheet.
Link of demo file exactly in line with the one I am using:
https://docs.google.com/spreadsheets/d/1oaDfavjdZrlB-IWdsEa6KAvKIWrgBLnSbg__Oo_88r0
Various Remote users (with View-only access) update the data using Google Forms in this Sheet. Once it is updated, the status column is edited by My Team to give feedback to the remote users. I want to capture the time taken for the response of my team. So I have used the above script. Hope it is clear. I have shared it to one of my other Gmail ID and tried editing after sign in using the alternate Gmail ID. Not working. The Column 13 is protected and editable by me only.
I removed the protection and Its working… But the protection was intended to ensure there is no tampering with the time recorded. Is there any option to retain the protection and record the timestamp?
Best Answer
The problem occurs because the cell to be modified is protected to allow only the owner to write on it but an on edit simple trigger can't made changes on such conditions.
The solution is to remove the protection or to add the editors to the authorized users to edit the protected range.