How to format UnprotectedRanges?

Question

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');
  } 
}

Answer 1

Issue:

There are two types of protections¹:

• 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()². 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();
}