How we can freeze Google sheet one cell-CodePudding

Can you help me to freeze the cell in the Google Sheet.

!.e:-Please refer the Image I have added checkbox If I ticked once the check box then it should not untick once again or If I write something in the cell it will be freeze

CodePudding user response：

In Google Sheets there is no way to "freeze" / "lock" a checkbox from being edited by the spreadsheet owner.

One workaround is to use two accounts, one to be used as the spreadsheet owner and another to edit the spreadsheet. Using the owner account you could protect the cell manually of by using an edit / change script.

Another workaround is to use an edit trigger to overwrite the new value by the old value. NOTE: Clicking very fast over the checkbox will eventually uncheck the checkbox.

Failure of calling Google App Script onEdit function many times in a second

References

CodePudding user response：

This is [sort of] possible without scripting, but the solution is a bit ugly. You will need to add two columns to the left of your column of tick boxes, and hide them. You also need to switch iterative calculations on, and set the max number of iterations to 1. If you're willing/able to do that, try the following in a blank sheet to demonstrate the general principle:

In C1, insert a tick box.
In B1:

=if(A1>0,{"",TRUE},"")

In A1:

=if(C1,A1 1,A1)

If you click on the tick box, this sets it to TRUE, which adds 1 to the value in A1 (default 0), which in turn causes B1 to try to expand an array literal over the tick box with a TRUE in that position. It can't do that as a TRUE is already there so a REF! error is generated in B1, but you'll find the tick box is 'stuck' on TRUE as the logic in cells A1 & B1 means that C1 can't not be TRUE.

N.B. 1 - you will need to protect the two columns containing the logic (A & B in the above example) so that only you as owner can change them, then hide the columns. This prevents editors from unhiding and/or modifying them.

N.B. 2 - this doesn't prevent an editor from deleting a tick box (with the delete key) after they have ticked it, but if they do this the formula in B1 will expand a TRUE into the deleted tick box cell; this can't be deleted, and is logically the same as the TRUE from a tick box (i.e. any formulas using the tick boxes would still work).

N.B. 3 - Editors could still potentially create a copy of the entire sheet which would give them the ability to edit the two protected and hidden columns (breaking the one-time-only logic for the tick boxes), but this sheet would obviously have a different ID to the original.