如何复制具有受保护范围的工作表? [英] How to duplicate a sheet with protected ranges?
问题描述
我已经建立了一张模板表,每周我都会复制。我注意到,当我去复制它时,没有受保护的范围与它一起被复制。是否可以使用Google Apps脚本将完全相同的受保护范围复制到复制表单中?这会为我每周节省很多时间。注意:在Web Apps上发布了类似的问题,因此我正在调整部分内容我的回答到目前的情况。
使用 sheet.getProtections
方法,您可以获得给定工作表上的保护数组,并在它们上循环,在目标上创建它们的模拟片。这有点令人讨厌,因为似乎没有办法简单地将保护克隆到另一个范围。 (可以改变保护范围,但是将它移动到新的范围,而不是复制。)
所以,在函数如下:
- 使用
p.getRange()获取每个受保护范围的A1表示法。 getA1Notation();
- 使用
p2 = sheet2.getRange(rangeNotation).protect();保护目标工作表的相应范围。
- 设置 p 的属性,新保护
p2
的属性。这包括如果保护不仅仅是警告类型,则删除/添加编辑器。
函数duplicateSheetWithProtections(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
sheet = ss.getSheetByName('Template');
sheet2 = sheet.copyTo(ss).setName('My Copy');
var protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
for(var i = 0; i< protections.length; i ++){
var p = protections [i];
var rangeNotation = p.getRange()。getA1Notation();
var p2 = sheet2.getRange(rangeNotation).protect();
p2.setDescription(p.getDescription());
p2.setWarningOnly(p.isWarningOnly());
if(!p.isWarningOnly()){
p2.removeEditors(p2.getEditors()); //删除编辑器
p2.addEditors(p.getEditors()); //除原来的
// p2.setDomainEdit(p.canDomainEdit()); //只有在使用应用程序域名
}
}
}
时
I have built a template sheet that I will duplicate every week. I noticed that when I go to duplicate it, none the protected ranges are copied along with it. Is it possible to use a Google Apps script to copy the exact same protected ranges into the duplicated sheet? It would save me lots of time every week.
Note: A similar question was posted on Web Apps, so I am adapting a part of my answer there to the present case.
Using sheet.getProtections
method, you can get the array of protections on a given sheet, and loop over them, creating their analogs on the target sheet. This is somewhat annoying because there seems to be no method to simply clone a protection to another range. (One can change the range of protection, but that would move it to the new range, instead of copying.)
So, in the function below I do the following:
- Get the A1 notation of each protected range with
p.getRange().getA1Notation();
- Protect the corresponding range of the target sheet with
p2 = sheet2.getRange(rangeNotation).protect();
- Set the properties of new protection
p2
according to the properties of original protectionp
. This includes removing/adding editors if the protection is not just of the warning type.
function duplicateSheetWithProtections() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
sheet = ss.getSheetByName('Template');
sheet2 = sheet.copyTo(ss).setName('My Copy');
var protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
for (var i = 0; i < protections.length; i++) {
var p = protections[i];
var rangeNotation = p.getRange().getA1Notation();
var p2 = sheet2.getRange(rangeNotation).protect();
p2.setDescription(p.getDescription());
p2.setWarningOnly(p.isWarningOnly());
if (!p.isWarningOnly()) {
p2.removeEditors(p2.getEditors()); // remove editors
p2.addEditors(p.getEditors()); // except those permitted for original
// p2.setDomainEdit(p.canDomainEdit()); // only if using an Apps domain
}
}
}
这篇关于如何复制具有受保护范围的工作表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!