0

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?

  • 1
    onEdit should work for all users having edit access to a spreadsheet. Consider to create a demo spreadsheet with anyone with the link with view access, add the link to the question so others could make a copy of it to test. – Rubén Apr 6 '18 at 13:21
  • Hi link of demo file exactly in line with the one i am using. docs.google.com/spreadsheets/d/… Various Remote users ( with View only access) update the data using Google Forms in this Sheet. Once it us updated, the status column is edited by My Team to give feed back 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. – Manoj Mathew Apr 9 '18 at 9:46
  • Please add those details to the question. – Rubén Apr 9 '18 at 11:51
  • Please don't use ALL CAPS for emphasis. – ale Apr 9 '18 at 14:38
0

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.

  • Yes Ruben. i removed the protection and Its working.. Thank you very much. BUT the protection was intended to ensure there is no tampering of the time recorded. Is there any option to retain the protection same time record the time stamp? – Manoj Mathew Apr 10 '18 at 10:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.