So I am trying to set up a google sheet that dozens of users are going to use and I want to automate the protection schemes since a new sheet is being made everyday. I’m trying to have sheet headers and certain columns protected from all users (besides explicit exceptions) and when an edit is made to a cell, edit permissions for all users are removed. I’ve tried attempting removing permissions both using the onOpen and onEdit functions via setUnprotectedRanges() and the regular range.protect() like in the documentation. But every time I try the onOpen, it breaks the other. I don’t know how to keep setting these because permissions need to be removed for as many users possible (besides editor and owner). Here’s what I have for the cell protection removal after edit that does work:
function onEdit(e) {
var sheet = e.source.getActiveSheet();
var editedCell = e.range;
if (editedCell.getColumn() == 4 && editedCell.getRow() > 1) {
var link = editedCell.getValue();
// If a link is pasted (non-empty cell)
if (link != "") {
var row = editedCell.getRow();
var currentTime = new Date(); // Get current date and time
// Set the timestamp in column F
sheet.getRange(row, 6).setValue(currentTime);
// Set the formula in column G to calculate elapsed time
var elapsedTimeFormula = '=IF(F' + row + '<>"", NOW() - F' + row + ', "")';
sheet.getRange(row, 7).setFormula(elapsedTimeFormula);
// Protect the specific cell in column D where the link was pasted
var cellToProtect = sheet.getRange(row, 4);
var protection = cellToProtect.protect().setDescription('Cell Locked After Link Added');
//var me = Session.getEffectiveUser();
protection.removeEditors(protection.getEditors());
//if (protection.canDomainEdit()) {
protection.setDomainEdit(false);
//}
}
}
}
As for protecting certain columns and rows onOpen(), version history failed me there but it was along the lines of:
function onOpen(e) {
var sheet = e.source.getActiveSheet();
var protection = sheet.protect().setDescription('Default Protected Headers and Columns');
var unprotectedLeftOfColumns = sheet.getRange('A2:H1000');
var unprotectedRightOfColumns = sheet.getRange('J2:N1000');
protection.setUnprotectedRanges([unprotected]);
// Ensure the current user is an editor before removing others. Otherwise, if the user's edit
// permission comes from a group, the script throws an exception upon removing the group.
//var me = Session.getEffectiveUser(); [Doesn't work for some reason for two users on same computer]
//protection.addEditor(me);
protection.removeEditors(protection.getEditors());
//if (protection.canDomainEdit()) {
protection.setDomainEdit(false);
//}
Using these methods I keep getting errors but I can’t find the documentation for them so its really hard trying to debug this. At this point it’s easier to but the header and column protections when copying the sheet, but I’m not the one doing it so I can’t be sure it’s done. The protection app script documentation is starting to look bleak, I wouldn’t even be sure about the other methods like removeEditor() since it’s a large sheet shared by link and speed is key. Any help would be highly appreciated because I can’t find much about any of these issues.