簡化保護腳本-有解無憂

我仍在學習 Javascript 的基礎知識，但我知道有一種方法可以簡化這個，我就是想不通。我修改了我在這里找到的腳本：保護電子表格，然后使用腳本取消保護特定單元格和范圍

我正在與多個用戶共享一張作業表，并希望每張作業表的大部分內容都受到保護。上面鏈接的腳本幫助我確保我的用戶需要訪問的范圍可以被所有人編輯，即使在作業表擴展時也是如此。但是，每張紙上的可編輯范圍都不同，因此我最終回收了該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()腳本相同，它們只是更改了定義的作業表名稱和范圍。

非常感謝任何指導！

的 ETA 詳細資訊unlockPendingTORanges();，unlockAccessoryRanges();以及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
      });
    }

uj5u.com熱心網友回復：

我相信你的目標如下。

您想降低腳本的流程成本。

在這種情況下，我想建議使用 Sheets API。我認為當您的腳本使用 Sheets API 時，流程成本會降低一點。當 Sheets API 反映在你的腳本中時，它變成如下。

修改后的腳本：

在使用此腳本之前，請在 Advanced Google Services 中啟用 Sheets API。

// 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。

參考：

方法：電子表格.get
方法：電子表格.batchUpdate
洗掉保護范圍請求
添加受保護范圍請求

轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/379791.html

標籤：javascript 谷歌应用程序脚本谷歌表格 google-sheets-api

上一篇：如何在Google表格中解碼特定字符（unicode）

下一篇：GoogleAppsScript：在將GoogleSheet轉換為PDFblob之前設定邊框