Depending upon a specific input in a set of cells on my Google Sheet, I want to be able to freeze a particular row and stop it being updated.
I have the following script:
function testFreeze() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
freezeRow(sheet,13);
}
function freezeRow(sheet,rowToFreeze) {
let freezeRange = sheet.getRange(rowToFreeze, 1, 1, sheet.getMaxColumns());
freezeRange.copyTo(sheet.getRange(rowToFreeze, 1), {contentsOnly: true}); /// replace formulae with values
freezeRange.setBackground("pink");
freezeRange.protect().setDescription('READ ONLY');
}
The testFreeze script is linked to a button to test this element of the process only. In normal operations, it will be executed via a simple trigger, specifically onEdit.
However, it only works (ie, protects the row) about 1 time in 5. I know I as the sheet owner will always be able to update it, but 4 times out of 5 the person I'm trying to protect the sheet from can also edit it.
I've tried running the script as me, running it as the other user, refreshing between script updates, but nothing seems to make it consistent.
In response to doubleunary's question: in the cut-down version I have presented here, the testFreeze script is tied to a button for the purposes of testing just this protection element. In normal operations, it's tied to a simple trigger, specifically onEdit.
I know I as the sheet owner will always be able to update it
Use .setWarningOnly() to prevent unintentional edits by anyone, including yourself, like this:
function testFreeze() {
const sheet = SpreadsheetApp.getActiveSheet();
freezeRange_(sheet.getRange('13:13'), true);
}
function freezeRange_(range, optWarningOnly) {
const me = Session.getEffectiveUser();
range.setValues(range.getValues());
range.setBackground('pink');
const prot = range.protect().setDescription('Read-only').setWarningOnly(!!optWarningOnly);
if (!optWarningOnly) {
prot.addEditor(me).removeEditors(prot.getEditors().filter(user => user.getEmail() !== me));
try {
prot.setDomainEdit(false);
} catch (error) {
; // we are not in a Google Workspace domain
}
}
}
This gives the same behavior as the Show a warning when editing this range option in the user interface. The user is shown a dialog box that halts the change from going through, but also lets them override the protection. If you don't want to give users that choice, omit the second argument (true).
See the Protection class.