Simplifying a Protection script-CodePudding

I'm still learning the fundamentals of Javascript, but I know there's a way to simplify this one, I just can't figure it out. I've modified a script I found here: Protect spreadsheet then unprotect specific cells and ranges with script

I'm sharing a sheet with multiple users and want most of each sheet protected. The above linked script helped me ensure that the ranges my users need access to can be edited by all even as the sheets expand. However, the editable ranges are different on each sheet, so I've ended up recycling the unlockCertainRanges() portion to apply it to each sheet individually. This has made the script take up to 70 seconds to run. I'm pretty sure I can use an array and a for loop to run through each sheet, but I'm having trouble figuring it out.

This is what I have so far:

    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
  });
}

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.

Any guidance is greatly appreciated!

ETA details of the unlockPendingTORanges(); , unlockAccessoryRanges(); and unlockApparelRanges();

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
      });
    }

CodePudding user response：

I believe your goal is as follows.

You want to reduce the process cost of your script.

In this case, I would like to propose using Sheets API. I thought that when Sheets API is used for your script, the process cost will be reduced a little. When Sheets API is reflected in your script, it becomes as follows.

Modified script:

Before you use this script, 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);
}

Note:

From 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. of your question, unfortunately, I couldn't understand other sheet names and unprotected ranges. So in this modified script, one pattern is used. Please add your other patterns to the variable of obj.

Modified script:

Note:

References: