Google Apps Script - Is it possible to update a protected range from a script?

Question

I want to use protected ranges to lock down the range, but by using a UI I want to enable users to make edits, insert new data, etc. I just want the edits to be deliberate.

I've thought of a few solutions but am unsure of how they would impact the load, and not sure which would be the best direction: Unprotect the ranges and reprotect, give edit permissions and take them away. Use java script instead of the built in protected ranges functionality. It doesn't seem possible, but even if the scripts were ran from my account that would work (the workbooks are all working as designed from my account, but I have edit access to the protected ranges).

Any help would be much appreciated, thanks in advance.

Background on the scripts: I have workbooks for specific projects within the company, and a master workbook, which sync two ways. There is a lookup key, simply the facility/feature specific title, and so I have a script that would allow both locations to be updated simultaneously. I also have a template for new features, or the facility specific projects each have their own templates, and I need users to be able to add a new sheet within these workbooks, and this data to update the master workbook "AllTasks" sheet used for various queries, but in both workbooks I need to have protected ranges.

Answer 1

Running scripts from your account will only work if you deploy the script as a web app. Users would go to a separate URL, fill out the form, submit, etc and as long as the script is being run under your account (there is an option for this when deploying as a web app), it will insert/update protected ranges since you have edit access. Users can still go into the workbook separately and add sheets to it (as long as they have edit rights), they just can't touch the protected ranges unless they have separate rights for those.

But if the users will run these scripts directly from the spreadsheet, I don't believe it is possible to run from your account. But I think the above would be the best solution. I don't see any scripts that can unprotect/protect ranges yet anyways, only sheets.

Answer 2

If it only needs to be protected against accidental editing you could set data validation rules. The Script for the UI can then adjust those data validation rules prior to editing.

var allowedText = "Yes" var cell = SpreadsheetApp.getActive().getRange('A1'); var rule = SpreadsheetApp.newDataValidation().requireTextEqualTo(allowedText).build(); cell.setDataValidation(rule); cell.setValue(allowedText);

This will not protect against malice, nor will it protect against hiding, unhiding, deleting or adding cells or deleting the contents of cells, but it will protect against accidental overwriting of the cell.