如何使用脚本忽略已受保护的工作表

Question

每天创建2-3张纸，但至少一张这些范围是保护["B3:U27"，"W3:AP27"，"B29:U33"，"W29:AP33"]

我把42个范围缩小到这4个范围，使它更快，但仍然在1分钟内，它可以保护大约8个文件。问题是，在几个月内，它可能会增长超过100个文件这将使我达到6分钟的超时限制，这将中断脚本。

这是我目前正在使用的脚本。我想知道是否可以通过某种方式对其进行修改，以忽略已经受到保护的图纸？

function main(){ //Main function to run
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var disregard = ["List", "Data", "Template"]; //ADD SHEET NAMES HERE THAT YOU WANT TO BE DISREGARDED
for(var x=0; x<sheets.length; x++){
if(disregard.some(data => sheets[x].getName().includes(data))){ 
//E.g. Disregard any sheet names added on the "disregard" array
}else{
unlockCertainRanges(sheets[x]);
}
}
}
function unlockCertainRanges(currentSheet){ //Function to unlock certain ranges on your spreadsheet
var sheet = currentSheet;
// Remove all range protections in the spreadsheet
var protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
for (var i = 0; i < protections.length; i++) {
var protection = protections[i];
protection.remove();
}
var protection = sheet.protect();
//restrict editors to owner
protection.getRange().getA1Notation();
var eds = protection.getEditors();
protection.removeEditors(eds);
//set unprotected ranges
var ranges = protection.getUnprotectedRanges();
var data = ["B3:U27", "W3:AP27", "B29:U33", "W29:AP33"]; // ADD YOUR RANGES HERE
data.forEach(res => { //LOOPS INTO EVERY ARRAY CONTAINING SPECIFIC RANGES
ranges.push(sheet.getRange(res));
protection.setUnprotectedRanges(ranges); //REMOVES THE PROTECTION ON THE RANGE
});
}

它会是已经受到保护的东西吗？或者上面有一把不能触摸的挂锁
我试图找到一种方法来检索已受保护的工作表的名称
我指的是类似getSheetName((的内容，但适用于受保护的内容。

或者，如果这个描述已经有这样的保护，可以把它放在例外中？

setDescription("已保护"(；

我在编码方面没有太多经验；我发现了一个非常相似的问题，但我不太了解代码

有人有主意吗？

Answer 1

我相信@MetaMan的简单意思是，您需要首先检查工作表是否包含受保护的范围。请参阅下面的代码。

代码：

function main() {
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
// Get list of sheets protected
var protections = SpreadsheetApp.getActiveSpreadsheet().getProtections(SpreadsheetApp.ProtectionType.SHEET);
var protectedSheets;
// If protections isn't set, initialize as empty array
if (protections)
protectedSheets = protections.map(protection => protection.getDescription());
else
protectedSheets = [];
var disregard = ["List", "Data", "Template"]; //ADD SHEET NAMES HERE THAT YOU WANT TO BE DISREGARDED
for (var x = 0; x < sheets.length; x++) {
if (disregard.some(data => sheets[x].getName().includes(data))) {
//E.g. Disregard any sheet names added on the "disregard" array
} else {
// If protectedSheets doesn't include the name, process the sheet
if (!protectedSheets.includes(sheets[x].getName()))
unlockCertainRanges(sheets[x]);
}
}
}
function unlockCertainRanges(currentSheet) {
Logger.log(""" + currentSheet.getName() + "" is being processed");
var sheet = currentSheet;
var protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
for (var i = 0; i < protections.length; i++) {
var protection = protections[i];
protection.remove();
}
// set names of sheets as description for future checks
var protection = sheet.protect().setDescription(currentSheet.getName());
//restrict editors to owner
protection.getRange().getA1Notation();
var eds = protection.getEditors();
protection.removeEditors(eds);
//set unprotected ranges
var ranges = protection.getUnprotectedRanges();
var data = ["B3:U27", "W3:AP27", "B29:U33", "W29:AP33"]; // ADD YOUR RANGES HERE
data.forEach(res => { //LOOPS INTO EVERY ARRAY CONTAINING SPECIFIC RANGES
ranges.push(sheet.getRange(res));
protection.setUnprotectedRanges(ranges); //REMOVES THE PROTECTION ON THE RANGE
});
}
// function to delete all existing protections
function deleteAllProtections() {
var protections = SpreadsheetApp.getActiveSpreadsheet().getProtections(SpreadsheetApp.ProtectionType.SHEET);
protections.forEach(protection => protection.remove());
}

注：

• 请注意，第一次运行需要运行deleteAllProtections()，因此所有工作表第一次都没有保护。成功的跑步现在将跳过那些有保护的工作表

参考：

• getProtections