CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-formulaspreadsheet
Shubham Lohia
Shubham Lohia

Reputation: 5

App-Scripts- How to unprotect series of repeated ranges at once?

I want to protect my whole sheet but just want some ranges unprotected, the ranges are in repeated progression series. So I don't want to create a new range and add it to the unprotected. Is there any shorter method. Just give you guys a broad picture I have dynamic sheet with 40 rows and "n" number of columns. but I want starting from Col3 C5:F35 unprotected then skip 4 columns completely then again K5:N35 unprotected and so on till lastCol. I have huge number of columns so doing this manually is next to impossible. Help on this matter would be highly appreciated. I have attached a picture which might help clearing up some confusions.

spreadsheet screenshot attached

Upvotes: 0

Views: 47

Answers (1)

memerson
memerson

Reputation: 36

I suggest using a Google Script and, instead of it protecting ranges, have it protect ranges like the following:

function protectColumns() {
  let ss = SpreadsheetApp.getActive();
  let spreadsheet = ss.getSheets()[0];

  let lastColumn = spreadsheet.getLastColumn();
  let lastRow = spreadsheet.getLastColumn();

  let range = spreadsheet.getRange(1, 1, lastRow, 2); // Sets the range from Row 1 Column1 to Last Row Column 2
  let protection = range.protect().setDescription('Sample protected range');

  for(let i=7; i<lastColumn; i+=8) { //Starting at column 7, Protect 4 rows, skip 4 rows, repeat
    range = spreadsheet.getRange(1, i, lastRow, 4);
    range.protect().setDescription('Sample protected range');
  }
}

This should do what I think you want. It protects the first 2 rows, skips the next 4, protects the next 4, and repeats. I would only caution it may miss protecting the last section of columns. Let me know if there are any issues.

Upvotes: 1

Related Questions