简化保护脚本
我仍在学习Java脚本的基础知识，但我知道有一种方法可以简化这个方法，我只是想不出来。我已经修改了我在这里找到的脚本：Protect spreadsheet then unprotect specific cells and ranges with script
我正在与多个用户共享工作表，并希望保护每个工作表的大部分内容。上面链接的脚本帮助我确保我的用户需要访问的范围可以由所有人编辑，即使在工作表展开时也是如此。但是，每个工作表上的可编辑范围是不同的，所以我最终回收了unlockCertainRanges()部分，将其分别应用于每个工作表。这使得该脚本最多需要70秒才能运行。我非常确定我可以使用一个数组和一个for循环来遍历每个工作表，但我很难弄清楚它。
这是我到目前为止所拥有的：
function mainProtection(){ //Main function to run
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var disregard = ["NetSuite INV", "Sales", "Delivery Schedule", "TO", "APP Arch", "ACC Arch", "INV REF"]; //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{
unlockOrderingRanges(sheets[x]);
unlockPendingTORanges(sheets[x]);
unlockAccessoryRanges(sheets[x]);
unlockApparelRanges(sheets[x]);
}
}
}
function unlockOrderingRanges(){ //Function to unlock ranges on Ordering spreadshseet
var sheet = SpreadsheetApp.getActive().getSheetByName("Ordering");
// 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 = ["O5:P", "C2:E2"]; // 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
});
}
为了简化这篇文章，我省略了unlockPendingTORanges();、unlockAccessoryRanges();和unlockApparelRanges();脚本，因为它们与unlockOrderingRanges()脚本相同，它们只是更改了定义的工作表名称和范围。
非常感谢您的指导！
unlockPendingTORanges();、unlockAccessoryRanges();和unlockApparelRanges();的ETA详细信息
function unlockPendingTORanges(){ //Function to unlock ranges on Pending TOs spreadshseet
var sheet = SpreadsheetApp.getActive().getSheetByName("Pending TOs");
// 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 = ["E6:H"]; // 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 unlockAccessoryRanges(){ //Function to unlock ranges on Accessory INV spreadshseet
var sheet = SpreadsheetApp.getActive().getSheetByName("Accessory INV");
// 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 = ["E5:H"]; // 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 unlockApparelRanges(){ //Function to unlock ranges on Apparel INV spreadshseet
var sheet = SpreadsheetApp.getActive().getSheetByName("Apparel INV");
// 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 = ["E5:F"]; // 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
});
}
解决方案
我相信您的目标如下。
- 您希望降低脚本的处理成本。
在这种情况下，我建议使用Sheets API。我想，当您的脚本使用Sheets API时，处理成本会降低一些。当Sheets API反映在您的脚本中时，它如下所示。
修改后的脚本：
在使用此脚本之前，please enable Sheets API at Advanced Google Services。
// This script is from https://tanaikech.github.io/2017/07/31/converting-a1notation-to-gridrange-for-google-sheets-api/
function a1notation2gridrange1(a1notation) {
var data = a1notation.match(/(^.+)!(.+):(.+$)/);
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(data[1]);
var range = ss.getRange(data[2] + ":" + data[3]);
var gridRange = {
sheetId: ss.getSheetId(),
startRowIndex: range.getRow() - 1,
endRowIndex: range.getRow() - 1 + range.getNumRows(),
startColumnIndex: range.getColumn() - 1,
endColumnIndex: range.getColumn() - 1 + range.getNumColumns(),
};
if (!data[2].match(/[0-9]/)) delete gridRange.startRowIndex;
if (!data[3].match(/[0-9]/)) delete gridRange.endRowIndex;
return gridRange;
}
// Please run this function.
function myFunction() {
// Please set your sheet names and unprotected ranges you want to use.
const obj = [
{ sheetName: "Ordering", unprotectedRanges: ["O5:P", "C2:E2"] },
{ sheetName: "Accessory INV", unprotectedRanges: ["E5:H"] },
{ sheetName: "Apparel INV", unprotectedRanges: ["E5:F"] },
{sheetName: "Pending TOs", unprotectedRanges: ["E6:H"] },
{sheetName: "INV REF", unprotectedRanges: ["C6:C"] },
];
// 1. Retrieve sheet IDs and protected range IDs.
const spreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId();
const sheets = Sheets.Spreadsheets.get(spreadsheetId, { ranges: obj.map(({ sheetName }) => sheetName), fields: "sheets(protectedRanges(protectedRangeId),properties(sheetId))" }).sheets;
const { protectedRangeIds, sheetIds } = sheets.reduce((o, { protectedRanges, properties: { sheetId } }) => {
if (protectedRanges && protectedRanges.length > 0) o.protectedRangeIds.push(protectedRanges.map(({ protectedRangeId }) => protectedRangeId));
o.sheetIds.push(sheetId);
return o;
}, { protectedRangeIds: [], sheetIds: [] });
// 2. Convert A1Notation to Gridrange.
const gridranges = obj.map(({ sheetName, unprotectedRanges }, i) => unprotectedRanges.map(f => a1notation2gridrange1(`${sheetName}!${f}`)));
// 3. Create request body.
const deleteProptectedRanges = protectedRangeIds.flatMap(e => e.map(id => ({ deleteProtectedRange: { protectedRangeId: id } })));
const protects = sheetIds.map((sheetId, i) => ({ addProtectedRange: { protectedRange: { range: { sheetId }, unprotectedRanges: gridranges[i] } } }));
// 4. Request to Sheets API with the created request body.
Sheets.Spreadsheets.batchUpdate({ requests: [...deleteProptectedRanges, ...protects] }, spreadsheetId);
}
注意：
- 在您问题的
I omitted the unlockPendingTORanges(); , unlockAccessoryRanges(); and unlockApparelRanges(); scripts for the sake of simplifying this post, as they are identical to the unlockOrderingRanges() script, they just change the defined sheet name and ranges.中，很遗憾，我无法理解其他工作表名称和不受保护的区域。因此，在这个修改后的脚本中，使用了一种模式。请将您的其他图案添加到obj的变量中。
引用：
- Method: spreadsheets.get
- Method: spreadsheets.batchUpdate
- DeleteProtectedRangeRequest
- AddProtectedRangeRequest
相关文章