CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsspreadsheet-protection
user1892770
user1892770

Reputation: 333

How to format UnprotectedRanges?

I would like to put a red border around each cell in a spreadsheet which does not have any protection assigned to the cell or range of cells. I know how to set a border but how do I access the formatting for the unprotectedranges?

function wtf() {
var ui = SpreadsheetApp.getUi();
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var protection = sheet.protect();
var unprotected = protection.getUnprotectedRanges();  
  for (var i = 0; i < unprotected.length; i++) {
  ui.alert('this cell is unprotected');
  } 
}

Upvotes: 1

Views: 2638

Answers (2)

TheMaster
TheMaster

Reputation: 50443

Issue:

There are two types of protections1:

  • RANGE - Protect certain ranges only.
  • SHEET - Protect entire sheets with exceptions.

If you use sheet protection, you can also exclude(except) certain ranges from the sheet protection(so called the unprotected ranges) using the UI. These unprotected ranges can then be retrieved using protection.getUnprotectedRanges()2. Sheet protection is better for what you want to do and this answer sufficiently explains it. However, the ranges that are unprotected in a sheet, where some ranges are protected using "range protection" cannot be retrieved easily.

Possible Solution:

  • Color the whole sheet and clearFormat/restore format over protected ranges only.

Snippet:

function colorUnprotectedRangesRed() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheets()[0]; //first sheet
  const prots = sh.getProtections(SpreadsheetApp.ProtectionType.RANGE); //get onlyRangeProtections
  const rngList = prots.map(function(pro) {
    return pro.getRange().getA1Notation();
  });
  sh.getRange('1:' + sh.getMaxRows()).setBorder(
    true,
    true,
    true,
    true,
    true,
    true,
    'red',
    SpreadsheetApp.BorderStyle.SOLID
  );
  //SpreadsheetApp.flush(); //flush the changes first before clearing format, if you have issues
  sh.getRangeList(rngList).clearFormat();
}

Upvotes: 3

tehhowch
tehhowch

Reputation: 9872

Since you state you want to treat all unprotected Ranges in the same manner, I recommend you construct a RangeList from the possibly-disjoint Ranges and then make a single call to Range#setBorder:

function mark_non_protected() {
  const wb = SpreadsheetApp.getActive();
  wb.getSheets().forEach(function (sheet) {
    var pr = sheet.protect();
    var upr = pr.getUnprotectedRanges().map(function (rg) { return rg.getA1Notation(); });
    // Since some sheets may not have any unprotected ranges, don't try to create an empty RangeList:
    if (!upr.length)
      return;

    var rgl = sheet.getRangeList(upr);
    rgl.setBorder(
      true, // top
      true, // left
      true, // bottom
      true, // right
      true, // internal vertical
      true, // internal horizontal
      "red", // color name or CSS hex
      /* optional borderstyle like SpreadsheetApp.BorderStyle.DOTTED */
    );
    /** do other stuff with the unprotected ranges as a whole unit */
  });
}

The alternative is to call setBorder and other methods directly for each range:

...
pr.getUnprotectedRanges().forEach(function (rg) {
  rg.setBorder(...);
  rg.someOtherMethod(...);
  ...
});
...

This approach may be useful if you need to format each range differently.

Refs

Upvotes: 2

Related Questions