Editor to unlock a row which already been locked

Ruby Chu

I have a sheet share with many collaborators with editor access. I have a workflow to lock the row when the "rating" column has been rated. But sometimes, the collaborators say they need to modify some cells after rating, but the row has been locked, so they cannot edit.

I set a unlock workflow. However, the collaborators cannot trigger the workflow because the row has already been locked.

Is there any ideas or formula to let the collaborators can trigger the unlock workflow after the row has been locked? 

Thank you

Kevin Smith

Hi Ruby

This is an interesting challenge but one that I think can be accomplished, depending on the details of your requirements.

One option to consider is having a notification sent to someone when a locked row is requested to be changed. This could be a new sheet, with a form. They submit the form or request and a notification is sent to an Admin to unlock the row. This might be possible to do on the sheet with a comment, I believe you can leave comments on the sheet but not on the locked rows.

Another option that uses automation would include another sheet, same concept, with a form to request a row be unlocked. The requestor would need to know the Project ID or something similar. This would be used to link to the main sheet. So on the main sheet you would have a checkbox that is labeled 'Unlock' and another checkbox col. named Request to Unlock. The request to unlock would have a formula in it that uses Index/Match to find an unlock request and check the box if one exists. THEN, you would have an automation that would run on date to see of the Request to Unlock is checked, if it is it would change the value of Unlock to checked. You would also have an automation that changes the row to unlocked if the Unlock col. is checked.

This works and I am happy to give you some formulas, etc. if you would like. A couple of notes, what I described above can run more than once a day but you would need to set up multiple automations to check the "Request to unlock" Maybe every couple of hours or more if you need. I am not sure how to make it work otherwise, because something needs to fire off the automation on the main sheet and that isn't fired off when the index/match is changed so the change happens when the automation changes the Unlock checkbox. If someone else knows a better way, I hope they will jump in.

Please let me know what else I can provide you.

Ruby Chu

Hi Kevin,

Thanks for your advice. The 1st option looks more simple and straightforward, but unfortunately, it cannot leave comments in conversation when the row is locked.

There are thousands of data in the sheet, and I hope all the actions can be made in the same sheet. It would be too complex to have the users submit a form to request to unlock rows.

That's would be great if the user can use conversation tools even the row has been locked.

Kevin Smith

Hi Ruby

Comments can be made to the sheet but not to a locked row. That doesn’t help much and doesn’t drive an automation.

The other option is more complicated. The user may never know there is another sheet involved if just have them use the form but I understand the the need to keep it simple. With some thought, it might be possible to use the same sheet but it might get messy. I would still probably move the requests to another sheet after they came in and then process them the same way as above to eliminate the user from being aware of a second sheet.

Automation aside and not using another sheet, I can’t think of another way other than comments to the sheet (not row specific) or using a form to get the request.

I would love to know if you take it further or need anything additional help.