1

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');
  } 
}
aynber
  • 22,380
  • 8
  • 50
  • 63
user1892770
  • 323
  • 2
  • 3
  • 15
  • Per documentation, it's an array of `Range` objects. So use normal range methods. – tehhowch Mar 29 '19 at 15:13
  • Possible duplicate of [Set border color & style in spreadsheet programmatically](https://stackoverflow.com/questions/12900359/set-border-color-style-in-spreadsheet-programmatically) – tehhowch Mar 29 '19 at 15:14

2 Answers2

3

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();
}
Community
  • 1
  • 1
TheMaster
  • 45,448
  • 6
  • 62
  • 85
2

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

tehhowch
  • 9,645
  • 4
  • 24
  • 42
  • Maybe something is wrong with the way I setup the sheet. I tried creating a new sheet and I added various ranges but I always get the error below. Ranges must have at least one range. (line 6, file "Code") `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(); }); var rgl = sheet.getRangeList(upr); rgl.setBorder(true, null, null, null, null, null, "red", SpreadsheetApp.BorderStyle.SOLID_MEDIUM); }); }` – user1892770 Mar 30 '19 at 12:09
  • 1
    @user Yes, there is the implicit assumption in my answer that every sheet will have at least one unprotected range when the sheet is protected. As the developer it is your duty to understand and then adapt this code to your specific scenario, including adding error checking. if you can't resolve an error then you should ask a new, well-researched question. As a second hint, consider how you might first check that there are any unprotected ranges before creating the `RangeList` – tehhowch Mar 30 '19 at 12:18
  • In the drop-down menus, under Data, there is a menu item "Protected Ranges and Sheets", which gives a list of the "protected" ranges. There is no menu item to display "unprotected" ranges. My sheet has some ranges which are protected and some ranges which do not have any protection. As the developer I understand this. I still get the error. – user1892770 Mar 30 '19 at 12:38