Google Apps脚本 - 是否可以从脚本更新受保护的范围？ [英] Google Apps Script - Is it possible to update a protected range from a script?

问题描述

我想使用受保护的范围来锁定范围，但通过使用用户界面，我希望用户能够进行编辑，插入新数据等。我只是希望编辑是有意的。

我想到了一些解决方案，但我不确定它们会如何影响负载，并且不确定哪个方向是最好的方向：
解除范围并重新保护，给出编辑权限并将其带走。使用java脚本代替内置的保护范围功能。这似乎不可能，但即使这些脚本是从我的帐户运行的（这些工作簿都按我的帐户设计的那样工作，但我可以编辑访问受保护的范围）。

任何帮助将非常感谢，预先感谢。

脚本背景：
我有特定项目的工作簿该公司和一个主要工作手册，可以同步两种方式。有一个查找键，只是设施/功能特定的标题，所以我有一个脚本，将允许两个位置同时更新。我还为新功能提供了模板，或者特定于设备的项目都有自己的模板，并且我需要用户能够在这些工作簿中添加新工作表，并使用这些数据更新主工作簿AllTask​​s工作表各种查询，但在这两个工作簿我需要有保护范围。

解决方案

如果只需要防止意外编辑可以设置数据验证规则。

var allowedText =是
var cell = 。SpreadsheetApp.getActive（）getRange（ 'A1'）;
var rule = SpreadsheetApp.newDataValidation（）。requireTextEqualTo（allowedText）.build（）;
cell.setDataValidation（rule）;
cell.setValue（allowedText）;

这不会防止恶意行为，也不会防止隐藏，取消隐藏，删除或添加单元格或删除单元格的内容，但它可以防止意外覆盖单元格。

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.

解决方案

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.

这篇关于Google Apps脚本 - 是否可以从脚本更新受保护的范围？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！