I am having a sheet that user will be using from its end, having dropdown options "Started, Working, Completed" in Column C. Requirement: Once user have selected "Completed", dropdown must get disabled and no more changes should be possible by user for that cell.
Is there any work around possible for this scenario.
SS : https://docs.google.com/spreadsheets/d/1tmnDOMyupjeO8d65qHQsxb5KrrKeq7pMYIE8h2VmEJ4/edit?usp=sharing
CodePudding user response：
I believe your goal is as follows.
- When the user changes the dropdown list of column "C" of "Sheet1" to "Completed", you don't want to make the user change it again.
In this case, how about protecting the cell? When this is reflected in a sample script, it becomes as follows.
Sample script:
In this case, the installable OnEdit trigger is used. So, please install the OnEdit trigger to the function installedOnEdit.
When you use this script, please change the dropdown list of column "C" of "Sheet1" to "Completed" by the user who is not the owner of Spreadsheet. By this, the cell is protected.
In the case of the owner of Spreadsheet, the owner can change the protected dropdown list. When the owner changes the dropdown list from "Completed" to another value, the protection is removed.
function installedOnEdit(e) {
const range = e.range;
const sheet = range.getSheet();
if (sheet.getSheetName() != "Sheet1" || range.columnStart != 3) return;
const p = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE).find(r => r.getRange().getRow() == range.rowStart);
if (!p && range.getValue() == "Completed") {
const p = range.protect();
p.removeEditors(p.getEditors());
if (p.canDomainEdit()) p.setDomainEdit(false);
} else if (p) {
p.remove();
}
}