How to protect cells from further edits after a specific condition is met in Google sheets?

Question

I am maintaining a workbook in google sheets where users update their status and date of completion after a certain task is done. A single sheet contains a status column with 2 input options, 'WIP' and 'Done'. The adjacent date column is to specify the date of completion once the task is 'Done'. I want to lock the the status cell once 'Done' is entered and then the adjacent date cell once the date has been entered. I have many sheets in the workbook with the same format and want to be able to protect these cells from further edits once a user updates 'Done' in any of the sheets. Thank you.

Answer 1

You could start by using an onEdit trigger in order to lock the cells after an edit is made. To write this function, you can follow some of the steps below:

1. Check whether the edited cell corresponds to the one which has the status; this is done by using the e event object and the range property:

if (e.range.getRow() == "CELL_ROW" && e.range.getColumn() == "CELL_COL")
// means that the edit is done in the cell you want

2. Protect the data range by using the below methods:

• getRange - to retrieve the range corresponding to the one which you would like to protect;

• protect - to create the protection;

• getEditors - to retrieve the editors corresponding to the protection created above;

• removeEditors - to remove all the editors from the protected range such that the cells are "locked".

Reference

• Apps Script Simple Triggers;

• Apps Script Event Objects;

• Apps Script Range Class - protect();

• Apps Script Protection Class - getEditors();

• Apps Script Protection Class - removeEditors().