Protect several rangers in the same line of code

[黃柏翔]

Hello,

I am trying to protect my sheet using appscript.

There are several ranges that I wanna portect. I recrorded the macro, but the result turn out to be seting ranges one at a time. Everytime, when I turn on the macro, it will cost a lot of time.

I'm currently using this code:

function LOCK() {
var spreadsheet = SpreadsheetApp.getActive();
var protection = spreadsheet.getRange('A:D').protect();
protection.setWarningOnly(true);
protection = spreadsheet.getRange('6:6').protect();
protection.setWarningOnly(true);
protection = spreadsheet.getRange('K5:U5').protect();
protection.setWarningOnly(true);
protection = spreadsheet.getRange('E5:E5').protect();
protection.setWarningOnly(true);
protection = spreadsheet.getRange('4:4').protect();
protection.setWarningOnly(true);
protection = spreadsheet.getRange('35:37').protect();
protection.setWarningOnly(true);
protection = spreadsheet.getRange('78:80').protect();
protection.setWarningOnly(true);
protection = spreadsheet.getRange('121:123').protect();
protection.setWarningOnly(true);
protection = spreadsheet.getRange('144:145').protect();
protection.setWarningOnly(true);
protection = spreadsheet.getRange('166:167').protect();
protection.setWarningOnly(true);
protection = spreadsheet.getRange('188:189').protect();
protection.setWarningOnly(true);
protection = spreadsheet.getRange('210:211').protect();
protection.setWarningOnly(true);
protection = spreadsheet.getRange('232:233').protect();
protection.setWarningOnly(true);
protection = spreadsheet.getRange('253:254').protect();
protection.setWarningOnly(true);
protection = spreadsheet.getRange('265:266').protect();
protection.setWarningOnly(true);
protection = spreadsheet.getRange('278:281').protect();
protection.setWarningOnly(true);
protection = spreadsheet.getRange('E7:X34').protect();
protection.setWarningOnly(true);
protection = spreadsheet.getRange('E38:K77').protect();
protection.setWarningOnly(true);
protection = spreadsheet.getRange('O38:U77').protect();
protection.setWarningOnly(true);
protection = spreadsheet.getRange('E81:L120').protect();
protection.setWarningOnly(true);
protection = spreadsheet.getRange('X81:X120').protect();
protection.setWarningOnly(true);
protection = spreadsheet.getRange('K124:K143').protect();
protection.setWarningOnly(true);
protection = spreadsheet.getRange('R124:R143').protect();
protection.setWarningOnly(true);
protection = spreadsheet.getRange('E146:E165').protect();
protection.setWarningOnly(true);
protection = spreadsheet.getRange('L146:O165').protect();
protection.setWarningOnly(true);
protection = spreadsheet.getRange('X146:X165').protect();
protection.setWarningOnly(true);
protection = spreadsheet.getRange('E168:H187').protect();
protection.setWarningOnly(true);
protection = spreadsheet.getRange('E190:H209').protect();
protection.setWarningOnly(true);
protection = spreadsheet.getRange('E234:O252').protect();
protection.setWarningOnly(true);
protection = spreadsheet.getRange('K1:X3').protect();
protection.setWarningOnly(true);
protection = spreadsheet.getRange('Y1:Y37').protect();
protection.setWarningOnly(true);
protection = spreadsheet.getRange('Y78:Y281').protect();
protection.setWarningOnly(true);
};

Is there any way that i can decrease the number of line? or make the process faster?

[Tanaike]

For example, in your showing script, when your script is run 2nd time, the same ranges are protected as the new protected ranges while the existing protected ranges are not updated. Namely, 2 the same protected ranges are created. In your actual goal, how will you do this?

[黃柏翔]

I made a worksheet as a template. the code I mention above is the  macro that I will use after duplicate from the template.

Tanaike 在 2022年10月11日 星期二下午3:14:22 [UTC+8] 的信中寫道：

[Tanaike]

Thank you for replying. If your script is run only one time, how about the following patterns?

Pattern 1

In this pattern, Spreadsheet service (SpreadsheetApp) is used.

function sample1() {
  const list = ["A:D", "6:6", "K5:U5", "E5:E5", "4:4", "35:37", "78:80", "121:123", "144:145", "166:167", "188:189", "210:211", "232:233", "253:254", "265:266", "278:281", "E7:X34", "E38:K77", "O38:U77", "E81:L120", "X81:X120", "K124:K143", "R124:R143", "E146:E165", "L146:O165", "X146:X165", "E168:H187", "E190:H209", "E234:O252", "K1:X3", "Y1:Y37", "Y78:Y281"];
  SpreadsheetApp.getActiveSheet().getRangeList(list).getRanges().forEach(r => r.protect().setWarningOnly(true));
}

Pattern 2

In this pattern, Sheets API is used. Before you use this script, please enable Sheets API at Advanced Google services.

function sample2() {
  const list = ["A:D", "6:6", "K5:U5", "E5:E5", "4:4", "35:37", "78:80", "121:123", "144:145", "166:167", "188:189", "210:211", "232:233", "253:254", "265:266", "278:281", "E7:X34", "E38:K77", "O38:U77", "E81:L120", "X81:X120", "K124:K143", "R124:R143", "E146:E165", "L146:O165", "X146:X165", "E168:H187", "E190:H209", "E234:O252", "K1:X3", "Y1:Y37", "Y78:Y281"];
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const ssId = ss.getId();
  const sheetId = sheet.getSheetId();
  const ranges = sheet.getRangeList(list).getRanges();
  const requests = ranges.map((range, i) => {
    var gridRange = {
      sheetId,
      startRowIndex: range.getRow() - 1,
      endRowIndex: range.getRow() - 1 + range.getNumRows(),
      startColumnIndex: range.getColumn() - 1,
      endColumnIndex: range.getColumn() - 1 + range.getNumColumns(),
    };
    var data = list[i].match(/(.+):(.+$)/);
    if (!data[1].match(/[0-9]/)) delete gridRange.startRowIndex;
    if (!data[2].match(/[0-9]/)) delete gridRange.endRowIndex;
    return { addProtectedRange: { protectedRange: { range: gridRange, warningOnly: true } } };
  });
  Sheets.Spreadsheets.batchUpdate({ requests }, ssId);
}